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

Carsten Hogertz carsten.hogertz at gmail.com
Mon Dec 10 12:54:41 PST 2012

Hello everybody,

I have two point layers and want to perform a spatial join on nearest

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

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

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

More information about the postgis-users mailing list