[postgis-users] most efficient way of finding closest feature
Michael Tafel
MTafel at drcog.org
Wed Mar 17 10:38:54 PDT 2010
Hello,
If this has been answered in a previous thread I apologize in advance. I've googled with little success.
I have two tables of points, table_a and table_b. I want to find the closest point for each record in table_a to table_b.
The query we've got to work looks like this:
SELECT DISTINCT ON (table_a.gid)
table_a.gid, table_b.gid, ST_Distance(table_a.the_geom, table_b.the_geom) AS dist
FROM table_a, table_b
WHERE ST_DWithin (table_a.the_geom, table_b.the_geom, 5280)
ORDER BY table_a.gid, dist ASC;
However I need to run this on large tables. Table_a would have 1.2 million records and table_b would have 25,000 records (potential more). I'm afraid with the way ST_Distance() works this query could take a very, very long time. Is the above query the most efficient way to run accomplish what I need?
Also would this work if table_b were polygons?
Thanks in advance for any advice you may have.
Cheers,
--Mike
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100317/586985c0/attachment.html>
More information about the postgis-users
mailing list