Artifact [f4198393ed]
Not logged in

Artifact f4198393ed6084c2dbe6ea0bb6a8c615bdc7d3ad:

Wiki page [ZippedSHP] by sandro 2020-07-18 17:55:08.
D 2020-07-18T17:55:08.737
L ZippedSHP
P 3da774363fc236b7060d15c330f90b976386bf5b
U sandro
W 12452
Back to <a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=5.0.0-doc">5.0.0-doc main page</a>
<h2>Reading SHP and DBF files directly from within a Zipfile</h2>
Starting since version <b>5.0.0</b> SpatiaLite has the capability to directly read any <b>Shapefile</b> (or <b>DBF</b> file) contained within a compressed <b>Zipfile</b>.<br>
Several closely related SQL functions have been introduced as well.<br><br>
<table bgcolor="#ffffd0" cellspacing="12" cellpadding="8">
<tr><td>
<b>Please note</b>: all the following SQL functions will access a Zipfile from the local file-system, and will consequently pose security concerns.<br><br>
All them will be effectively available only if the external variable <b>SPATIALITE_SECURITY=relaxed</b> has been set.
</td></tr>
</table>
<h3>Accessing a Zipped SHP</h3>
All these new SQL functions are now supported:
<ul>
<li><b>ImportZipSHP()</b>: the main function allowing to import a whole Shapefile into a new Spatial Table into the currently connected Database.</li>
<li><b>PROJ_GuessSridFromZipSHP()</b>: a helper function for attempting to guess the SRID for a given zipped SHP.</li>
<li><b>Zipfile_NumSHP()</b>: a helper function returning the total count of Shapefiles contained within a given Zipfile.</li>
<li><b>Zipfile_ShpN()</b>: a helper function returning the <b><i>basename</i></b> for the <b>Nth</b> Shapefile contained within a given Zipfile.</li>
</ul>
<br>
We'll now start a quick tutorial about using all these new SQL functions.
<b>Note</b>: the tutorial is based on the dataset you can download from <a href="http://www.istat.it/storage/cartografia/confini_amministrativi/non_generalizzati/Limiti01012020.zip">here</a>
<verbatim>
SELECT Zipfile_NumSHP ( 'c:/users/afuri/Downloads/Limiti01012020.zip' )
------------
4
</verbatim>
<ul>
<li>the SQL function <b>Zipfile_NumSHP()</b> will inspect a Zipfile then returning the total count of Shapefiles it contains.</li>
<li>it requires just a single argument: the absolute or relative path leading to the Zipfile.</li>
<li><b>Note</b> it will return <b>0</b> if the Zipfile does not contain any Shapefile.
<ul>
<li><b>NULL</b> will be returned if the Zipfile does not exist of if it's corrupted or invalid.</li>
</ul></li>
</ul>
<verbatim>
SELECT Zipfile_shpN ( 'c:/users/afuri/Downloads/Limiti01012020.zip' , 1 )
------
Limiti01012020/Com01012020/Com01012020_WGS84

SELECT Zipfile_shpN ( 'c:/users/afuri/Downloads/Limiti01012020.zip' , 2 )
------
Limiti01012020/ProvCM01012020/ProvCM01012020_WGS84

SELECT Zipfile_shpN ( 'c:/users/afuri/Downloads/Limiti01012020.zip' , 3 )
------
Limiti01012020/Reg01012020/Reg01012020_WGS84

SELECT Zipfile_shpN ( 'c:/users/afuri/Downloads/Limiti01012020.zip' , 4 )
-------
Limiti01012020/RipGeo01012020/RipGeo01012020_WGS84
</verbatim><ul>
<li>the SQL function <b>Zipfile_ShpN()</b> will inspect a Zipfile then returning the <b><i>basename</i></b> of the <b>Nth</b> Shapefile it contains.</li>
<li>it requires two arguments:
<ul>
<li>the absolute or relative path leading to the Zipfile.</li>
<li>the relative index (<b>1 based</b>) of the Shapefile within the Zipfile.</li>
</ul></li>
<li><b>Note</b>: <b>NULL</b> will be returned if the Zipfile does not exist of if it's invalid or if the index value is outside the expected range.</li>
</ul>
<verbatim>
SELECT PROJ_GuessSridFromZipSHP ( 'c:/users/afuri/Downloads/Limiti01012020.zip' , 'Limiti01012020/Com01012020/Com01012020_WGS84' )
------------
32632
</verbatim>
<li>the SQL function <b>PROJ_GuessSridFromZipSHP()</b> will inspect a Zipfile then returning the <b>SRID</b> value declared by the <b>.PRJ</b> member  of the Shapefile contained into the Zipfile as identified by its <b><i>basename</i></b>.</li>
<li>it requires two arguments:
<ul>
<li>the absolute or relative path leading to the Zipfile.</li>
<li>the <b><i>basename</i></b> of the Shapefile within the Zipfile (without adding any <b>.shp</b>, <b>.shx</b> or <b>.dbf</b> suffix).</li>
</ul></li>
<li><b>Note</b>: <b>-1</b> will be returned if the Shapefile has no <b>.PRJ</b> member, of if it contains an invalid WKT definition.</li>
<ul>
</li><b>NULL</b> will be returned on invalid arguments or if the Shapefile does not exist.</li>
</li></ul>
</ul>
<table bgcolor="#ffffd0" cellspacing="12" cellpadding="8">
<tr><td>
<b>Please note</b>: the SQL function <b>PROJ_GuessSridFromZipSHP()</b> will be available only when SpatiaLite is linked to a recent version of <b>PROJ</b> (<b>PROJ.6.x.x</b> or any later).
</td></tr>
</table>
<verbatim>
SELECT ImportZipSHP ( 'c:/users/afuri/Downloads/Limiti01012020.zip' , 
                      'Limiti01012020/Com01012020/Com01012020_WGS84' , 
                      'comuni', 'UTF-8' ) 
