Opened 7 years ago

Closed 6 years ago

#2300 closed defect (fixed)

v.out.ogr: spatialite support not working

Reported by: hamish Owned by: grass-dev@…
Priority: critical Milestone: 7.0.3
Component: Vector Version: svn-trunk
Keywords: v.out.ogr, spatialite Cc:
CPU: x86-64 Platform: Linux

Description

Hi, as noted on the grass-users ML,

http://lists.osgeo.org/pipermail/grass-user/2014-May/070328.html

I'm not able to get Spatialite export working from v.out.ogr. Seen on both GRASS 6.x and latest trunk for debian/wheezy.

Loading the mapset into the qgis grass toolbox on the same machine and saving from there works, so I think the system libraries are all ok (gdal 1.9.0).

for my test I'm trying the usgsgages vector map from the North Carolina dataset. I've tried roadsmajor type=line and census_wake2000 type=area and they give the same trouble.

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

the error, reported for each point is:

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

In trunk it seems to be happening on line 128 of v.in.ogr's export_lines.c (~788 in main.c for devbr6):

  OGR_L_CreateFeature(Ogr_layer, Ogr_feature);

api ref:

http://www.gdal.org/ogr/ogr__api_8h.html#a301d319111285a47fe6cda6e079214f8

MarkusN pulled up some hints from the gdal ML:

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

where Even wrote:

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

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?

the code seems suitably pointy, even if it is embedded in the "lines" part of the code, but I wonder if 2D vs 3D definition is causing trouble? Converting usgsgages to a 3D points map didn't help.

    else if ((type == GV_POINT) ||
	     ((type == GV_CENTROID) && (otype & GV_CENTROID))) {

	Ogr_geometry = OGR_G_CreateGeometry(wkbPoint);
	OGR_G_AddPoint(Ogr_geometry, Points->x[0], Points->y[0],
		       Points->z[0]);
    }

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

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.

thanks, Hamish

Attachments (2)

