I need to do a simple merge polygon in PostGIS. The attribute table from both input shapefiles needs to be preserved. This is easily done In QGIS by using the merge polygon tool. But I don’t know how to do this in PostGIS.

I want to merge poly1 and poly2 – see picture (done in QGIS).

enter image description here

I have tried with the code below, but this gives way to many rows (should be around 5000, but it gives more 1 millions rows:

CREATE TABLE land_merge as
SELECT ST_Union(poly1.geom, poly2.geom) as geom
FROM poly1, poly2;
  • 1
    Look at what your SQL really requests: "Generate one row in land_merge for every row in poly for every row in poly2." So yeah, starting with thousands, the Cartesian product should be millions. Do you just want to append the two tables, or do you want to merge, dissolving on some common key? – Vince Jan 10 at 1:04

Your tables doesnt look like they overlap so I think you want to use the postgres union operator to combine the two selections:

SELECT * from poly1
SELECT * from poly2

enter image description here

  • 1
    Thanks! This is exactely what i needed – heljor Jan 10 at 12:13
  • do you know how I can merge with different number of columns? I got this error when i tried to merge two polygons with different # of columns: ERROR: each UNION query must have the same number of columns LINE 4: SELECT * poly2 – heljor Jan 10 at 15:39
  • I havent tried that. But a quick google says it seems to be possible, for example: stackoverflow.com/questions/50122151/… – BERA Jan 10 at 15:41

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.