[gdal-dev] [Proposed new feature] A '"SQLite" SQL dialect for OGR

[Even Rouault]

Hi folks,

I've attached in 
http://trac.osgeo.org/gdal/attachment/ticket/4782/sqlite_dialect.patch a patch 
that adds a SQLite alternate SQL dialect that can be used with any OGR 
datasource (only available if GDAL/OGR is configured with SQLite support of 
course)

To remind you the concept of SQL dialects, for non-RDBMS OGR drivers, OGR uses 
its own SQL engine, which is the called the OGRSQL dialect. Whereas for RDBMS 
OGR drivers, their own SQL engine will be used, unless otherwise specified.

This patchs adds the capability to use a SQLite dialect (through the -dialect 
option of ogrinfo or ogr2ogr for example). When doing so, the SQLite SQL 
engine is used, and when Spatialite is available, all the Spatialite functions 
( see http://www.gaia-gis.it/gaia-sins/spatialite-sql-3.0.0.html ) can also be 
used.

Technically, this works thanks to a temporary in-memory SQLite DB and a module 
(ogr/ogrsf_frmts/sqlite/ogrsqlitevirtualogr.cpp) that exposes OGR layers to 
SQLite through its Virtual Table mechanism ( http://www.sqlite.org/vtab.html 
). When the datasource you operate on is opened in update mode and that the 
corresponding OGR driver supports CreateFeature()/SetFeature()/DeleteFeature() 
operations, SQL INSERT/UPDATE/DELETE operations will work too.

What do you think about it ?

A few non-exhaustive examples of things you can do :

# Initial CSV file :

$ cat my.csv
id,foo,bar,long,lat
1,"foo","bar",2,49
1,"foo","bar",3,50
2,"foo2","bar2",-2,49
2,"foo2","bar2",-3,51

# Creates a Geometry field from each (long,lat) tuple :

$ ogrinfo my.csv -dialect sqlite -sql "SELECT *, MakePoint(CAST(long AS 
FLOAT), CAST(lat AS FLOAT)) as geometry FROM my" -q

Layer name: SELECT
OGRFeature(SELECT):0
  id (String) = 1
  foo (String) = foo
  bar (String) = bar
  long (String) = 2
  lat (String) = 49
  POINT (2 49)

OGRFeature(SELECT):1
  id (String) = 1
  foo (String) = foo
  bar (String) = bar
  long (String) = 3
  lat (String) = 50
  POINT (3 50)

OGRFeature(SELECT):2
  id (String) = 2
  foo (String) = foo2
  bar (String) = bar2
  long (String) = -2
  lat (String) = 49
  POINT (-2 49)

OGRFeature(SELECT):3
  id (String) = 2
  foo (String) = foo2
  bar (String) = bar2
  long (String) = -3
  lat (String) = 51
  POINT (-3 51)


# Merge all the points that have the same id into a line :

$ ogrinfo my.csv -dialect sqlite -sql "SELECT id, foo,bar, 
MakeLine(MakePoint(CAST(long AS FLOAT), CAST(lat AS FLOAT))) FROM my GROUP BY 
id" -q

Layer name: SELECT
OGRFeature(SELECT):0
  id (String) = 1
  foo (String) = foo
  bar (String) = bar
  LINESTRING (2 49,3 50)

OGRFeature(SELECT):1
  id (String) = 2
  foo (String) = foo2
  bar (String) = bar2
  LINESTRING (-2 49,-3 51)

# Compute the geodesic length of each line :

$ ogrinfo my.csv -dialect sqlite -sql "SELECT id,  
GeodesicLength(SetSRID(MakeLine(MakePoint(CAST(long AS FLOAT), CAST(lat AS 
FLOAT))),4326)) AS total_length FROM my GROUP BY id" -q

Layer name: SELECT
OGRFeature(SELECT):0
  id (String) = 1
  total_length (Real) = 132725.477910869

OGRFeature(SELECT):1
  id (String) = 2
  total_length (Real) = 233720.037020965

# Appends a new entry in the CSV :

$ ogrinfo my.csv -dialect sqlite -sql "insert into my (id,foo,bar,long,lat) 
values (3,'foo3','bar3',2.5,49.5)"

$ cat my.csv
id,foo,bar,long,lat
1,"foo","bar",2,49
1,"foo","bar",3,50
2,"foo2","bar2",-2,49
2,"foo2","bar2",-3,51
3,foo3,bar3,2.5,49.5

# Reprojection from EPSG:32631 to EPSG:4326 :

$ ogrinfo poly.shp -dialect sqlite -sql "SELECT 
ST_Transform(SetSRID(GEOMETRY,32631),4326) AS GEOMETRY, * FROM poly WHERE 
EAS_ID = 170"
INFO: Open of `poly.shp'
      using driver `ESRI Shapefile' successful.

Layer name: SELECT
Geometry: Unknown (any)
Feature Count: 1
Extent: (2.750069, 43.034444) - (2.751428, 43.035184)
Layer SRS WKT:
GEOGCS["WGS 84",
    DATUM["WGS_1984",
        SPHEROID["WGS 84",6378137,298.257223563,
            AUTHORITY["EPSG","7030"]],
        AUTHORITY["EPSG","6326"]],
    PRIMEM["Greenwich",0,
        AUTHORITY["EPSG","8901"]],
    UNIT["degree",0.0174532925199433,
        AUTHORITY["EPSG","9122"]],
    AUTHORITY["EPSG","4326"]]
Geometry Column = GEOMETRY
AREA: Real (0.0)
EAS_ID: Real (0.0)
PRFEDEA: String (0.0)
OGRFeature(SELECT):0
  AREA (Real) = 5268.813
  EAS_ID (Real) = 170
  PRFEDEA (String) = 35043413
  POLYGON ((2.751427625469495 43.034734578878634,2.750298298604006 
43.034443959553869,2.75006933958772 43.03490271631064,2.75124435992688 
43.035184432169061,2.751427625469495 43.034734578878634))

Note: a similar capability was already available in OGR 1.9 for Shapefiles, 
through the use of the VirtualShape module that is embedded in Spatialite. See 
the http://gdal.org/ogr/drv_sqlite.html page.

Best regards,

Even