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

[Francois Hugues]

Hello,
 
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
 
Hugues. 
 
________________________________

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