Merging polygons that intersect by more than specified amount using PostGIS

Question

I am working with a dataset that contains a subset of property boundaries in a local government area, thus my dataset is made up of several thousand properties but not all are touching. Since it is a subset, some of these properties are touching "side by side", some are touching "back to back", some are only touching "corner to corner" and some are not touching any other properties at all. What I need to do is merge (i.e. ST_Union) properties that are either touching "back to back" or "side to side" while leaving the isolated properties or ones only touching "corner to corner" alone. Basically I am looking to amalgamate properties on which development can be continuous. I am trying to accomplish this using Postgres and Postgis.

That way I thought I could go about this was to first buffer all the geometries by a small amount, such as 0.5 meters. Then I could perhaps run ST_Union() on the dataset where the intersection of two overlapping buffers is greater than 1 or 2 square metres. This is how I thought I could get around ST_Intersects returning true for properties touching "corner to corner" when I would actually want these left out. The greater the overlap, the more likely the properties are situation "side by side" or "back to back".

The issue with this is that when I run the queries they end up taking a really long time and a running this query once does not do the trick. If 3 properties are sitting "side by side", for instance, the query will create a union of the left two and right two, thus creating two overlapping polygons rather than one amalgamated polygon. I will have to run some kind of a loop I think.

Does anyone have an easier solution?

I haven't done loops in Postgres either and am not sure how to execture one.

Answer 1

Try running St_Union and then explode the multipart polygons with St_Dump. The polygons that only touched at a corner should not be combined.

Answer 2

This is a thought to get you started, assuming I understand all the requirements correctly.

This approach assumes that 2 properties that touch "corner to corner" have much less overlap than 2 properties that are "side by side".

In a pairwise fashion:

Given property X, determine if Y is a corner or a "side" property
  -Get the bounding box (BB) of X and Y (for speed)
  -Find the geometric difference
  -Calculate the area of this difference
  -If this area is less than a predetermined amount, Y is a corner property

The bounding box is optional. It's quicker to work with bounding boxes, normally. If you need more exact measurements, don't use it, but expect longer processing time.

Also, this doesn't totally address the issue of finding all of the unique unions. For example, you could end up with results such as (X, Y, Z) and (Y, X, Z) and (Y, Z, X). You would need to account for unique grouping somehow. This gets into greedy search algorithms, etc.

That's a start for now at least.