Finding the most “innovative” square kilometer in Europe with spatial SQL

Topi Tjukanov
Aug 21 · 11 min read
Millions of patent points forming beautiful patterns around Germany

The technical nitty gritty

Population grid covers the whole continent. There are some gaps in places with no population and of course those grids were left out from the final analysis, but luckily most of the continent is populated and thus very few places had patent points but no population.
CREATE TABLE patent_share
AS
SELECT appln_id, 1.0/COUNT(*) fract
FROM geoc_inv
GROUP BY appln_id;
CREATE TABLE
innovation_analysis
AS
SELECT id,geom,
count(geom) OVER (PARTITION BY geom) as similar_points,
ps.appln_id, fract, patent_office, filing_date, lat, lng, ctry_code, name_0, name_1, name_2, name_3, name_4, name_5, city, coord_ source, source, type
FROM geoc_inv gi
LEFT JOIN patent_share ps
ON ps.appln_id = gi.appln_id
WHERE filing_date::date > ‘2000–01–01’;
CREATE TABLE innovation_grid
AS
SELECT a.grd_id, tot_p, count(*) AS patent_count, sum(fract) AS patent_fractions, sum(fract)::real / tot_p::real AS patent_per_capita, max(similar_points) as highest_single, ctry_code, a.geom
FROM eu_pop_grid2011 a
JOIN innovation_analysis b ON
ST_INTERSECTS(a.geom, ST_TRANSFORM(b.geom, 3035))
GROUP BY a.grd_id, tot_p, a.geom, ctry_code;
SELECT DISTINCT ON (ctry_code)
ctry_code, patent_per_capita, patent_count, similar_max, geom
FROM innovation_grid
WHERE patent_count > 10 AND tot_p::int > 10 AND patent_count != highest_single
ORDER BY ctry_code, patent_per_capita DESC;

Results are in! Where are the innovation hotspots in different countries?

Disclaimers and conclusions

Innovation grid on European scale looks oddly satisfying

Topi Tjukanov

Written by

I mostly put lines, points and polygons on maps 🌍

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade