This is "wicked cool". Mike
-- Michael Smith US Army Corps Remote Sensing GIS/Center On 8/18/12 1:03 PM, "Even Rouault" <even.roua...@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@lists.osgeo.org >http://lists.osgeo.org/mailman/listinfo/gdal-dev _______________________________________________ gdal-dev mailing list gdal-dev@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/gdal-dev