Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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

Normally it is easy with ArcMap. But now 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.

Does anybody have an idea how I could solve this?

EDIT: Image I have following attributes: Table1(hotels): h_key, l_iso_a2, geom and in Table2(iata_codes): loc, ctry, geom

EDIT2: 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;
share|improve this question
1  
Are there many countries involved? You could just select one country first and then do the spatial join. Then select other country, etc. If it are many countries this might not be the most efficient solution however. –  Stefan Dec 5 '12 at 14:41
    
Hello Stefan, thanks for your help. But I need to do it for the whole world. –  hoge6b01 Dec 5 '12 at 15:06
1  
I thought so... I don't if such a function exists in ArcGIS. If the data was in Postgres I could probably help. –  Stefan Dec 5 '12 at 15:09
2  
add a country2 field to data number 2. Then after the spatial join select country <> country2. Delete them. –  Brad Nesom Dec 5 '12 at 16:40
    
Thank you both for your input! I actually decided to put the three layers (point1, point2, poly_world) into a postgres. Now that they are stored in the DB I would appreciate your input to perform the calculation. Thanks in advance! Best Regards –  hoge6b01 Dec 6 '12 at 6:15
add comment

3 Answers

I think the Comment by @Brad Nesom provides an Answer with minimal steps to do what you want but may process a lot of data that gets deleted at the end.

For an alternative with more steps but less processing you could try this:

  1. Iterate through your countries (using ModelBuilder or Python) one at a time to Select out your points for that country.
  2. Run your Spatial Join on each country's data
  3. Append your data for each country into a whole world dataset
share|improve this answer
add comment
up vote 1 down vote accepted

Found it. This helps:

with dist_min as ( 
select h.gid, min(st_distance(h.geom, i.geom)) dist_min from t1 h, t2 i 
where h.country = i.country 
group by h.gid 
) 
select h.attribute1, h.gid, i.attribute1, i.gid, d.dist_min from dist_min d, t1 h, t2 i 
where st_distance(h.geom, i.geom) = dist_min 
order by h.attribute1
share|improve this answer
    
Cool! Similar to what I suggested, isn't it. –  Stefan Dec 18 '12 at 20:40
add comment

In postgres it would be something like

select 
   distinct on (t2.point1_attribute) t2.point1_attribute, t2.country, t2.point2_attribute, t2.country2, t2.distance
from
  (
  select t1.point1_attribute, t1.country, t2.point2_attribute, t2.country as country2, st_distance (t1.geom, t2.geom) as distance
  from poin1 t1, point t2
  where t1.country = t2.country
  order by t1.geom  t2.geom
 ) 
as t2

Hope this helps

share|improve this answer
    
Thank you Stefan. Got a question to your SELECT. Aren't there too many t2 in the select-row? I defined the columns in my initial question above. Sorry, I'm a newbie on this area. Thanks for your help! –  hoge6b01 Dec 7 '12 at 16:58
    
Have you tried this? It is a select within a select so that is why there are so many t2. –  Stefan Dec 11 '12 at 8:52
    
Hello Stefan, thank you very much. Well, I cannot get it run. I tried this: select distinct on (i.gid) i.loc, i.ctry, i.country2, i.distance from ( select h.h_key, h.l_iso_a2, i.ctry as country2, st_distance (h.geom, i.geom) as distance from point hotels, point iata_codes where h.l_iso_a2 = i.ctry order by h.geom i.geom ) as iata_codes –  hoge6b01 Dec 11 '12 at 9:54
    
Maybe you can help me again? –  hoge6b01 Dec 11 '12 at 9:54
    
select distinct on (t2.h_key), t2.h_key, t2.l_iso_a2, t2.country2, t2.distance from (select h.h_key, h.l_iso_a2, i.ctry as country2, st_distance (h.geom, i.geom) as distance from point_hotels h, point_iata_codes i where h.l_iso_a2 = i.ctry order by h.geom igeom) as t2 –  Stefan Dec 11 '12 at 10:21
show 11 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.