Women Statues in the UK: Database Design and Spatial Analysis

Part 1: Database Design

As reported by the BBC, women are still severely underrepresented in public statues in the UK. The Public Statues and Sculpture Association lists 122 public statues of women. The WikiData knowledge base only lists 98 items described as statues of women in the UK, but many are not geolocated – i.e., the precise latitude and longitude of the location are not available.

The Assignment_1--Statues-Wikidata.csv dataset available in the datapackage on Blackbaord was created from data available on Wikidata, including information about 54 public statues listed and geolocated in WikiData as statues of women in the UK. It is a small but rather messy dataset including the following columns:

• Statue: WikiData ID of the statue;

• StatueLabel: name of the statue;

• Inception: date of inception;

• Lat: latitude of the place where the statue is located;

• Lon: longitude of the place where the statue is located;

• Place: WikiData ID of the place where the statue is located;

• PlaceLabel: name of the place where the statue is located;

• PlaceAdmin: administrative area containing the place where the statue is located;

• Depicted: WikiData ID of the person depicted by the statue;

• DepictedLabel: name of the person depicted by the statue;

• DepictedAltLabel: alternative names of the person depicted by the statue;

• DepictedDescription: description provided by WikiData of the person depicted by the statue;

• Creator: WikiData ID of the person who created the statue;

• CreatorLabel: name of the person who created the statue;

The dataset clearly contains a wide range of redundancies that could create severe anomalies if the data are uploaded to a database as they are, as a single table. The aim of this part of the assignment is to test your understanding of the principles and practices of database design.

• Write up to 500 words describing what those issues are and suggest a structure that would avoid such issues as far as possible, including table(s), table name(s), columns, types and constraints.

• Working on your database for this exercise:

Points of Interest (POIs) are a common feature in GIS and cartography, where the term is used to refer to geographic entities that might be relevant or interesting for the user of a system or reader of the map. The term POI might be applied to different types of entities, from cafes to restaurants, from schools to hospitals. Their representation within a GIS or a map can range from a single point to a multi-polygon, depending on the type of entity and the scale at which they are represented. POIs are commonly understood as a marker of the urban activity present in an area, such as residential, commercial or industrial (see, e.g., Gao et al., 2017)1. In this part of the assignment, you will explore the relationship between POIs and their surrounding environment.

The first three are greater_london_osm_point, greater_london_osm_line, greater_london_osm_polygon. Those three tables are derived from OpenStreetMap (OSM) data. The tables contain point, line and poly- gon objects representing geographic entities in London, as detailed by OSM. The tables include columns representing geometries, IDs, names and objects’ classification, as described in the Map Features OSM wiki page. Within the OSM tables, objects such as university can be encoded as either points or polygons, where the OSM tag amenity has value university. Note that the Geofabrik dataset encodes the tags in a slightly different way in the two tables, as in the example shown below. Further information on the OSM and its use of tags can be found on the OSM Wiki pages about Map Features.

The fourth table is greater_london_loac, which contains the London Output Area Classification (LOAC). The LOAC is a geodemographic classification specifically developed for London, similar to and developed from the same variables used to create the 2011 Output Area Classification (OAC) that we have discussed multiple times in past lectures. A map of the LOAC can be seen using the CDRC Mapmaker developed by the Consumer Data Research Centre. A discussion of the LOAC can be found in the report included in the datapack, or on the London Datastore page about the LOAC.

The aim of this part of the assignment is to test your ability to use SQL – possibly in conjunction with other programming languages or tools – to execute a spatial analysis.

• Write an introduction (up to 500 words), proposing a spatial analysis aimed at describing the amount of POIs of the type assigned to you in the borough assigned to you in the Appendix, as well as the urban environment in which they are placed. The analysis should use the four tables above and the SQL spatial functions and operators made available by PostGIS.

• Write the analysis using only SQL queries and include the results in the document. If the results include large tables, please limit the output shown in the document to only a few rows per query. Connect to the shared sds27 database. Please, use only SELECT operations on the shared database. Do not update, alter, delete or drop information from any table. If you need to create tables, contact the module convener .

• Write a final discussion (up to 300 words), presenting the final results of your analysis. In this section, additional tools such as R, Python, QGIS or ArcGIS can be used to illustrate the final results.