1

I have 10 tables with different attributes. Geometries are grid polygons. Each table have their own gridID numbering, so that cannot be used for joining.

Pure union of tables producing total of 2 600 000 rows. Roughly 60 percent of grid polygons have exactly the same geometry. They do overlap exactly.

I need to reduce the number of rows in order to speed up further spatial analysis.

How to combine tables so that each unique geometry is listed once.

Table1    
text1a, text1b, geom1

Table2    
int2a, int2b, geom2

TableN    
varN1, varN2, geomN

Expected result:
All distinct geometries, one row for each.
Each row contains all attribute columns.
Null values in columns if there is no exactly matching polygon existing in table in question.

unique_geom, text1a, text1b, int2a, int2b.... varN1, varN2

Tools I have PostGIS & QGIS

6
  • 2
    Welcome to GIS-SE. If you can merge the files into one layer, you can use the delete duplicate geometries tool. If not, you can use the NNJoin plugin to compute distance to nearest neighbor in the other layers, then remove the ones with distance = 0.
    – Kartograaf
    Sep 30 at 18:58
  • Some polygons may fall inside a bigger ones. 250m x 250m vs 1km x 1km square polygons (statistical grid) . Is NNJoin able to deal those.
    – user120165
    Sep 30 at 19:42
  • 1
    If needed you can compute the centroids of the polygons and compare the distances of those.
    – Kartograaf
    Sep 30 at 19:43
  • Thx. W'll give a try tomorrow
    – user120165
    Sep 30 at 19:49
  • If the geometries are the same you can follow this thread gis.stackexchange.com/questions/124583/… Oct 1 at 4:04
0

Actually was able to do this in following manner

All the tables did have same SRID thus joining based on geom value. In my case for this no spatial functions needed

First collect all unique geometries

CREATE tmp_table AS
SELECT DISTINCT geom FROM
(
select geom from table1
union
select geom from table2
... 
... 
) 

then normal left joins to the tmp_table

SELECT a.geom,t1.text1a,t1.text1b,t2.int2a...... 
FROM temp_table a
LEFT JOIN table1 t1 ON a.geom = t1.geom
LEFT JOIN table2 t2 ON a.geom = t2.geom
.....
.....

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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