[postgis-users] spatial join nearest neighbor

[ByronCinNz]

Hello,

This link might provide the solution you need. I am using this for a similar
issue to yours with good results.

http://www.bostongis.com/?content_name=postgis_nearest_neighbor_generic

Cheers,
Byron



Lazrak, F. (Faroek) wrote:
> 
> 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
> 
>  
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
> 

-- 
View this message in context: http://old.nabble.com/spatial-join-nearest-neighbor-tp30246195p30252222.html
Sent from the PostGIS - User mailing list archive at Nabble.com.