[GRASS-user] trouble exporting to spatialite

GRASS GRASS user
, , ,
1

Hi,

I'm have trouble exporting in spatialite format in GRASS 6, wondering
if anyone else has been able to have it work? Seems like a common
task.

using the SQLite driver with v.out.ogr works ok, but when I
add the spatialite option as described in the OGR format page
it gives an error for every data point:

# NC08 dataset
v.out.ogr in=usgsgages dsn=usgsgages.sqlite \
  format=SQLite type=point dsco='SPATIALITE=yes'

the error message is:

ERROR 1: sqlite3_step() failed:
  usgsgages.GEOMETRY violates Geometry constraint [geom-type or SRID
not allowed] (19)

and looking inside the resulting file with sqlitebrowser it seems no
points are uploaded to the table.

Installed GDAL is 1.9.0 so should be new enough. (debian/wheezy)

On the same system with self-compiled qgis 2.2* I can use the GRASS
toolbox to load in the grass map and right click it in the layer list
to 'Save As..' a seemingly-good copy.

any ideas?

[*] if anyone wants qgis 2.2 packages for amd64 deb/stable, just ask.

Ben had trouble with sqlite3_step() a while ago, but it's not quite
the same error:
http://thread.gmane.org/gmane.comp.gis.grass.devel/35027/

regards,

--
Hamish <hamish.webmail@gmail.com>

2

On Mon, May 19, 2014 at 7:50 AM, Hamish <hamish.webmail@gmail.com> wrote:

Hi,

I'm have trouble exporting in spatialite format in GRASS 6, wondering
if anyone else has been able to have it work? Seems like a common
task.

using the SQLite driver with v.out.ogr works ok, but when I
add the spatialite option as described in the OGR format page
it gives an error for every data point:

# NC08 dataset
v.out.ogr in=usgsgages dsn=usgsgages.sqlite \
  format=SQLite type=point dsco='SPATIALITE=yes'

the error message is:

ERROR 1: sqlite3_step() failed:
  usgsgages.GEOMETRY violates Geometry constraint [geom-type or SRID
not allowed] (19)

and looking inside the resulting file with sqlitebrowser it seems no
points are uploaded to the table.

See
http://lists.osgeo.org/pipermail/gdal-dev/2013-May/036148.html
http://lists.osgeo.org/pipermail/gdal-dev/2013-May/036152.html

...perhaps a pointer.

Markus

3

On Mon, 19 May 2014 21:35:43 +0200
Markus Neteler <neteler@osgeo.org> wrote:

Hamish wrote:

> I'm have trouble exporting in spatialite format in GRASS 6,
> wondering if anyone else has been able to have it work? Seems like
> a common task.
>
> using the SQLite driver with v.out.ogr works ok, but when I
> add the spatialite option as described in the OGR format page
> it gives an error for every data point:
>
> # NC08 dataset
> v.out.ogr in=usgsgages dsn=usgsgages.sqlite \
> format=SQLite type=point dsco='SPATIALITE=yes'
>
> the error message is:
>
> ERROR 1: sqlite3_step() failed:
> usgsgages.GEOMETRY violates Geometry constraint [geom-type or SRID
> not allowed] (19)
>
> and looking inside the resulting file with sqlitebrowser it seems no
> points are uploaded to the table.

MarkusN:

See
http://lists.osgeo.org/pipermail/gdal-dev/2013-May/036148.html
http://lists.osgeo.org/pipermail/gdal-dev/2013-May/036152.html

...perhaps a pointer.

yes, thanks, that looks quite helpful.

Even wrote on gdal-dev:

The driver takes responsibility of assigning the SRID automatically
from the layer SRS. So the error is likely due to an attempt of
inserting a geometry whose type doesn't match the layer geometry
type. Spatialite is really strict on that: POLYGON != MULTIPOLYGON,
and (perhaps I'm not sure) 2D != 2.5D

Here I'm using type=point, and the usgsgages vector map is 2D, so
seems like the most simple case.

from the partially created sqlite file created by v.out.ogr here's the
creation log:

$ echo "SELECT * from spatialite_history;" | sqlite3 usgsgages4.sqlite
1|spatial_ref_sys||table successfully created|2014-05-19 23:14:31|
1|3.7.13|3.0.0-beta
2|geometry_columns||table successfully created|2014-05-19 23:14:31|
2|3.7.13|3.0.0-beta
3|spatial_ref_sys||table successfully populated|2014-05-19 23:14:32|
3|3.7.13|3.0.0-beta
4|usgsgages|GEOMETRY|Geometry [POINT,XY,SRID=40004] successfully
4|usgsgages|GEOMETRY|created|2014-05-19 23:14:33|3.7.13|3.0.0-beta
5|usgsgages|GEOMETRY|R*Tree Spatial Index successfully created|
2014-05-19 23:14:34|3.7.13|3.0.0-beta

In the gdal-dev thread the solution was to pick the correct one
of OGR_G_SetPoint() vs. OGR_G_SetPoint_2D(), maybe that helps here?

Same trouble with
census_wake2000, type=area
roadsmajor, type=line

One thing I notice is that SRID 40004 is a custom one added at the
end of the spatial_ref_sys table, after four other custom ones from
Italy. I'm not sure if that is relevant or not.

Anyway, I'll take this to a ticket.

thanks,

--
Hamish <hamish.webmail@gmail.com>