2

I have 61 pts and numerous rds. I would like to find the shortest distance to the nearest road using st_shortestline. I need 61 unique line segments, one for each point to its nearest road. Instead I am getting 61 line segments from each point to a particular road (not the nearest). How can I adjust my query to produce the proper results?

SELECT DISTINCT ON (p.id) p.id, p._unique, r.rd_name, ST_ShortestLine(p.geom,r.geom) into shortest_line FROM pois_sample p, allrds_sample r;

Outcome:

enter image description here

Desired Outcome:

enter image description here

4
  • you are looking for a (K) Nearest Neighbor search, see e.g. here for a comprehensive SELECT example, here for a generic UPDATE syntax, and here for more on both. - all my own answers, there's plenty more on (K)NN searches on GIS.SE – geozelot Aug 14 '19 at 21:18
  • This does not utilize st_shortest line. Is this not the exact use case for st_shortestline? – wtgeographer Aug 14 '19 at 21:33
  • yes and no; ST_ShortesLine does give you what it says, the issue is the cross join of your tables; for each point, this will create the shorest line from it to every road! if you want the shortest line to the one closest road for each point, a LATERAL JOIN is needed. to speed things up, PostGIS offers the KNN operator <-> (see links). if you then also only want the (numerical) distance, use e.g. ST_Distance (or replace it with ST_ShortestLine otherwise). if you have trouble translating it to your usecase, I write up an answer. this is, however, a close duplicate to the links. – geozelot Aug 14 '19 at 21:46
  • Thanks @thingumabob. I can see the similarities. What I need in the end is the line geometry (as returned with st_shortestline) and not point geometry returned. Can you provide an example which does this? These examples look complex and I'm having trouble following. – wtgeographer Aug 15 '19 at 14:41
4

Try this:

CREATE TABLE shortest_line AS
  SELECT p.id,
         p._unique,
         r.rd_name
         ST_ShortestLine(p.geom, r.geom)::GEOMETRY(LINESTRING, <your_epsg>) AS geom
  FROM  pois_sample AS p
  CROSS JOIN LATERAL(
    SELECT rd_name,
           geom
    FROM   allrds_sample
    ORDER BY
           geom <-> p.geom
    LIMIT  1
  ) AS r
;

Make sure you have spatial indexes in place, at least on the allrds_sample table, for this to be highly performant.

The explicit cast to GEOMETRY(LINESTRING, <your_epsg>) will ensure the shortest_line.geom column will be correctly registered in PostGIS' geometry_columstable.


The core concept is the CROSS JOIN LATERAL:

  • pois_sample will be traversed sequentially and each row passed (made available) to the right hand query of the join as reference
  • in that sub-query, the geometries from allrds_sample will get ordered by distance to the geometry passed in from the outer row, using the special (K)NN distance operator <->
  • the default ascending order plus the LIMIT 1 makes sure we get only the closest geometry to the reference geometry
  • the JOIN itself then makes all returned rows in allrds_sample available to the left hand (outer) SELECT

In other words:

  • for each geometry in pois_sample, scan allrds_sample for the nearest and return it

where the time consuming operation that one might expect here is highly optimized by the <-> operator in the ORDER BY, in conjunction with a spatial index.

1
  • Thank you @thingumabob. This did exactly what I needed it to. – wtgeographer Aug 16 '19 at 15:41

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.