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

The dataset provides geographic coordinates for inventor and applicant locations in 18.8 million patent documents spanning over more than 30 years. The geocoded data are further allocated to the corresponding countries, regions and cities. When the address information was missing in the original patent document, we imputed it by using information from subsequent filings in the patent family. The resulting database can be used to study patenting activity at a fine-grained geographic level without creating bias towards the traditional, established patent offices.

Millions of patent points forming beautiful patterns around Germany
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;
Innovation grid on European scale looks oddly satisfying

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store