[GRASS-dev] [GRASS GIS] #3563: v.in.ogr can't create tables in GRASS 7.4

GDAL · May 16, 2018, 1:58am

#3563: v.in.ogr can't create tables in GRASS 7.4
---------------------------+-------------------------
Reporter: pierreroudier | Owner: grass-dev@…
Type: defect | Status: new
Priority: normal | Milestone:
Component: Vector | Version: 7.4.0
Keywords: | CPU: Unspecified
Platform: Unspecified |
---------------------------+-------------------------
I don't seem to be able to import OGR sources (I tried both ESRI Shapefile
and GPKG sources):

{{{
GRASS 7.4.0 (nz_soil_ph):~ > v.in.ogr in=river-flows.gpkg out=rivers --o
Check if OGR layer <river_flows> contains polygons...
100%
WARNING: Vector map <rivers> already exists and will be overwritten
Creating attribute table for layer <river_flows>...
DBMI-SQLite driver error:
Error in sqlite3_prepare():
near "ORDER": syntax error

DBMI-SQLite driver error:
Error in sqlite3_prepare():
near "ORDER": syntax error

ERROR: Unable to create table: 'create table rivers (cat integer, ORDER
        double precision, NZREACH double precision, MALFCumecs double
        precision, MeanFlowCumecs double precision, Feb double precision,
        FRE3 double precision, SpecMeanFlowCumecs double precision,
        SpecMALFCumecs double precision, Shape_Length double precision)'

}}}

Here is more info about the dataset I'm using:

{{{
GRASS 7.4.0 (nz_soil_ph):~ > ogrinfo -al -so river-flows.gpkg
INFO: Open of `Dropbox/projects/nz-national-grid-ph/covariates/river-
flows.gpkg'
using driver `GPKG' successful.

Layer name: river_flows

[...]

Geometry: Multi Line String
Feature Count: 576277
Extent: (1090150.000000, 4748850.000000) - (2089110.000000,
6193370.000000)
Layer SRS WKT:
PROJCS["NZGD2000 / New Zealand Transverse Mercator 2000",
     GEOGCS["NZGD2000",
         DATUM["New_Zealand_Geodetic_Datum_2000",
             SPHEROID["GRS 1980",6378137,298.257222101,
                 AUTHORITY["EPSG","7019"]],
             TOWGS84[0,0,0,0,0,0,0],
             AUTHORITY["EPSG","6167"]],
         PRIMEM["Greenwich",0,
             AUTHORITY["EPSG","8901"]],
         UNIT["degree",0.0174532925199433,
             AUTHORITY["EPSG","9122"]],
         AUTHORITY["EPSG","4167"]],
     PROJECTION["Transverse_Mercator"],
     PARAMETER["latitude_of_origin",0],
     PARAMETER["central_meridian",173],
     PARAMETER["scale_factor",0.9996],
     PARAMETER["false_easting",1600000],
     PARAMETER["false_northing",10000000],
     UNIT["metre",1,
         AUTHORITY["EPSG","9001"]],
     AUTHORITY["EPSG","2193"]]
FID Column = fid
Geometry Column = geom
ORDER: Real (0.0)
NZREACH: Real (0.0)
MALFCumecs: Real (0.0)
MeanFlowCumecs: Real (0.0)
Feb: Real (0.0)
FRE3: Real (0.0)
SpecMeanFlowCumecs: Real (0.0)
SpecMALFCumecs: Real (0.0)
Shape_Length: Real (0.0)
}}}

This dataset is publicly available: https://data.mfe.govt.nz/layer/53309
-river-flows/

--
Ticket URL: <https://trac.osgeo.org/grass/ticket/3563>
GRASS GIS <https://grass.osgeo.org>

GDAL · May 16, 2018, 4:23am

#3563: v.in.ogr can't create tables in GRASS 7.4
----------------------------+-------------------------
  Reporter: pierreroudier | Owner: grass-dev@…
      Type: defect | Status: new
  Priority: normal | Milestone:
Component: Vector | Version: 7.4.0
Resolution: | Keywords:
       CPU: Unspecified | Platform: Unspecified
----------------------------+-------------------------

Comment (by hellik):

>ORDER: Real (0.0)

Order may be a SQL reserved word. try to rename it before

--
Ticket URL: <https://trac.osgeo.org/grass/ticket/3563#comment:1>
GRASS GIS <https://grass.osgeo.org>

