### Sidebar

spatial-ecology.org

Trainings:

Learn:

Data:

Community:
teachers
students
projects
Matera 2015
Vancouver 2015
Santa Barbara 2015
Site design
Hands on:
Installations

Donations
USD

GBP

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`