# [postgis-users] spatial join nearest neighbor

Paragon Corporation lr at pcorp.us
Fri Nov 19 13:27:59 PST 2010

```Faroek ,

Sounds like you probably want to use a DISTINCT ON clause.  Your below query
looks needlessly complicated.  Try this one which will do a distance check
of 60000 and for each point that is within 60000 units of a polygon will
return
The gid of that polygon and the distance between the 2.

SELECT DISTINCT ON (p.gid,)          p.gid,
v.gid,
st_distance (p.the_geom,v.the_geom)  As dist
from
nvm_85_09  p
ON st_dwithin
(p.the_geom,v.the_geom,60000)
ORDER BY p.gid, v.gid, st_distance (p.the_geom,v.the_geom);

Leo,
http://www.postgis.us

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Lazrak,
F. (Faroek)
Sent: Friday, November 19, 2010 8:49 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] spatial join nearest neighbor

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
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

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
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

```