---------
7904
</verbatim>
The <b>ImportZipSHP()</b> closely mimics <b>ImportSHP()</b>, except in that it requires one more argument specifying the pathname of the Zipfile containing the Shapefile to be imported.<br>
On success it will return the number of features inserted into the Spatial Table that has just been created; on failure it will just return <b>NULL</b>.<br><br>
In its simplest form it requires just four arguments:
<ul>
<li>the absolute or relative path leading to the Zipfile.</li>
<li>the <b><i>basename</i></b> of the Shapefile within the Zipfile (without adding any <b>.shp</b>, <b>.shx</b> or <b>.dbf</b> suffix).</li>
<li>the name of the Spatial Table to be created and then populated by importing the whole Shapefile.</li>
<li>the name of the charset encoding adopted by the <b>.dbf</b> member of the Shapefile.</li>
</ul>
<verbatim>
SELECT ImportZipSHP ( 'c:/users/afuri/Downloads/Limiti01012020.zip' , 
                      'Limiti01012020/Reg01012020/Reg01012020_WGS84' , 
                      'regioni', 'UTF-8' , 32632 , 'the_geom' , 'cod_reg' ,
                      'MULTIPOLYGON' , 0 , 0 , 1 , 0 , 'LOWER' , 1 , 0 ) 
-------------
20
</verbatim>
In its complete form <b>ImportZipSHP()</b> supports these further optional arguments:
<ul>
<li>an explicitly stated SRID</li>
<li>the name of the Geometry Column</li>
<li>the name of the Shapefiles column to be assumed as the Primary Key</li>
<li>an explicitly set Geometry Type</li>
<li>a <i>boolean</i> flag requesting to cast all coordinates to XY</li>
<li>a <i>boolean</i> flag requesting for Compressed Geometries</li>
<li>a <i>boolean</i> flag requesting for creating a Spatial Index supporting the Geometry Column</li>
<li>a <i>boolean</i> flag requesting for importing all date values as plain text (instead of as Julian Dates)</li>
<li>a text string such as <b>LOWER</b>, <b>UPPER</b> or <b>SAME</b> specifying if all column names should be respectively Lowercase or Uppercase or as is.</li>
<li>a <i>boolean</i> flag for immediately updating table's Statistics</li>
<li>a <i>boolean</i> flag activating verbose mode</li>  
</ul>
<br><br>
<hr>
<h3>Accessing a Zipped DBF</h3>
All these new SQL functions are now supported:
<ul>
<li><b>ImportZipDBF()</b>: the main function allowing to import a whole DBF file into a new Table into the currently connected Database.</li>
<li><b>Zipfile_NumDBF()</b>: a helper function returning the total count of DBF files contained within a given Zipfile.</li>
<li><b>Zipfile_DbfN()</b>: a helper function returning the <b><i>filename</i></b> for the <b>Nth</b> DBF file contained within a given Zipfile.</li>
</ul>
<br>
We'll continue to use the same Zipfile in this tutorial too.
<verbatim>
SELECT Zipfile_NumDBF ( 'c:/users/afuri/Downloads/Limiti01012020.zip' )
------------
4
</verbatim>
<ul>
<li>the SQL function <b>Zipfile_NumDBF()</b> will inspect a Zipfile then returning the total count of DBF files it contains.</li>
<li>it requires just a single argument: the absolute or relative path leading to the Zipfile.</li>
<li><b>Note</b> it will return <b>0</b> if the Zipfile does not contain any DBF file.
<ul>
<li><b>NULL</b> will be returned if the Zipfile does not exist of if it's corrupted or invalid.</li>
</ul></li>
</ul>
<verbatim>
SELECT Zipfile_dbfN ( 'c:/users/afuri/Downloads/Limiti01012020.zip' , 1 )
------
Limiti01012020/Com01012020/Com01012020_WGS84.dbf

