SymDifference

[Roberto Angeletti]

Hello all,

I need to make a SymDifference from two polygon layers, so I executed the following SpatiaLite  SQL statement:

create table  diffe  as
select SymDifference (st_collect(a.geometry), st_collect(m.geometry)) as geometry
from polygons001 as a ,polygons002 as m

But, it seems to be too slow  (it is running from three days... ).

So, before to kill the process, I like to know if some trick exists to make it faster.

These are my test SHP layers:

https://docs.google.com/file/d/0By5p_WoL_LoFMUVhYU1WOGJlalU/edit?usp=sharing
https://docs.google.com/file/d/0By5p_WoL_LoFbzJRSHZFZXctMmc/edit?usp=sharing

Somebody can help me ?

Thank you in advance

Roberto

[sandro furieri]

But, it seems to be too slow  (it is running from three days... ).

So, before to kill the process, I like to know if some trick exists to make it faster.

Ciao Roberto,

I suppose you've killed your process in the meanwhile :-D

Just a general order consideration; aggregating two whole layers each one
into a single (obvious huge and highly complex) MultiPolygon collection
and then pretending to compute a really time-consuming function (as
ST_SymDifference is) doesn't seems to be the optimal/faster strategy.

As a rule-of-the-thumb, you can expect to get a reasonable optimization
working exactly in the opposite way, i.e. by performing many and many
simpler elementary operations at the lowest possible complexity level.

Just an elementary consideration: computing the SymDifference between
two Polygons is a meaningful operation only when the two Polygons
share a common intersection.

So I've rewritten your original query as follows:

CREATE TABLE diffe AS
SELECT a.pk_uid AS uid_a, m.pk_uid AS uid_m,
  ST_SymDifference(a.geometry, m.geometry) AS geometry
FROM poligons001 AS a, poligons002 AS m
WHERE ST_Intersects(a.geometry, m.geometry) = 1
  AND m.ROWID IN (
    SELECT ROWID FROM SpatialIndex
    WHERE f_table_name = 'poligons002' AND
    search_frame = a.geometry);

remarks:
1) now we are just processing two simple Polygons during each
   single step
2) ST_Intersects will ensure that ST_SymDifference will be effectively
   computed only for surely intersecting Polygons
3) using the Spatial Index will avoid to quickly skip many useless
   steps (i.e. when an intersection is surely impossible)

conclusion: my query completed in about 5 minutes

bye Sandro

[Roberto Angeletti]

Hi Alessandro,

Thank you for help.  :)

So I've rewritten your original query as follows:

CREATE TABLE diffe AS
SELECT a.pk_uid AS uid_a, m.pk_uid AS uid_m,
  ST_SymDifference(a.geometry, m.geometry) AS geometry
FROM poligons001 AS a, poligons002 AS m
WHERE ST_Intersects(a.geometry, m.geometry) = 1
  AND m.ROWID IN (
    SELECT ROWID FROM SpatialIndex
    WHERE f_table_name = 'poligons002' AND
    search_frame = a.geometry);

Yes, but before these are needed:

SELECT CreateSpatialIndex('poligons001','Geometry');
SELECT CreateSpatialIndex('poligons002','Geometry');

Right ?

Other question:

For what reason, if i want to save the diffe table into SHP format, I had:
 "The SQL SELECT returned an invalid result set

... [not corresponding to the Shapefile format] ... "  ?

Bye

Roberto