Spatial SQL: Difference between revisions

From GRASS-Wiki
Jump to navigation Jump to search
(Created page with ''''!!! THIS PAGE IS HEAVILY UNDER CONSTRUCTION !!!''' In recent years, many DBMS have learned to store and manage spatial data directly. Clearly, having feature geometries store...')
 
(→‎Conclusions: wikilinks)
 
(3 intermediate revisions by 2 users not shown)
Line 1: Line 1:
'''!!! THIS PAGE IS HEAVILY UNDER CONSTRUCTION !!!'''
{{ToModify}}


In recent years, many DBMS have learned to store and manage spatial data directly. Clearly,
In recent years, many DBMS have learned to store and manage spatial data directly. Clearly,
Line 5: Line 5:
a lot easier in many ways:
a lot easier in many ways:


*make use of advanced capabilities provided by powerful DBMS engines: user authentication, data
*make use of advanced capabilities provided by powerful DBMS engines: user authentication, data integrity and backup, replication mechanisms, fast indexing, full SQL support, triggers, etc.
integrity and backup, replication mechanisms, fast indexing, full SQL support, triggers, etc.
*easily manage 1:m and m:n relationships directly in the database
*easily manage 1:m and m:n relationships directly in the database
*use external front-ends to ease entry of attribute data while managing geometries in the GIS
*use external front-ends to ease entry of attribute data while managing geometries in the GIS
Line 18: Line 17:


While GRASS GIS does not currently support any spatial DBMS directly, it can still interface
While GRASS GIS does not currently support any spatial DBMS directly, it can still interface
them using the OGR-provided drivers and v.in.ogr/v.out.ogr for data import/export.
them using the OGR-provided drivers and {{cmd|v.in.ogr}}/{{cmd|v.out.ogr}} for data import/export.
Several open source databases are supported this way:
Several open source databases are supported this way:


PostgreSQL (with the PostGIS extension)
* PostgreSQL (with the PostGIS extension)
MySQL (Spatial)
* MySQL (Spatial)
SQLite
* SQLite


In addition, the OGR-supplied command line tool "ogr2ogr" can be used to manage all OGR-supported
In addition, the OGR-supplied command line tool "ogr2ogr" can be used to manage all OGR-supported
data sources directly, including  
data sources directly, including  


=== SQLite ===
== SQLite ==


SQLite is a light-weight, embeddable database. It works using single, fully portable files.
SQLite is a light-weight, embeddable database. It works using single, fully portable files.
Line 48: Line 47:
http://www.gdal.org/ogr/drv_sqlite.html
http://www.gdal.org/ogr/drv_sqlite.html


 
=== ogr2ogr ===
ogr2ogr
-------


Creation of new database and dumping of 3D Shapefile points into it works. 3D info is preserved,
Creation of new database and dumping of 3D Shapefile points into it works. 3D info is preserved,
Line 69: Line 66:
ISO-8859-1. UTF-8 encoding worked fine. Original NULL fields are preserved correctly.
ISO-8859-1. UTF-8 encoding worked fine. Original NULL fields are preserved correctly.


 
=== v.out.ogr ===
v.out.ogr
---------


This module is currently (GRASS 6.4.0 RC4) not able to correctly export data to an SQLite DB.
This module is currently (GRASS 6.4.0 RC4) not able to correctly export data to an SQLite DB.
Line 78: Line 73:
existing database (not only SQLite DBs!):
existing database (not only SQLite DBs!):


<source lang="cpp">
     papszDSCO = dsco->answers;
     papszDSCO = dsco->answers;
     Ogr_ds = OGR_Dr_CreateDataSource(Ogr_driver, dsn_opt->answer, papszDSCO);
     Ogr_ds = OGR_Dr_CreateDataSource(Ogr_driver, dsn_opt->answer, papszDSCO);
Line 104: Line 100:
         G_fatal_error(_("Unable to open OGR data source '%s'"),
         G_fatal_error(_("Unable to open OGR data source '%s'"),
               dsn_opt->answer);
               dsn_opt->answer);
</source>


For new databases, only the first geometry gets stored correctly, all subsequent ones generate
For new databases, only the first geometry gets stored correctly, all subsequent ones generate
Line 116: Line 113:
The module bails out here (main.c):
The module bails out here (main.c):


<source lang="cpp">
     CSLDestroy(papszLCO);
     CSLDestroy(papszLCO);
     if (Ogr_layer == NULL)
     if (Ogr_layer == NULL)
         G_fatal_error(_("Unable to create OGR layer"));
         G_fatal_error(_("Unable to create OGR layer"));
</source>


 
=== {{cmd|v.in.ogr}} ===
 
v.in.ogr
--------


Import of 3D point data w/o projection information into a region with UTM system worked using:
Import of 3D point data w/o projection information into a region with UTM system worked using:


v.in.ogr dsn=tmp3.sqlite layer=finds_xyz output=tmp2 -oz --o
  v.in.ogr dsn=tmp3.sqlite layer=finds_xyz output=tmp2 -oz --o


Import of UTF-8 encoded attribute strings worked fine. There was a warning about text fields
Import of UTF-8 encoded attribute strings worked fine. There was a warning about text fields
Line 135: Line 131:
Import of layers from a database without metadata tables works fine.
Import of layers from a database without metadata tables works fine.


 
=== Conclusions ===
Conclusions
-----------


The result is a mixed bag. While the OGR driver itself works fine with some small
The result is a mixed bag. While the OGR driver itself works fine with some small
Line 147: Line 141:


The SQLite support will be significantly improved in GDAL 1.7.0. At least support for
The SQLite support will be significantly improved in GDAL 1.7.0. At least support for
SpatiaLite geometry BLOBs will be added, perhaps also AutoDesk's format in the future.
[[SpatiaLite]] geometry BLOBs will be added, perhaps also AutoDesk's format in the future.


It is unclear if/how OGR handles character encodings other than UTF-8 correctly!  
It is unclear if/how OGR handles character encodings other than UTF-8 correctly!  
When exporting data as UTF from e.g. OOo Calc, double the field length for text fields, since
When exporting data as UTF from e.g. OpenOffice.org Calc, double the field length for text fields, since
we are dealing with 16-Bit units!
we are dealing with 16-Bit units!


Line 157: Line 151:
driver's capabilities plus we'd get that circular linking nightmare again!
driver's capabilities plus we'd get that circular linking nightmare again!


The message about truncation issued by v.in.ogr is worrying, since that seemingly
The message about truncation issued by {{Cmd|v.in.ogr}} is worrying, since that seemingly
needs to be handled by OGR itself, however we could easily add an option to specify
needs to be handled by OGR itself, however we could easily add an option to specify
maximum field length to v.in.ogr (maybe also a function that catches strings which
maximum field length to v.in.ogr (maybe also a function that catches strings which
Line 164: Line 158:
''Note:'' the source code for v.out.ogr contains a note that support for kernel type
''Note:'' the source code for v.out.ogr contains a note that support for kernel type
geometries has not yet been implemented. I am not currently sure about v.in.ogr and kernels.
geometries has not yet been implemented. I am not currently sure about v.in.ogr and kernels.
[[Category: Documentation]]

Latest revision as of 05:32, 19 May 2014

In recent years, many DBMS have learned to store and manage spatial data directly. Clearly, having feature geometries stored alongside their attribute data in one table makes data management a lot easier in many ways:

  • make use of advanced capabilities provided by powerful DBMS engines: user authentication, data integrity and backup, replication mechanisms, fast indexing, full SQL support, triggers, etc.
  • easily manage 1:m and m:n relationships directly in the database
  • use external front-ends to ease entry of attribute data while managing geometries in the GIS
  • never worry again about breaking the relationship between features and attribute records
  • drop restrictions imposed by file-based storage (maximum size, data table limits, slowness)
  • transfer data from one system to another without loss of information using SQL dumps
  • dramatically improve data access times
  • limit data to be held in memory by importing only spatial subregions from the DBMS
  • create central data repositories where layers will always be found in the same place
  • etc., etc.

While GRASS GIS does not currently support any spatial DBMS directly, it can still interface them using the OGR-provided drivers and v.in.ogr/v.out.ogr for data import/export. Several open source databases are supported this way:

  • PostgreSQL (with the PostGIS extension)
  • MySQL (Spatial)
  • SQLite

In addition, the OGR-supplied command line tool "ogr2ogr" can be used to manage all OGR-supported data sources directly, including

SQLite

SQLite is a light-weight, embeddable database. It works using single, fully portable files. Without the need to set up a complex client/server DBMS, it still provides a powerful data storage back-end:

http://www.sqlite.org

SQLiteStudio is a highly recommended application to manage SQLite databases of any kind:

http://www.sqlitestudio.org

There is a document that outlines how to store spatial data in an SQLite database:

http://trac.osgeo.org/fdo/wiki/FDORfc16

The OGR SQLite driver can store spatial data in an SQLite table in a straight-forward manner:

http://www.gdal.org/ogr/drv_sqlite.html

ogr2ogr

Creation of new database and dumping of 3D Shapefile points into it works. 3D info is preserved, it is OK to not have any SRS info. In that case, an empty references table will be created and the SRID for the geometries table will be set to NULL.

Creation of new table in an existing database using the "-update" option works. It is necessary to also use -nln <name> to specify the new table name.

However, it is possible to manually drop the metadata table and the database keeps working as expected.

Creation of a new database with DSCO "METADATA=no" does not work. This is a known bug to be fixed in GDAL 1.7.0.

French special chars from the DBF file were not preserved. Any attribute fields with a special character will come out as empty strings (not NULL!). Encoding used to save the DBF file was ISO-8859-1. UTF-8 encoding worked fine. Original NULL fields are preserved correctly.

v.out.ogr

This module is currently (GRASS 6.4.0 RC4) not able to correctly export data to an SQLite DB.

A quick, dirty hack allowed opening of an OGR data source in update mode, as required for existing database (not only SQLite DBs!):

    papszDSCO = dsco->answers;
    Ogr_ds = OGR_Dr_CreateDataSource(Ogr_driver, dsn_opt->answer, papszDSCO);
    
    /* Some OGR drivers do not support overwriting existing sources 
       or creating new ones.
       
       Note: this is just a blind attempt, as we don't really know, why
       the call to OGR_Dr_CreateDataSource() failed in the first place!
       
       Maybe it would be better to have an "-u" flag here to open an existing
       DS in update mode (like ogr2ogr does)?
    */
    if (Ogr_ds == NULL) {
        if (!strcmp (frmt_opt->answer, "SQLite") ||
            !strcmp (frmt_opt->answer, "PostgreSQL") ||
            !strcmp (frmt_opt->answer, "MySQL")) {
            G_message(_("Attempting to open existing OGR data source.\n"));
            Ogr_ds =
                OGR_Dr_Open(Ogr_driver, dsn_opt->answer, TRUE);    
        }
    }
    
    CSLDestroy(papszDSCO);
    if (Ogr_ds == NULL)
        G_fatal_error(_("Unable to open OGR data source '%s'"),
        	      dsn_opt->answer);

For new databases, only the first geometry gets stored correctly, all subsequent ones generate a useless error message:

 ERROR 1: sqlite3_step() failed:
   SQL logic error or missing database


Adding a new table to an existing DB fails for unknown reasons. The OGR driver creates the table itself with the correct structure, but not a single geometry is written into it. The module bails out here (main.c):

    CSLDestroy(papszLCO);
    if (Ogr_layer == NULL)
        G_fatal_error(_("Unable to create OGR layer"));

v.in.ogr

Import of 3D point data w/o projection information into a region with UTM system worked using:

 v.in.ogr dsn=tmp3.sqlite layer=finds_xyz output=tmp2 -oz --o

Import of UTF-8 encoded attribute strings worked fine. There was a warning about text fields being truncated to a maximimum length of 255 chars. However, none of the fields exceeded 100 chars, anyway.

Import of layers from a database without metadata tables works fine.

Conclusions

The result is a mixed bag. While the OGR driver itself works fine with some small glitches that can be worked around, export of data directly from GRASS using v.out.ogr is currently impossible. Import using v.in.ogr, however works well.

GRASS' v.out.ogr module needs a "-u" (update flag) to open existing data sources in update mode. It remains useless in its current state.

The SQLite support will be significantly improved in GDAL 1.7.0. At least support for SpatiaLite geometry BLOBs will be added, perhaps also AutoDesk's format in the future.

It is unclear if/how OGR handles character encodings other than UTF-8 correctly! When exporting data as UTF from e.g. OpenOffice.org Calc, double the field length for text fields, since we are dealing with 16-Bit units!

It might be possible to compile GDAL with GRASS support and use ogr2ogr to access GRASS vector data instead of v.in.ogr/v.out.ogr. However, there might be limits in the OGR driver's capabilities plus we'd get that circular linking nightmare again!

The message about truncation issued by v.in.ogr is worrying, since that seemingly needs to be handled by OGR itself, however we could easily add an option to specify maximum field length to v.in.ogr (maybe also a function that catches strings which are too long and were thus truncated?).

Note: the source code for v.out.ogr contains a note that support for kernel type geometries has not yet been implemented. I am not currently sure about v.in.ogr and kernels.