Opened 10 years ago
Closed 9 years ago
#2300 closed defect (fixed)
v.out.ogr: spatialite support not working
Reported by: | hamish | Owned by: | |
---|---|---|---|
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)
Change History (16)
follow-up: 2 comment:1 by , 10 years ago
comment:2 by , 10 years ago
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 by , 10 years ago
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."
comment:4 by , 10 years ago
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 by , 9 years ago
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).
follow-up: 7 comment:6 by , 9 years ago
Milestone: | 6.4.4 → 7.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)
follow-up: 8 comment:7 by , 9 years ago
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.
by , 9 years ago
Attachment: | v.out.ogr.patch added |
---|
patch against release70 (based on Hamish' patch for grass6)
follow-up: 9 comment:8 by , 9 years ago
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?
follow-up: 10 comment:9 by , 9 years ago
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 by , 9 years ago
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...
by , 9 years ago
Attachment: | v_out_ogr_alltypes.patch added |
---|
patch for all types (points, lines, areas)
comment:11 by , 9 years ago
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.
follow-up: 13 comment:12 by , 9 years ago
I'd suggest to apply the change to trunk for easier testing.
(otherwise: https://grasswiki.osgeo.org/wiki/Patches#Downloading_patches_from_SVN )
follow-up: 14 comment:13 by , 9 years ago
comment:14 by , 9 years ago
Resolution: | → fixed |
---|---|
Status: | new → closed |
See also Debian bug 746566: gdal-bin: ogr2ogr fails to create SQLite db using SPATIALITE=YES.