Note: these pages are no longer maintained

Never the less, much of the information is still relevant.
Beware, however, that some of the command syntax is from older versions, and thus may no longer work as expected.
Also: external links, from external sources, inside these pages may no longer function.

	Importing / Exporting Shapefiles (DBF, TXT ...)
2011 January 28

Previous Slide

Table of Contents

Next Slide

There are several data formats that are absolutely widespread in the GIS professional world.
All them are open formats (i.e. they are not specifically bounded to any specific proprietary software, they are not patent covered, and they are publicly documented).

Not at all surprisingly, such formats are universally supported by any GIS-related software, and can be safely used for data interchange purposes between different platforms and systems.

the ESRI Shapefile format represents the de-facto universal format for GIS data exchange.
the DBF format was introduced in the very early days of personal computing by dBase (the first DBMS-like software to gain universal popularity):
a DBF file is included in every Shapefile but is quite common finding naked DBF files simply used to ship flat tables.
the TXT/CSV format simply identifies any structured text file (usually, tab separated values or comma separated values are the most often found variants).

SpatiaLite supports all the above formats for import and/or export.

Please note well: other data formats are very popular and widespread. e.g. the following ones:

Microsoft Excel spreadsheets (.xls)
Microsoft Access database (.mdb)
and many, many others.

All them are closed (proprietary) formats.
i.e. some specific proprietary software or operating system is strictly required, there is no publicly available documentation, and/or they are patent covered.
And all this easily explains why SpatiaLite (as many others open source packages) cannot support such closed formats.

Shapefiles

SpatiaLite supports both import and export for Shapefiles:

you can directly access any external Shapefile via SQL as a VirtualShapefile table
you can import any Shapefile into a DB Table:
- using spatialite_gui you'll find a Load Shapefile item in the main menu and in the toolbar.
- using the spatialite CLI front-end you can use the .loadshp macro
- or you can use the spatialite_tool shell command
you can export any Geometry Table as a Shapefile:
- using spatialite_gui you'll find an Export As Shapefile item on the context menu corresponding to any Geometry column within the main tree-view.
- using the spatialite CLI front-end you can use the .dumpshp macro
- or you can use the spatialite_tool shell command

spatialite_gui: Shapefile import

spatialite_gui: Shapefile export

> spatialite counties.sqlite
SpatiaLite version ..: 2.4.0-RC5 Supported Extensions:
        - 'VirtualShape'        [direct Shapefile access]
        - 'VirtualDbf'          [direct DBF access]
        - 'VirtualText'         [direct CSV/TXT access]
        - 'VirtualNetwork'      [Dijkstra shortest path]
        - 'RTree'               [Spatial Index - R*Tree]
        - 'MbrCache'            [Spatial Index - MBR cache]
        - 'VirtualFDO'          [FDO-OGR interoperability]
        - 'SpatiaLite'          [Spatial SQL - OGC]
PROJ.4 version ......: Rel. 4.7.1, 23 September 2009
GEOS version ........: 3.3.0-CAPI-1.7.0
SQLite version ......: 3.7.4
Enter ".help" for instructions
spatialite> .loadshp prov2010_s counties CP1252 23032
the SPATIAL_REF_SYS table already contains some row(s)
========
Loading shapefile at 'prov2010_s' into SQLite table 'counties'

BEGIN;
CREATE TABLE counties (
PK_UID INTEGER PRIMARY KEY AUTOINCREMENT,
"OBJECTID" INTEGER,
"COD_PRO" INTEGER,
"NOME_PRO" TEXT,
"SIGLA" TEXT);
SELECT AddGeometryColumn('counties', 'Geometry', 23032, 'MULTIPOLYGON', 'XY');
COMMIT;

Inserted 110 rows into 'counties' from SHAPEFILE
========
spatialite> .headers on
spatialite> SELECT * FROM counties LIMIT 5;
PK_UID|OBJECTID|COD_PRO|NOME_PRO|SIGLA|Geometry
1|1|1|Torino|TO|
2|2|2|Vercelli|VC|
3|3|3|Novara|NO|
4|4|4|Cuneo|CN|
5|5|5|Asti|AT|
spatialite>

spatialite CLI front-end: shapefile import

spatialite> .dumpshp counties Geometry exported_counties CP1252
========
Dumping SQLite table 'counties' into shapefile at 'exported_counties'

SELECT * FROM "counties" WHERE GeometryAliasType("Geometry") = 'POLYGON'
OR GeometryAliasType("Geometry") = 'MULTIPOLYGON' OR "Geometry" IS NULL;

Exported 110 rows into SHAPEFILE
========
spatialite> .quit

>

spatialite CLI front-end: shapefile export

