PostgreSQL - attach to point table the maxium distance value to edge of containing polygon

Question

I'm trying to find the furthest distance from a point to the edge of the polygon it sits in. The polygons in this case have been converted to vertex points.

I have two POINT tables in Postgres (and a POLYGON table with 3 shapes).

One POINT table (vertextable) contains vertex points of the 3 polygons (about 18 points in total). I created this vertex point table using ST_DumpPoints.

The other POINT table (insidepointtable) contains 3 points, each one lies somewhere within each of the original polygons. Each point shares a unique ID number with the polygon vertex points they fall inside (see pic).

I want to create a new table containing the 3 points from insidepointtable and the maximum distance value of the point from vertextable.

I'm having trouble returning just the 3 distinct rows. When I use:

CREATE TABLE public.vertexmaxdistances AS
SELECT a.id, ST_MaxDistance(a.geom, b.geom)
FROM public.insidepointtable a, public.vertextable b
WHERE a.id = b.id
ORDER BY id DESC, st_maxdistance DESC;

I get every distance to every point returned. How can I get:

id   |  st_maxdistance
______________________
1    |             5.5
2    |             4.2
3    |             4.9

(for example)

Furthermore, I'd actually like to do this distance attachment for millions of sites. So ideally the solution needs to be scaleable with decent performance.

Answer 1

You would need to group by ID. Then you need to compute the distance and keep the biggest one.

Note that st_maxDistance is usefull to know the distance between a point and the furthest part of a polygon

CREATE TABLE public.vertexmaxdistances AS
SELECT a.id, max(ST_Distance(a.geom, b.geom)) as dist
FROM public.insidepointtable a, public.vertextable b
WHERE a.id = b.id
GROUP BY a.id
ORDER BY id DESC;

For efficiency, you may have to use a lateral join

CREATE TABLE public.vertexmaxdistances AS
select a.id, far_pt.dist
from public.insidepointtable a
CROSS JOIN LATERAL
  (SELECT
     st_distance(a.geom,b.geom) as dist
     FROM public.vertextable b
     WHERE a.id = b.id
     ORDER BY a.geom <-> b.geom DESC
   LIMIT 1) AS far_pt
ORDER BY a.id DESC;

Answer 2

To fill in with the mentioned ST_MaxDistance, assuming your <polygontable> has an <id> that matches insidepointtable.id:

CREATE TABLE public.vertexmaxdistance AS
  SELECT a.id,
         ST_MaxDistance(a.geom, b.geom) AS dist
  FROM   public.insidepointtable AS a
  JOIN   <schema>.<polygontable> AS b
    ON   a.id = b.<id>
;

Less complex, no need to extract vertices, but with the caveat that ST_MaxDistance is bound to the GEOMETRY type, and thus returns distance in CRS units (i.e. degrees for e.g. EPSG:4326, which are meaningless as a measure).

Answer 3

Testing on 1000 polygons with a point inside each (using LIMIT 1000), ThingumaBob's answer takes 22 seconds:

CREATE TABLE public.vertexmaxdistance AS
  SELECT a.id, a.geom, ST_MaxDistance(a.geom, b.geom) AS maxdist
  FROM   public.insidepointtable AS a
  JOIN   public.polygon AS b
   ON   a.id = b.id
LIMIT 1000

Again testing on 1000 polygons (using LIMIT 1000), my revised solution takes 22 seconds:

CREATE TABLE public.vertexmaxdistance AS
SELECT a.id, a.geom, ST_MaxDistance(a.geom, b.geom) AS maxdist
FROM public.insidepointtable a, public.polygon b
WHERE a.id = b.id
LIMIT 1000

So there is as suggested, no performance distance using JOIN ON id = id vs. WHERE id = id

(Interestingly, changing the LIMIT to 100 or even 10 produces the same query time...!)

This is not techincally the correct answer to my original question as I specifically asked about distances between point and polygon vertex points. Not point and the polygon itself.