[GRASS-user] Is it possible to export a vector as new table in existing spatialite database?

[Moritz Lennert]

On 06/09/15 23:43, John A Stevenson wrote:
> On 04/09/15 18:38, Markus Neteler wrote:
>> On Fri, Sep 4, 2015 at 5:49 PM, John A Stevenson
>> <john.stevenson at ed.ac.uk>  wrote:
>>> I would like to export a vector as new table in existing spatialite
>>> database.
>> Please let us know how it goes, then we may add that to the manual as
>> a new example.
>>
>> Markus
> When I tried exporting to Spatialite from GRASS using v.out.ogr, I got
> an error, and although the table was created, the geometry didn't work.
> (Example using nc_spm_08_grass7).
>
> v.out.ogr --overwrite input=firestations at PERMANENT output=nc.sqlite
> format=SQLite dsco=SPATIALITE=YES
>
> Gives:
>
> -----------------------
>
> Exporting 71 features...
> ERROR 1: sqlite3_step() failed:
>     firestations.GEOMETRY violates Geometry constraint [geom-
> type or SRID not allowed] (19)
> ERROR 1: sqlite3_step() failed:
>     firestations.GEOMETRY violates Geometry constraint [geom-
> type or SRID not allowed] (19)
>
> <... lots of these ...>
>
> ERROR 1: sqlite3_step() failed:
>     firestations.GEOMETRY violates Geometry constraint [geom-
> type or SRID not allowed] (19)
> ERROR 1: COMMIT transaction failed: cannot commit - no
> transaction is active
> v.out.ogr complete. 71 features (Point type) written to <firestations>
> (SQLite format).
> (Sun Sep  6 22:07:37 2015) Command finished (3 sec)
>
> -----------------------
>
> The database is created, including a table with a GEOMETRY column, but
> when I try to view it with the Map Preview in the spatialite_gui
> software, it says "This Column doesn't contains any Geometry: sorry...".

For me the table is created, but it is empty as the transaction failed. 
Maybe this is why you get the error message about missing geometry.

>
> However, if I export to a shapefile first then use ogr2ogr, it works:
>
> v.out.ogr --overwrite input=firestations at PERMANENT
> output=nc_firestations.shp
> v.out.ogr --overwrite input=geology at PERMANENT output=nc_geology.shp
> ogr2ogr -f sqlite -dsco SPATIALITE=YES nc.sqlite nc_firestations.shp
> ogr2ogr -f sqlite -dsco SPATIALITE=YES nc.sqlite nc_geology.shp -append
>
>
> I'm not sure what is causing the error, but a Google search brings up a
> ticket for an already existing bug:
>
> https://trac.osgeo.org/grass/ticket/2300
>
> If not the case of the GEOMETRY column, then perhaps it is the case of
> the geometry type e.g. GRASS passes 'Point' but Spatialite wants 'POINT'?

The ticket is the right one, but the issue is not the name of the 
geometry type, but apparently that if data is 2D this has to be made 
explicit for SpatiaLite. Hamish has already outlined the solution in the 
ticket, but this needs a careful implementation to make all other 
options (notably the -2 option) work correctly. So, it's "just" a matter 
of finding developer time and motivation to do this...

Moritz