Difference between revisions of "Working With Shapefiles"
Jump to navigation
Jump to search
m |
m |
||
Line 22: | Line 22: | ||
[http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.spatial.topics.doc/doc/r0059680.html?lang=en db2se] shape_info -filename shapefile | [http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.spatial.topics.doc/doc/r0059680.html?lang=en db2se] shape_info -filename shapefile | ||
| | ||
+ | ==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== | ==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: | 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 [http://download.geofabrik.de/ download.geofabrik.de] that contain the latest OpenStreetMap data arranged into individual layer shapefiles: | *Download zip files from [http://download.geofabrik.de/ download.geofabrik.de] that contain the latest OpenStreetMap data arranged into individual layer shapefiles: | ||
Line 38: | Line 44: | ||
*Set the values in the new column for all records | *Set the values in the new column for all records | ||
ogrinfo wdba.dbf -dialect sqlite -sql "UPDATE 'wdba' SET status = 'OK'" | ogrinfo wdba.dbf -dialect sqlite -sql "UPDATE 'wdba' SET status = 'OK'" | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− |
Revision as of 12:00, 30 November 2017
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
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'"