2

I am trying to replicate this study using the counties (comarques) in my country. https://tjukanov.org/closest-to-me Thus, I'm trying to find which is the closest neighbouring county to any point in the country. I have managed to make the voronoi polygons for all vertices of countries neighbouring another one with the following code. Thus, I'll be able to loop through a list of county names on Python using sqlalchemy.

WITH altcamp AS (SELECT nomcomar, ST_Transform(wkb_geometry, 4326) as geom
                FROM comarques
                WHERE nomcomar = 'Alt Camp'),
noaltcamp AS (SELECT nomcomar, ST_Transform(wkb_geometry, 4326) as geom
                    FROM comarques
                    WHERE nomcomar NOT IN ('Alt Camp')),
voronoi as (SELECT (ST_DUMP(ST_VoronoiPolygons(ST_COLLECT(noaltcamp.geom)))).geom as voronoipoly
FROM noaltcamp)
SELECT v.voronoipoly
from voronoi v, altcamp a
WHERE ST_Intersects(v.voronoipoly, a.geom);

comarques is the table with the counties and nomcomar is the column where the name of each county is stored.

What I'm trying to do is get the name of the "original" county polygon so that I can dissolve them later. I've tried the following but it did not work, returning the following error

SELECT v.voronoipoly, n.nomcomar
from voronoi v, altcamp a
JOIN noaltcamp n
ON ST_INTERSECTS(n.geom, v.voronoipoly
WHERE ST_Intersects(v.voronoipoly, a.geom);
ERROR:  invalid reference to FROM-clause entry for table "v"
LINE 12: ON ST_INTERSECTS(n.geom, v.voronoipoly)
                                  ^
HINT:  There is an entry for table "v", but it cannot be referenced from this part of the query.
2

The query is doing an implicit cross join between v and a, then you join a to n with a ON condition between n and v, which is invalid (the implicit cross join with v will occur after the explicit joins between a and n)

If you do want to keep the implicit cross join, you would have to swap v and a:

SELECT v.voronoipoly, n.nomcomar
from altcamp a, voronoi v
JOIN noaltcamp n
ON ST_INTERSECTS(n.geom, v.voronoipoly)
WHERE ST_Intersects(v.voronoipoly, a.geom);

but the cleaner way would be to replace the implicit join with a real join

SELECT v.voronoipoly, n.nomcomar
from voronoi v
JOIN altcamp a 
  ON ST_Intersects(v.voronoipoly, a.geom)
JOIN noaltcamp n
  ON ST_INTERSECTS(n.geom, v.voronoipoly);
1
  • 1
    Worked beautifully. Thank you so much! – marcbosch Apr 30 at 13:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.