# Union multiple layers - reduce rows if exactly same geometry

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

• 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. Sep 30, 2021 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. Sep 30, 2021 at 19:42
• If needed you can compute the centroids of the polygons and compare the distances of those. Sep 30, 2021 at 19:43
• Thx. W'll give a try tomorrow Sep 30, 2021 at 19:49
• If the geometries are the same you can follow this thread gis.stackexchange.com/questions/124583/… Oct 1, 2021 at 4:04

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
.....
.....
``````
• There is a new QGis3 plugin named "Multi Union" ! Nov 15, 2023 at 18:22