D 2020-05-10T18:03:27.141 L ZippedSHP P 00e3958bc4a3cf17f05a6bdb319b92a3fae43f78 U sandro W 12454 Back to main SpatiaLite page

Reading SHP and DBF files directly from within a Zipfile

Starting since version 5.0.0 SpatiaLite has the capability to directly read any Shapefile (or DBF file) contained within a compressed Zipfile.
Several closely related SQL functions have been introduced as well.

Please note: all the following SQL functions will access a Zipfile from the local file-system, and will consequently pose security concerns.

All them will be effectively available only if the external variable SPATIALITE_SECURITY=relaxed has been set.

Accessing a Zipped SHP

All these new SQL functions are now supported:

ImportZipSHP(): the main function allowing to import a whole Shapefile into a new Spatial Table into the currently connected Database.
PROJ_GuessSridFromZipSHP(): a helper function for attempting to guess the SRID for a given zipped SHP.
Zipfile_NumSHP(): a helper function returning the total count of Shapefiles contained within a given Zipfile.
Zipfile_ShpN(): a helper function returning the basename for the Nth Shapefile contained within a given Zipfile.

We'll now start a quick tutorial about using all these new SQL functions. Note: the tutorial is based on the dataset you can download from here SELECT Zipfile_NumSHP ( 'c:/users/afuri/Downloads/Limiti01012020.zip' ) ------------ 4

the SQL function Zipfile_NumSHP() will inspect a Zipfile then returning the total count of Shapefiles it contains.
it requires just a single argument: the absolute or relative path leading to the Zipfile.
Note it will return 0 if the Zipfile does not contain any Shapefile.
- NULL will be returned if the Zipfile does not exist of if it's corrupted or invalid.

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

the SQL function Zipfile_ShpN() will inspect a Zipfile then returning the basename of the Nth Shapefile it contains.
it requires two arguments:
- the absolute or relative path leading to the Zipfile.
- the relative index (1 based) of the Shapefile within the Zipfile.
Note: NULL will be returned if the Zipfile does not exist of if it's invalid or if the index value is outside the expected range.

SELECT PROJ_GuessSridFromZipSHP ( 'c:/users/afuri/Downloads/Limiti01012020.zip' , 'Limiti01012020/Com01012020/Com01012020_WGS84' ) ------------ 32632

the SQL function PROJ_GuessSridFromZipSHP() will inspect a Zipfile then returning the SRID value declared by the .PRJ member of the Shapefile contained into the Zipfile as identified by its basename.

it requires two arguments:

the absolute or relative path leading to the Zipfile.
the basename of the Shapefile within the Zipfile (without adding any .shp, .shx or .dbf suffix).

Note: -1 will be returned if the Shapefile has no .PRJ member, of if it contains an invalid WKT definition.

NULL

Please note: the SQL function PROJ_GuessSridFromZipSHP() will be available only when SpatiaLite is linked to a recent version of PROJ (PROJ.6.x.x or any later).

SELECT ImportZipSHP ( 'c:/users/afuri/Downloads/Limiti01012020.zip' , 'Limiti01012020/Com01012020/Com01012020_WGS84' , 'comuni', 'UTF-8' ) --------- 7904 The ImportZipSHP() closely mimics ImportSHP(), except in that it requires one more argument specifying the pathname of the Zipfile containing the Shapefile to be imported.
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 NULL.

In its simplest form it requires just four arguments:

the absolute or relative path leading to the Zipfile.
the basename of the Shapefile within the Zipfile (without adding any .shp, .shx or .dbf suffix).
the name of the Spatial Table to be created and then populated by importing the whole Shapefile.
the name of the charset encoding adopted by the .dbf member of the Shapefile.

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 In its complete form ImportZipSHP() supports these further optional arguments:

