Search code examples
sqlpostgresqlpostgis

Perform multiple nearest neighbor query by JOIN clause on PostGis in Postgres

With a query table

create table query (q geometry(Point));

and a data table

create table data (d geometry(Point));
create index on data using gist(d);

For each point P in query, I can query its nearest neighbor by

select P,data.d from query order by P<->data.d limit 1;

Now I want to perform this nearest neighbor queries in table "query" together, and I also want to use the index. Therefore, i get a result table with two columns, first is identical to each row in "query", and second column for the retrieved nearest neighbor in from "data".

Is that possible? How to express it?

Solution

  • You want a LATERAL join, where a subquery can reference a column from an earlier table in the join list.

    SELECT * FROM query CROSS JOIN LATERAL 
    (SELECT * FROM data ORDER BY q<->d LIMIT 1) foo;