Merge non-adjacent polygons in Qgis or Postgres

Question

I am working with Qgis and PostgreSQL. But i can't figure out how to merge the non-adjacent polygons in my screenshot into one record? Can anybody help mee with this problem?

I want all the polygons with the same cat to be merged in one record.

See screenshot here:

Thnx

score 1 · Accepted Answer · edited May 23 '17 at 12:30

1

If you want use Postgres you need create a SELECT using ST_Union

geometry ST_Union(geometry set g1field);

geometry ST_Union(geometry g1, geometry g2);

geometry ST_Union(geometry[] g1_array);

You can try this to create one array

Concatenate multiple rows in an array with SQL on PostgreSQL

I try this one in my states tables and work ok

SELECT ST_Union(a_geom)
FROM  (SELECT  array_agg(e.geom) as a_geom 
       FROM mapas.estadosven_region e
      ) t

I make another test and looks like this also may work

SELECT ID, ST_Union(geom) 
FROM test_dissolve_function t 
WHERE ST_isValid(geom)='t' 
GROUP BY ID;

edited May 23 '17 at 12:30

Community

1
1

answered Nov 24 '15 at 21:29

Juan Carlos Oropeza

47,252
12
78
118

Thnx for replying Juan! I looked it up and i have this so far but how can you show the cat column? SELECT geom, ST_multi(ST_Union(f.geom)) FROM test_dissolve_function As f GROUP BY geom – L. Grunn Nov 24 '15 at 23:27
What code did you run? Because i get this ERROR: GEOSUnaryUnion: TopologyException: Input geom 1 is invalid: Self-intersection at or near point after running this code:SELECT cat, ST_multi(ST_Union(f.geom)) FROM test_dissolve_function As f GROUP BY cat – L. Grunn Nov 25 '15 at 00:02
I get this ERROR: GEOSUnaryUnion: TopologyException: Input geom 0 is invalid: Self-intersection at or near point 234754.01867566622 579837.20636547857 at 234754.01867566622 579837.20636547857 – L. Grunn Nov 25 '15 at 00:13
Looks like is something with your data. In my case im sure states borders arent exact so some intersection also occurs. Can you try join only two simple objects first? instead of all the table? use the first signature of `ST_Union` Also you can encampsule the function to something like join_mygeom(id) so you find what join is giving you error. Make the function return -1 if any error – Juan Carlos Oropeza Nov 25 '15 at 00:17
i ran SELECT ST_IsValid(geom) FROM test_dissolve_function; and i got 1 rows that returned falls so i think it's my data but i don't know how to solve that yet – L. Grunn Nov 25 '15 at 00:23
Well try with ST_IsValidDetail. But that should be a different question. http://postgis.net/docs/ST_IsValidDetail.html – Juan Carlos Oropeza Nov 25 '15 at 00:27
Yeah you're right that is a different question. But they are self intersecting. But i will check how to solve that. Thnx a lot for all your help! – L. Grunn Nov 25 '15 at 00:35
try again the `ST_Union` function with `where id not in (not valid_ids) ` – Juan Carlos Oropeza Nov 25 '15 at 00:36
doesn't recognize the valid_ids. But can't i just use ST_MakeValid? Or will that screw up my data? – L. Grunn Nov 25 '15 at 00:48
dont do that unless you back your data first. Or create a new table. But again you can do `ST_UNION ..... Where ST_isValid(geom)` – Juan Carlos Oropeza Nov 25 '15 at 00:50
Now my query looks like this SELECT ST_Union(geom) FROM (SELECT array_agg(test_dissolve_function.geom) as geom FROM test_dissolve_function WHERE ST_isValid(geom)='t' )t; But it only returns one row. What am i doing wrong? – L. Grunn Nov 25 '15 at 01:12
`SELECT ID, ST_Union(geom) FROM ( SELECT ID, array_agg(t.geom) as geom FROM test_dissolve_function t WHERE ST_isValid(geom)='t' group by ID) t;` – Juan Carlos Oropeza Nov 25 '15 at 04:13
Please check my edit. Looks like you can use the `ST_Union()` as a `SUM()` or `COUNT()` let me know if work for you. – Juan Carlos Oropeza Nov 25 '15 at 14:20
Hey Juan, both codes work! thnx a lot! The code is a lot cleaner when you use ST_Union() like you would with SUM() or COUNT(). – L. Grunn Nov 25 '15 at 20:02

Merge non-adjacent polygons in Qgis or Postgres

1 Answers1