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?


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 

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

enter image description here


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? Apr 6, 2017 at 21:00
  • in place of st_makeline?
    – ziggy
    Apr 6, 2017 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, 2017 at 21:01

1 Answer 1


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.) Apr 7, 2017 at 5:54
  • I thought that was the problem of OP to find points between the vertex points. Apr 7, 2017 at 6:01
  • Oh I see from the angels of the lines that they will more or less connect to the vertex points anyway. Apr 7, 2017 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 Apr 7, 2017 at 16:52
  • 1
    So, ST_Length(ST_ShortestLine(a,b) = ST_Distance(a,b) Apr 7, 2017 at 16:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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