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.