Difference between revisions of "Working With Shapefiles"
Jump to navigation
Jump to search
(Created page with " == 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 len...") |
|||
| (3 intermediate revisions by the same user not shown) | |||
| Line 17: | Line 17: | ||
db2se import_shape iocdata -filename /full/path/to/shapefilename -srsName WGS84_SRS_1003 -tableSchema staging -tableName anything -spatialColumn shape | 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 | + | See the [http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.spatial.topics.doc/doc/r0059680.html?lang=en DB2 Knowledge Center] for more details |
Shape file info: | Shape file info: | ||
db2se shape_info -filename shapefile | db2se shape_info -filename shapefile | ||
| − | | + | |
| − | Advanced Topic | + | See the [http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.spatial.topics.doc/doc/r0059680.html?lang=en db2se] command reference for details |
| − | *Download zip files from download.geofabrik.de that contain the latest OpenStreetMap data arranged into individual layer shapefiles: | + | [[Category:Geospatial]] |
| + | |||
| + | ==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 [http://download.geofabrik.de/ download.geofabrik.de] that contain the latest OpenStreetMap data arranged into individual layer shapefiles: | ||
michigan-latest-free.shp.zip | michigan-latest-free.shp.zip | ||
ontario-latest-free.shp.zip | ontario-latest-free.shp.zip | ||
*Extract the gis.osm_roads_free_1.* files from each zip into separate directories. | *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: | + | *Create individual shape files with the road segments needed by extracting the [http://wiki.openstreetmap.org/wiki/Key:highway#Values 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 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 | 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 | ||
| Line 36: | Line 47: | ||
*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'" | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
Latest revision as of 14:19, 31 December 2019
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'"
