SQLite Extensions: Intro to Geopoly

SQLite Cloud combines the performance, efficiency, and reliability of SQLite with a distributed architecture and real-time event synchronization. Check out what we’re building with a free account.

Continuing our series on SQLite extensions, today we take a look at how SQLite's pre-installed geopoly module can augment your SQLite Cloud database with niche geospatial capabilities.

What is Geopoly?

Per its specification, geopoly is an alternative interface to the R*Tree extension.

R*Tree facilitates efficient storage and fast query and retrieval of geospatial data for further processing and/or analytics using (you guessed it) a tree data structure optimal for indexing multi-dimensional data such as geometric shapes.

As an "alternative" spatial SQLite extension, geopoly initially seems functionally more limited than R*Tree. R*Tree can handle 1-5 coordinate dimensions, whereas geopoly manages a particular type of 2D shape: "simple" polygons whose (up to 1000!) sides connect up and never intersect.

However, geopoly is not strictly an "alternative" to R*Tree. geopoly is built atop of and extends R*Tree. Each geopoly polygon is stored in an underlying R*Tree shadow table as a minimal bounding rectangle (MBR), or rectangular "bounding box". R*Tree uses MBRs to narrow down query search space. Then geopoly's functions further narrow R*Tree's identified candidate subset to exact query results.

Both extensions accept and describe shapes using the GeoJSON standard. In GeoJSON, a polygon is a JSON array of four or more vertices. Each vertex is a JSON array of X (longitude) and Y (latitude) coordinates. Since the first and last coordinate pairs represent the same vertex, a polygon with four vertices is a triangle. Note that geopoly also accepts polygons in binary format and stores polygons in this format only (i.e. as SQL BLOBs).

How and when to use R*Tree

R*Tree is ideal for geospatial apps or services that must efficiently index multi-dimensional spatial data and/or quickly perform spatial searches, such as nearest neighbor or range searches.

We cover R*Tree usage and spatial indexing in detail in SQLite Extensions: Intro to Rtrees for Spatial Data. You can also reference the geopoly and R*Tree specifications linked earlier in this article.

How and when to use geopoly

Geopoly is simple but powerful, offering a handful of new SQL functions to generate and work with simple polygons.

To create a geopoly table in your SQLite (Cloud) database, run:

CREATE VIRTUAL TABLE my_polygons USING geopoly();

This SQL command creates a table with just 2 fields: rowid and _shape. The latter stores the polygon data.

You can augment a geopoly table with as many custom fields:

CREATE VIRTUAL TABLE parks USING geopoly(name, avgDailyVisitors);

These additional columns can hold any (not strictly geometric) type of data you want to associate with the polygons. For example, if your geopoly table stores park data, each polygon would represent a park's boundaries and you could add fields to hold each park's name, surface area, average daily visitors, amenities, etc.

Inserts, updates, deletes, etc. use unchanged SQL syntax:

INSERT INTO parks(_shape, name, avgDailyVisitors) VALUES([
    [40.8005, -73.9580],
    [40.7993, -73.9605],
    ...
    [40.7239, -74.0340],
    [40.8005, -73.9580]
], 'Central Park', 6000);

Many online geospatial datasets contain polygon GeoJSON Features representing, say, land parcels or administrative boundaries. But let's say you don't have a polygon's JSON representation. You can use multiple geopoly functions in a single SQL statement to generate, insert, parse, and return a new polygon's JSON:

INSERT INTO polygons(_shape) VALUES(geopoly_regular(40.8005, -73.9580, 1, 20)) RETURNING geopoly_json(_shape);`

Additional available functions check if a polygon contains (`geopoly_within`) or overlaps with (`geopoly_overlap`) another, return a polygon's enclosed area (`geopoly_area`), generate a bounding box around a polygon (`geopoly_bbox`), generate a SVG representation (`geopoly_svg`) of a polygon, etc.

But the most useful real-world application of geopoly is geofencing, or creating a boundary around a location and triggering actions like calculating proximity of a location to other locations of interest, or target messages to potential customers in a certain area. For this use case, you can leverage geopoly's geopoly_contains_point function to perform point-in-polygon queries.

Conclusion

At SQLite Cloud, not only do we recommend you use SQLite extensions, we also want to offer you a launch pad. We've developed a full example geofencing app that utilizes geopoly and other cutting-edge open source tech (including our flagship product) as a playground for you to start exploring. Follow this walkthrough to build the app from scratch and see first-hand the power of combining built-in extensions with a SQLite database in the cloud!

Discover how SQLite extensions can expand your SQLite Cloud or other current data management capabilities by visiting our docs or our blog.