[postgis-users] most efficient way of finding closest feature

[Michael Tafel]

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>