Update of "Supporting GeoJSON"
Not logged in

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview

Artifact ID: c6337ef0ee1dc162632d1afdaaf3490d67ffe649
Page Name:Supporting GeoJSON
Date: 2019-12-26 15:52:39
Original User: sandro
Parent: 56fbb6b4917c1100c41821e35ebbca0c76b0aeb2
Content

Back to 5.0.0-doc main page



Introduction

GeoJSON is an open standard data format based on JSON (JavaScript Object Notation) that is a very popular data format widely used by many web-apps as a replacement for XML.
The intended use of GeoJSON is the extension of the basic capabilities of JSON so to adequately support geographic features including both Geometries and non-spatial attributes.

GeoJSON, which has existed for many years, was initially based on a loose and informal data specification.
Only very recently (2016) it has finally become a respectable standard format based on a formal specification, RFC 7946, released by IETF (Internet Engineering Task Force).
Note: RFC 7946 introduced several relevant requirements and restrictions, so that pre-RFC and post-RFC GeoJSON files are not mutually interoperable.

A very remarkable feature of RFC 7946 is that it's explicitly declared to be a fixed and immutable specification.
There will never be updated versions of GeoJSON; even the slightest change will require a rename from GeoJSON to something else.
Such a restriction is obviously intended to ensure a very strong stability during its lifetime (and represents a strong reaction to the many GML versions that abruptly introduced radical changes upsetting the well established behavior of existing implementations).

The most obvious competitors of GeoJSON are the ESRI Shapefile and GML
The following chart will quickly list the main differences between them.