an explicitly stated SRID
the name of the Geometry Column
the name of the Shapefiles column to be assumed as the Primary Key
an explicitly set Geometry Type
a boolean flag requesting to cast all coordinates to XY
a boolean flag requesting for Compressed Geometries
a boolean flag requesting for creating a Spatial Index supporting the Geometry Column
a boolean flag requesting for importing all date values as plain text (instead of as Julian Dates)
a text string such as LOWER, UPPER or SAME specifying if all column names should be respectively Lowercase or Uppercase or as is.
a boolean flag for immediately updating table's Statistics
a boolean flag activating verbose mode

Accessing a Zipped DBF

All these new SQL functions are now supported:

ImportZipDBF(): the main function allowing to import a whole DBF file into a new Table into the currently connected Database.
Zipfile_NumDBF(): a helper function returning the total count of DBF files contained within a given Zipfile.
Zipfile_DbfN(): a helper function returning the filename for the Nth DBF file contained within a given Zipfile.

We'll continue to use the same Zipfile in this tutorial too. SELECT Zipfile_NumDBF ( 'c:/users/afuri/Downloads/Limiti01012020.zip' ) ------------ 4

the SQL function Zipfile_NumDBF() will inspect a Zipfile then returning the total count of DBF files it contains.
it requires just a single argument: the absolute or relative path leading to the Zipfile.
Note it will return 0 if the Zipfile does not contain any DBF file.
- NULL will be returned if the Zipfile does not exist of if it's corrupted or invalid.

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

the SQL function Zipfile_DbfN() will inspect a Zipfile then returning the filename of the Nth DBF file it contains.
it requires two arguments:
- the absolute or relative path leading to the Zipfile.
- the relative index (1 based) of the DBF file within the Zipfile.
Note: NULL will be returned if the Zipfile does not exist of if it's invalid or if the index value is outside the expected range.

SELECT ImportZipDBF ( 'c:/users/afuri/Downloads/Limiti01012020.zip' , 'Limiti01012020/Com01012020/Com01012020_WGS84.dbf' , 'comuni_dbf', 'UTF-8' ) --------- 7904 The ImportZipDBF() closely mimics ImportDBF(), except in that it requires one more argument specifying the pathname of the Zipfile containing the DBF file to be imported.
On success it will return the number of rows inserted into the Table that has just been created; on failure it will just return NULL.

In its simplest form it requires just four arguments:

the absolute or relative path leading to the Zipfile.
the filename of the DBF file within the Zipfile (including its .dbf suffix).
the name of the Table to be created and then populated by importing the whole DBF file.
the name of the charset encoding adopted by the DBF file.

SELECT ImportZipDBF ( 'c:/users/afuri/Downloads/Limiti01012020.zip' , 'Limiti01012020/Reg01012020/Reg01012020_WGS84.dbf' , 'regioni_dbf', 'UTF-8' , 'cod_reg' , 1 , 'LOWER' ) ------------- 20 In its complete form ImportZipDBF() supports these further optional arguments:

the name of the DBF column to be assumed as the Primary Key
a boolean flag requesting for importing all date values as plain text (instead of as Julian Dates)
a text string such as LOWER, UPPER or SAME specifying if all column names should be respectively Lowercase or Uppercase or as is.

GUI support for importing SHP and DBF datasets directly from a Zipfile

Using the standard file selection dialog box you simply have to select the intended Zipfile.
This Zipfile will be automatically scanned so to identify all Shapefiles (or respectively DBF files) it contains:

If the Zipfile does not contain any Shapefile (or DBF file) an error message will be shown and the whole import operation will faiil.
Otherwise the dialog box shown on the side figure will be shown.
Select one of the Shapefiles (of DBF file= from the list an then confirm.

A slightly modified version of the dialog box allowing to set all import options will appear.
Set any appropriate option and then continue as usual.

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.

Selecting multiple datasest in a single pass is not allowed.

Back to main SpatiaLite page Z 57519d7e65179daccb3ee16109c42a40