SpatiaLite logo

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: shapefile import
spatialite_gui: Shapefile import

shapefile export
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: DBF import
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: TXT/CSV import
spatialite_gui: TXT/CSV import

TXT/CSV export
spatialite_gui: TXT/CSV export


Other supported export formats

Using spatialite_gui you can also export your data as: HTML export
spatialite_gui: HTML export

HTML sample
HTML export sample



MAP export 1
spatialite_gui: PNG / PDF / SVG export (1)

MAP export 2
spatialite_gui: PNG / PDF / SVG export (2)



PNG sample
PNG export sample

PDF sample
PDF export sample

SVG sample
SVG export sample

Previous Slide Table of Contents Next Slide

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

GNU logo 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.