TopicShapefileGMLGeoJSONRemarks
File organization At least three independent files sharing the same name and respectively identified by suffixes .shp, .shx and .dbf
  • Both the .shp and .shx members are binary files intended to store Geometries and should be encoded accordingly to the ESRI open specification
  • The .dbf member is intended to store non-spatial attributes.
    This binary file is also expected to be encoded accordingly to Ashton-Tate dBase specification; unhappily this very old specification (born in the '80s) had a savage proliferation of different dialects (Clipper, FoxPro) becoming quite messy and chaotic.
GML is based on XML, and is just a single, monolithic text file.
As any other XML file, GML too can be strongly constrained to conform to a formally defined XML Schema
Single monolithic text file.
Similar to XML, but intended to be far simpler and less verbose.
The three-files layout of Shapefile is clearly obsolete, and it frequently poses many headaches causing unexpected troubles.

The single-file layout adopted by both GML and GeoJSON is clearly better and safer, and being text files they can be easily inspected and eventually debugged just using any generic text editor without requiring any specific tool.
Geometry classes
  • Null Shape
  • Point
  • MultiPoint
  • PolyLine (without distinguishing between single- and multi-part)
  • Polygon (without distinguishing between single- and multi-part)

Notes:
  • All Geometries in the same Shapefile must share the same class (or be Null).
  • All Geometries in the same Shapefile must share the same SRID.
  • The rules for identifying Exterior and Interior Polygon rings are awkward and can frequently cause interoperability issues.
GML allows many different ways to define the same type of Geometry, and the specifications have radically changed from version to version.

GML has a really impressive flexibility (e.g. each single Geometry can freely declare its own SRID), but at the cost of imposing an overwhelming complexity.
  • Null
  • Point
  • Linestring
  • Polygon
  • MultiPoint
  • MultiLinestring
  • MultiPolygon
  • GeometryCollection

Notes:
  • This exactly corresponds to the standard 7 classes model adopted by Spatial SQL.
  • The same GeoJSON file can freely contain any kind of Geometry classes without restrictions.
  • All Geometries in the same GeoJSON file must share the same SRID.
  • Shapefile is obviously obsolete, and someway messy and limited.
  • GML is elegant and very sophisticated: sometimes too sophisticated and complex to be really usable.
  • GeoJSON matches the Spatial SQL requirements, and therefore avoids any unnecessary complexity.
Dimensions
  • XY
  • XYM
  • XYZ
  • XYZM
  • XY
  • XYM
  • XYZ
  • XYZM
  • XY
  • XYZ

Note: RFC 7946 just supports 2 or 3 coordinates, and the third value (when declared) is always expected to correspond to an Elevation (Z axis).

Supporting XYM or XYZM is not technically unfeasible. Both writers and readers could support such options, but all this is surely outside the standard and will surely impair the universal portability of any non canonical file.
GeoJSON lacks the capability to support XYM and XYZM, other than by adopting (and to others unknown) vicious tricks.
May well be it's not a forbidding limitation in many common cases, but it's indisputably a limitation.
SRID Not internally declared by the Shapefile itself.
Deploying a further .prj file describing the intended SRID is the usual solution adopted by ESRI itself, but correctly parsing these extra files is an usually flimsy process falling outside real capabilities of many third party readers.
Each single Geometry is allowed to freely define its own SRID, as well as defining the SRID for the whole layer. Accordingly to RFC 7946 all coordinates are always expected to be expressed as longitudes and latitudes ((i.e: x-position and y-position).
So any canonical GeoJSON file is always expected to reference SRID=4326 WGS 84 (i.e. the SRID is not stored within the GeoJSON file).

Using any other SRID is technically possible, but requires a conventional agreement between writers and readers, but all this is surely outside the standard and will surely impair the universal portability of any non canonical file.
The unique effective solution is the one adopted by GML.
Both Shapefile and GeoJSON are clearly inferior under this peculiar aspect.
Non-spatial attributes
  • CHAR (limited to max. 254 bytes)
  • NUMBER (represented by an ASCII string of max. 32 bytes)
  • DATE (YYYYMMDD)
  • LOGICAL (T/F)

Note: all attribute names are limited to a length of max. 10 bytes. There is no safe way for declaring NULL values.
Any possible datatype you can imagine.
And defining further derived datatypes is an option supported by the XML Schema.

Note: attribute names and text values can have any arbitrary length.
  • text (unconstrained length)
  • number
  • null
  • true
  • false

Note: attribute names can have any arbitrary length.
  • Shapefile (or more precisely in this case DBF) clearly suffers from too many unpleasant limitations.
  • GML (more precisely XML) can effectively support an impressive flexibility but can easily become too complex and difficult to parse.
  • GeoJSON offers a well balanced mix; it's still reasonably simple and it's powerful at the same time.
Charset encoding
see the CookBook for more on this topic.
Not internally defined by the Shapefile itself.
Attempting to guess the appropriate charset encoding required by some Shapefile is more a magic art than rational science.
Always internally defined by the GML/XML file itself. RFC 7946 strictly requires that all GeoJSON files must be encoded as UTF-8

In pure theory both UTF-16 and UTF-32 could be used for encoding a legitimate GeoJSON file, but such options seems to be very rarely (if ever) adopted in real world.
  • Shapefiles leaves a lot to be desired, and lack of defined charset encoding causes many serious portability issues.
  • GML/XML nicely supports any possible charset in the most flexible (and safe) way
  • Once again, GeoJSON is straightforward and simple, but really effective.

Skeletal GeoJSON anatomy

{
   "type": "FeatureCollection",
   "features": 
   [{
       "type": "Feature",
       "geometry": 
       {
           "type": "Point",
           "coordinates": [102.0, 0.5]
       },
       "properties": 
       {
           "prop0": "value0"
       }
    }, 
    {
       "type": "Feature",
       "geometry": 
       {
           "type": "LineString",
           "coordinates": 
          [
               [102.0, 0.0],
               [103.0, 1.0],
               [104.0, 0.0],
               [105.0, 1.0]
           ]
       },
       "properties": 
       {
           "prop0": "value0",
           "prop1": 0.0
       }
    }, 
    {
       "type": "Feature",
       "geometry": 
       {
           "type": "Polygon",
           "coordinates": 
           [
               [
                   [100.0, 0.0],
                   [101.0, 0.0],
                   [101.0, 1.0],
                   [100.0, 1.0],
                   [100.0, 0.0]
               ]
           ]
       },
       "properties": 
       {
           "prop0": "value0",
           "prop1": {"this": "that"}
       }
   }]
}
As you can easily notice, GeoJSON has a plain regular structure and is decisively most concise and less verbose than GML/XML:

Short conclusions

GeoJSON is widely adopted by many web-apps; being a notation directly based on JavaScript it has a natural integration in popular client-side JS libraries such as OpenLayers and Leaflet.
It's adequately supported by GDAL, and consequently by many others free/libre sw components (as e.g. MapServer and GeoServer).

Outside the web-gis arena GeoJSON still has limited adoption, and it's a real pity because under many aspects it's a very valid full replacement for the nowadays irremediably obsolete Shapefile.
Starting since version 5.0.0 SpatiaLite offers full supports to GeoJSON as defined by RFC 7946.
The implementation strongly resembles the one supported by all previous versions for Shapefile, and this isn't at all surprising because both them cover the same functional area.


SQL functions supporting GeoJSON

Exporting data to GeoJSON

SELECT ExportGeoJSON2('mytable', 'geom', '/home/sandro/mytable.geojson');
---------
1234

SELECT ExportGeoJSON2('mytable', 'geom', '/home/sandro/mytable.geojson', 12);
---------
1234

SELECT ExportGeoJSON2('mytable', 'geom', '/home/sandro/mytable.geojson', 12, 1);
---------
1234

SELECT ExportGeoJSON2('mytable', 'geom', '/home/sandro/mytable.geojson', 12, 1, 0);
---------
1234

SELECT ExportGeoJSON2('mytable', 'geom', '/home/sandro/mytable.geojson', 12, 1, 0, 1);
---------
1234

SELECT ExportGeoJSON2('mytable', 'geom', '/home/sandro/mytable.geojson', 12, 1, 0, 1, 'LOWERCASE');
---------
1234
The SQL function ExportGeoJSON2() can directly export a whole Spatial Table (aka Layer) into an external GeoJSON file conformant to RFC 7946.
This function accepts the following arguments:
  1. table: (mandatory) name of the Spatial Table to be exported.
  2. geom_column: (mandatory) name of the Column containing the Geometries to be exported.
    Can be NULL, and in this case the function will automatically determine the appropriate Column-name if the Table just contains a single Geometry, otherwise a failure will follow.
  3. filename: (mandatory) absolute or relative path of the GeoJson file to be created (output file).
  4. precision: (optional) number of decimal digits to be used for coordinate values; the default setting is 8.
  5. lon_lat: (optional) this is a boolean flag:
    • if set to FALSE all coordinates will be exported exactly as they are.
    • if set to TRUE all coordinates will be automatically transformed (if required) into SRID=4326 WGS 84 as required by RFC 7946.
      This is the default setting.
  6. M_coords: (optional) this too is a boolean flag:
    • if set to FALSE eventual M-values will be simply ignored, as required by RFC 7946.
      This is the default setting.
    • if set to TRUE eventual M-values will be exported into the GeoJSON file as extra-coordinates.
      Note: the resulting output file could easily suffer from limited portability.
  7. indented: (optional) yet another boolean flag:
    • If set to TRUE the output file will be properly indented for enhanced human readability.
      This is the default setting.
    • If set to FALSE the output file will be all in a single monolithic line without blank spaces or newlines (noticeably smaller).
  8. colname_case: (optional) can assume one the following values:
    • 'UPPER' or 'UPPERCASE': all attribute names will be inserted into the output file in full upper case.
    • 'LOWER' or 'LOWERCASE': all attribute names will be inserted into the output file in full lower case.
      This is the default setting.
    • 'SAME' or 'SAMECASE': all attribute names will be inserted into the output file preserving all upper- and lower-case letters exactly as they are defined in the corresponding column names.
  9. the return value is an INTEGER corresponding to the number of exported rows, or NULL in the case of failure or invalid arguments.

Caveat

A similar function named ExportGeoJSON() was already supported by previous versions of SpatiaLite, but it was modelled against the old (pre-RFC) GeoJSON specification.
ExportGeoJSON() is now DEPRECATED, and will remain to avoid breaking existing applications and scripts.

It is strongly recommended to use ExportGeoJSON2() for any new development as full replacement conformant to RFC 7946.

Warning

This SQL function open the door to many potential security issues, and thus is always disabled by default.
Explicitly setting the external variable SPATIALITE_SECURITY=relaxed is absolutely required in order to enable this function.


Importing data from GeoJSON

SELECT ImportGeoJSON('/home/sandro/somelayer.geojson', 'somelayer');
-----------
99

SELECT ImportGeoJSON('/home/sandro/somelayer.geojson', 'somelayer', 'geom');
-----------
99

SELECT ImportGeoJSON('/home/sandro/somelayer.geojson', 'somelayer', 'geom', 1);
-----------
99

SELECT ImportGeoJSON('/home/sandro/somelayer.geojson', 'somelayer', 'geom', 1, 3003);
-----------
99

SELECT ImportGeoJSON('/home/sandro/somelayer.geojson', 'somelayer', 'geom', 1, 3003, 'LOWERCASE');
-----------
99
The SQL function ImportGeoJSON() can directly create a new Spatial Table by importing data from an extenal GeoJSON file conformant to RFC 7946.
This function accepts the following arguments:
  1. filename: (mandatory) absolute or relative path of the GeoJson file to be imported (input file).
  2. table: (mandatory) name of the Spatial Table to be created.
  3. geom_column: (optional) name of the Geometry Column to be created.
    If not explicitly specified or NULL a column named 'geometry' will be created by default.
  4. spatial_index: (optional) this is a boolean flag:
    • if set to TRUE a SpatialIndex supporting the Geometry Column will be automatically created.
    • if set to FALSE no SpatialIndex will be created.
      This is the default setting.
  5. srid: (optional):
    • When it's a positive integer it corresponds to the SRID value to be assigned to all imported Geometries.
    • If 0 or negative all imported Geometries will be assumed to be in SRID=4326 WGS 84 (geographic coordinates expressed as Longitudes and Latitudes) as required by RFC 7946.
      This is the default setting.
  6. colname_case: (optional) can assume one the following values:
    • 'UPPER' or 'UPPERCASE': all columns will be created into the table in full upper case.
    • 'LOWER' or 'LOWERCASE': all columns will be created into the table in full lower case.
      This is the default setting.
    • 'SAME' or 'SAMECASE': all columns will be created into the table preserving all upper- and lower-case letters exactly as they are defined in the corresponding GeoJSON attribute names.
  7. the return value is an INTEGER corresponding to the number of exported rows, or NULL in the case of failure or invalid arguments.

Warning

This SQL function open the door to many potential security issues, and thus is always disabled by default.
Explicitly setting the external variable SPATIALITE_SECURITY=relaxed is absolutely required in order to enable this function.


VirtualGeoJSON

CREATE VIRTUAL TABLE some_name USING VirtualGeoJSON( path, [ srid , [ colname_case ]] );
A VirtualGeoJSON table is strictly similar to a VirtualShape table, the only difference between them being the file format of the underlaying datasource.
Except for this small but fundamental detail both them behave in exactly the same way.


GeoJSON support in spatialite_gui

The latest version of the GUI tool now supports GeoJSON in exactly the same way it supports Shapefiles.

Two new buttons have been added to the toolbar:
  • one for importing GeoJSON files
  • the other for creating VirtualGeoJSON tables
GeoJSON toolbar buttons
The side figure is an example of the dialog box that's used for setting options when creating a new Spatial Table by importing data from an external GeoJSON file. GeoJSON load
The side figure is an example of the dialog box that's used for setting options when creating a VirtualGeoJSON Table. GeoJSON virtual table
For exporting a whole Spatial Table into an external GeoJSON file:
  • in the tree-list right-click on the requested Geometry.
    • then click the Export as GeoJSON item on the context-menu that will appear.
GeoJSON dump




Back to 5.0.0-doc main page