Upgrading GIS Capacity at Parks and Recreation, Tirana

Friday, December 23, 2016

Agjencia e Parqeve dhe Rekreacionit Tiranë, or APR, manages 40,000+ ha of parks, and has an ambitious plan to plant 2 million trees in Tirana by 2030. To prepare for this task, to continuously monitor progress toward it, and to finally achieve this target, APR will require in-house technical capacity. A crucial component of such technical capacity is a robust geographic information system (GIS) that stores and manages the authoritative record of all of APR’s land holdings and resources.

Currently a map of parcels of land managed by APR is maintained as a layer in AutoCAD. There are 46 parcels in the layer right now. Each parcel has trees planted already, however, there is no geolocation information for the trees. There is, however, information about the number and species of trees, and other information about each tree.

All this information is maintained in a MS-Excel spreadsheet with one sheet for every parcel. The 46 sheets containing the parcel-level information are names “Parcel 1,” “Parcel 2,” and so on. There are other sheets in the spreadsheet containing other related information about the trees. Other than the naming convention, there is no link between the parcels in AutoCAD and the sheets in Excel.

park parcels stored in a spreadsheet

First, the parcels layer was exported from AutoCAD to ESRI shapefile format as polygons. This ensured that closed polylines were treated as polygons in the GIS. And the 46 individual sheets in the Excel spreadsheet were combined into a single sheet, and a column was added to the sheet for the parcel number of each sheet. This resulted in a table with more than 900 rows that was then exported to a comma separated value (CSV) format:

apr-tirana$ ll
total 528
drwxr-xr-x  8 punkish  staff     272 Dec 22 18:11 .
drwxr-xr-x  7 punkish  staff     238 Dec 22 18:10 ..
-rwxrwxrwx  1 punkish  staff       4 Dec 13 12:34 parcels.cpg
-rwxrwxrwx  1 punkish  staff   24038 Dec 13 12:37 parcels.dbf
-rwxrwxrwx  1 punkish  staff    3120 Dec 13 12:34 parcels.idx
-rwxrwxrwx  1 punkish  staff  180244 Dec 13 12:37 parcels.shp
-rwxrwxrwx  1 punkish  staff     820 Dec 13 12:37 parcels.shx
-rw-r--r--@ 1 punkish  staff   47781 Dec 15 16:22 trees.csv
apr-tirana$ less trees.csv
1,Akacie,Trungishte,8,2,1,4,2,,Në gjëndje të mirë
1,Dafinë ,Shkurre,16,,,,,,Në gjëndje të mirë
1,Eukalipt,Trungishte,,1,4,4,4,,Në gjëndje të mirë
2,Akacie,Trungishte,20,20,40,10,12,,Mbi 20 cm degraduara
2,Akacie,Trungishte,10,20,,,,,Mbi 20 cm degraduara
2,Akacie,Trungishte,20,10,4,,,,Mbi 20 cm degraduara
apr-tirana$ wc -l parcels.csv 
    928 parcels.csv

The next step was to download and install Quantum GIS aka QGIS, a free and open source desktop GIS that allows users to “Create, edit, visualise, analyse and publish geospatial information on Windows, Mac, Linux, BSD (Android coming soon)” on their “desktop, server, in the web browser and as developer libraries.” QGIS is primarily a desktop product designed to work with local or remote data. There is a QGIS Server project as well, but it is mostly for publishing data using web mapping service (WMS) or web feature service (WFS) specifications. In any case, the QGIS family of products is not designed to serve as a central store of GIS data.

To enable central storage of a single, authoritative APR dataset, and to provide advanced analytical and publishing capabilities for the long term, PostGIS was chosen as the data storage option. PostGIS is a spatial extension to the free and open source relational database PostgreSQL (aka Postgres) that has been around for decades and is considered “the world’s most advanced opensource database.” Simple to get started, Postgres is capable of handling hundreds of millions of entities for virtually any business or analytical scenario. PostGIS, also free and open source, allows storing spatial data in Postgres and manipulating and analyzing these data with the help of dozens of spatial functions that allow querying spatial data like any other relational data.

Once Postgres/PostGIS were installed, a database was created to hold all the APR data, and this database was spatially enabled by installing the spatial extension via psql, the command line interface to Postgres:

apr-tirana$ psql -d apr -c "CREATE EXTENSION postgis;"
apr=# \l
                                List of databases
Name       |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
apr        | punkish  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
postgres   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
template0  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)
apr=# \q

As we can see above in the psql console, \l lists all the database, showing our newly created apr database with the default UTF8 encoding. We quit psql with the command \q to get back at the console prompt.

The shapefile was imported into Postgres with the help of a standalone utility program called shp2pgsql which comes with PostGIS

apr-tirana$ shp2pgsql -c -D -s 32634 -I parcels.shp public.parcels | psql -d apr

The shp2pgsql command is used with the following switches (note the Albania specific SRID as Albania is in UTM zone 34N).

-c Creates a new table and populates it, default if you do not specify any options.
-D Use postgresql dump format (defaults to sql insert statments).
-s from_srid 32634 for Albania (no to_srid is specified so no transformation happens).
-I Create a GiST index on the geometry column.

The output from shp2pgsql is piped directly to psql and into the apr database with the -d switch.

Finally, we create a table in Postgres to hold our trees.csv and import the CSV into it. For this, we go back into psql and ru the following commands

apr-tirana$ psql -d apr
apr=# CREATE TABLE trees (
apr(#    parcel_number INTEGER, 
apr(#    tree_type VARCHAR, 
apr(#    govt_type VARCHAR, 
apr(#    dia1_6cm INTEGER, 
apr(#    dia6_9cm INTEGER, 
apr(#    dia10_22cm INTEGER, 
apr(#    dia23_42cm INTEGER, 
apr(#    dia_42cm_plus INTEGER, 
apr(#    damaged_trees VARCHAR, 
apr(#    description VARCHAR
apr(# );
apr=# COPY trees FROM '~/apr-tirana/trees.csv' DELIMITER ',' CSV HEADER;

Now that all our data are imported into Postgres, we can connect to the database from QGIS and work with it in a real, world class, modern GIS environment.

parks coverage

From here, the next major step would be to hook a web server to serve data to the browser directly from PostGIS. But that is for another day…