Dissolve problem

[Nick]

Some discussion of the problem may be found here:
http://gis.stackexchange.com/questions/50290/how-can-i-merge-adjacent-polygons-in-a-shapefile-that-have-been-split-at-tile-bo

In short, I have a number of polygon layers where individual features
have been split by lines that coincide with grid lines and I wish to
dissolve the polygons split in this way. The polygons do not have
meaningful attributes.

A contributor to SE suggests that this can be done in PostGIS using
the following SQL statement (a simple 'dissolve' won't work with these
datasets):

===========
DROP TABLE merged;
CREATE TABLE merged AS
SELECT
-- Merge polygons that are within 0.01 metres of each other
(ST_Dump(ST_Union(ST_Buffer(the_geom, 0.01)))).geom AS the_geom
FROM unmerged;

-- Update the geometry_columns table
SELECT Populate_Geometry_Columns();
============

Postgres/PostGIS and I don't get along I'm afraid and I wonder if the
above can be done in Spatialite? If so, I'd be very grateful if
someone could tell me how.

Regards,
Nick.

[a.fu...@lqt.it]

Hi Nick,

here is a basic solution for your problem.
please note: there are several fine-grained details worth to
be carefully evaluated, but this clearly overshoots my very
limited analysis:
1) in many real world cases two (or even more) polygons could
eventually touch for some legitimate reason; merging them
together seems to be a little bit arbitrary.
2) so a correct recognition of the original grid used to split
the polygons isn't a so easy and obvious step.

in the following example we'll simply assume that any couple of
touching polygons should be dissolved, but this seems to be
rather dangerous as a general order solution.

a) we'll start from the attached sample dataset; you simply have
to load this shapefile into a "buildings" table.
as you can easily check (first figure), there in vertical line
splitting many buildings into several parts.

SELECT a.pk_uid
FROM buildings AS a
JOIN buildings AS b ON (ST_Touches(a.geometry, b.geometry) = 1);

this query will identify (by its pk_uid value) any Polygon touching
some
other polygon.
please note: a Polygon could eventually touch more than a single
polygon,
so we have to reduce this to univocity:

SELECT a.pk_uid
FROM buildings AS a
JOIN buildings AS b ON (ST_Touches(a.geometry, b.geometry) = 1)
GROUP BY a.pk_uid;

please note: the sample dataset is very small-sized. using a real word
dataset you'll be probably required to use some Spatial Index in order
to speed up this step. in this case the SQL query will become:

SELECT a.pk_uid
FROM buildings AS a
JOIN buildings AS b ON (ST_Touches(a.geometry, b.geometry) = 1
AND b.ROWID IN (
SELECT ROWID
FROM SpatialIndex
WHERE f_table_name = 'buildings' AND search_frame = a.geometry))
GROUP BY a.pk_uid;

the simplest thing to do is now saving all "pk_uid" values into some
temporary table, so:

CREATE TABLE tmp_ids AS
SELECT a.pk_uid AS pk_uid
FROM buildings AS a
JOIN buildings AS b ON (ST_Touches(a.geometry, b.geometry) = 1
AND b.ROWID IN (
SELECT ROWID
FROM SpatialIndex
WHERE f_table_name = 'buildings' AND search_frame = a.geometry)
)
GROUP BY a.pk_uid;

now we are ready to dissolve our polygons; anyway a further difficulty
arises. sometimes we'll be required to reassemble a single polygon
starting from two halves, but other times we'll have to merge more
polygons into a single one.
you could cyclically iterate more dissolve ops (merging a couple at
each
time) until you've consumed all your input.
the simplest solution for this limited-size sample anyway is by
invoking
an "unary" Union, so to get a single MultiPolygon in a single pass.
(you can eventually invoke the "elementary geometries" tool in order to
get many individual polygons instead of a single MultiPolygon).

CREATE TABLE merged AS
SELECT ST_Union(b.geometry) AS geometry
FROM tmp_ids AS t
JOIN buildings AS b ON (b.pk_uid = t.pk_uid);

you can check the second figure; now we have succesfully dissolved
our polygons. now we simply have to perform two trivial post-processing
steps:

DELETE FROM buildings
WHERE pk_uid IN (
SELECT pk_uid
FROM tmp_ids);

this will delete all original fragments (now reassembled).

INSERT INTO buildings
SELECT NULL, geometry
FROM merged_elem;

and this will finally re-insert the dissolved polygons; finished.

bye Sandro




--
Il messaggio e' stato analizzato alla ricerca di virus o
contenuti pericolosi da MailScanner, ed e'
risultato non infetto.

[Nick]

Hi Sandro, many thanks for the response, it's appreciated. The bad news is that I can't get the queries to work, but the good news is that this means I have to start learning about Spatialite. I'll get there.

What version of Spatialite is needed to run the queries?

Regards,
Nick.


 

[a.fu...@lqt.it]

> What version of Spatialite is needed to run the queries?
>

Hi Nick,

using the most recent version is always warmly suggested:
i.e. 4.0.0

just a general order consideration: SpatiaLite (as many other open
source projects) has no regular maintenance on a version basis.
i.e. in any case the most recent version will probably support many
nice cool features, but will surely fix many issues or malfunctions
reported on previous releases (it will probably introduce some further
instabilities, nobody is perfect ...)
anyway the general trend you can always assume is that any recent
version is surely better (and more stable) then any previous one.

using an obsolete version never implies using a "well staged and
absolutely stable" code; it simply means being absolutely sure to
encounter many old bugs resolved in some subsequent version ;-)