This page describes the database structure of the monuments database. The database contains a table for every source and an aggregated table with all data combined.

Source tables

The source tables match the templates in the structured lists. These fields are defined in the monuments_config. The naming convention for these source tables is "monuments_<countrycode>_(<language>). The program monument_tables.py can be run to generate the sql statements. When a source is added or changed, the mysql statements have to be regenerated and applied so that the source table is correct.

Monuments_all

The monuments_all table contains the following fields:

• country - The ISO 3166 code of the country or region with something appended if multiple sources exist for a country or region
• lang - The two-letter ISO 639-1 language code of the entry. This is always the language code of the source Wikipedia
• id - Id of the monument
• adm0 - The ISO 3166-1 alpha-2 country codethis table
• adm1 - The first level administrative subdivision, usually an ISO 3166-2 code.
• adm2 - The second level administrative subdivision, if possible an ISO 3166-2 code.
• adm3 - The third level administrative subdivision, if possible an ISO 3166-2 code. Can be NULL if there is no such level.
• adm4 - The fourth level administrative subdivision. Can be NULL if there is no such level.
• name - Name of the monument
• address - Address of the monument
• municipality - Municipality of the monument (or if not available a suitable subdivision)
• lat - The latitude of the monument
• lon - The longitude of the monument
• lat_int - Integer version derived from lat
• lon_int - Integer version derived from lon
• image - Image of the monument
• commonscat - Name of the category here at Commons with images of the monument
• source - Source of this data (permalink to a Wikipedia page)
• changed - Timestamp when this data was updated in the database
• monument_article - wiki article about the monument, formatted as (also contains section links like: 'Wiener_Wienflussbrücken#Brücken')
• registrant_url - Link to the register
• monument_random - Random number (deprecated)

Indexes

• Country, lang, id form the primary key.
• Lat and lon are both indexed so that map related operations are fast
• adm0-4 are indexed

Contents

The table is filled with data from all the source tables. This is done in one big query which maps fields from the source tables to the monuments_all table. You can download the result as a mysql dump.

dbeaver connection from laptop

Using the opensource tool dbeaver to understand and browse the database. Dbeaver supports setting up an ssh tunnel for the connection

If you create a connection via Database/New Database Connection, you first choose the mysql driver. Next page, the host is tools-db, port leave at 3306 and the database will be s51138__heritage_p. Username/password you copy over from .my.cnf in your tools-login homedir.

Next you go to the SSH tab, hostname tools-login.wmflabs.org, username your tools-login username, choose authentication methode public key, and point to the private key of your ssh keypair. After that you can test the connection and then if you finish you have a connection. Although you specified the database, it still shows all databases on tools-db, so you have to scroll to s51138__heritage_p. From there you can use dbeaver to browse or run ad-hoc SQL queries.