Say I have table
CREATE TABLE gps( gps_id serial primary key, measured_timestamp timestamp, geom Geometry(Point,4326), stop_id int --Null );
And I have a table of stops like:
CREATE TABLE stops( stop_id serial primary key, geom Geometry(Point,4326) );
If I want to do an
gps to find the nearest stop to each point, is there a way to use a
I tried something like
UPDATE gps 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?
UPDATE gps 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