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

Micha Silver micha at arava.co.il
Mon Sep 17 11:37:44 PDT 2012 

Hi Julian:

On 17/09/2012 13:22, Julian Bogdani wrote:
>
>
> 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
>

First, don't ignore the option (in spatialite-gui) to connect to a 
"Virtual shapefile". If you only need to copy some features into a 
spatialite table, this is a good way to proceed. After copying the 
features, you can DROP the virtual table.


> 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).

Regarding the arbitrary selection, if you need to *visually* select some 
features, then migrate them to spatialite, I don't see any way to do 
this automatically.  The only idea that comes to mind is, after 
selecting the features, add a new attribute column with some value that 
identifies those features, then connect to the shapefile (virtual 
shapefile in spatialite), and to the INSERT INTO...SELECT 
FROM...WHERE... as above.

Hopefully soon the ogr2ogr driver in QGIS will be improved to allow 
"Save as..." directly to an existing spatialite database. Currently when 
you use "Save as..." or "Save selection as..." the target sqlite 
database is *overwritten*. The command line ogr2ogr has the '-append' 
and '-nln' (new layer name) parameters to migrate i.e. a shapefile to an 
existing spatialite DB. But it doesn't seem to be available yet in QGIS :-(

Micha


> 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> 
>>> <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> <mailto:jbogdani at gmail.com>>
>>>>    To: qgis-user <qgis-user at lists.osgeo.org 
>>>> <mailto: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>
>>>>    <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> 
>>>> <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 <mailto:Qgis-user at lists.osgeo.org>
>>> http://lists.osgeo.org/mailman/listinfo/qgis-user
>>>
>>> This mail was received via Mail-SeCure System.
>>>
>>>
>>
>>
>
>
> This mail was received via Mail-SeCure System.

More information about the Qgis-user mailing list