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

menes
  • 1,421
  • 2
  • 7
  • 24
user120165
  • 21
  • 4
  • 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 '21 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 '21 at 19:42
  • 1
    If needed you can compute the centroids of the polygons and compare the distances of those. – Kartograaf Sep 30 '21 at 19:43
  • Thx. W'll give a try tomorrow – user120165 Sep 30 '21 at 19:49
  • If the geometries are the same you can follow this thread https://gis.stackexchange.com/questions/124583/delete-duplicate-geometry-in-postgis-tables – Timothy Dalton Oct 01 '21 at 04:04
  • Actually i solved this by doing normal database joins in postgis. – user120165 Oct 01 '21 at 11:24

1 Answers1

1

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
.....
.....
Atm
  • 1,517
  • 3
  • 12
user120165
  • 21
  • 4