SELECT Zipfile_dbfN ( 'c:/users/afuri/Downloads/Limiti01012020.zip' , 2 )
------
Limiti01012020/ProvCM01012020/ProvCM01012020_WGS84.dbf

SELECT Zipfile_dbfN ( 'c:/users/afuri/Downloads/Limiti01012020.zip' , 3 )
------
Limiti01012020/Reg01012020/Reg01012020_WGS84.dbf

SELECT Zipfile_dbfN ( 'c:/users/afuri/Downloads/Limiti01012020.zip' , 4 )
-------
Limiti01012020/RipGeo01012020/RipGeo01012020_WGS84.dbf
</verbatim><ul>
<li>the SQL function <b>Zipfile_DbfN()</b> will inspect a Zipfile then returning the <b><i>filename</i></b> of the <b>Nth</b> DBF file it contains.</li>
<li>it requires two arguments:
<ul>
<li>the absolute or relative path leading to the Zipfile.</li>
<li>the relative index (<b>1 based</b>) of the DBF file within the Zipfile.</li>
</ul></li>
<li><b>Note</b>: <b>NULL</b> will be returned if the Zipfile does not exist of if it's invalid or if the index value is outside the expected range.</li>
</ul>
<verbatim>
SELECT ImportZipDBF ( 'c:/users/afuri/Downloads/Limiti01012020.zip' , 
                      'Limiti01012020/Com01012020/Com01012020_WGS84.dbf' , 
                      'comuni_dbf', 'UTF-8' ) 
---------
7904
</verbatim>
The <b>ImportZipDBF()</b> closely mimics <b>ImportDBF()</b>, except in that it requires one more argument specifying the pathname of the Zipfile containing the DBF file to be imported.<br>
On success it will return the number of rows inserted into the Table that has just been created; on failure it will just return <b>NULL</b>.<br><br>
In its simplest form it requires just four arguments:
<ul>
<li>the absolute or relative path leading to the Zipfile.</li>
<li>the <b><i>filename</i></b> of the DBF file within the Zipfile (including its  <b>.dbf</b> suffix).</li>
<li>the name of the Table to be created and then populated by importing the whole DBF file.</li>
<li>the name of the charset encoding adopted by the DBF file.</li>
</ul>
<verbatim>
SELECT ImportZipDBF ( 'c:/users/afuri/Downloads/Limiti01012020.zip' , 
                      'Limiti01012020/Reg01012020/Reg01012020_WGS84.dbf' , 
                      'regioni_dbf', 'UTF-8' , 'cod_reg' , 1 , 'LOWER' ) 
-------------
20
</verbatim>
In its complete form <b>ImportZipDBF()</b> supports these further optional arguments:
<ul>
<li>the name of the DBF column to be assumed as the Primary Key</li>
<li>a <i>boolean</i> flag requesting for importing all date values as plain text (instead of as Julian Dates)</li>
<li>a text string such as <b>LOWER</b>, <b>UPPER</b> or <b>SAME</b> specifying if all column names should be respectively Lowercase or Uppercase or as is.</li> 
</ul>
<br>
<hr>
<h3>GUI support for importing SHP and DBF datasets directly from a Zipfile</h3>
<table width="100%" cellpadding="12" cellspacing="8">
<tr><td>
<ol>
<li>Using the standard file selection dialog box you simply have to select the intended Zipfile.</li>
<li>This Zipfile will be automatically scanned so to identify all Shapefiles (or respectively DBF files) it contains:</li>
<ul>
<li>If the Zipfile does not contain any Shapefile (or DBF file) an error message will be shown and the whole import operation will faiil.</li>
<li>Otherwise the dialog box shown on the side figure will be shown.<br>
Select one of the Shapefiles (of DBF file= from the list an then confirm.</li>
</ul></li>
</ol></td>
<td align="center"><img src="https://www.gaia-gis.it/gaia-sins/gui2-pics/zip-shp-1.png" alt="GUI_1"></td></tr>
<tr><td>A slightly modified version of the dialog box allowing to set all import options will appear.<br>
Set any appropriate option and then continue as usual.
<br>
<br>
<table bgcolor="#ffffd0" cellspacing="12" cellpadding="8">
<tr><td>
If you wish to import more than a single Shapefile (od DBF file) from the same Zipfile you must reapete the same procedure for each dataset.<br><br><br>
Selecting multiple datasest in a single pass is not allowed.
</td></tr>
</table></td>
<td align="center"><img src="https://www.gaia-gis.it/gaia-sins/gui2-pics/zip-shp-2.png" alt="GUI_2"></td></tr>
</table>
<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 40160da240d37339eb5cedf57ad9a82c