4

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.

  • PK are unique by definition. also we assume that the geometry is a point geometry? – nickves Jul 22 '15 at 10:46
  • yes, both are point geometries. – Egidi Jul 23 '15 at 13:46
3
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.

  • I'm curious if there's any profit from using lateral join over the normal one? – Michal Zimmermann Jul 22 '15 at 12:17
  • 1
    @MichalZimmermann. I have been playing with numerous version of kNN queries, with or without LATERAL, with or without DISTINCT and done lots of profiling and come to the conclusion that each query is different. Unless you use ST_Dwithin inside the LATERAL query performance can be really horrible, as the <-> operator only kicks in when you have a constant as one of the geometries. And as soon as you start using ST_DWithin with a large search distance, you have lost the benefit of <-> and LATERAL. Sorry, not a very helpful answer. – John Powell Jan 18 '18 at 15:09

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.