[postgis-users] spatial join nearest neighbor

[Lazrak, F. (Faroek)]

Hi,

 

I have a really simple problem but I am not able to solve the problem.

 

I have a point data and a have polygon data and I want to merge the data
of the polygon to the points based on the nearest distance to polygon.

 

The problem is that my point data is 2,5 million observations and the
polygon are approximately 700 observations resulting in 1,75 billion
rows where I have to minimize over 700.

 

Currently I have the following query:

 

SELECT

a.key,

b.gid, 

st_distance(a.the_geom, b.the_geom) as dist,

s.min_dist

FROM

  Neighbor as b, point as a

LEFT JOIN

  (SELECT 

     a.key, MIN(st_distance(a.the_geom, b.the_geom)) as min_dist

   FROM

    Neighbor as b, point as a

   GROUP BY 

     a.key) s

ON

  a."key" = S."key"

ORDER by a.key 

 

This querty gives all the 1,75 billion possibilities which then can be
used to minimize over a.key restricted by dist=min_dist, the problem is
that by the first query the computer run out of memory, so the second
query is not working.

 

In arcgis the problem is joining by closed distance but I need to do it
in postgis.

 

Hope you can help my,

 

Faroek

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20101118/086b635a/attachment.html>