I need to obtain on each element on one table the closest point of another table. The first table contains traffic signs and the second one the Entrance Halls of the town. The thing is that I can't use ST_ClosestPoint function and I have to use ST_Distance function and get the min(ST_distance) record but I am quite stuck building the query.
CREATE TABLE traffic_signs ( id numeric(8,0) ), "GEOMETRY" geometry, CONSTRAINT traffic_signs_pkey PRIMARY KEY (id), CONSTRAINT traffic_signs_id_key UNIQUE (id) ) WITH ( OIDS=TRUE ); CREATE TABLE entrance_halls ( id numeric(8,0) ), "GEOMETRY" geometry, CONSTRAINT entrance_halls_pkey PRIMARY KEY (id), CONSTRAINT entrance_halls_id_key UNIQUE (id) ) WITH ( OIDS=TRUE );
I need to obtain the id of the closest entrance_hall of every traffic_sign.
I already know how to do it by joining the whole two tables. See question and answers here: PostGIS nearest point with ST_Distance
In this mentioned thread it is said that a new cool feature is out in PostgreSQL 9.3+. I would like to use this cool feature in PostgreSQL 9.3+ called LATERAL JOIN to achieve this same thing, but there is not much info about this feature and I don't figure out how it would be in this case to get the closest point of another table.
Hope someone can clear this out.