Commons:Monuments database/Database structure

From Wikimedia Commons, the free media repository
Jump to navigation Jump to search

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[edit]

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 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.


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)
  • Country, lang, id form the primary key.
  • Lat and lon are both indexed so that map related operations are fast
  • adm0-4 are indexed

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.

Monuments database