I am testing both rtree and geopoly extensions for searching points, and I am having mixed luck -- for the same data rtree works and geopoly does not. Perhaps some kind soul can tell me the error of my ways. Given the following tables:

CREATE TABLE m (…, lat, lng);
CREATE VIRTUAL TABLE mGeopoly USING geopoly (lat, lng);
CREATE VIRTUAL TABLE mRtree USING rtree (
    id, minX, maxX,minY, maxy
);

Below is my pseudo-ish JavaScript code that inserts the data

rows = `SELECT DISTINCT lat, lng FROM m`;
insGeopoly = `INSERT INTO mGeopoly (_shape, lat, lng) 
    VALUES (@_shape, @lat, @lng)`;
insRtree = `INSERT INTO mRtree (id, minX, maxX, minY, maxY) 
    VALUES (@id, @minX, @maxX, @minY, @maxY)`;

id = 0;
for (row of rows) {
    id++;
    minX = row.lng;
    maxX = row.lng;
    minY = row.lat;
    maxY = row.lat;
    ll = `[${minX},${minY}]`;
    lr = `[${maxX},${minY}]`;
    ur = `[${maxX},${maxY}]`;
    ul = `[${minX},${maxY}]`;
    _shape = `[${ll},${lr},${ur},${ul},${ll}]`;
    
    insGeopoly.run({_shape, lat: row.lat, lng: row.lng});
    insRtree.run({id, minX, maxX, minY, maxY});
}

Now I select for points within random lng/lat boxes, and get results from the rtree table but nothing from the geopoly table.

selRtree = `SELECT *   
            FROM mRtree r JOIN m ON r.minX = m.lng AND r.minY = m.lat 
            WHERE 
                minX>=@minX AND maxX<=@maxX AND 
                minY>=@minY AND maxY<=@maxY`;
selGeopoly = `SELECT * 
                FROM mGeopoly g JOIN m ON g.lng = m.lng AND g.lat = m.lat 
                WHERE geopoly_within(_shape, @_shape)`;

for (let i = 0, j = 50; i < j; i++) {
    minX = randomLng();
    maxX = minX + 5;
    minY = randomLat();
    maxY = minY + 5;
    ll = `[${minX},${minY}]`;
    lr = `[${maxX},${minY}]`;
    ur = `[${maxX},${maxY}]`;
    ul = `[${minX},${maxY}]`;
    _shape = `[${ll},${lr},${ur},${ul},${ll}]`;

    selRtree.all({minX, maxX, minY, maxY});
    selGeopoly.all({_shape})
        
    if (resRtree.length) {
        console.log(`${minX.toFixed(2)}, ${minY.toFixed(2)}, ${maxX.toFixed(2)}, ${maxY.toFixed(2)}: rtree (${resRtree.length}), geopoly (${resGeopoly.length})`);
    }
}

// output
-61.06, -11.57, -56.06, -6.57: rtree (1), geopoly (0)
25.97, 33.91, 30.97, 38.91: rtree (7), geopoly (0)
7.60, -31.09, 12.60, -26.09: rtree (1), geopoly (0)
15.32, 16.93, 20.32, 21.93: rtree (1), geopoly (0)
58.14, 40.94, 63.14, 45.94: rtree (2), geopoly (0)
-61.15, -36.35, -56.15, -31.35: rtree (4), geopoly (0)
69.97, 31.80, 74.97, 36.80: rtree (3), geopoly (0)
-137.29, 54.07, -132.29, 59.07: rtree (1), geopoly (0)
98.06, 7.11, 103.06, 12.11: rtree (6), geopoly (0)
-79.21, 18.92, -74.21, 23.92: rtree (1), geopoly (0)

Does it work if you use shapes with a non-zero area? Here everything you insert has minX=maxX and minY=maxY, which at the very least creates ambiguity about whether the points are in clockwise or counter-clockwise order because all four corners share the same coordinate.

hi Rowan, I am actually in the process of writing up my very detailed observations and experience on this issue to not just document it forever but also hopefully to help someone else in my situation. For now, let me quickly respond to your question -- yes and no. search in geopoly doesn't work with zero-area shapes while rtree does (basically because rtree doesn't really store polys… it stores only the four corners of a poly). Both do accept zero-area shapes though. I will finish my documentation and make it available soon as I can.

Did you ever finish writing up your observations for this? I would be interested to know what you found

see https://sqlite.org/forum/forumpost/5e12f4f3c0 (or https://punkish.org/Geodata-with-SQLite/). Please let me know if it helps or needs modification to make it more useful.

thanks for your suggestions. I have incorporated them in the article.