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.
Best Answer
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.