SQLite Forum

I am back with a question about `geopoly` and performance. I have tables like so

```sql
CREATE TABLE a (a_id);
CREATE TABLE b (b_id, a_id, lat, lon);

sqlite> SELECT Count(*) FROM a;
370313
sqlite> SELECT Count(*) FROM b;
397008
sqlite> SELECT Count(*) FROM b WHERE lon != '' AND lat != '';
134681
```

I create a `geopoly` table and load it with tiny triangles generated around each point with a delta of 0.0001 degree (from what I understand, `geopoly` and `r*tree` tables can't deal with points… they need polys)

```sql
CREATE VIRTUAL TABLE vloc USING geopoly(a_id, b_id);
INSERT INTO vloc (a_id, b_id, _shape) SELECT a.a_id, b_id, geopoly_regular(lon, lat, 0.0001, 3) AS _shape FROM a JOIN b ON a.a_id = b.a_id WHERE lon != '' AND lat != '';
sqlite> SELECT Count(*) FROM vloc;
134681
```

I want to be able to find all rows in table `a` within say, 10 kms of a given `lon`, `lat`. I use the following two queries as test, and a `radius` of `0.1` (1 deg is ~111 kms near the equator, so I am just using 0.1 deg as an approximation here)

```sql
sqlite> SELECT DISTINCT a_id FROM vloc WHERE geopoly_within(_shape, geopoly_regular(0, 0, 0.1, 4)) != 0;
(… 8 rows are returned …)
Run Time: real 0.400 user 0.348746 sys 0.048510
sqlite> SELECT DISTINCT a.a_id FROM a JOIN b ON a.a_id = b.a_id WHERE lat BETWEEN -0.1 AND 0.1 AND lon BETWEEN -0.1 AND 0.1;
(… 9 rows are returned …)
Run Time: real 0.027 user 0.001259 sys 0.006363
sqlite>
```

The results are not going to be exact because the queries are ever so different, but they are close enough for me. However, using `geopoly` takes 14 times longer than just doing a simple `BETWEEN` over a `JOIN`.

The reason I want to use `geopoly` is that I can use other geospatial libraries to convert from lat,lon to cartesian coordinates and allow more natural queries such as "all the rows within `x` kms of a given point", but some of the results are two or even three orders of magnitude slower with `geopoly`.

What am I doing wrong? How can I improve the speed of `geopoly`?

**Update:** In my application, I use `geopoly_within(_shape, @poly)` where I computer the value for `poly` using a geospatial library so I can use a radius in kilometers. The `SELECT` time is even slower when using a circle. In fact, even with `geopoly_within(_shape, geopoly_regular(0, 0, 0.1, 20))` in the above query, the performance degrades from 0.400 to 0.636