Een onderzoek naar de meest gebruikte tags in Openstreetmap en de Top10nl

Voor het uitfilteren van toeristische bezienswaardigheden bieden de Top10nl en Openstreetmap waardevolle informatie. Belangrijk is dan na te gaan welke trefwoorden of tags in allebei de cartografische databases worden gebruikt en hoe vaak. SQL queries zijn vrij eenvoudig samen te stellen, maar hoe verzamel je álle data op overzichtelijke wijze in één Excel-bestand? Python, openpyxl en psycopg2 bieden uitkomst.

Welke waarden staan in een veld en hoe vaak  = SQL (een SELECT, met COUNT en GROUP BY) en het resultaat kan je zelfs met COPY TO naar een CSV file wegschrijven. Maar wat als je een hele trits velden en tabellen hebt en je ook nog sommige data in één lijst wilt hebben? Dan biedt Python uitkomst.

Twee modules moeten daarbij in gebruik worden genomen:

  • Psycopg2 is een PostgreSQL database adapter for Python
  • Openpyxl is een Python library om Excel bestanden te gebruiken

In essentie worden er lijsten aangemaakt van de relevante tabellen en velden in die tabellen, waarvan we de waardes en de aantallen willen weten.

Daarna worden de lijstitems ingezet om de queries naar de Postgresql database uit te voeren.

De geretourneerde data worden naar ofwel een bestaande worksheet weggeschreven, of, als de worksheet niet bestaat, naar een nieuwe worksheet.

Bovendien wordt de naam van de tabel aan elke Excel-rij toegevoegd. Dit omdat sommige velden in meerdere tabellen voorkomen en we voor elk veld één worksheet willen maken, maar dan wel willen weten op welke tabel de rij betrekking heeft.

Hieronder de code en achteraan een voorbeeld van de Excel-lijst. Binnen Excel kan je de informatie vervolgens sorteren volgens de gewenste instellingen.

(Ik heb na de nodige installatieproblemen met OSGEO4W, pip en pythonpath perikelen, het script werkend gekregen binnen de nieuwe Visual Studio Code van Microsoft op een Windows 64 PC)

#PostgreSQL database adapter for Python
import psycopg2 

# Python library to read/write Excel 2010 xlsx/xlsm files
from openpyxl import Workbook 

#define fields and tables to select
SQLLIST_TTNL = [["typefunctioneelgebied", "ttnl.functioneelgebied_vlak"],
                ["typefunctioneelgebied", "ttnl.functioneelgebied_punt"],
                ["typegebouw", "ttnl.gebouw_punt"],
                ["typegebouw", "ttnl.gebouw_vlak"],
                (etc...)
              ]

SQLLIST_OSM = [["leisure", "public.planet_osm_point"],
               ["tourism", "public.planet_osm_point"],
               ["historic", "public.planet_osm_point"],
               (etc...)
              ]

#create workbook
WORKBOOKCREATED = Workbook()

#Converts certain values and tables into an Excel file
def convert2excel(dbname, sqllist):

    #setup the database connection
    connstr = "dbname='" + dbname + "' user='postgres' host='localhost' password='postgres'"

    conn = psycopg2.connect(connstr)
    cur = conn.cursor()

    #loop through the list elements
    for i in range(len(sqllist)):

        #if proposed worksheet allreadey exists
        if sqllist[i][0] in WORKBOOKCREATED.sheetnames:

            #then add the rows to the existing worksheet
            worksheetactive = WORKBOOKCREATED.get_sheet_by_name(sqllist[i][0])
        #else create new worksheet
        else:
            worksheetactive = WORKBOOKCREATED.create_sheet(sqllist[i][0])

        sql = "SELECT DISTINCT " + sqllist[i][0] + ", COUNT(" + sqllist[i][0] + ") FROM " + sqllist[i][1] + " GROUP BY " + sqllist[i][0] + " ORDER BY " + sqllist[i][0]

        cur.execute(sql)
        rows = cur.fetchall()

        for row in rows:
            worksheetactive.append(row)

            #Append the table name in the third column of the last inserted row
            addedcell = "C" + str(worksheetactive.max_row)
            worksheetactive[addedcell] = sqllist[i][1]

        #give info on progress
        print "Data of " + sqllist[i][1] + " added to worksheet " + sqllist[i][0]

    #close cursor and connection to the database
    cur.close()
    conn.close()

#Call the function twice
convert2excel("osmnetherlandsbb", SQLLIST_OSM)
convert2excel("top10nl", SQLLIST_TTNL)

#Save the workbook
WORKBOOKCREATED.save("distinctvaluesOSMenTTNL.xlsx")

#give final message
print "All is done"