Skip to content

Latest commit

 

History

History
98 lines (81 loc) · 4.95 KB

GIS.md

File metadata and controls

98 lines (81 loc) · 4.95 KB

GIS support in SQLite Foreign Data Wrapper for PostgreSQL

PostGIS + SpatiaLite

SQLite FDW for PostgreSQL can connect PostgreSQL with or without PostGIS to SpatiaLite SQLite database file. This description contains only information about GIS support without common SQL and system descriptions from common FDW description.

Notes about compilation environment and PROJ library

Both SpatiaLite and PostGIS uses PROJ C++ library from Open Source Geospatial Foundation for reprojecting and some calculations. Recommended configuration of SQLite FDW with GIS support contains both SpatiaLite and PostGIS. It is recommended to install only 1 version of PROJ in a system to avoid integration issue. Before compilation you should ensure equal versions of PROJ library required by SpatiaLite and PostGIS on your system. Otherwise some encapsualted and unwanted memory freeing errors can occur. If you need different PROJ library versions, you can reference this PROJ issue and try to use something like CFLAGS="$CFLAGS -DPROJ_RENAME_SYMBOLS -O2" during compilation of SpatiaLite or PostGIS to link one of this extensions with other PROJ version.

Common conditions of GIS support

  1. SQLite FDW should be compiled with ENABLE_GIS=1 environment variable value.

  2. You must install SpatiaLite header files before compilation. Linux packages like libspatialite-dev or libspatialite-devel can contain this files.

  3. A column should have data type (domain) name from following list:

    • addbandarg
    • box2d
    • box3d
    • geography
    • geometry
    • geometry_dump
    • geomval
    • getfaceedges_returntype
    • rastbandarg
    • raster
    • reclassarg
    • summarystats
    • topoelement
    • topoelementarray
    • topogeometry
    • unionarg
    • validatetopology_returntype

    Only listed data types have full data transformation support:

    • geography
    • geometry

All other data types (domains) are treated as PostGIS specific, but unsupported.

You can use SpatiaLite GIS data support without PostGIS installation after such SQL commands as CREATE DOMAIN geometry AS bytea; and CREATE DOMAIN geography AS bytea;. This allows to have in PostgreSQL PostGIS compatible bytea data easy convertable to PostGIS storage format.

PostGIS and SpatiaLite vector data formats

Vector GIS data in PostGIS can be stored in a columns with geography or geometry data type. This columns contains a binary data. Well-known binary (WKB) data storage format is a standard of Open Geospatial Consortium (OGC) and supported by GEOS library. PostGIS internal GIS data storage format based on WKB with SRID additions. This format is known as EWKB and supported by GEOS library and SpatiaLite input/output functions.

Hexadecimal text representation of EWKB data is a transport form for geography and geometry GIS data between PostgreSQL and SpatiLite input/output functions. Hence no PostGIS input/output functions are necessary, but all of this functions are supported.

EWKB hexadecimal text data transport is faster than EWKT but slower than EWKB blob data transport.

SpatiaLite internal storage based on blob data affinity and is not a standard of OGC. Also this format doesn't supported by GEOS library.

Limitations

  • In opposition to PostGIS, SpatiaLite doesn't allow to store any GIS vector data without SRID. Hence any well-formed SpatiaLite data can be converted for PostGIS, but well-formed PostGIS data without SRID cannot be converted for SpatiaLite. All of SpatiaLite input functions will return NULL in this case. Please use ST_SetSRID PostGIS function in case of incomplete SRID data to prepare PostGIS data for importing to SpatiaLite or comparing with SpatiaLite data.

  • Only = PostgreSQL operator is pushed down to SQLite (SpatiaLite) for vector GIS data such as geography or geometry. <> PostgreSQL operator is NOT pushed down.

End of description.