<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=iso-8859-1" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 8.00.6001.19328"></HEAD>
<BODY bgColor=#ffffff text=#000000>
<DIV dir=ltr align=left><SPAN class=453283207-11122012><FONT color=#0000ff
size=2 face=Tahoma>Hello,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=453283207-11122012><FONT color=#0000ff
size=2 face=Tahoma></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=453283207-11122012><FONT color=#0000ff
size=2 face=Tahoma>To keep the nearest object, i think you should calculate
minimal distance first and then use it as a join attribute. Something like this
query should work :</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=453283207-11122012> </SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=453283207-11122012><FONT color=#0000ff
size=2 face=Tahoma>with dist_min as (</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=453283207-11122012> <FONT
color=#0000ff size=2 face=Tahoma>select a.gid, min(st_distance(a.geom, b.geom))
dist_min from table1 a, table2 b</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=453283207-11122012> <FONT
color=#0000ff size=2 face=Tahoma>where a.country = b.country</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=453283207-11122012> <FONT
color=#0000ff size=2 face=Tahoma>group by a.gid</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=453283207-11122012><FONT color=#0000ff
size=2 face=Tahoma> )</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=453283207-11122012><FONT color=#0000ff
size=2 face=Tahoma></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=453283207-11122012><FONT color=#0000ff
size=2 face=Tahoma>select a.gid, b.gid, dist_min from dist_min a, table2
b</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=453283207-11122012><FONT color=#0000ff
size=2 face=Tahoma>where st_distance(a.geom, b.geom) =
dist_min</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV dir=ltr lang=fr class=OutlookMessageHeader align=left><SPAN
class=453283207-11122012><FONT color=#0000ff size=2
face=Tahoma>Hugues. </FONT></SPAN></DIV>
<DIV dir=ltr lang=fr class=OutlookMessageHeader align=left><SPAN
class=453283207-11122012> </SPAN></DIV>
<DIV dir=ltr lang=fr class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
</DIV>
<DIV dir=ltr lang=fr class=OutlookMessageHeader align=left><FONT size=2
face=Tahoma><B>De :</B> postgis-users-bounces@lists.osgeo.org
[mailto:postgis-users-bounces@lists.osgeo.org] <B>De la part de</B> Carsten
Hogertz<BR><B>Envoyé :</B> lundi 10 décembre 2012 21:55<BR><B>À :</B>
postgis-users@lists.osgeo.org<BR><B>Objet :</B> [postgis-users] How to
perform a join on simultaneous spatial and attribute criteria in
PostGIS<BR></FONT><BR></DIV>
<DIV></DIV><BR>
<DIV class=moz-forward-container><BR><PRE>Hello everybody,
I have two point layers and want to perform a spatial join on nearest
distance.
I have a restricting condition on the spatial join. The restriction only
allows the attributes to be joined if the nearest point is within the same
country.
Both point layers have information about the country in their attribute
table (e.g., "de" for Germany). So it must be some spatial join like:
<B>Join attribute B to layer A on nearest distance where countryID_Layer A =
countryID_Layer B.</B>
I tried to write the SQL-Statement, but it does not work somehow. Can
anybody help me with this statement?:
<B>select distinct on (h.h_key) h.h_key, h.l_iso_a2, i.loc, i.ctry, distance
from ( select h.h_key, h.l_iso_a2, i.loc, i.ctry as country2, st_distance
(h.geom,
i.geom) as distance
from hotels AS h, iata_codes AS i
where h.l_iso_a2 = i.ctry order by h.geom <-> i.geom ) as iata_codes_h_key;</B>
Thanks for your help!
--
hoge6b01
</PRE></DIV><BR></BODY></HTML>