Loading data into Spatialite

See also Loading data into PostGIS.

Using ogr2ogr is convenient because you can load the data and reproject it in one step. Also very convenient because you can load any data that ogr2ogr knows about, do ogr2ogr --formats for the list.

# Define desired spatial reference system using EPSG code, this one is WGS84.
PRJ="EPSG:4326"
# Define the source and destination files
SRC=building.shp
DST='building.sql3'
# Do the work
ogr2ogr -t_srs $PRJ -f SQLite $DST $SRC

Do note the order is "destination source" not "source destination"

For Mapping Vietnam I want to load data from GIS Data Depot into PostGIS. At the moment I only want to do this so I have some data for testing. Later on I might even want these layers in a map.

The downloaded data are in ESRI "E00" format and have been compressed.

# Uncompress all downloaded files
for file in *.E00.GZ; do gunzip $file; done

# put the data into "Web Mercator" projection
PRJ="EPSG:900913"
DB=vietnam

# The original files were coverages, so I have to know something about
# what I want to transfer. For example for this file I want only PAL
ogrinfo PPPOLY.E00

1: ARC (Line String) 2: CNT (Point) 3: LAB (Point) 4: PAL (Polygon) This is the one I want.

ogr2ogr -t_srs $PRJ -f PostgreSQL \
PG:"user=postgres dbname=$DB" \
PPPOLY.E00 PAL \
-lco GEOMETRY_NAME=the_geom -nln political_boundaries

Loading broken shapefiles

You could simply drop all the broken bits and pieces, or you could try to repair the uncooperative rows.

I am beginning to think that all shapefiles are "broken" and that this fact might be why ArcGIS models crash randomly.

I jumped through many hoops (see below for now) trying to get shapefiles to load with ogr2ogr. Then I found that shp2pgsql just worked.

shp2pgsql -c -s 32026 $SRCDIR/centerline.shp > centerline.sql
psql -U postgres -f centerline.sql solarmap

Dropping broken rows

First off, if you have access to ArcGIS, run your shapefiles through the "Repair Geometry" tool to repair or remove broken features.

Secondly, apparently it is common for shapefiles to have columns that exceed the constraints defined in their DBF fork. Use PRECISION=NO to force them to load.

Thirdly, use 'skipfailures' to drop features that are wrong, for example in multilinestring format in a linestring shapefile.

ogr2ogr -skipfailures -t_srs $PRJ $DST $SRC -lco GEOMETRY_NAME=the_geom -lco PRECISION=NO

Repairing

BUT WAIT! I really don't want to simply drop the benighted features. I want to fix them. And by the way, I don't happen to have ArcGIS right now.

First generate a SQL file.

ogr2ogr -t_srs $PRJ -f PGDump $SRC.sql $SRC -lco GEOMETRY_NAME=the_geom -lco PRECISION=NO

Next edit the file to force the AddGeometryColumn function to create a GEOMETRY type column instead of a LINESTRING column. This will allow any combination of LINESTRING and MULTILINESTRING objects to be loaded.

Finally, load the SQL data using psql

psql -U postgres -d corvallis -f $SRC.sql

This worked; I now have a centerlines file with 5218 rows in it. See?

corvallis=# select COUNT(ogc_fid) from centerline;
count 
-------
 5218

Here are 2 queries that list the MULTISTRING rows

SELECT ogc_fid,fullname FROM centerline WHERE GeometryType(geom_nad27)='MULTILINESTRING';

SELECT ogc_fid,fullname FROM centerline WHERE ST_NumGeometries(geom_nad27)>1;

Break into pieces (works for single part objects too)

SELECT ogc_fid,ST_Dump(geom_nad27) FROM centerline WHERE ST_NumGeometries(geom_nad27)>1;

Comparison

Here are commands I used to load the data for the ArcGIS For Server comparison article.

First I need a database.

Then I need data, loaded. Check your work

The ogrinfo command can peek into your PostGIS database just as it can look into any spatial file it knows how to read. The -so option gives summary output instead of dumping every row to the screen.

ogrinfo -so PG:"user=postgres dbname=vietnam" political_boundaries