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

Sat Aug 18 10:28:09 PDT 2012

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.
>
>Best regards,
>
>Even
>
>_______________________________________________
>gdal-dev mailing list
>gdal-dev at lists.osgeo.org
>http://lists.osgeo.org/mailman/listinfo/gdal-dev