Searching for Locations Within an Area Using SpatiaLite¶

As as spatial database, SpatiaLite supports geospatial queries, such as identifying locations that exist within a particular spatially defined area.

This notebook provides some simple examples of searching for locations of establishments rated by the UK Food Standards Agency.

The search context is based on three SpatiaLite tables:

• a table containing shapefiles specifying the area the establishments should be located within;
• a spatial index table corresponding to the shapefile table that contains bounding box limits for shapefile areas to allow a preliminary quick search;
• a table containing point locations, some of which hope to dientify as being located within the specified area.

The boundary files we'll use come from the Ordnance Survey BoundaryLine product. In particular, we'll be searching within parish boundaries.

Point Location Data¶

The point location data is loaded from a data file published by the Food Standards Agency.

Note that the foodgov co-ordinates seem to be postcode centroid locations, or some other area based value, and as such are not very accurate.

It would perhaps be better to use OpenStreetMap to re-geocode the locations from provided addresses?

Modify the ratings table with a column that allows for the storage of point location data as a Geometry and use the latitide and longitude columns as the basis for the points. Use EPSG / CRSID 4326 (WGS64) as the projection.

The original shapefile uses the OSGB projection (CRSID 27700), so let's also create a point geometry column using that projection. We can use the ST_Transform() SpatiaLite function to transform the WGS84 (EPSG:4326) tagged Geometry point column to create the new column.

Create a spatial index on the boundary data table to speed up queries.

The index table is created as idx_TABLENAME_COLUMN, in this case idx_parish_region_geometry.

Let's have a quick look at it.

Let's also just have a quick look at a query on the foodratings table:

Creating Spatial Queries¶

One of the advantages of using a geo-sensitive database such as SpatiaLite is that it allows us to run spatial queries. For example, the SpatialLite within(POINT, REGION) function allows us to check whether a point lays within a particular spatial boundary (I'm not sure if the function is inclusive or exclusive, which is to say whether a point is within an area if it is on the boundary of the area.)

We can naively run a query that returns the points listed in one table that are contained within a shapefile retrieved from another table. For example, something like:

SELECT BusinessName, AsGeoJSON(foodratings.Geometry)
    FROM foodratings, parish_region 
    WHERE parish_region.NAME LIKE '%Arreton%' AND foodratings.Geometry NOT NULL
          AND within(foodratings.Geometry, parish_region.wgs84) ;

However, such queries can take a long time to process. Instead, we might run a query against the shapefile index to locate points with the bounding box that encloses the shapefile:

SELECT FHRSID, p.wgs84, f.Geometry, f.BusinessName, f.BusinessType FROM 
         (SELECT FHRSID, Geometry, X(osgb) x, Y(osgb) y, BusinessName, BusinessType
              FROM foodratings WHERE Geometry NOT NULL) f,
         parish_region p, idx_parish_region_geometry i
         WHERE p.NAME LIKE '%Arreton%' AND p.pk_uid= i.pkid AND 
             i.xmin < f.x and i.xmax > f.x and i.ymin < f.y and i.ymax > f.y) fr;

and then tighten up the query around those efficiently selected points:

We can now display those points on a map:

For small areas, using the marker cluster may not be appropriate. On the one hand, the clusterer is quite aggressive and may cluster nearby items inappropriately. On the other, where points are geocoded as postcode centroids, the marker cluster does provide an advantage becuase it allows us to split out multiple points that are colocated, which hides all but the last rendered marker from the user if the marker cluster is not used.

To be able to see multiple co-located points, we need to use map extension that allows us to explode colacated points, or add a small random perturbation to each marker so that it is plotted uniquely:

Another approach might be to try to geocode different addresses more accurately than their postcode centroid.

To check that the markers are indeed bounded by the search area, we can add the boundary to the map as well as the points that supposedly lay within it.

Enhancing Point Location Data Using OpenStreetMap¶

Locations generated as postcode centroids are not very accurate. To improve the location data for particular points, we could use the OSM geolocation API to get more exact location data from the geocoded address.

The osmnx package provides a convenient utility function for this:

As well as geocoding addresses, the OpenStreetMap API also allows us to search for poitns of interest (POIs) within a particular boundary directly. In addition, we can limit the search using particular amenity tags. Once again, there is an osmnx utulity function to help us with this.

First, let's get a shapely representation of the area we want to search within:

We can now search OpenStreetMap for POIs associated with particular amemity tags within that area: