Commons:Monuments database/Database structure

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 [1] (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.