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

GRASS GRASS user
, , ,
1

I would like to export a vector as new table in existing spatialite database.

According to the GDAL pages (http://www.gdal.org/drv_sqlite.html) the SQLite driver supports "creating new SQLite database files, or adding tables to existing ones", but I'm not sure if this applies to spatialite, or how to create a v.out.ogr command to achieve this.

Is it possible?

John

--
Dr John A Stevenson
RSE/Scottish Government Personal Research Fellow
(co-funded by Marie Curie Actions)
School of GeoSciences
The University of Edinburgh
Grant Institute
James Hutton Road
Edinburgh EH9 3FE
Scotland
PHONE: (+44) 131 650 7526
FAX: (+44) 131 668 3184
email: john.stevenson@ed.ac.uk
twitter: @volcan01010
web: http://www.geos.ed.ac.uk/homes/jsteven5
blog: http://all-geo.org/volcan01010
**ICELAND MOBILE: (+354) 8417106**

The University of Edinburgh is a charitable body, registered in
Scotland, with registration number SC005336.

2

On Fri, Sep 4, 2015 at 5:49 PM, John A Stevenson
<john.stevenson@ed.ac.uk> wrote:

I would like to export a vector as new table in existing spatialite
database.

According to the GDAL pages (http://www.gdal.org/drv_sqlite.html) the SQLite
driver supports "creating new SQLite database files, or adding tables to
existing ones", but I'm not sure if this applies to spatialite, or how to
create a v.out.ogr command to achieve this.

Is it possible?

I suppose so since v.out.ogr offers the flag

-a Append to existing layer
     A new OGR layer is created if it does not exist

https://grass.osgeo.org/grass70/manuals/v.out.ogr.html

Please let us know how it goes, then we may add that to the manual as
a new example.

Markus

3

On 04/09/15 18:38, Markus Neteler wrote:

On Fri, Sep 4, 2015 at 5:49 PM, John A Stevenson
<john.stevenson@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@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...".

However, if I export to a shapefile first then use ogr2ogr, it works:

v.out.ogr --overwrite input=firestations@PERMANENT output=nc_firestations.shp
v.out.ogr --overwrite input=geology@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'?

I'm on GRASS 7.0.1 from the UbuntuGIS unstable repository.

Cheers
John

--
Dr John A Stevenson
RSE/Scottish Government Personal Research Fellow
(co-funded by Marie Curie Actions)
School of GeoSciences
The University of Edinburgh
Grant Institute
James Hutton Road
Edinburgh EH9 3FE
Scotland
PHONE: (+44) 131 650 7526
FAX: (+44) 131 668 3184
email:john.stevenson@ed.ac.uk
twitter: @volcan01010
web:http://www.geos.ed.ac.uk/homes/jsteven5
blog:http://all-geo.org/volcan01010
**ICELAND MOBILE: (+354) 8417106**

The University of Edinburgh is a charitable body, registered in
Scotland, with registration number SC005336.

4

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@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@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@PERMANENT
output=nc_firestations.shp
v.out.ogr --overwrite input=geology@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