Deep merge data from multiple layers

Question

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!

Answer 1

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.