On 17 Sep 2012, at 20:37 , Micha Silver wrote:
> 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. Thank you Micha, It seems I have to better study Spatialite :) Just one more question: spatialite-qui is the same thing as QspatiaLite plugin? If so I'm not able to find this feature :( > > >> 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 >>>> jbogd...@gmail.com <mailto:jbogd...@gmail.com> <mailto:jbogd...@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 <jbogd...@gmail.com <mailto:jbogd...@gmail.com> >>>>> <mailto:jbogd...@gmail.com>> >>>>> To: qgis-user <qgis-user@lists.osgeo.org >>>>> <mailto:qgis-user@lists.osgeo.org> >>>>> <mailto:qgis-user@lists.osgeo.org>> >>>>> Subject: [Qgis-user] Copy / paste features to spatialite generates >>>>> error >>>>> Message-ID: <a7512d49-0f85-4cb5-a3d2-85da4919e...@gmail.com >>>>> <mailto:a7512d49-0f85-4cb5-a3d2-85da4919e...@gmail.com> >>>>> <mailto:a7512d49-0f85-4cb5-a3d2-85da4919e...@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 >>>>> jbogd...@gmail.com <mailto:jbogd...@gmail.com> <mailto:jbogd...@gmail.com> >>>>> http://bradypus.net <http://bradypus.net/> >>>>> >>>> >>>> >>>> >>>> This mail was received via Mail-SeCure System. >>>> >>>> >>>> _______________________________________________ >>>> Qgis-user mailing list >>>> Qgis-user@lists.osgeo.org <mailto:Qgis-user@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. >
_______________________________________________ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
