PostGIS Voronoi Polygons with extend_to parameter

Question

I am creating voronoi polygons for locations inside a country, using PostGIS. The function creates these areas for ~80,000 locations in less than a second.

However, I can only get the polygons to extend to the default envelope area, which is 50% of the bounding box of points.

WITH voronoi (vor) AS 
 (SELECT ST_Dump(ST_VoronoiPolygons(ST_Collect(geom))) FROM public.branches_atms_0601)
SELECT (vor).path, (vor).geom FROM voronoi;

This generates the polygon(s) below:

What I would like it to do is to clip this to the footprint of UK. This can apparently be defined within the function https://postgis.net/docs/ST_VoronoiPolygons.html However when I try to run it with my UK footprint polygon, I get the error with the code...

WITH voronoi (vor) AS 
 (SELECT ST_Dump(ST_VoronoiPolygons(ST_Collect(geom), public."Dissolved")) FROM 
public.branches_atms_0601)
SELECT (vor).path, (vor).geom FROM voronoi;

ERROR:  missing FROM-clause entry for table "public"
LINE 2: ...LECT ST_Dump(ST_VoronoiPolygons(ST_Collect(geom), public."Di...
                                                         ^
SQL state: 42P01
Character: 82

How can I fix this? I assume the issue is coming from assigning the extends_to parameter incorrectly, but I don't know how to approach it...

Answer 1

Generally, in SQL you specify the (schema qualified) relation (table) identifier (i.e. public.branches_atms_0601 as part of the FROM expression, and optionally add an ALIAS (e.g. AS br_at), after which you can use that identifier (or ALIAS exclusively, when added) throughout the query.

However, it is required that all relations used in the query are somehow referenced in a FROM clause, either directly (on multiple occasions) or via a JOIN expression!

When stating public."Dissolved" PostgreSQL looks for an already referenced relation (where it also expects a column only)!

---

Assuming that you actually want to refer to and use a column geom in relation public."Dissolved", and given that public."Dissolved" only has a single row, you could e.g. sub-select the wanted public."Dissolved".geom value in the function directly:

SELECT ST_Dump(
         ST_VoronoiPolygons(
           ST_Collect(br.geom),
           0.0,
           (SELECT geom FROM public."Dissolved" [WHERE <it_returns_only_a_single_row>])
         )
       )
FROM   public.branches_atms_0601 AS br
;

It is likely that the UK footprint consists of multiple geometries (or a Multi geometry); make it so that the sub-query returns a single (Multi) geometry then.

Note that you will have to either

• add the tolerance parameter to the function call
• use the named notation extend_to => (<sub-select>) in the function call

to fulfill the function signature.

---

However:

The function will use the envelope of the extend_to geometry, i.e. the bounding box, and only if it is larger than the default geometry!

If the exact outlines of the UK are desired, you'd need to get the intersection between the function output and the footprint explicitly:

SELECT ST_Intersection(dmp.geom, ds.geom) AS geom
FROM   (
    SELECT ST_Collect(geom) AS geom
    FROM   public.branches_atms_0601
) t,   LATERAL ST_Dump(ST_VoronoiPolygons(t.geom)) AS dmp
JOIN   public."Dissolved" AS ds
  ON   ST_Intersects(dmp.geom, ds.geom)
;