2

I have two tables. An exploded points parcels layer

create table a as
select id, (ST_Dumppoints(geom)).geom as geom FROM tableblah;

and another parcels table call it b which is a polygon

I wrote a a query that calculates the shortest distance from the exploded parcels/as points to layer b

select id,min(st_distance(a.geom,b.geom)) as min_distance
    from a join b on a.id = b.id group by a.id

Now I want to be able to create that minimum line from the points to the parcel. I am unsure how to format this part. I know I probably need st_makeline.

should I convert the layer b parcels to points as well?

UPDATE

I converted layer b too a points also

create table b_points as
select b.id,(ST_Dumppoints(geom)).geom as geom from b;

then i ran

select distinct on(a.id) a.id, st_makeline(a.geom,b_points.geom) as geom
from a join b_points on a.id = b_points.id order by 
a.id,st_distance(a.geom,b.geom)

this gives me this which is somewhat correct, but the parcel I converted to points only produced 4 points...

enter image description here

ANSWERED:

ended up using st_shortestline too replace my use of st_makeline. I converted both my polygon parcels table to lines and ran it.as you can see below the results from using st_shortestline is more accurate than st_makeline in regards to finding the shortest path from the parcels boundaries

enter image description here

  • would ST_ClosestPoint work? – DPSSpatial Apr 6 '17 at 21:00
  • in place of st_makeline? – ziggy Apr 6 '17 at 21:01
  • wouldnt it need to be aggregated? that seems to be my problem and I cant figure out the logic for a subquery – ziggy Apr 6 '17 at 21:01
2

ST_Shortestline is for that purpose http://postgis.net/docs/ST_ShortestLine.html

ST_Closestpoint is actually a wrapper around ST_Shortestline

  • 1
    Worth noting that this differs from what OP has done so far in that actually finds the closest point on the edge of the geometry, not just the closest vertex. (IMO this is better, but I guess it depends on what exactly is being modelled.) – Richard Law Apr 7 '17 at 5:54
  • I thought that was the problem of OP to find points between the vertex points. – Nicklas Avén Apr 7 '17 at 6:01
  • Oh I see from the angels of the lines that they will more or less connect to the vertex points anyway. – Nicklas Avén Apr 7 '17 at 6:36
  • 1
    But the idea is quite simple. Using ST_Distance you get a distance. Between what points is that distance calculated? That is what you get from ST_Shortestline.The length of the shortest line is exacly the same as the answer from ST_Distance – Nicklas Avén Apr 7 '17 at 16:52
  • 1
    So, ST_Length(ST_ShortestLine(a,b) = ST_Distance(a,b) – Nicklas Avén Apr 7 '17 at 16:53

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.