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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

[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

_______________________________________________
gdal-dev mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: [Proposed new feature] A '"SQLite" SQL dialect for OGR

Smith, Michael ERDC-RDE-CRREL-NH
This is "wicked cool".

Mike

--
Michael Smith

US Army Corps
Remote Sensing GIS/Center



On 8/18/12 1:03 PM, "Even Rouault" <[hidden email]> 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
>[hidden email]
>http://lists.osgeo.org/mailman/listinfo/gdal-dev

_______________________________________________
gdal-dev mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: [Proposed new feature] A '"SQLite" SQL dialect for OGR

jmckenna
Administrator
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" <[hidden email]> 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
[hidden email]
http://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: [Proposed new feature] A '"SQLite" SQL dialect for OGR

Even Rouault
In reply to this post by Even Rouault
Le samedi 18 août 2012 19:03:56, Even Rouault a écrit :
> 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)
>

This is now commited in trunk. Will need some polishing likely, and above all
some documentation, but it should be ready enough for general testing.

Best regards,

Even
_______________________________________________
gdal-dev mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/gdal-dev