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" <[email protected]> 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/ _______________________________________________ gdal-dev mailing list [email protected] http://lists.osgeo.org/mailman/listinfo/gdal-dev
