Opened 23 months ago

Last modified 12 months ago

#3563 reopened enhancement

v.in.ogr can't create tables in GRASS 7.4

Reported by: pierreroudier Owned by: grass-dev@…
Priority: normal Milestone: 7.6.2
Component: Vector Version: svn-trunk
Keywords: v.in.ogr Cc:
CPU: Unspecified Platform: Unspecified

Description

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/

Attachments (1)

v.in.ogr.html.patch (1.8 KB) - added by pierreroudier 23 months ago.
Patch for the v.in.ogr documentation

Download all attachments as: .zip

Change History (14)

comment:1 Changed 23 months ago by hellik

ORDER: Real (0.0)

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

comment:2 in reply to:  1 Changed 23 months ago by pierreroudier

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

comment:3 Changed 23 months ago by pierreroudier

Resolution: fixed
Status: newclosed

comment:4 Changed 23 months ago 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)

comment:5 Changed 23 months ago by neteler

Keywords: v.in.ogr added
Milestone: 7.4.1
Resolution: fixed
Status: closedreopened

comment:6 Changed 23 months ago by martinl

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

Changed 23 months ago by pierreroudier

Attachment: v.in.ogr.html.patch added

Patch for the v.in.ogr documentation

comment:7 in reply to:  6 Changed 23 months ago by pierreroudier

Replying to 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)

comment:8 Changed 23 months ago 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).

See also: #3071

comment:9 Changed 22 months ago by neteler

Milestone: 7.4.17.4.2

comment:10 in reply to:  3 Changed 18 months ago by neteler

Milestone: 7.4.27.6.0
Type: defectenhancement
Version: 7.4.0svn-trunk

Replying to pierreroudier:

Patch for the v.in.ogr documentation

Thanks, applied with modifications to trunk (r73530), relbr76 (r73531), and relbr74 (r73532).

Turning into enhancement ticket for "Rename column to eg. ORDER_, print warning and continue" suggestion from above.

Last edited 18 months ago by neteler (previous) (diff)

comment:11 Changed 18 months ago by neteler

For SQL reserved word collisions, see also #578, #1755, #3563

comment:12 Changed 14 months ago by martinl

Milestone: 7.6.07.6.1

Ticket retargeted after milestone closed

comment:13 Changed 12 months ago by martinl

Milestone: 7.6.17.6.2

Ticket retargeted after milestone closed

Note: See TracTickets for help on using tickets.