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.