2

enter image description here

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.

3

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;
| improve this answer | |
  • "Note that st_maxDistance is usefull to know the distance between a point and the furthest part of a polygon", exactly what I'm trying to do. I thought converting the polygon to points would speed up the process. Or am I wrong? Should I skip that step. – Theo F Oct 17 '19 at 8:28
  • Thanks, your first query returns as expected. Each of the points has the max distance attached. The second query however returns 0 rows! – Theo F Oct 17 '19 at 12:54
  • the 2nd query is correct (I just validated it) – JGH Oct 17 '19 at 13:44
  • ok I'll have another look. However, your first solution I've accepted as the answer to the OP. As it directly answers the proposed question. However I'll be using Thingumabob's answer as I don't need to create a vertex point table beforehand, instead it directly queries the polygon itself. – Theo F Oct 17 '19 at 13:52
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).

| improve this answer | |
  • my CRS is 27700 (British National Grid) in metres. – Theo F Oct 17 '19 at 8:42
  • @TheoF well then, nothing to worry about ;-) – geozelot Oct 17 '19 at 8:43
  • The polygon table does have a matching id yes. I see you use a JOIN poly ON pointid = polyid, whereas I use a WHERE pointid = polyid .Is there a performance benefit to using JOIN ON? – Theo F Oct 17 '19 at 8:48
  • 1
    @TheoF in this particular case, no; both your implicit cross join (FROM <table1>, <table2>) plus WHERE and my (INNER) JOIN ON will result in the exact same query plan. the JOIN syntax can have significant differences, though, and the table listing like you did can have significant drawbacks! I'd recommend to use the JOIN syntax, even if only for readability. – geozelot Oct 17 '19 at 8:55
0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.