Working With Shapefiles

Quick Reference

• Look at shapefile info:

 ogrinfo -al filename.shp | less

• Convert to IOC's GCS:

 org2org -t_srs EPSG:4326 output.shp input.shp

• In case of field length errors, select the fields to be included:

 org2org -t_srs EPSG:4326 -select field1,field2,field3 output.shp input.shp

• Converting shapefiles to CSV with geometry in WKT format:

 ogr2ogr -f csv output.csv input.shp -lco GEOMETRY=AS_WKT

Starting with GDAL v2.0, the default is to produce three dimensional WKT geometry values. DB2 will not accept these. Add the option "-dim XY" to specify two dimensional values instead.

Importing shapefile into DB2 table:

 db2 connect to iocdata
 db2se import_shape iocdata -filename /full/path/to/shapefilename -srsName WGS84_SRS_1003 -tableSchema staging -tableName anything -spatialColumn shape

See the DB2 Knowledge Center for more details

Shape file info:

 db2se shape_info -filename shapefile

See the db2se command reference for details ​​​​​​

DB2 commands

 db2 list tables for schema schema
 db2 drop table schema.tablename
 db2 select \* from schema.tablename
 db2 describe table schema.tablename
 db2 ? errorNumber

Advanced Topic

For a transportation use case demo, create a shapefile that contains the road segments for the major highways in the area of a new bridge being built between Detroit and Windsor. Add a column to the shapefile that will contain a string indicating the status of each road segment:

• Download zip files from download.geofabrik.de that contain the latest OpenStreetMap data arranged into individual layer shapefiles:

 michigan-latest-free.shp.zip
 ontario-latest-free.shp.zip

• Extract the gis.osm_roads_free_1.* files from each zip into separate directories.
• Create individual shape files with the road segments needed by extracting the relevant road types in the relevant area from each of the downloaded shapefiles:

 ogr2ogr -t_srs EPSG:4326 -f "ESRI Shapefile" -where "fclass like 'primary%' or fclass like 'motorway%' or fclass like 'trunk%'" -clipsrc -83.2275 42.2316 -82.9898 42.3390 wdba.shp michigan/gis.osm_roads_free_1.shp
 ogr2ogr -t_srs EPSG:4326 -f "ESRI Shapefile" -where "fclass like 'primary%' or fclass like 'motorway%' or fclass like 'trunk%'" -clipsrc -83.2275 42.2316 -82.9898 42.3390 ontario.shp ontario/gis.osm_roads_free_1.shp

• Merge the ontario.shp data into the wdba.shp file:

 ogr2ogr -f ‘ESRI Shapefile’ -update -append wdba.shp ontario.shp -nln wdba

• Add a column for road segment status to the shapefile:

 ogrinfo wdba.dbf -sql "ALTER TABLE wdba ADD COLUMN  status varchar(20)"

• Set the values in the new column for all records

 ogrinfo wdba.dbf -dialect sqlite -sql "UPDATE 'wdba' SET status = 'OK'"