Say I have table gps like:

   gps_id serial primary key,
   measured_timestamp timestamp, 
   geom Geometry(Point,4326),
   stop_id int --Null

And I have a table of stops like:

   stop_id serial primary key,
   geom Geometry(Point,4326)

If I want to do an UPDATE on gps to find the nearest stop to each point, is there a way to use a LATERAL query?

I tried something like

SET stop_id = nearest.stop_id
FROM LATERAL(SELECT stop_id FROM stops ORDER BY stops.geom <-> gps.geom LIMIT 1) nearest

but that told me

ERROR:  invalid reference to FROM-clause entry for table "gps"
HINT:  There is an entry for table "gps", but it cannot be referenced from this part of the query.

So is the only way to do?

SET stop_id = nearest.stop_id
FROM (SELECT gps.gps_id, stop_id 
      FROM gps
      LATERAL JOIN (SELECT stop_id FROM stops ORDER BY stops.geom <-> gps.geom LIMIT 1) stops) nearest
WHERE nearest.gps_id = gps.gps_id

This feels like joining the same table to itself, which wouldn't need to happen with a SELECT INTO


No need for JOIN LATERAL (or do you really just want to use it?); an UPDATE will pass each processing row to the following query, which is the same concept as using a JOIN LATERAL.[*]


SET stop_id = (
  SELECT stops.stop_id
  FROM stops
  ORDER BY gps.geom <-> stops.geom

You are a very experienced PostGIS user; still, let me add some notes on distances and the KNN operator ,)

For better precision, consider casting to geography; the <-> operator then measures on a sphere, while ST_Distance uses the actual spheroid. In my experience, for points the <-> operator tends to perform only slightly faster than with plain old ST_Distance with geography type and a limit condition (check if the index scan actually kicks in with <->; it should consider the passed in geometry as a constant, but sometimes it doesn't for me).

If the tables are large, you can add a WHERE ST_DWithin(gps.geom, stops.geom, <apt_distance>) (or, if the planner denies an index only scan, use ST_Expand(gps.geom, <apt_distance>) && stops.geom; for point on point KNN and geometry type, this is ultimatively fast) to only compare those stops in each gps points' vicinity (note that the distance given uses the CRS units (i.e. degrees for EPSG:4326) for geometry, but meter for geography).

[*] Just to give an example on that; consider a SELECT instead to find the closest stop to each gps point using JOIN LATERAL:

SELECT a.gps_id,
FROM gps AS a
JOIN LATERAL (                         -- you can use 'CROSS JOIN LATERAL' without 'ON true',
  SELECT stops.stop_id                 -- but I get slightly faster results this way
  FROM stops
  ORDER BY a.geom <-> stops.geom
) AS b
ON true;

Each row in gps is now passed individually and subsequentially to the JOIN LATERAL sub-query to be processed; this (sort of) mimicks the UPDATE command (note how it is the same sub-query).

  • 1
    Follow up question: if I wanted to SET multiple columns this way would I move the subquery to FROM or would I repeat the subquery for each column? – raphael Apr 5 '18 at 18:33
  • 1
    @raphael damn, accidentally deleted my comment, so again: move to FROM (...you got me there, I'm not 100% sure, but I guess that you can assign multiple SET columns if the subquery returns only one distinct value per column, as with the stop_id in the example, without a WHERE condition) – ThingumaBob Apr 5 '18 at 19:18
  • 1
    I'll play around with it and report back. – raphael Apr 5 '18 at 19:59
  • 2
    @raphael got it: ... SET (<col1>, <col2>) = (<query>) where <query> (as above) returns two columns in respective order, having one distinct value each.. – ThingumaBob Apr 6 '18 at 10:53
  • 1
    @DPSSpatial I think that´s in fact a common practice for large tables since an UPDATE is resource heavy most of the time. The JOIN LATERAL will add up to this, maybe using a CTE can be helpful? I´m no expert here, but maybe it´s worth checking your work_mem value to minimize temporary disk storage etc. Also, on large tables, it´s at least faster, maybe even less demanding, to create a new table instead (I read that a long while ago, can´t find source) – ThingumaBob May 24 '18 at 18:27

And if you want to store this minimal distance in your gps table you can use this code:

ADD min_distance float8;

SET min_distance = list.distance
    SELECT gps.gps_id AS name, MIN(gps.geom <-> stops.geom) AS distance
    FROM gps
    CROSS JOIN stops
    GROUP BY gps.gps_id
    ORDER BY gps.gps_id)
    AS list
WHERE gps.gps_id = list.name;

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.