Spatialite ST_Union works differently than in Union in QGIS

[artkraw]

Hi all ... 

Just simple question ... 

I have two overlapped polygons on two different layers - after Union operation in QGIS third  layer consists three polygons (see attached graphic file). 

after ST_Union in Spatialite based on two tables, third table consists only one geometry - not three ..  (see attached graphic file). 

How to formulate question in Spatialite to obtain the same results  as in QGIS ? 

My first question:

 

CREATE TABLE wynik3 AS 

SELECT St_union(p1.geom, p2.geom)

FROM p1, p2;

Best regards,

Artur K, 

  

[mj10777]

On Thursday, 26 April 2018 01:49:23 UTC+2, artkraw wrote:

Hi all ... 

Just simple question ... 

I have two overlapped polygons on two different layers - after Union operation in QGIS third  layer consists three polygons (see attached graphic file). 

after ST_Union in Spatialite based on two tables, third table consists only one geometry - not three ..  (see attached graphic file). 

How to formulate question in Spatialite to obtain the same results  as in QGIS ? 

Use the ST_Collect function to replicate the QGIS operation for POLYGON types.

ST_Union is used to (where possible) to combine/merge multiple Geometries into 1 Geometry when they share common portions. 

ST_Collect is used to simply collect multiple Geometries WITHOUT attempting to merge the common portions.

The QGIS terminology is therefore misleading, but is explained as such in the shown text for POLYGON types.

Mark

[artkraw]

How to formulate query in Spatialite to obtain the same results  as in QGIS ? 

Use the ST_Collect function to replicate the QGIS operation for POLYGON types.

ST_Union is used to (where possible) to combine/merge multiple Geometries into 1 Geometry when they share common portions. 

ST_Collect is used to simply collect multiple Geometries WITHOUT attempting to merge the common portions.

The QGIS terminology is therefore misleading, but is explained as such in the shown text for POLYGON types.

Mark

Thank you Mark for the advice .... from the ST_Collect function I have got  multipoligon geometries without merging them but without cutting common area also (as separate poligon) 

see attached file   ... 

Best regards,

Artur K.

[mj10777]

Have a look at ST_Intersection at:

http://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html 

Best regards,

Artur K.

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

On Thu, 26 Apr 2018 00:40:49 -0700 (PDT), artkraw wrote:
> from the ST_Collect function I have
> got  multipoligon geometries without merging them but without cutting
> common area also (as separate poligon) 
>

SELECT ST_Unon( ST_Collect(p1.geom, p2.geom) )
FROM p1, p2;

useful documentation:
http://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html

bye Sandro

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

On Thu, 26 Apr 2018 00:40:49 -0700 (PDT), artkraw wrote:

> How to formulate query in Spatialite to obtain the same results 
> as in QGIS ? 
>

Premise: what QGIS intends for "Union" is completely different
from what OGC (ISO) international standards dictate.

An Union (as implemented by QGIS) seems to be a rather complex
mix of several spatial operations, and cannot be simply translated
into a single SQL function.

the following SQL script represents a sketchy (and probably
incomplete) attempt to emulate the QGIS own Union.

====================
CREATE TABLE p1 (id INTEGER PRIMARY KEY);
SELECT AddGeometyColumn('p1', 'geom', 4326, 'POLYGON', 'XY');
CREATE TABLE p2 (id INTEGER PRIMARY KEY);
SELECT AddGeometyColumn('p2', 'geom', 4326, 'POLYGON', 'XY');

CREATE TABLE p1 (id INTEGER PRIMARY KEY);
SELECT AddGeometryColumn('p1', 'geom', 4326, 'POLYGON', 'XY');
CREATE TABLE p2 (id INTEGER PRIMARY KEY);
SELECT AddGeometryColumn('p2', 'geom', 4326, 'POLYGON', 'XY');

INSERT INTO p1 VALUES (NULL,
ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))', 4326));
INSERT INTO p1 VALUES (NULL,
ST_GeomFromText('POLYGON((0 15, 30 15, 30 25, 0 25, 0 15))', 4326));
INSERT INTO p1 VALUES (NULL,
ST_GeomFromText('POLYGON((0 30, 10 30, 10 40, 0 40, 0 30))', 4326));

INSERT INTO p2 VALUES (NULL,
ST_GeomFromText('POLYGON((5 2, 15 2, 15 8, 5 8, 5 2))', 4326));
INSERT INTO p2 VALUES (NULL,
ST_GeomFromText('POLYGON((30 0, 40 0, 40 10, 30 10, 30 0))', 4326));
INSERT INTO p2 VALUES (NULL,
ST_GeomFromText('POLYGON((2 34, 20 34, 20 36, 2 36, 2 34))', 4326));

