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.

Let me explain what I mean by "Deep Merge" and what I am trying to do.

Suppose that I have 3 layers (Layer1, Layer2 and Layer3) with the same field names (field_a, field_b and field_c). For any record, each layer has a value only in one of fields. For example, One row in Layer1 has a non-empty value only in field_a. One row in Layer2 only in field_b. One row in Layer3 only in field_c. The tricky part is that, different rows in the same layer may not have a non-empty value in the same field. My goal is to merge all 3 layers into one target layer, which contains all non-empty values from all 3 layers.

In my mind, it involves some serious loops or joins. So I am wondering if anybody knows any existing tool (open source or commercial) to do that.

Thank you very much!

share|improve this question
1  
Are you using "row" as a synonym for "feature"? If not, it's unclear how "rows" in one layer are supposed to be associated with rows in another layer. –  whuber Sep 7 '11 at 14:17
add comment

1 Answer

Do you mean that Layer1 only has values in field_a, Layer2 only in field_b, and Layer3 only in field_c? Because if so, and assuming they have a shared id, you could try something like this (in a DBMS like PostgreSQL/PostGIS, for example):

SELECT Layer1.id AS orig_id, field_a, field_b INTO intermediate_table
FROM Layer1, Layer2 WHERE Layer1.id = Layer2.id;

SELECT orig_id, field_a, field_b, Layer3.field_c AS field_c INTO merged_table 
FROM intermediate_table, Layer3 WHERE intermediate_table.orig_id = Layer3.id;

However, I suspect you mean that the combination of Layers 1, 2 and 3 always populates field_a, field_b and field_c when combined, but different rows from the same table would not always have a non-null value for the same field? Then yes, it could get a whole lot more complicated...

EDIT:

OK, so given that there is no id field, and that the non-null field is not the same one between rows in the same table, try something like this instead. First, create the target table for the merge:

SELECT * FROM Layer1 INTO merged;
UPDATE merged SET field_a = null;
UPDATE merged SET field_b = null;
UPDATE merged SET field_c = null;

Then, for every field in every Layer you want to merge, do this:

UPDATE merged SET field_X =
  (SELECT LayerY.field_X FROM LayerY
  WHERE LayerY.the_geom = merged.the_geom AND LayerY.field_X IS NOT NULL);

I'm assuming here that the geometries are in fact identical (as in, derived from the same table originally), and are called "the_geom". If they are not identical, you'll have to modify the WHERE clause to use some SQL functions from PostGIS which can detect spatial intersection/overlap/etc.

share|improve this answer
    
Unfortunately they don't have a shared ID. Also different rows from the same table may not always have a non-null value for the same field.The only common denominator is the similar shapes. –  freddell Sep 7 '11 at 8:53
    
OK, my idea is too long to put in a comment, so I'll edit my answer instead. –  R Thiede Sep 7 '11 at 9:25
    
Thanks R! That looks like a pretty promising solution, although the geometries are not identical, I could work further on your idea. And my database is Oracle (without spatial). I will see if I could apply SDE geometries on the same idea. –  freddell Sep 7 '11 at 11:54
    
No probs! :) The general idea should work though, as long as there's some way to uniquely identify each row between the different tables. –  R Thiede Sep 7 '11 at 12:06
add comment

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.