D 2020-05-08T09:16:14.826 L ZippedSHP P d1f953cf63330714941fe06556b55e00996f8cd1 U sandro W 4576 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.

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 fuction 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 argumenst:
- 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 Zipfiles as identified by its basename.

it requires two argumenst:

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

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.

Back to main SpatiaLite page Z 51021521a3f2e42a91b5654e0e3f56d3