[postgis-users] Increase KNN efficiency

Vispo Leblanc vispoleblanc at gmail.com
Wed May 30 14:07:19 PDT 2018

Hello PostGIS users,

I am looking for a way to increase the speed of my query with KNN.

I have two tables with millions of points in each table. I would like to
find the nearest neighbor (only one) of my points in table A with my points
in table B.

I wrote the following query:

WITH index_query as (
    t1.geom AS geom,
    t2.geom AS geom2,
    t1.geom <-> t2.geom AS distance
  FROM research_scrapper.table1 t1,
      research_scrapper.table2 t2
  ORDER BY t1.geom <-> t2.geom
SELECT tbl.cas_id, tbl.geom
FROM index_query tbl
        SELECT geom2,
               MIN(distance) as distance
        FROM index_query
        GROUP BY geom2
    ON tbl1.geom_generated=tbl.geom_generated
    AND tbl1.distance = tbl.distance

This is taking forever, would you have any suggestions on how to increase
the efficiency?

Thanks for your help,
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20180530/2065c600/attachment.html>

More information about the postgis-users mailing list