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

[Jeff McKenna]

I agree, fascinating.

-jeff



On 12-08-18 2:28 PM, Smith, Michael ERDC-RDE-CRREL-NH wrote:
> This is "wicked cool".
> 
> Mike
> 
> -- Michael Smith US Army Corps Remote Sensing GIS/Center On 8/18/12 1:03
> PM, "Even Rouault" <even.rouault at mines-paris.org> wrote:
>> >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.
>> >


-- 
Jeff McKenna
MapServer Consulting and Training Services
http://www.gatewaygeomatics.com/