Tuesday 26 June 2018

PostGIS nearest point with LATERAL JOIN in PostgreSQL 9.3+



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.



Answer



Create Table p(
id serial primary key,

geom geometry(point,2100)
);

The table has ~250k Points, and with the following query you can find the closest point in the same dataset (that is not the same point).


select 
a.id
,b.id target
,b.d distance
from p as a
cross join lateral

(select distinct on (a.id)
b.id
,a.geom <-> b.geom d
from p as b where a.id != b.id and st_dwithin(a.geom,b.geom,400)
order by a.id, d asc
) as b;

As you can see the lateral keyword allows me to reference columns from preceding items in the FROM list.
Before, without the lateral clause I would get the error
invalid reference to FROM-clause entry for table "a"

and a hint of
There is an entry for table "a", but it cannot be referenced from this part of the query.


No comments:

Post a Comment

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...