Tasks 1) Highest Flickr photo density Which cell has the highest Flickr photo density? Give the result as the centroid point coordinate in the British National Grid Coordinate system (i.e. the same as the points are stored in). Include the SQL you used to determine the result. 2) Route lengths.
Tasks:
1) Highest Flickr photo density:
Which cell has the highest Flickr photo density? Give the result as the centroid point coordinate in the British National Grid Coordinate system (i.e. the same as the points are stored in). Include the SQL you used to determine the result.
2) Route lengths:
Load the following datasets
• routeb.sql
• routec.sql
How long is each of the routes – routea, routeb, routec – in metres? (show the SQL you used)
3) Route popularity:
Use the Flickr photo data to show the popularity of each route according to how many photos have been taken along the route within a 25m buffer. You should normalize this by route length (i.e. photos per meter). Give the SQL used and show your results in order from the most popular route to the least popular route.
(Preferably do this as a single SQL statement.)
4) Temporal Patterns:
Make a new table and add a point at Nelson Monument on Calton Hill which is British National Grid coordinates (326253.33,674110.63).
5) Data Aggregation:
Use the supplied hexagon grid to calculate the most popular locations for photos where the tag includes each of the following terms – giving the centroid of the most popular cell in each case (i.e. 4 queries and outputs).
• Castle
• Calton Hill
• Royal Mile
• Meadows
(For each term show your SQL and the cell centroid with the highest count.)
6) Visualization:
Make an interesting visualization of the Flickr data – this should include a map using QGIS to show the spatial pattern of the term across Edinburgh and may include other visualizations or summaries you think are useful. For example, you could focus on 1 of the terms from task 5.
• Remember to check the lecture slides for tips about map design
• Treat the output as if it is to be displayed stand alone at A4 size (i.e. small poster)
• Export your design as an image to include in your report as a full A4 page – you may add additional
features using Powerpoint / Word or other editor.
7) Write up:
Give an overview of your poster (from task 6) and explain the decisions you made regarding its design, and what you wanted it to show/why. Highlight any issues and how you might be able to improve your analysis of the Flickr dataset.
• Limited to 2 sides of A4 @ 10 point font and 2cm margins.
• Maximum of 500 words (excluding figures/tables/references).
• You can use bullet points, figures, tables, references, etc.
