[postgis-users] How to perform a join on simultaneous spatial and attribute criteria in PostGIS

[Carsten Hogertz]

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:

*Join attribute B to layer A on nearest distance where countryID_Layer A =
countryID_Layer B.*

I tried to write the SQL-Statement, but it does not work somehow. Can
anybody help me with this statement?:

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


Thanks for your help!
-- 
hoge6b01




-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20121210/888b052b/attachment.html>