v.out.ogr.patch (885 bytes) - added by mlennert 6 years ago.
patch against release70 (based on Hamish' patch for grass6)
v_out_ogr_alltypes.patch (2.4 KB) - added by mlennert 6 years ago.
patch for all types (points, lines, areas)

Download all attachments as: .zip

Change History (16)

comment:1 Changed 7 years ago by mlennert

comment:2 in reply to:  1 Changed 7 years ago by hamish

Replying to mlennert:

See also Debian bug 746566: gdal-bin: ogr2ogr fails to create SQLite db using SPATIALITE=YES.

Hi,

I don't think the two are related, since the export fails with gdal 1.9.0 and qgis with the same libraries works fine. I more suspect the 2D vs. 3D geometry struct issue.

thanks, Hamish

comment:3 Changed 7 years ago by hamish

here is another possibility,

"When creating a spatial table and recovering the geometry using the RecoverGeometryColumn function if you don't specify the geometry column as lower case you end up getting a 'violates Geometry constraint [geom-type or SRID not allowed]' error."

http://stackoverflow.com/questions/15203403/spatialite-v4-0-0-recovergeometrycolumn-violates-geometry-constraint-geom-type

comment:4 in reply to:  description Changed 7 years ago by hamish

Replying to hamish:

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?

the code seems suitably pointy, even if it is embedded in the "lines" part of the code, but I wonder if 2D vs 3D definition is causing trouble?

right, that's it. The following patch vs. devbr6 makes it work:

Index: main.c
===================================================================
--- main.c	(revision 59151)
+++ main.c	(working copy)
@@ -753,9 +753,13 @@
 	    }
 	    else if ((type == GV_POINT) ||
 		     ((type == GV_CENTROID) && (otype & GV_CENTROID))) {
+
 		Ogr_geometry = OGR_G_CreateGeometry(wkbPoint);
-		OGR_G_AddPoint(Ogr_geometry, Points->x[0], Points->y[0],
-			       Points->z[0]);
+		if (Vect_is_3d(&In))
+		    OGR_G_AddPoint(Ogr_geometry, Points->x[0], Points->y[0],
+				   Points->z[0]);
+		else
+		    OGR_G_AddPoint_2D(Ogr_geometry, Points->x[0], Points->y[0]);
 	    }
 	    else {		/* GV_LINE or GV_BOUNDARY */
 

but the v.out.ogr z-flag logic might require some modifications to that. (the force-2D flag in trunk makes a bit more sense, but also would need a different check for the flag)

Converting usgsgages to a 3D points map didn't help.

but I don't think I used the -z flag... ? All ways need to be tested, and other geometry types also need to receive the same 2D vs 3D treatment I guess.

Hamish

comment:5 Changed 6 years ago by neteler

It also fails in GRASS 7:

GRASS 7.0.1svn (nc_spm_08_grass7):~ > v.out.ogr input=usgsgages output=usgsgages.sqlite format=SQLite type=point dsco='SPATIALITE=yes' 
Exporting 433 features...
ERROR 1: sqlite3_step() failed:
  usgsgages.GEOMETRY violates Geometry constraint [geom-type or SRID not allowed] (19)
...
ERROR 1: sqlite3_step() failed:
  usgsgages.GEOMETRY violates Geometry constraint [geom-type or SRID not allowed] (19)
 100%
ERROR 1: sqlite3_step() failed:
  usgsgages.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. 433 features (Point type) written to <usgsgages>
(SQLite format).

comment:6 Changed 6 years ago by neteler

Milestone: 6.4.47.0.3

Testing again, the "GEOMETRY violates Geometry constraint" error is gone but there is still an issue:

GRASS 7.0.3svn (nc_spm_08_grass7):~ > 

v.info usgsgages | grep 3D
 |   Map is 3D:              No |

v.out.ogr input=usgsgages output=usgsgages.sqlite format=SQLite type=point dsco='SPATIALITE=yes' 
Exporting 433 features...
ERROR 1: Cannot insert feature with geometry of type POINTZ in column GEOMETRY. Type POINT expected
ERROR 1: Cannot insert feature with geometry of type POINTZ in column GEOMETRY. Type POINT expected
ERROR 1: Cannot insert feature with geometry of type POINTZ in column GEOMETRY. Type POINT expected
...
ERROR 1: Cannot insert feature with geometry of type POINTZ in column GEOMETRY. Type POINT expected
ERROR 1: Cannot insert feature with geometry of type POINTZ in column GEOMETRY. Type POINT expected
ERROR 1: Cannot insert feature with geometry of type POINTZ in column GEOMETRY. Type POINT expected
 100%
ERROR 1: Cannot insert feature with geometry of type POINTZ in column GEOMETRY. Type POINT expected
v.out.ogr complete. 433 features (Point type) written to <usgsgages>
(SQLite format).

While the resulting file is of 5.8MB, it does not contain geometry, likely due to above error:

ogrinfo -so usgsgages.sqlite usgsgages
INFO: Open of `usgsgages.sqlite'
      using driver `SQLite' successful.

Layer name: usgsgages
Geometry: Point
Feature Count: 0
Layer SRS WKT:
PROJCS["Lambert Conformal Conic",
    GEOGCS["grs80",
        DATUM["North_American_Datum_1983",
            SPHEROID["Geodetic_Reference_System_1980",6378137,298.257222101]],
        PRIMEM["Greenwich",0],
        UNIT["degree",0.0174532925199433],
        AUTHORITY["EPSG","4269"]],
    PROJECTION["Lambert_Conformal_Conic_2SP"],
    PARAMETER["standard_parallel_1",36.16666666666666],
    PARAMETER["standard_parallel_2",34.33333333333334],
    PARAMETER["latitude_of_origin",33.75],
    PARAMETER["central_meridian",-79],
    PARAMETER["false_easting",609601.22],
    PARAMETER["false_northing",0],
    UNIT["Meter",1]]
FID Column = ogc_fid
Geometry Column = GEOMETRY
cat: Integer (0.0)
objectid: Integer (0.0)
site_id: String (0.0)
site_name: String (0.0)
telemetry: String (0.0)
stream_flo: String (0.0)
stage_only: String (0.0)
qw_samples: String (0.0)
wq_monitor: String (0.0)
rainfall: String (0.0)
met: String (0.0)
office: String (0.0)
hucode: String (0.0)
county: String (0.0)

comment:7 in reply to:  6 ; Changed 6 years ago by mlennert

Replying to neteler:

Testing again, the "GEOMETRY violates Geometry constraint" error is gone

With a completely fresh release70 checkout, I still get the error:

v.out.ogr input=usgsgages output=usgsgages.sqlite format=SQLite type=point dsco='SPATIALITE=yes'
Exporting 433 features...
ERROR 1: sqlite3_step() failed:
  usgsgages.GEOMETRY violates Geometry constraint [geom-type or SRID not allowed] (19)
ERROR 1: sqlite3_step() failed:
  usgsgages.GEOMETRY violates Geometry constraint [geom-type or SRID not allowed] (19)
[...]

Did you apply Hamish' patch locally ?

Or maybe a question of spatialite version ? I have 4.3.0. with SQLite version 3.9.2.

The 'POINTZ' error is weird, as AFAIK POINTZ is a response to the specific shapefile driver option 'SHPT=type' and I don't know how this gets chosen for the SQLite format.

Changed 6 years ago by mlennert

Attachment: v.out.ogr.patch added

patch against release70 (based on Hamish' patch for grass6)

comment:8 in reply to:  7 ; Changed 6 years ago by neteler

Replying to mlennert:

Did you apply Hamish' patch locally ?

No, I didn't.

Or maybe a question of spatialite version ? I have 4.3.0. with SQLite version 3.9.2.

I use

[neteler@pgis_north grass70]$ rpm -qa | grep sqlite sqlite-devel-3.9.0-1.fc23.x86_64 sqlite-3.9.0-1.fc23.x86_64

[neteler@pgis_north grass70]$ rpm -qa | grep spatialite libspatialite-4.3.0a-1.fc23.x86_64

The 'POINTZ' error is weird, as AFAIK POINTZ is a response to the specific shapefile driver option 'SHPT=type' and I don't know how this gets chosen for the SQLite format.

Maybe the wrong driver is picked up?

comment:9 in reply to:  8 ; Changed 6 years ago by mlennert

Replying to neteler:

Replying to mlennert:

Did you apply Hamish' patch locally ?

No, I didn't.

Or maybe a question of spatialite version ? I have 4.3.0. with SQLite version 3.9.2.

I use

[neteler@pgis_north grass70]$ rpm -qa | grep sqlite sqlite-devel-3.9.0-1.fc23.x86_64 sqlite-3.9.0-1.fc23.x86_64

[neteler@pgis_north grass70]$ rpm -qa | grep spatialite libspatialite-4.3.0a-1.fc23.x86_64

The 'POINTZ' error is weird, as AFAIK POINTZ is a response to the specific shapefile driver option 'SHPT=type' and I don't know how this gets chosen for the SQLite format.

Maybe the wrong driver is picked up?

But why ? And why with you and not for me ?

I've just attached a patch that implements Hamish' solution for release70. With this I can export the points without error.

But as Hamish mentioned, this is only a partial solution and the module probably needs similar fixes for other geometry types.

comment:10 in reply to:  9 Changed 6 years ago by neteler

Replying to mlennert:

I've just attached a patch that implements Hamish' solution for release70. With this I can export the points without error.

Success!

GRASS 7.0.3svn (nc_spm_08_grass7):~ > v.out.ogr input=usgsgages output=usgsgages.sqlite format=SQLite type=point dsco='SPATIALITE=yes'
Exporting 433 features...
 100%
v.out.ogr complete. 433 features (Point type) written to <usgsgages>
(SQLite format).
GRASS 7.0.3svn (nc_spm_08_grass7):~ > ogrinfo -so usgsgages.sqlite usgsgages
INFO: Open of `usgsgages.sqlite'
      using driver `SQLite' successful.

Layer name: usgsgages
Geometry: Point
Feature Count: 433
Extent: (153611.375000, 38448.441406) - (898629.937500, 407620.562500)
Layer SRS WKT:
PROJCS["Lambert Conformal Conic",
    GEOGCS["grs80",
        DATUM["North_American_Datum_1983",
            SPHEROID["Geodetic_Reference_System_1980",6378137,298.257222101]],
        PRIMEM["Greenwich",0],
        UNIT["degree",0.0174532925199433],
        AUTHORITY["EPSG","4269"]],
    PROJECTION["Lambert_Conformal_Conic_2SP"],
    PARAMETER["standard_parallel_1",36.16666666666666],
    PARAMETER["standard_parallel_2",34.33333333333334],
    PARAMETER["latitude_of_origin",33.75],
    PARAMETER["central_meridian",-79],
    PARAMETER["false_easting",609601.22],
    PARAMETER["false_northing",0],
    UNIT["Meter",1]]
FID Column = ogc_fid
Geometry Column = GEOMETRY
cat: Integer (0.0)
objectid: Integer (0.0)
site_id: String (0.0)
site_name: String (0.0)
telemetry: String (0.0)
stream_flo: String (0.0)
stage_only: String (0.0)
qw_samples: String (0.0)
wq_monitor: String (0.0)
rainfall: String (0.0)
met: String (0.0)
office: String (0.0)
hucode: String (0.0)
county: String (0.0)

But as Hamish mentioned, this is only a partial solution and the module probably needs similar fixes for other geometry types.

At least one would be fixed...

Changed 6 years ago by mlennert

Attachment: v_out_ogr_alltypes.patch added

patch for all types (points, lines, areas)

comment:11 Changed 6 years ago by mlennert

Please try the new patch ( v_out_ogr_alltypes.patch) which covers points, lines and areas. With this patch applied, I can do each of the following which throws an error without the patch:

v.out.ogr input=usgsgages output=usgsgages.sqlite format=SQLite type=point dsco='SPATIALITE=yes'
v.out.ogr input=roadsmajor output=roadsmajor.sqlite format=SQLite type=line dsco='SPATIALITE=yes'
v.out.ogr input=urbanarea output=urbanarea.sqlite format=SQLite type=area dsco='SPATIALITE=yes'

I also tested exporting each of these maps to shapefile.

The patch was made with release70, but applies to trunk as well.

I would appreciate some testing and a critical eye from someone a bit knowledgeable in OGR/v.out.ogr to make sure that this does not break anything in such an important module.

comment:12 Changed 6 years ago by neteler

I'd suggest to apply the change to trunk for easier testing.

(otherwise: https://grasswiki.osgeo.org/wiki/Patches#Downloading_patches_from_SVN )

comment:13 in reply to:  12 ; Changed 6 years ago by mlennert

Replying to neteler:

I'd suggest to apply the change to trunk for easier testing.

Done in r67451.

comment:14 in reply to:  13 Changed 6 years ago by martinl

Resolution: fixed
Status: newclosed

Replying to mlennert:

Done in r67451.

Tested and backported in r67569. Closing.

Note: See TracTickets for help on using tickets.