[Qgis-user] Copy / paste features to spatialite generates

[Micha Silver]

On 17/09/2012 11:07, Julian Bogdani wrote:
> Hi Josef
> and thank you for your reply. I am actually using Qspatialite plugin 
> to manage my spatialite layers. I checked the metadata of my database 
> and my geometry column is of type MULTISTRING.

Probably should be MULTILINESTRING ??

> I use Qspatialite to transform shapefiles in spatialite and the other 
> way, but I'm not sure how to use it to copy only some feature from a 
> shapefile to a spatialite table. I think I have to upload it to the 
> spatialite archive and then use sql to copy data from one table to 
> another.
>

As Josef mentioned, it's quite easy once you get the hang of it. A 
statement like:
INSERT INTO <target table> ( <target_col1>, <target_col2>,...)
SELECT <source_colA>, <source_colB>
FROM <source_table>
WHERE <condition>;
will copy selected features from one table to another.

> I'm afraid this is not so user-friendly, and my colleagues can hardly 
> handle data in this way. Maybe actually it's better to continue using 
> shapefiles as feature containers and wait until spatialite becomes a 
> little bit more handily.
>
>
> -----
> Julian Bogdani
> jbogdani at gmail.com <mailto:jbogdani at gmail.com>
> http://bradypus.net
>
>
>
>
>
> On 17 Sep 2012, at 2:01 , Josef K wrote:
>
>> Hi Julian
>>
>> I guess your problem is related to geometry types 
>> (http://www.gaia-gis.it/spatialite-2.4.0-4/spatialite-cookbook/html/new-geom.html). 
>> Try to define your spatialite geometries as multipoint, 
>> multilinestring and multipolygons. Then they will be more compatible 
>> with shapefiles and you may copy/paste in qgis.
>>
>> Although I would recommend that you tried using spatialite-gui (or 
>> perahaps qspatialite) instead. By those tools you can temporarily 
>> upload the shapefile to your database file and then use sql syntax 
>> 'insert into ... select ... ' the features you want into your db 
>> tables. Using sql syntax will give you more control of fields, data 
>> types etc.
>>
>> regards
>> Josef
>>
>>     Date: Sun, 16 Sep 2012 11:53:30 +0200
>>     From: Julian Bogdani <jbogdani at gmail.com <mailto:jbogdani at gmail.com>>
>>     To: qgis-user <qgis-user at lists.osgeo.org
>>     <mailto:qgis-user at lists.osgeo.org>>
>>     Subject: [Qgis-user] Copy / paste features to spatialite generates
>>             error
>>     Message-ID: <A7512D49-0F85-4CB5-A3D2-85DA4919E24C at gmail.com
>>     <mailto:A7512D49-0F85-4CB5-A3D2-85DA4919E24C at gmail.com>>
>>     Content-Type: text/plain; charset=us-ascii
>>
>>     Hello all,
>>     I've tried to add to an existing spatialite layer some features
>>     from another layer.
>>     The existing layer is a simple shapefile: I selected the features
>>     I wanted to copy and pasted to the spatialite layer. Everything
>>     was working file until I tried to save the changes. The following
>>     error appears:
>>
>>     Could not commit changes to layer Plan_gen
>>
>>     Errors: ERROR: 903 feature(s) not added.
>>     Provider errors:
>>     SQLite error: Plan_gen.Geometry violates Geometry constraint
>>     [geom-type or SRID not allowed]
>>     SQL: INSERT INTO "Plan_gen"("PKUID","Geometry","Layer","area")
>>     VALUES (NULL,GeomFromWKB(?, 32634),?,?)
>>
>>
>>     it states it might be a problem of geometry type or a SRID
>>     problem. All layers are in the same CRS (EPSG:32634), and all
>>     features, in both layers, are of type polyline.
>>
>>     Is it a spatialite bug? a qgis bug? or Am I missing anything?
>>
>>     I've had this issue with other data before. The solution I
>>     adopted to work around this was:
>>     1) save the spatialite layer as shapefile
>>     2) paste data into the newly created shapefile
>>     3) delete the spatialite table
>>     4) load in the spatialite DB the shapefile
>>
>>     This works, but it is too tricky for everyday use. I tried to
>>     convert all my shapefiles to spatialite, but the limitation
>>     spatilite still have in data structure manipulation
>>     (adding/removing/renaming fields, etc, a sqlite limit in fact)
>>     and now in content editing make me think that that was not a good
>>     idea. I can not switch this archives to postGIS yet, because of
>>     the actual work-flow organization of the project.
>>
>>     Any help or advice will be appreciated!
>>
>>
>>     -----
>>     Julian Bogdani
>>     jbogdani at gmail.com <mailto:jbogdani at gmail.com>
>>     http://bradypus.net <http://bradypus.net/>
>>
>
>
>
> This mail was received via Mail-SeCure System.
>
>
> _______________________________________________
> Qgis-user mailing list
> Qgis-user at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/qgis-user
>
> This mail was received via Mail-SeCure System.
>
>