DS7002 Spatial Data Analysis
Practical Exercises
1
Session 5 – Thinking London Spatially Further (Spatial Query with SpatiaLite Advance)
Objectives: To develop skills of spatial query with SQL in SpatiaLite. Explore a range of
spatial variables of London.
Connect the existing SQLite Database “test.sqlite”
Task 1: Spatial Extent of London
Union multiple conditions
Using spatial functions of “Area”, “Mbr” along with “Max”, “Min”
Find out London boroughs with the smallest / largest area, with the lowest / highest
population density, as the southernmost / northernmost / most east / most west
borough.
Task 2: Neighbour Boroughs
Join tables with multiple conditions
Using spatial relation of “Touches”
Find out adjacent London boroughs.
Task 3: An Isolated Island in London?
Insert a new row as the isolated island
Left join neighbour borough to make sure that all London boroughs included
Using spatial relation of “Disjoint”
Using post-processing of “Having” to reduce the result-set of query
Find out isolated district.
Task 4: Business / Services within Boroughs (Points within Polygons)
Open Visual Shapefile “credit_union.shp”
Join or Left Join the Point table and the Polygon table
Using spatial relation of “Contain”
Find out which credit unions within which London boroughs.
Try the same procedure with the table of “UndergroundStations”
Find out which underground stations within which London boroughs.
Task 5: Near Neighbour Business / Services (Distance between Points)
(Need to load Shapefile “credit_union.shp”)
Using spatial relation of “Distance” calculated by “Length”.
Find out all credit Unions within distance < 2000 meters for each credit union.
DS7002 Spatial Data Analysis
Practical Exercises
2
Task 6: Motorways across London Boroughs (Intersection)
Open Shapefile “motorway.shp”
Using spatial relation of “Intersect”
Join the other table
Find out Which motorways across which London boroughs.
Task7: Tube Stations near Motorways in London (Distance between Points and Lines)
(Need to load Shapefile “UndergroundStations.shp”)
Using spatial relation of “Distance”
Multiple Join and Left Join
Find out distance between Underground Stations and Motorways.
Task 8: Buffers of Motorways
Create a table of motorway_buffers with Multipolygon geometry
Using spatial function of “CastToMultipolygon” to create 1km buffer zones for
Motorways
Using spatial function of “CastToMultipolygon” to create 1km – 2.5km buffer rings for
Motorways.
Task 9: Merging London Boroughs
Create a view of view_london
Using spatial relation of “Union” along with Group to merge London boroughs by
Inner / Outer borough
Further merge all London boroughs into one polygon in different way.
Task 10: Add SpatiaLite Table(s) into GIS
Show maps in QGIS.
Disconnect the current SQLite Database
Please export the following CSV files and put them into your portfolio: the query of
london_guinness_book in Task1, the count of underground station by Borough in Task4,
motorways across London in Task 6, and underground stations within 1 km of motorways in
Task 7.
Please export maps (in PNG format from SpatiaLite, or in other formats from QGIS) from Task
8, and put them into your portfolio.
Please have comment on each Task (such as what other applications can use these SQL
commands / functionalities, could these SQL commends could be extended / improved /
simplified …).