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
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'"
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
