mapnik/mapnik — Issue #1039: Refactor SQLite plugin
Author: kkaefer
Created: 2012-01-18 20:02:00+00:00
State: open

Currently, the SQLite plugin tries to do a lot of magic to figure out the table the geometry column in a query came from in order to use the correct index. The naming convention for indexes is `idx_TABLENAME_FIELDNAME`. The plugin needs to know the table name in order to construct the query, which references the R*Tree index table.

I suggest removing all index join functionality from the plugin and instead rely on views that automatically join the index to the table. The end user only supplies the view name and SQLite handles the R*Tree index join in the background. On binding, we check the fields of query, and if there's a xmin, xmax, ymin and ymax column in the result columns of the query, we just assume that the table is actually a view and internally joins to the index. That change would make the SQLite plugin more similar to how the PostGIS plugin works.

Additionally, instead of using `IN()` subqueries for the indexes, we should recommend using a `JOIN` to filter by bbox:

```sql
SELECT [fields] FROM ([query]) WHERE id IN
(SELECT pkid FROM idx_osm_minorroads_geometry
WHERE xmin >= -1 AND ymin >= -1 AND xmax >= 1 AND ymax <= 1);
```

becomes

```sql
SELECT [fields] FROM ([query])
WHERE xmin >= -1 AND ymin >= -1 AND xmax <= 1 AND ymax <= 1;
```

with `[query]` being a view

```sql
SELECT m.*, k.xmin, k.xmax, k.ymin, k.ymax
FROM [table] m
JOIN [table_index] k
ON m.id = k.pkid;
```

or some other query that operates on that view. Alternatively, users can just specify the query that joins the data table to the R*Tree index table.

My benchmarks suggest that this is a lot faster (I measured an improvement of about 3x). Adding the views into the mix doesn't have any measurable negative effects on query performance.

If a table doesn't have xmin/xmax/ymin/ymax fields, we just run a non-spatial query:

```sql
SELECT [fields] FROM ([query]);
```

This doesn't deal with indexes in external files or automatic index creation. Instead of automatically creating an index, we should expose an API that allows creating the index and the according view programatically so that end users can use a product that employs Mapnik to explicitly create an indexed view.