CREATE TEMPORARY TABLE temp_intersections AS
SELECT p1.id AS id_1, p2.id AS id_2,
ST_Intersection(p1.geom, p2.geom) AS geom
FROM p1, p2
WHERE ST_Intersects(p1.geom, p2.geom) = 1;

CREATE TEMPORARY TABLE temp_differences AS
SELECT p1.id AS id_1, NULL AS id_2,
ST_Difference(p1.geom, tmp.geom) AS geom
FROM p1, temp_intersections AS tmp
WHERE ST_Intersects(p1.geom, tmp.geom) = 1
UNION
SELECT NULL AS id1, p2.id AS id_2,
ST_Difference(p2.geom, tmp.geom) AS geom
FROM p2, temp_intersections AS tmp
WHERE ST_Intersects(p2.geom, tmp.geom) = 1;

CREATE TEMPORARY TABLE temp_disjoints AS
SELECT p1.id AS id_1, NULL AS id_2, p1.geom AS geom
FROM p1, (SELECT ST_Collect(p2.geom) AS g FROM p2) AS x
WHERE ST_Disjoint(p1.geom, x.g) = 1
UNION
SELECT NULL AS id_1, p2.id AS id_2, p2.geom AS geom
FROM p2, (SELECT ST_Collect(p1.geom) AS g FROM p1) AS x
WHERE ST_Disjoint(p2.geom, x.g) = 1;

CREATE TABLE qgis_like_union AS
SELECT * from temp_intersections
UNION
SELECT * from temp_differences
UNION
SELECT * from temp_disjoints;
SELECT RecoverGeometryColumn('qgis_like_union', 'geom', 4326,
'POLYGON', 'XY');
====================

bye Sandro

[artkraw]

Dear Sandro,

I would like to thank you for such a wonderful example of help in solving my questions. I did not even think that solving the problem will take up so many lines of code.

I would like to thank you for your time ...

During execution your code I am solved one smal problem but next one I can't solve. 

 I do not know enough sql to deal with this error ... screenshot in the attached file ...

CREATE TEMPORARY TABLE temp_differences AS 

SELECT p1.id AS id_1, NULL AS id_2, 

   ST_Difference(p1.geom, tmp.geom) AS geom 

FROM p1, temp_intersections AS tmp 

WHERE ST_Intersects(p1.geom, tmp.geom) = 1 

UNION 

SELECT NULL AS id1, p2.id AS id_2, 

   ST_Difference(p2.geom, tmp.geom) AS geom 

FROM p2, temp_intersections AS tmp 

WHERE ST_Intersects(p2.geom, tmp.geom) = 1; 

many thanks!

-

Best regards,

Artur K,

[mj10777]

On Friday, 27 April 2018 02:09:39 UTC+2, artkraw wrote:

Dear Sandro,

I would like to thank you for such a wonderful example of help in solving my questions. I did not even think that solving the problem will take up so many lines of code.

I would like to thank you for your time ...

During execution your code I am solved one smal problem but next one I can't solve. 

 I do not know enough sql to deal with this error ... screenshot in the attached file ...

CREATE TEMPORARY TABLE temp_differences AS 

SELECT p1.id AS id_1, NULL AS id_2, 

   ST_Difference(p1.geom, tmp.geom) AS geom 

FROM p1, temp_intersections AS tmp 

WHERE ST_Intersects(p1.geom, tmp.geom) = 1 

UNION 

SELECT NULL AS id1, p2.id AS id_2, 

   ST_Difference(p2.geom, tmp.geom) AS geom 

FROM p2, temp_intersections AS tmp 

WHERE ST_Intersects(p2.geom, tmp.geom) = 1; 

Note:

1) Sql-Syntax:  'CREATE TEMPORARY TABLE'

- the 'TEMPORARY' means that the TABLE will be automatically removed when the connection is closed

-- through: close of a Database connection ; or when run as a sql-script and the script ends ; or when done from inside spatialite_gui  and then closing spatialite_gui 

---> these TABLEs will then be removed

2) Sql-Syntax error during the 'CREATE' statement can also cause the TABLE not to be created

- this can be easily checked by executing the 'SELECT' portion of the command

Goal of the  'CREATE TEMPORARY TABLE' Syntax

- is to create, final, permanent results through the temporary results 

-> once the permanent results have been created, the temporary results  are removed.

From the Screenshot I do not see the:

CREATE TEMPORARY TABLE temp_intersections AS
SELECT p1.id AS id_1, p2.id AS id_2,
   ST_Intersection(p1.geom, p2.geom) AS geom
FROM p1, p2
WHERE ST_Intersects(p1.geom, p2.geom) = 1;

portion of the script, so that portion that created the (temporary) TABLE does not exist.

That is the likely cause, since the syntax looks correct

Mark

[artkraw]

Dear Mark,

Thank you for clarifying and suggesting a better query. It was helpful.

Best regards,

Artur K.