http://simonwillison.net/2023-09-25T19:45:03+00:00Simon Willison2023-09-25T19:45:03+00:002023-09-25T19:45:03+00:00https://simonwillison.net/2023/Sep/25/tg-sqlite/#atom-tag

<p><strong><a href="https://til.simonwillison.net/sqlite/sqlite-tg">Geospatial SQL queries in SQLite using TG, sqlite-tg and datasette-sqlite-tg</a></strong></p> Alex Garcia built sqlite-tg—a SQLite extension that uses the brand new TG geospatial library to provide a whole suite of custom SQL functions for working with geospatial data.</p> <p>Here are my notes on trying out his initial alpha releases. The extension already provides tools for converting between GeoJSON, WKT and WKB, plus the all important tg_intersects() function for testing if a polygon or point overlap each other.</p> <p>It’s pretty useful already. Without any geospatial indexing at all I was still able to get 700ms replies to a brute-force point-in-polygon query against 150MB of GeoJSON timezone boundaries stored as JSON text in a table. <p>Tags: <a href="https://simonwillison.net/tags/geospatial">geospatial</a>, <a href="https://simonwillison.net/tags/sqlite">sqlite</a>, <a href="https://simonwillison.net/tags/geojson">geojson</a>, <a href="https://simonwillison.net/tags/datasette">datasette</a>, <a href="https://simonwillison.net/tags/alex-garcia">alex-garcia</a>, <a href="https://simonwillison.net/tags/tg">tg</a></p>

2023-09-23T04:32:14+00:002023-09-23T04:32:14+00:00https://simonwillison.net/2023/Sep/23/tg-polygon-indexing/#atom-tag

<p><strong><a href="https://github.com/tidwall/tg/blob/v0.1.0/docs/POLYGON_INDEXING.md">TG: Polygon indexing</a></strong></p> TG is a brand new geospatial library by Josh Baker, author of the Tile38 in-memory spatial server (kind of a geospatial Redis). TG is written in pure C and delivered as a single C file, reminiscent of the SQLite amalgamation.</p> <p>TG looks really interesting. It implements almost the exact subset of geospatial functionality that I find most useful: point-in-polygon, intersect, WKT, WKB, and GeoJSON—all with no additional dependencies.</p> <p>The most interesting thing about it is the way it handles indexing. In this documentation Josh describes two approaches he uses to speeding up point-in-polygon and intersection using a novel approach that goes beyond the usual RTree implementation.</p> <p>I think this could make the basis of a really useful SQLite extension—a lighter-weight alternative to SpatiaLite. <p><small></small>Via <a href="https://news.ycombinator.com/item?id=37619100">Hacker News</a></small></p> <p>Tags: <a href="https://simonwillison.net/tags/c">c</a>, <a href="https://simonwillison.net/tags/geospatial">geospatial</a>, <a href="https://simonwillison.net/tags/spatialite">spatialite</a>, <a href="https://simonwillison.net/tags/sqlite">sqlite</a>, <a href="https://simonwillison.net/tags/geojson">geojson</a>, <a href="https://simonwillison.net/tags/tg">tg</a></p>