[Qgis-user] Copy / paste features to spatialite generates
Julian Bogdani
jbogdani at gmail.com
Mon Sep 17 03:22:13 PDT 2012
On 17 Sep 2012, at 12:04 , Micha Silver wrote:
> 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 ??
Sorry MULTISTRING is a typo! My column's type is 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.
So, to sum up a possible work flow could be the following:
1) Use Qspatiaite to migrate all tables in a spatialite db
2) Write down the sql query to copy features from one table to another
3) Eventually delete the source table, if not needed
Point 2 makes me think: is there a sql statement to get only selected rows? I don't think so because selection is a QGIS feature and SQL statements just involves origin tables, am I wrong?. In some case it's not possible to get the features you need by SQL statements, but just by selecting them on map.I think about arbitrary selections, not depending on regular patterns (attributes).
Or the solution might be to import into spatialite only the features I need. So in point 1 I would have:
1a) select features to copy using map view in qgis
1b) migrate only selected features to spatialite
Do you think reporting this copy/paste problem as subject for further enhancement could be useful, at least as far as it concerns spatialite?
Thank you
>
>> 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.
>>
>>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20120917/50df823a/attachment.html>
More information about the Qgis-user
mailing list