SpatiaLite: Update of "ZippedSHP"

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

Overview

Artifact ID:	00e3958bc4a3cf17f05a6bdb319b92a3fae43f78
Page Name:	ZippedSHP
Date:	2020-05-08 13:37:15
Original User:	sandro
Parent:	a2a1995d003686095779367b8112c58f1ea06037 (diff)
Next	3da774363fc236b7060d15c330f90b976386bf5b

Content

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 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 Zipfiles 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

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 explcitly 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.

Back to main SpatiaLite page