Counting adjacent polygons in PostGIS

Which country has the most neighbours? It´s probably not Australia (surrounded by ocean) or the US (huge country, but few neighbours). Counting polygons manually stops being fun after you reach around 5, so how is it done smarter? There are quite a few tools to choose from, but since I have a soft spot for spatial SQL, I decided to ask PostGIS, using this query:


SELECT
    countries.name AS country,
    COUNT(bordering_countries.name) AS bordering_countries,
	countries.geom
FROM
    countries
LEFT JOIN
    countries AS bordering_countries ON ST_Intersects(countries.geom, bordering_countries.geom) AND countries.name != bordering_countries.name
GROUP BY
    countries.name, countries.geom
ORDER BY
    bordering_countries DESC;

It counts the number of neighbours for each country and lists them in descending order:

The exact numbers depend on how the countries are defined in your data, disputed areas being one cause, so the exact answer may vary. Since the query answer includes country name, number of neighbours and the geometry, it´s easy to visualize as with any other vector data:

1 Comment on “Counting adjacent polygons in PostGIS

Leave a Reply

Your email address will not be published. Required fields are marked *

Please reload

Please Wait