GDAL · May 16, 2018, 9:35pm

#3563: v.in.ogr can't create tables in GRASS 7.4
----------------------------+-------------------------
  Reporter: pierreroudier | Owner: grass-dev@…
      Type: defect | Status: new
  Priority: normal | Milestone:
Component: Vector | Version: 7.4.0
Resolution: | Keywords:
       CPU: Unspecified | Platform: Unspecified
----------------------------+-------------------------

Comment (by pierreroudier):

Replying to [comment:1 hellik]:
> >ORDER: Real (0.0)
>
> Order may be a SQL reserved word. try to rename it before

Thanks -- that was indeed the problem! (This goes to show how limited my
knoywledge of SQL is).

It would maybe pay off to put a warning about this in the v.in.ogr
documentation for users who like me don't do SQL very well.

This is a patch that tries to address this:

{{{
--- v.in.ogr.html 2018-05-17 09:20:57.587213200 +1200
+++ v.in.ogr-pr.html 2018-05-17 09:30:55.141497148 +1200
@@ -411,19 +411,41 @@

<h2>ERROR MESSAGES</h2>

-<dl>
-<dt>DBMI-DBF driver error: SQL parser error: syntax error,
-unexpected DESC, expecting NAME processing 'DESC'</dt>
-<dd>indicates that a column name corresponds to a reserved SQL word
(here: 'DESC').
+<h3>SQL synthax errors</h3>
+
+Depending on the SQL driver, you can have errors such as:
+
+<div class="code"><pre>
+DBMI-SQLite driver error:
+Error in sqlite3_prepare():
+near "ORDER": syntax error
+</pre/</div>
+
+Or:
+
+<div class="code"><pre>
+DBMI-DBF driver error:
+SQL parser error:
+syntax error, unexpected DESC, expecting NAME processing 'DESC
+</pre/</div>
+
+This indicates that a column name in the input dataset corresponds to a
reserved SQL word (here: 'ORDER' and 'DESC' respectively).
  A different column name should be used. The <b>columns</b> parameter can
be used
-to assign different column names on the fly.</dd>
-<dt>Projection of dataset does not appear to match the current
location.</dt>
-<dd>You need to create a location whose projection matches the data
+to assign different column names on the fly.
+
+See a list of SQL reserved words for SQLite (the default driver) <a
href='https://www.sqlite.org/lang_keywords.html’>here</a>.
+
+<h3>Projection errors</h3>
+
+<div class="code"><pre>
+Projection of dataset does not appear to match the current location.
+</pre/</div>
+
+You need to create a location whose projection matches the data
  you wish to import. Try using <b>location</b> parameter to create a
  new location based upon the projection information in the file. If
  desired, you can then re-project it to another location
-with <em><a href="v.proj.html">v.proj</a></em>.</dd>
-</dl>
+with <em><a href="v.proj.html">v.proj</a></em>.

<h2>REFERENCES</h2>

}}}

--
Ticket URL: <https://trac.osgeo.org/grass/ticket/3563#comment:2>
GRASS GIS <https://grass.osgeo.org>

GDAL · May 16, 2018, 9:36pm

#3563: v.in.ogr can't create tables in GRASS 7.4
----------------------------+-------------------------
  Reporter: pierreroudier | Owner: grass-dev@…
      Type: defect | Status: closed
  Priority: normal | Milestone:
Component: Vector | Version: 7.4.0
Resolution: fixed | Keywords:
       CPU: Unspecified | Platform: Unspecified
----------------------------+-------------------------
Changes (by pierreroudier):

* status: new => closed
* resolution: => fixed

--
Ticket URL: <https://trac.osgeo.org/grass/ticket/3563#comment:3>
GRASS GIS <https://grass.osgeo.org>

GDAL · May 17, 2018, 8:00am

#3563: v.in.ogr can't create tables in GRASS 7.4
----------------------------+-------------------------
  Reporter: pierreroudier | Owner: grass-dev@…
      Type: defect | Status: closed
  Priority: normal | Milestone:
Component: Vector | Version: 7.4.0
Resolution: fixed | Keywords:
       CPU: Unspecified | Platform: Unspecified
----------------------------+-------------------------

Comment (by neteler):

Reopening: please add the patch as attachment, thanks

