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

Francois Hugues hugues.francois at irstea.fr
Tue Dec 11 00:30:01 PST 2012

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 :
with dist_min as (
    select a.gid, min(st_distance(a.geom, b.geom)) dist_min from table1 a, table2 b
    where a.country = b.country
    group by a.gid
select a.gid, b.gid, dist_min from dist_min a, table2 b
where st_distance(a.geom, b.geom) = dist_min

De : postgis-users-bounces at lists.osgeo.org [mailto:postgis-users-bounces at lists.osgeo.org] De la part de Carsten Hogertz
Envoyé : lundi 10 décembre 2012 21:55
À : postgis-users at lists.osgeo.org
Objet : [postgis-users] How to perform a join on simultaneous spatial and attribute criteria in PostGIS

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/20121211/c185824f/attachment.html>

More information about the postgis-users mailing list