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

Artifact ID:	a2a1995d003686095779367b8112c58f1ea06037
Page Name:	ZippedSHP
Date:	2020-05-08 11:32:57
Original User:	sandro
Parent:	64df71ad6734f958727608ce4e1f6c3dd10bb7d4 (diff)
Next	00e3958bc4a3cf17f05a6bdb319b92a3fae43f78

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 will be returned on invalid arguments or if the Shapefile does not exist.

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 expplcitly 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 (istead of as Julian Dates)
• a text string such as LOWER, UPPER or SAME specifying if all column names should be resptectively Lowercase or Uppercase or as is.
• a boolean flag for immeditely 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.

---

Back to main SpatiaLite page