D 2019-12-26T15:52:39.912
L Supporting\sGeoJSON
P 56fbb6b4917c1100c41821e35ebbca0c76b0aeb2
U sandro
W 23226
Back to <a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=5.0.0-doc">5.0.0-doc main page</a><hr><br>
<h1>Introduction</h1>
<a href="https://en.wikipedia.org/wiki/GeoJSON">GeoJSON</a> is an open standard data format based on <a href="https://en.wikipedia.org/wiki/JSON">JSON</a> (<b>JavaScript Object Notation</b>) that is a very popular data format widely used by many web-apps as a replacement for XML.<br>
The intended use of GeoJSON is the extension of the basic capabilities of JSON so to adequately support <b>geographic features</b> including both <b>Geometries</b> and
<b>non-spatial attributes</b>.<br><br>
GeoJSON, which has existed for many years, was initially based on a loose and informal data specification.<br>
Only very recently (2016) it has finally become a respectable standard format based on a formal specification, <a href="https://tools.ietf.org/html/rfc7946">RFC 7946</a>, released by <b>IETF</b> (<i>Internet Engineering Task Force</i>).<br>
<u>Note</u>: RFC 7946 introduced several relevant requirements and restrictions, so that <b>pre-RFC</b> and <b>post-RFC</b> GeoJSON files are not mutually interoperable.<br><br>
A very remarkable feature of RFC 7946 is that it's explicitly declared to be a <b>fixed and immutable</b> specification.<br>
There will never be updated versions of GeoJSON; even the slightest change will require a rename from GeoJSON to something else.<br>
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).<br><br>
The most obvious competitors of GeoJSON are the <a href="https://en.wikipedia.org/wiki/Shapefile">ESRI Shapefile</a> and <a href="
https://en.wikipedia.org/wiki/Geography_Markup_Language">GML</a><br>
The following chart will quickly list the main differences between them.<br><br>
<table cellspacing="4" cellpadding="8" bgcolor="#c0eec0" border="1">
<tr><th>Topic</th><th>Shapefile</th><th>GML</th><th>GeoJSON</th><th>Remarks</th></tr>
<tr>
<th valign="middle">File organization</th>
<td valign="middle">At least three independent files sharing the same name and respectively identified by suffixes <b>.shp</b>, <b>.shx</b> and <b>.dbf</b>
<ul>
<li>Both the <b>.shp</b> and <b>.shx</b> members are <b>binary files</b> intended to store Geometries and should be encoded accordingly to the <a href="https://www.esri.com/library/whitepapers/pdfs/shapefile.pdf">ESRI open specification</a></li>
<li>The <b>.dbf</b> member is intended to store non-spatial attributes.<br>
This <b>binary file</b> is also expected to be encoded accordingly to <a href="https://en.wikipedia.org/wiki/DBase">Ashton-Tate dBase specification</a>; unhappily this very old specification (born in the '80s) had a savage proliferation of different dialects (<i>Clipper</i>, <i>FoxPro</i>) becoming quite messy and chaotic.</li>
</ul>
</td>
<td>GML is based on XML, and is just a single, monolithic <b>text file.</b><br>
As any other XML file, GML too can be strongly constrained to conform to a formally defined <a href="https://it.wikipedia.org/wiki/XML_Schema">XML Schema</a>
</td>
<td valign="middle">
Single monolithic <b>text file</b>.<br>
Similar to XML, but intended to be far simpler and less verbose.
</td>
<td>
The three-files layout of Shapefile is clearly obsolete, and it frequently poses many headaches causing unexpected troubles.<br><br>
The single-file layout adopted by both GML and GeoJSON is clearly better and safer, and being <b>text files</b> they can be easily inspected and eventually debugged just using any generic <b>text editor</b> without requiring any specific tool.
</td>
</tr>
<tr>
<th>Geometry classes</th>
<td>
<ul>
<li>Null Shape</li>
<li>Point</li>
<li>MultiPoint</li>
<li>PolyLine (without distinguishing between single- and multi-part)</li>
<li>Polygon (without distinguishing between single- and multi-part)</li>
</ul>
<br>
<u>Notes</u>: 
<ul>
<li>All Geometries in the same Shapefile must share the same class (or be <b>Null</b>).</li>
<li>All Geometries in the same Shapefile must share the same SRID.</li>
<li>The rules for identifying <b>Exterior</b> and <b>Interior</b> Polygon rings
are awkward and can frequently cause interoperability issues.</li>
</ul>
</td>
<td>
GML allows many different ways to define the same type of Geometry, and the specifications have radically changed from version to version.<br><br>
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.
</td>
<td>
<ul>
<li>Null</li>
<li>Point</li>
<li>Linestring</li>
<li>Polygon</li>
<li>MultiPoint</li>
<li>MultiLinestring</li>
<li>MultiPolygon</li>
<li>GeometryCollection</li>
</ul>
<br>
<u>Notes</u>: 
<ul>
<li>This exactly corresponds to the standard <b>7 classes</b> model adopted by Spatial SQL.</li>
<li>The same GeoJSON file can freely contain any kind of Geometry classes without restrictions.</li>
<li>All Geometries in the same GeoJSON file must share the same SRID.</li>
</ul>
</td>
<td>
<ul>
<li>Shapefile is obviously obsolete, and someway messy and limited.</li>
<li>GML is elegant and very sophisticated: sometimes too sophisticated and complex to be really usable.</li>
<li>GeoJSON matches the Spatial SQL requirements, and therefore avoids any unnecessary complexity.</li>
</ul>
</td>
</tr>
<tr>
<th>Dimensions</th>
<td>
<ul>
<li>XY</li>
<li>XYM</li>
<li>XYZ</li>
<li>XYZM</li>
</ul>
</td>
<td>
<ul>
<li>XY</li>
<li>XYM</li>
<li>XYZ</li>
<li>XYZM</li>
</ul>
</td>
<td>
<ul>
<li>XY</li>
<li>XYZ</li>
</ul>
<br>
<u>Note</u>: RFC 7946 just supports 2 or 3 coordinates, and the third value (when declared) is always expected to correspond to an Elevation (Z axis).<br><br>
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.
</td>
<td>
GeoJSON lacks the capability to support XYM and XYZM, other than by adopting (and to others unknown) vicious tricks.<br>
May well be it's not a forbidding limitation in many common cases, but it's indisputably a limitation.
</td>
</tr>
<tr>
<th>SRID</th>
<td>
Not internally declared by the Shapefile itself.<br>
Deploying a further <b>.prj</b> 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.
</td>
<td>
Each single Geometry is allowed to freely define its own SRID, as well as defining the SRID for the whole layer.
</td>
<td>
Accordingly to RFC 7946 all coordinates are always expected to be expressed as <b>longitudes</b> and <b>latitudes</b> ((i.e: x-position and y-position).<br>
So any canonical GeoJSON file is always expected to reference <b>SRID=4326 WGS 84</b> (i.e. the SRID is <b>not</b> stored within the GeoJSON file).<br><br>
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.
</td>
<td>
The unique effective solution is the one adopted by GML.<br>
Both Shapefile and GeoJSON are clearly inferior under this peculiar aspect.
</td>
</tr>
<tr>
<th>Non-spatial attributes</th>
<td>
<ul>
<li>CHAR (limited to max. 254 bytes)</li>
<li>NUMBER (represented by an ASCII string of max. 32 bytes)</li>
<li>DATE (YYYYMMDD)</li>
<li>LOGICAL (T/F)</li>
</ul>
<br>
<u>Note</u>: all attribute names are limited to a length of max. 10 bytes. There is no safe way for declaring <b>NULL</b> values.
</td>
<td>
Any possible datatype you can imagine.<br>
And defining further derived datatypes is an option supported by the XML Schema.<br><br>
<u>Note</u>: attribute names and text values can have any arbitrary length.
</td>
<td>
<ul>
<li>text (unconstrained length)</li>
<li>number</li>
<li>null</li>
<li>true</li>
<li>false</li>
</ul>
<br>
<u>Note</u>: attribute names  can have any arbitrary length.
</td>
<td>
<ul>
<li>Shapefile (or more precisely in this case DBF) clearly suffers from too many unpleasant limitations.</li>
<li>GML (more precisely XML) can effectively support an impressive flexibility but can easily become too complex and difficult to parse.</li>
<li>GeoJSON offers a well balanced mix; it's still reasonably simple and it's powerful at the same time.</li>
</ul>
</td>
</tr>
<tr>
<td align="center">
<b>Charset encoding</b>
<table cellpadding="4" cellspacing="4">
<tr><td align="left">
see the <a href="http://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.01.html#topic_About_Charset_Encoding">CookBook</a> for more on this topic.
</td></tr>
</table>
</td>
<td>
Not internally defined by the Shapefile itself.<br>
Attempting to guess the appropriate charset encoding required by some Shapefile is more a magic art than rational science.
</td>
<td>
Always internally defined by the GML/XML file itself.
</td>
<td>
RFC 7946 strictly requires that all GeoJSON files must be encoded as <b>UTF-8</b><br><br>
In pure theory both <b>UTF-16</b> and <b>UTF-32</b> could be used for encoding a legitimate GeoJSON file, but such options seems to be very rarely (if ever) adopted in real world.
</td>
<td>
<ul>
<li>Shapefiles leaves a lot to be desired, and lack of defined charset encoding causes many serious portability issues.</li>
<li>GML/XML nicely supports any possible charset in the most flexible (and safe)
way</li>
<li>Once again, GeoJSON is straightforward and simple, but really effective.</li>
</ul>
</td>
</tr>
</table>
<h2>Skeletal GeoJSON anatomy</h2>
<verbatim>
{
   "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"}
       }
   }]
}
</verbatim>
As you can easily notice, GeoJSON has a plain regular structure and is decisively most concise and less verbose than GML/XML:
<ul>
<li>Every GeoJSON file must contain a <b>FeatureCollection</b> object.
<ul>
<li>A  <b>FeatureCollection</b> contains one or more individual <b>Feature</b> objects.
<ul>
<li>Each <b>Feature</b> is expected to declare a <b>Geometry</b> and a <b>properties</b> array.
<ul>
<li>Each property corresponds to an attribute with a <b>name</b> and a <b>value</b>.</li>
</li></ul>
</li></ul>
</li></ul>
</ul>
<h3>Short conclusions</h3>
GeoJSON is widely adopted by many web-apps; being a notation directly based on <b>JavaScript</b> it has a natural integration in popular <i>client-side</i> JS libraries such as <a href="https://en.wikipedia.org/wiki/OpenLayers">OpenLayers</a> and <a href="https://en.wikipedia.org/wiki/Leaflet_(software)">Leaflet</a>.<br>
It's adequately supported by <a href="https://en.wikipedia.org/wiki/GDAL">GDAL</a>, and consequently by many others free/libre sw components (as e.g. <a href="https://en.wikipedia.org/wiki/MapServer">MapServer</a> and <a href="https://en.wikipedia.org/wiki/GeoServer">GeoServer</a>).<br><br>
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.<br>
Starting since version <b>5.0.0</b> SpatiaLite offers full supports to GeoJSON as defined by RFC 7946.<br>
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.
<br><br>
<hr>
<h1>SQL functions supporting GeoJSON</h1>
<h3>Exporting data to GeoJSON</h3>
<verbatim>
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
</verbatim>
The SQL function <b>ExportGeoJSON2()</b> can directly export a whole Spatial Table (aka <i>Layer</i>) into an external GeoJSON file conformant to RFC 7946.<br>
This function accepts the following arguments:
<ol>
<li><b>table</b>: (<i>mandatory</i>) name of the Spatial Table to be exported.</li>
<li><b>geom_column</b>: (<i>mandatory</i>) name of the Column containing the Geometries to be exported.<br>
Can be <b>NULL</b>, 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.</li>
<li><b>filename</b>: (<i>mandatory</i>) absolute or relative path of the GeoJson file to be created (<i>output file</i>).</li>
<li><b>precision</b>: (<i>optional</i>) number of decimal digits to be used for coordinate values; the default setting is <b>8</b>.</li>
<li><b>lon_lat</b>: (<i>optional</i>) this is a boolean flag:
<ul>
<li>if set to <b>FALSE</b> all coordinates will be exported exactly as they are.</li>
<li>if set to <b>TRUE</b> all coordinates will be automatically transformed (if required) into <b>SRID=4326 WGS 84</b> as required by RFC 7946.<br>
This is the default setting.</li>
</ul></li>
<li><b>M_coords</b>: (<i>optional</i>) this too is a boolean flag:
<ul>
<li>if set to <b>FALSE</b> eventual <b>M-values</b> will be simply ignored, as required by RFC 7946.<br>
This is the default setting.</li>
<li>if set to <b>TRUE</b> eventual <b>M-values</b> will be exported into the GeoJSON file as extra-coordinates.<br>
<u>Note</u>: the resulting output file could easily suffer from limited portability.</li>
</ul></li>
<li><b>indented</b>: (<i>optional</i>) yet another boolean flag:
<ul>
<li>If set to <b>TRUE</b> the output file will be properly indented for enhanced human readability.<br>
This is the default setting.</li>
<li>If set to <b>FALSE</b> the output file will be all in a single monolithic line without blank spaces or newlines (noticeably smaller).</li>
</ul></li>
<li><b>colname_case</b>: (<i>optional</i>) can assume one the following values:
<ul>
<li><b>'UPPER'</b> or <b>'UPPERCASE'</b>: all attribute names will be inserted into the output file in full upper case.</li>
<li><b>'LOWER'</b> or <b>'LOWERCASE'</b>: all attribute names will be inserted into the output file in full lower case.<br>
This is the default setting.</li>
<li><b>'SAME'</b> or <b>'SAMECASE'</b>: 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.</li>
</ul></li>
<li>the <b>return value</b> is an <b>INTEGER</b> corresponding to the number of exported rows, or <b>NULL</b> in the case of failure or invalid arguments.</li>
</ol>
<table cellspacing="4" cellpadding="8" bgcolor="#ffffd0"><tr><td>
<h3>Caveat</h3>
A similar function named <b>ExportGeoJSON()</b> was already supported by previous versions of SpatiaLite, but it was modelled against the old (<b>pre-RFC</b>) GeoJSON specification.<br>
<b>ExportGeoJSON()</b> is now <b>DEPRECATED</b>, and will remain to avoid breaking existing applications and scripts.<br><br>
It is strongly recommended to use <b>ExportGeoJSON2()</b> for any new development as full replacement conformant to RFC 7946.
</td></tr></table>
<br>
<table cellspacing="4" cellpadding="8" bgcolor="#ffc0a0"><tr><td>
<h3>Warning</h3>
This SQL function open the door to many potential security issues, and thus <b><i>is always disabled by default</i></b>.<br>
Explicitly setting the external variable <b>SPATIALITE_SECURITY=relaxed</b> is absolutely required in order to enable this function.
</td></tr></table>
<br>
<hr>
<h3>Importing data from GeoJSON</h3>
<verbatim>
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
</verbatim>
The SQL function <b>ImportGeoJSON()</b> can directly create a new Spatial Table by importing data from an extenal GeoJSON file conformant to RFC 7946.<br>
This function accepts the following arguments:
<ol>
<li><b>filename</b>: (<i>mandatory</i>) absolute or relative path of the GeoJson file to be imported (<i>input file</i>).</li>
<li><b>table</b>: (<i>mandatory</i>) name of the Spatial Table to be created.</li>
<li><b>geom_column</b>: (<i>optional</i>) name of the Geometry Column to be created.<br>
If not explicitly specified or <b>NULL</b> a column named <b>'geometry'</b> will be created by default.</li>
<li><b>spatial_index</b>: (<i>optional</i>) this is a boolean flag:
<ul>
<li>if set to <b>TRUE</b> a <b>SpatialIndex</b> supporting the Geometry Column will be automatically created.</li>
<li>if set to <b>FALSE</b> no SpatialIndex will be created.<br>
This is the default setting.</li>
</ul></li>
<li><b>srid</b>: (<i>optional</i>):
<ul>
<li>When it's a <b>positive integer</b> it corresponds to the SRID value to be assigned to all imported Geometries.</li>
<li>If <b>0</b> or <b>negative</b> all imported Geometries will be assumed to be in <b>SRID=4326 WGS 84</b> (<i>geographic coordinates expressed as <b>Longitudes</b> and <b>Latitudes</b></i>) as required by RFC 7946.<br>
This is the default setting.</li>
</ul></li>
<li><b>colname_case</b>: (<i>optional</i>) can assume one the following values:
<ul>
<li><b>'UPPER'</b> or <b>'UPPERCASE'</b>: all columns will be created into the table in full upper case.</li>
<li><b>'LOWER'</b> or <b>'LOWERCASE'</b>: all columns will be created into the table in full lower case.<br>
This is the default setting.</li>
<li><b>'SAME'</b> or <b>'SAMECASE'</b>: 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.</li>
</ul></li>
<li>the <b>return value</b> is an <b>INTEGER</b> corresponding to the number of exported rows, or <b>NULL</b> in the case of failure or invalid arguments.</li>
</ol>
<table cellspacing="4" cellpadding="8" bgcolor="#ffc0a0"><tr><td>
<h3>Warning</h3>
This SQL function open the door to many potential security issues, and thus <b><i>is always disabled by default</i></b>.<br>
Explicitly setting the external variable <b>SPATIALITE_SECURITY=relaxed</b> is absolutely required in order to enable this function.
</td></tr></table>
<br>
<hr>
<h3>VirtualGeoJSON</h3>
<verbatim>
CREATE VIRTUAL TABLE some_name USING VirtualGeoJSON( path, [ srid , [ colname_case ]] );
</verbatim>
A <b>VirtualGeoJSON</b> table is strictly similar to a <b>VirtualShape</b> table, the only difference between them being the file format of the underlaying datasource.<br>
Except for this small but fundamental detail both them behave in exactly the same way.
<br>
<ul>
<li>Just a single mandatory argument is required for creating a VirtualGeoJSON table; you are required to specify the <b>absolute</b> or <b>relative path</b> leading to the external GeoJSON file on the filesystem.</li>
<li>You can explicitly specify the optional argument <b>srid</b> for out-of-the-spec GeoJSON files based on some arbitrary Coordinate Reference System.<br>
By default VirtualGeoJSON will always assume that all Geometries should be assigned to <b>SRID=4326 WGS 84</b> (<i>longitudes and latitudes</i>), accordingly to RFC 7946 specifications.</li>
<li>A further optional argument <b>colname_case</b> is supported, and it has exactly the same interpretations as in <b>ImportGeoJSON()</b></li>
</ul>
<br>
<hr>
<h3>GeoJSON support in spatialite_gui</h3>
The latest version of the GUI tool now supports GeoJSON in exactly the same way it supports Shapefiles.
<br><br>
<table cellspacing="4" cellpadding="8" bgcolor="#fffff0" border="1">
<tr>
<td>
Two new buttons have been added to the toolbar:
<ul>
<li>one for <b>importing GeoJSON files</b></li>
<li>the other for <b>creating VirtualGeoJSON tables</b></li>
</ul>
</td>
<td align="center">
<img src="https://www.gaia-gis.it/gaia-sins/geojson-pics/toolbar.png" alt="GeoJSON toolbar buttons">
</td>
</tr>
<tr>
<td>
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.
</td>
<td align="center">
<img src="https://www.gaia-gis.it/gaia-sins/geojson-pics/load.png" alt="GeoJSON load">
</td>
</tr>
<tr>
<td>
The side figure is an example of the dialog box that's used for setting options when creating a VirtualGeoJSON Table.
</td>
<td align="center">
<img src="https://www.gaia-gis.it/gaia-sins/geojson-pics/virtual.png" alt="GeoJSON virtual table">
</td>
</tr>
<tr>
<td>
For exporting a whole Spatial Table into an external GeoJSON file:
<ul>
<li>in the tree-list <b>right-click</b> on the requested Geometry.
<ul>
<li>then click the <b>Export as GeoJSON</b> item on the context-menu that will appear.</li>
</ul></li>
</ul>
</td>
<td align="center">
<img src="https://www.gaia-gis.it/gaia-sins/geojson-pics/dump.png" alt="GeoJSON dump">
</td>
</tr>
</table>
<br><br>
<hr><br>
Back to <a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=5.0.0-doc">5.0.0-doc main page</a>
Z 1b5101b244857a131d5a06092b500a89