Working With Shapefiles

From Nearline Storage
Revision as of 14:19, 31 December 2019 by Dlk (talk | contribs) (→‎Quick Reference)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

Jump to navigation Jump to search

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