> spatialite_tool -i -shp prov2010_s -d db.sqlite -t counties -c CP1252 -s 23032
SQLite version: 3.7.4
SpatiaLite version: 2.4.0-RC5
Inserted 110 rows into 'counties' from 'prov2010_s.shp'

>

spatialite_tool shell command: shapefile import

> spatialite_tool -e -shp exported_counties -d db.sqlite -t counties -g Geometry -c CP1252
SQLite version: 3.7.4
SpatiaLite version: 2.4.0-RC5
Exported 110 rows into 'exported_counties.shp' from 'counties'

>

spatialite_tool shell command: shapefile export

DBF files

SpatiaLite simply supports import for DBF files:

you can directly access any external DBF file via SQL as a VirtualDbf table
you can import any DBF file into a DB Table:
- using spatialite_gui you'll find a Load DBF item in the main menu and in the toolbar.
- using the spatialite CLI front-end you can use the .loaddbf macro
- or you can use the spatialite_tool CLI command

spatialite_gui: DBF import

> spatialite local_councils.sqlite
SpatiaLite version ..: 2.4.0-RC5 Supported Extensions:
        - 'VirtualShape'        [direct Shapefile access]
        - 'VirtualDbf'          [direct DBF access]
        - 'VirtualText'         [direct CSV/TXT access]
        - 'VirtualNetwork'      [Dijkstra shortest path]
        - 'RTree'               [Spatial Index - R*Tree]
        - 'MbrCache'            [Spatial Index - MBR cache]
        - 'VirtualFDO'          [FDO-OGR interoperability]
        - 'SpatiaLite'          [Spatial SQL - OGC]
PROJ.4 version ......: Rel. 4.7.1, 23 September 2009
GEOS version ........: 3.3.0-CAPI-1.7.0
SQLite version ......: 3.7.4
Enter ".help" for instructions
spatialite> .loaddbf com2010_s.dbf local_councils CP1252
========
Loading DBF at 'com2010_s.dbf' into SQLite table 'local_councils'

BEGIN;
CREATE TABLE local_councils (
PK_UID INTEGER PRIMARY KEY AUTOINCREMENT,
"OBJECTID" INTEGER,
"COD_REG" INTEGER,
"COD_PRO" INTEGER,
"COD_COM" INTEGER,
"PRO_COM" INTEGER,
"NOME_COM" TEXT,
"NOME_ITA" TEXT,
"NOME_TED" TEXT);
COMMIT;

Inserted 8094 rows into 'local_councils' from DBF ========
spatialite> .headers on
spatialite> SELECT * FROM local_councils LIMIT 5 OFFSET 5000;
PK_UID|OBJECTID|COD_REG|COD_PRO|COD_COM|PRO_COM|NOME_COM|NOME_ITA|NOME_TED
5001|4958|12|58|54|58054|Manziana|Manziana|
5002|4959|12|58|55|58055|Marano Equo|Marano Equo|
5003|4960|12|58|56|58056|Marcellina|Marcellina|
5004|4961|12|58|57|58057|Marino|Marino|
5005|4962|12|58|58|58058|Mazzano Romano|Mazzano Romano|
spatialite> .quit

>

spatialite CLI front-end: DBF import

> spatialite_tool -i -dbf com2010_s -d db.sqlite -t local_councils -c CP1252
SQLite version: 3.7.4
SpatiaLite version: 2.4.0-RC5
Inserted 8094 rows into 'local_councils' from 'com2010_s.dbf'

>

spatialite_tool shell command: DBF import

TXT/CSV files

SpatiaLite supports both import and export for TXT/CSV files:

you can directly access any external TXT/CSV file via SQL as VirtualText table
you can import any TXT/CSV file into a DB Table:
- using spatialite_gui you'll find a Load TXT/CSV item in the main menu and in the toolbar.
you can export any Table as a TXT/CSV file:
- using spatialite_gui you'll find an Export As TXT/CSV item on the context menu corresponding to any Table within the main tree-view.

spatialite_gui: TXT/CSV import

spatialite_gui: TXT/CSV export

Other supported export formats

Using spatialite_gui you can also export your data as:

HTML web pages
PNG images (Geometries only)
PDF documents (Geometries only)
SVG vector graphics (Geometries only)

spatialite_gui: HTML export

HTML sample

HTML export sample

spatialite_gui: PNG / PDF / SVG export (1)

MAP export 2

spatialite_gui: PNG / PDF / SVG export (2)

PNG export sample

PDF sample

PDF export sample

SVG sample

SVG export sample

Previous Slide

Table of Contents

Next Slide

	Author: Alessandro Furieri a.furieri@lqt.it
	This work is licensed under the Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0) license.

	Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.3 or any later version published by the Free Software Foundation; with no Invariant Sections, no Front-Cover Texts, and no Back-Cover Texts.