User Tools

Site Tools


wiki:exercise7c

Exercise 7c: Using sql commands

GDALOGR: ogr2ogr ogrinfo

Check the different classes (attribute=landuse) in the OSM polygons:

ogrinfo ${OUTPUTDIR}/exercise7/osm_multipolygons.sqlite -sql "select DISTINCT landuse from multipolygons"

Count number of polygons where landuse is forest:

ogrinfo ${OUTPUTDIR}/exercise7/osm_multipolygons.sqlite -sql "select COUNT(landuse) from multipolygons where landuse='forest'"

Script to count the polygons for each landuse class: show only those classes with more than 20 polygons

for LU in $(ogrinfo ${OUTPUTDIR}/exercise7/osm_multipolygons.sqlite -sql "select DISTINCT landuse from multipolygons"|grep 'landuse (String) =' |awk -F= '{print $2}');do 
ogrinfo ${OUTPUTDIR}/exercise7/osm_multipolygons.sqlite -sql "select COUNT(landuse) from multipolygons where landuse='$LU'"|grep "COUNT(landuse) (Integer) ="|awk -F= -v lu=$LU '{if ($2>20) print lu,$2}'
done

Create a SQLite vector with single attribute 'landuse' and retain only forest polygons

ogr2ogr -f "SQLite" ${OUTPUTDIR}/exercise7/osm_forest.sqlite ${OUTPUTDIR}/exercise7/osm_multipolygons.sqlite -where "landuse='forest'" -select 'landuse'

Create a SQLite vector with single attribute 'landuse' and retain selection of non-forest polygons: 'meadow','industrial','commercial','residential','grass'

ogr2ogr -f "SQLite" ${OUTPUTDIR}/exercise7/osm_nonforest.sqlite ${OUTPUTDIR}/exercise7/osm_multipolygons.sqlite -where "landuse in ('meadow','industrial','commercial','residential','grass')" -select 'landuse'

Merge the SQLite vectors

ogr2ogr -f SQLite ${OUTPUTDIR}/exercise7/osm_merged.sqlite ${OUTPUTDIR}/exercise7/osm_forest.sqlite
ogr2ogr -f SQLite -update -append ${OUTPUTDIR}/exercise7/osm_merged.sqlite ${OUTPUTDIR}/exercise7/osm_nonforest.sqlite
wiki/exercise7c.txt · Last modified: 2017/12/05 22:53 (external edit)