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

geozelot
  • 30,050
  • 4
  • 32
  • 56
wtgeographer
  • 900
  • 1
  • 13
  • 29
  • 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

1 Answers1

5

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.

geozelot
  • 30,050
  • 4
  • 32
  • 56