2

I have a few scenarios where I need to dissolve vector data, and I'm wondering how to do this using PostGIS. What would the queries look like to perform these operations?

Below I'll describe the different cases through some simple examples.

The actual data I'm working with is considerably more massive and complex than these examples, in reality I'm working with data from OpenStreetMap (road networks, landuse polygons et.c.). PostGIS seems to be the foremost alternative for handling heavy data.

Segmented lines

Lines are split up into segments similarly to what is shown in the two images. The first line ("A-D") consists of three segments and has been split at B and C. I want to dissolve these three individual lines to get a line consisting of the vertices A,D. (vertices B,C are entirely redundant)

The second line isn't very different. It has been split at E and F into three smaller lines.

line1

line2

Dissolve polygons

The second issue concerns dissolving polygon data. The geometries can either overlap or be within each other, touch (shared vertices) or nearly touch with some tiny distance separating the polygons.

For all of these cases I want to dissolve the polygons into larger geometries. I guess the last case where the polygons nearly touch is a bit special as the small space in between should be "filled out".

poly1

poly2

Final note

In the above cases the dissolving is solely based on spatial relationships. Non-geometric attributes aren't relevant.

As stated earlier the actual data I'm working with is pretty massive, and the base queries will likely have to be tweaked and optimized for handling very complex geometries.

If anyone would like to get the data shown in the examples it can be downloaded here as GeoJSON.

1

If all your overlaps/touchings were in pairs, it would be a simple self-join, which would be ideal for efficiency.

The brute force answer is just something like:

CREATE TABLE merged AS
SELECT (ST_Dump(ST_Union(geom)).geom AS geom
FROM mytable;

That'll take all the geometries into memory, throw them into a topology and extract the largest merged objects as a single geometry collection, then dump each element of the collection out into its own row.

For sufficiently large inputs, it could be terribly slow.

The trouble is, to do a more subtle merge operation you have to know a priori which groups of objects to merge. There's an ST_ClusterIntersecting function in PostGIS 2.2 what gets you some of the way there, but it's still an in-memory algorithm, so it might not be ideal.

  • why don't you use WHERE ST_Intersects() or something similar with a self-join? Could you please elaborate? I thought it would work for more than pairs as well... If not, how about making an array out of it and send it to ST_Union()? – Michal Zimmermann Nov 8 '15 at 18:25
  • WHERE ST_Intersects() will find each pair separately, so you'll end up with a much of unioned pairs, but not a single unioned area in the case where multiple objects overlap. You'll also end up with duplication. Have three overlapping objects? You'll get back three pairs in the simple self-join case. – Paul Ramsey Nov 9 '15 at 13:22
  • Doing WHERE ST_Intersects with ST_Union gives an expected result from postimg.org/image/f6c7po9f3 - a single polygon based on those 3. Am I missing something? – Michal Zimmermann Nov 9 '15 at 14:08
  • Show the actual SQL. You're probably multiplexing your data w/ the join then aggregating it all into one bucket in the end anyways unless there's some GROUP BY I'm not understanding in there. – Paul Ramsey Nov 9 '15 at 14:28
  • SELECT ST_Intersection(a.wkb_geometry, b.wkb_geometry) FROM test.test a, test.test b WHERE ST_Intersects(a.wkb_geometry, b.wkb_geometry); OK... long hours today, used intersection instead of union :-/ – Michal Zimmermann Nov 9 '15 at 14:52

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.