[postgis-users] spatial join nearest neighbor

[Lazrak, F. (Faroek)]

I have read this but I realy don't know how to write the query..
Currently I use:


CREATE TEMPORARY TABLE afstand 
                (p_gid INTEGER,
                v_gid INTEGER,
                afstand NUMERIC,
                CONSTRAINT afstand_pk PRIMARY KEY (p_gid))


INSERT INTO afstand
SELECT          p.gid,
                v.gid,
                st_distance (p.the_geom,v.the_geom)
from
                nvm_85_09  p
                INNER JOIN stads_dorp_gezicht v
                                ON st_dwithin
(p.the_geom,v.the_geom,60000) 
                INNER JOIN (
                                                Select
                                                                p.gid as
p2gid,
 
MIN(st_distance (p.the_geom,v.the_geom)) as mindist
                                                from
 
nvm_85_09  p
                                                                INNER
JOIN stads_dorp_gezicht v
 
ON st_dwithin (p.the_geom,v.the_geom,60000) 
                                                where
                                                                p.gid
not in (select p_gid from afstand)
                                                GROUP BY
                                                                p.gid
                                ) as mindistance
                ON         p.gid = p2gid AND
                                st_distance (p.the_geom,v.the_geom) =
mindist
WHERE
                p.gid not in (select p_gid from afstand)


But this gives the following error:

ERROR:  duplicate key value violates unique constraint "afstand_pk"

********** Fout **********

ERROR: duplicate key value violates unique constraint "afstand_pk"
SQL status:23505


I have checked if "p.gid" has multiple outcomes but this is not the
case...

Best wishes,

Faroek 

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
ByronCinNz
Sent: donderdag 18 november 2010 22:06
To: postgis-users at postgis.refractions.net
Subject: Re: [postgis-users] spatial join nearest neighbor


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.

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users