Home Blog CV Projects Patterns Notes Book Colophon Search

Query Geographic Extents in SQLite

11 Apr, 2015

Imagine a situation where you have a set of polygons of different shapes (perhaps representing county boundaries on a map for example) and a user is moving a map around to view them. In order to avoid rendering them all, you need to find out which shapes might be visible on the screen at the moment.

To simplify the calculation a bit, we'll just use the geographic extents of each polygon, not its real shape. The geographic extent is just the box you draw around the shape (also known as the bounding box). If this box overlaps with the current screen, we'll assume we need to include the shape.

Now, there are lots of fancy ways of doing this calculation (using PostGIS etc), but actually it can be done in pure SQL if you think carefully about the md_code(WHERE) clause.

The stages in the process will be to:

Create the SQLite database and table
Import the data
Perform a query

Create the SQLite Database and Table

You can create the required table in Python like this:

undivert(`../file/bounding_box/bbox.py')

If you save this as md_code(bbox.py) you can create a md_code(bbox.db) database with the md_code(bbox) table like this:

python bbox.py bbox.db

Import the data

undivert(`../file/bounding_box/process.py')

Assuming you had a CSV file with the columns polygon_id, left, bottom, right, top like this one you could import it like this:

XXX

Perform queries

Let's have a think about the algorithm we need to use. Rather than trying to work it out in one go, let's think separately about whether each shape is at the same height as the screen, and then whether each shape is at the same horizontal location.

The screen and shape will overlap when both of the above conditions are true.

Here are the basic rules:

The screen area you are looking at has a vertical position that overlaps a shape when the shape's top or its bottom are between the screen's top and bottom, or its bottom is below and its top is above
The screen area you are looking at has a horizonal position that overlaps a shape when the shape's left edge or right edge are between the screen's left and right edges or its left is before and its right is after

With these rules in mind we can prepare a where clause. It looks like this:

undivert(`../file/bounding_box/query.py')