Copy | Paste in QGIS - Polygons vs Multipolygons

[Alexandre Neto]

I am using Spatialite 3.0.1 as data provider for QGIS 1.8.0.

While editing geographical data, a very often operation is to copy\paste features, from one layer to another. 

QGIS do it with no problems with shapefiles, but gives some fight with SpatiaLite.

As I see it, the problem lies in trying to copy POLYGON features to a MULTIPOLYGON tables. Even importing the shapefile to Spatialite (using Spatialite-GUI) , unless you have at least one multipolygon feature, your table geometry will be set to the POLYGON type. And of course trying to copy\paste feature from it will give the same error.

My workaround is add a multipolygon feature to the shapefile before import it to SpatiaLite.

My question is, is there a way to change the triggers so that if the table is a MULTIPOLYGON type, it will accept also POLYGON Geometries? will that affect the rest of the Spatialite database?

Thank you very much.

Alexandre Neto

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

Hi Alexandre,

> QGIS do it with no problems with shapefiles, but gives some fight
> with
> SpatiaLite.
>

please note: the ESRI Shapefile format is really ancient, and was
invented well before any international standard specification
about Geometry.
so SHP doesn't make any difference between Linestrings and
MultiLinestrings, and the same is for Polygons and MultiPolygons;
but curiously enough, Points and MultiPoints are two completely
different things for SHP :-)

SpatiaLite (as many others Spatial DBMSes) makes a strong distinction
between Points and MultiPoints, Linestrings and MultiLinestrings,
Polygons and MultiPolygons; it's not at all an odd invention of our
own,
it's a strictly required design feature so to be standard compliant.

> As I see it, the problem lies in trying to copy POLYGON features to a
> MULTIPOLYGON tables. Even importing the shapefile to Spatialite
> (using
> Spatialite-GUI) , unless you have at least one multipolygon feature,
> your table geometry will be set to the POLYGON type. And of course

> trying to copypaste feature from it will give the same error.
>

if verified, this looks like a QGIS own failure; you can never insert
a MultiPolygon geometry (containing more elementary Polygons) into a
Polygon table (for obvious reasons).

but there is no real reason forbidding to insert a simple Polygon into
a
MultiPolygon table ... the sw agent is simply required to apply the
appropriate
type casting (promoting the Polygon to MultiPolygon), and that's all.
Just for my personal curiosity: have you ever tried the same operation
on behalf of PostGIS ? what happens in this second case ?

a) if the behavior is the same for PostGIS and SpatiaLite, than it
would
be obviously mean that QGIS has some idiosyncrasy.
b) if PostGIS and SpatiaLite behave differently, this could probably
mean
that the spatialite own data-provider has some problem requiring to
be fixed someway.

> My workaround is add a multipolygon feature to the shapefile before
> import it to SpatiaLite.
>

there is a (may be) simpler way allowing to get exactly the same
result:
a) import your shapefile, and check if it's of the Polygon type.
b) if yes, you can easily promote it to the MultiPolygon type by
immediately executing these (really tricky) SQL statements:

UPDATE geometry_columns SET type = 'MULTIPOLYGON'
WHERE f_table_name = 'my_table';
UPDATE my_table SET Geometry = CastToMultiPolygon(Geometry);

> My question is, is there a way to change the triggers so that if the
> table is a MULTIPOLYGON type, it will accept also POLYGON Geometries?
> will that affect the rest of the Spatialite database?
>

it's not a technical problem; if you carefully examine the internal
binary representation, you'll soon discover that a Polygon and a
MultiPolygon containing a single elementary Polygon are quite exactly
the same.
but they actually belong to two completely different Geometry Classes,
and the OGC-SFS standard strictly requires to consider the one and the
other as very different objects.
it's mainly a standard compliance question; by applying a patch like
this one we'll automatically break any standard requirement.
... and this doesn't look wise at all ;-)

bye Sandro

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

[Micha Silver]

ן¿½ן¿½ be obviously mean that QGIS has some idiosyncrasy.

b) if PostGIS and SpatiaLite behave differently, this could probably mean

ן¿½ן¿½ that the spatialite own data-provider has some problem requiring to
ן¿½ן¿½ be fixed someway.

OK, I'll take the bait.

First I made two test tables in spatialite - a POLYGON, and MULTIPOLYGON. Then with QGIS, I created a simple single polygon in the POLYGON table. Next I tried to copy/pasta as Alexandre did, straight into the MULTIPOLYGON table. As Alexandre says It doesn't work - constraint failed. Next I tried from Spatialite-gui INSERT INTO...SELECT FROM. This also, no surprise, failed unless I use CastToMultiPolygon().

Now for PostGIS, again two tables, one POLYGON, and one MULTIPOLYGON as above. I again made a feature in the POLYGON table, but this time I could copy/pasta right in QGIS from the POLYGON table directly to the MUTIPOLYGON table.

Best,
Micha

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

Hi Micha,

> OK, I'll take the bait.
>

honestly, I hoped someone did :-)

> First I made two test tables in spatialite - a POLYGON, and
> MULTIPOLYGON. Then with QGIS, I created a simple single polygon in
> the
> POLYGON table. Next I tried to copy/pasta as Alexandre did, straight
> into the MULTIPOLYGON table. As Alexandre says It doesn't work -
> constraint failed. Next I tried from Spatialite-gui INSERT
> INTO...SELECT FROM. This also, no surprise, failed unless I use
> CastToMultiPolygon().
>
> Now for PostGIS, again two tables, one POLYGON, and one MULTIPOLYGON
> as above. I again made a feature in the POLYGON table, but this time
> I
> could copy/pasta right in QGIS from the POLYGON table directly to the
> MUTIPOLYGON table.
>

all right; this definitively points our attention in the right
direction.
so in QGIS casting the geometry type as appropriate (if possible) is
obviously a task delegated to the data-provider itself.
the PostGIS one takes care of this, the SpatiaLite one doesn't.

good new: in the next few days I was already planning to update
the QGIS data-provider so to correctly support the latest v.4.0.0;
simply one more feature to insert into the shopping list.

Micha, many thanks ;-)

[Alexandre Neto]

Micha and Sandro,

I have just made my tests with Postgis (I did not saw Micha's post before). And I was also able to copy past Polygons into a MultiPolygons without problems.

I was thinking that something could be wrong with my tables, I'm glad it wasn't :-)

Hope it was useful for improving SpatiaLite.

I will keep working on my project and will workaround if needed, as probably When I pass the project to my colleagues the problem will be sorted.

Thank you both!

Alexandre Neto