MVW PostgreSQL tables

MVW PostgreSQL tables

The MapViewer is a special web page of the Mukau website and hence makes use of all the PostgreSQL tables seen for the Content Management System (CMS), i.e. webheaders, webfooters, webpages, webhits, dns_ip, employees, persons, languages, webpage_translations, webcomments.

In addition, the following tables are necessary to make the MapViewer work and to display geographical data in it.

Spatial tables containing geographical background

Resource	Type	Description
coastlines	spatial table	Coastal lines at high resolution, displayed by the MapViewer at scales > 1:5000000
coasts	spatial table	Coastal lines at low resolution, displayed by the MapViewer at scales < 1:5000000
countries	spatial table	Country polygons
fao_rwdb_rivers	spatial table	River line strings from FAO RWDB2 River-Surface Water Body Network
populated_places	spatial table	Point location of towns from Natural Earth Populated Places
surface_water	spatial table	Water body polygons from FAO RWDB2 Surface Water Bodies

Grids and gridded data

Resource	Type	Description
grid_083dd	spatial table	Grid of 1/12 dd cells. Contains also several columns with thematic data, such as the themes of the MapViewer's "FAO Land Management" layer.
grid_1dd	spatial table	Grid of 1 dd cells. There are two spatial columns: CELL with quadrilateral-shaped geometries (i.e. proper cells) and CORR_CELL with quadrilateral cells cut by coastlines (so geometries contain only the continental part of the cell and not the sea).
grid_1dd_gpcc	table	Contains precipitation data from the Global Precipitation Climatology Centre (GPCC) of the Deutscher Wetterdienst. Data columns have a _MM suffix to refer to a month (e.g. RAIN_07 contains precipitation data of July). YEAR is the column specifying the year of data. G1D_ID is the reference to the ID column of GRID_1DD.
grid_1dd_spi	table	Contains data of the Standardized Precipitation Index (SPI) from the Global Precipitation Climatology Centre (GPCC) of the Deutscher Wetterdienst. Seven time scales are available: 1, 3, 6, 9, 12, 24, and 48 months. Data columns have a double _MMTS suffix to refer to month and time scale (e.g. SPI_1124 contains SPI-12 data of November). YEAR is the column specifying the year of data. G1D_ID is the reference to the ID column of GRID_1DD.
grid_1dd_gpcc_year	view	Join between GRID_1DD and GRID_1DD_GPCC on GRID_1DD_GPCC.G1D_ID=GRID_1DD.ID
grid_1dd_spi_year	view	Join between GRID_1DD and GRID_1DD_SPI on GRID_1DD_SPI.G1D_ID=GRID_1DD.ID

Tables and views to manage data properties and to provide functionalities of the MapViewer

Resource	Type	Description
country_list_mukau	view	List of all countries included in the Mukau's area of interest, used by the "Zoom to Country" function.
identify_queries	table	Contains the definition of queries to perform MapViewer functions, such as identify or zoom. The SQL statement of the query is composed of the values of special columns dealing with specific clauses, namely SELECT, FROM, WHERE, ORDER BY, GROUP BY.
mapserver_selected	spatial table	Functional spatial table used to highlight on the map features subject to zoom or identify (query) actions. When a geographical feature is queried or is selected by a "Zoom to" function, it is copied from its source table to MAPSERVER_SELECTED and highlighted on the map by means of the MapServer layers "highlighted" or "int_highlighted". When a new geographical feature is inserted into MAPSERVER_SELECTED, those already present and older than 30 minutes are deleted. This persistence time is set by DEL_H_FEATURES_OTHER_SESSION_OLDER_THAN in /mukau/php/gis/gisconfig.php.
mapviewer_help	table	List of pieces of information displayed by the MapViewer Help webpage (ID=7115)
period_layout	table	Look-up table to manage thematic layers. This is a legacy table from former versions of the MapViewer and can be neglected in this installation of Mukau
predefined_extents	spatial table	List of names and bounding boxes of pre-defined areas of interest

Tables for internationalization (option not present in the current instance of Mukau)

Resource	Type	Description
layer_translations	table	List of translations for texts of layers
mapviewer_translations	table	List of translations for texts in the whole MapViewer