(the ticket may be closed once the patch is applied, like this or in
modified form and backported if needed)

--
Ticket URL: <https://trac.osgeo.org/grass/ticket/3563#comment:4>
GRASS GIS <https://grass.osgeo.org>

GDAL · May 17, 2018, 8:00am

#3563: v.in.ogr can't create tables in GRASS 7.4
----------------------------+-------------------------
  Reporter: pierreroudier | Owner: grass-dev@…
      Type: defect | Status: reopened
  Priority: normal | Milestone: 7.4.1
Component: Vector | Version: 7.4.0
Resolution: | Keywords: v.in.ogr
       CPU: Unspecified | Platform: Unspecified
----------------------------+-------------------------
Changes (by neteler):

* status: closed => reopened
* keywords: => v.in.ogr
* resolution: fixed =>
* milestone: => 7.4.1

--
Ticket URL: <https://trac.osgeo.org/grass/ticket/3563#comment:5>
GRASS GIS <https://grass.osgeo.org>

GDAL · May 17, 2018, 8:33am

#3563: v.in.ogr can't create tables in GRASS 7.4
----------------------------+-------------------------
  Reporter: pierreroudier | Owner: grass-dev@…
      Type: defect | Status: reopened
  Priority: normal | Milestone: 7.4.1
Component: Vector | Version: 7.4.0
Resolution: | Keywords: v.in.ogr
       CPU: Unspecified | Platform: Unspecified
----------------------------+-------------------------

Comment (by martinl):

G74:v.in.ogr could be more clever about such issues. Rename column to eg.
ORDER_, print warning and continue.

--
Ticket URL: <https://trac.osgeo.org/grass/ticket/3563#comment:6>
GRASS GIS <https://grass.osgeo.org>

GDAL · May 17, 2018, 9:52pm

#3563: v.in.ogr can't create tables in GRASS 7.4
----------------------------+-------------------------
  Reporter: pierreroudier | Owner: grass-dev@…
      Type: defect | Status: reopened
  Priority: normal | Milestone: 7.4.1
Component: Vector | Version: 7.4.0
Resolution: | Keywords: v.in.ogr
       CPU: Unspecified | Platform: Unspecified
----------------------------+-------------------------
Changes (by pierreroudier):

* Attachment "v.in.ogr.html.patch" added.

Patch for the v.in.ogr documentation

--
Ticket URL: <https://trac.osgeo.org/grass/ticket/3563>
GRASS GIS <https://grass.osgeo.org>

GDAL · May 17, 2018, 9:56pm

#3563: v.in.ogr can't create tables in GRASS 7.4
----------------------------+-------------------------
  Reporter: pierreroudier | Owner: grass-dev@…
      Type: defect | Status: reopened
  Priority: normal | Milestone: 7.4.1
Component: Vector | Version: 7.4.0
Resolution: | Keywords: v.in.ogr
       CPU: Unspecified | Platform: Unspecified
----------------------------+-------------------------

Comment (by pierreroudier):

Replying to [comment:6 martinl]:
> G74:v.in.ogr could be more clever about such issues. Rename column to
eg. ORDER_, print warning and continue.

That would be a great feature -- with maybe a flag to control this
behaviour: when using a flag such as say `-s`, `v.in.ogr` would add an
underscore to any reserved SQL word rather than failing.

In SQLite you could also bracket those words, but this does not look like
standard SQL (source: https://www.sqlite.org/lang_keywords.html)

--
Ticket URL: <https://trac.osgeo.org/grass/ticket/3563#comment:7>
GRASS GIS <https://grass.osgeo.org>

GDAL · May 18, 2018, 5:35am

#3563: v.in.ogr can't create tables in GRASS 7.4
----------------------------+-------------------------
  Reporter: pierreroudier | Owner: grass-dev@…
      Type: defect | Status: reopened
  Priority: normal | Milestone: 7.4.1
Component: Vector | Version: 7.4.0
Resolution: | Keywords: v.in.ogr
       CPU: Unspecified | Platform: Unspecified
----------------------------+-------------------------

Comment (by sbl):

Quoting identifiers (as also described in the link you provided) should
work...

However, this would require significant changes, as modules (subsequent to
v.in.ogr) are not necessarily able to handle quoted identifiers.

In addition, this SQL standard is not implemented in all DBMS equally.
E.g. MySQL uses backticks for quoting (instead of quotes).