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.