issues: 1124731464

This data as json

id node_id number title user state locked assignee milestone comments created_at updated_at closed_at author_association pull_request body repo type active_lock_reason performed_via_github_app reactions draft state_reason
1124731464 I_kwDOCGYnMM5DCgpI 399 Make it easier to insert geometries, with documentation and maybe code 9599 open 0     25 2022-02-05T00:11:26Z 2023-05-16T03:11:52Z   OWNER  

In playing with the new SpatiaLite helpers from #385 I noticed that actually populating geometry columns is still a little bit tricky. Here's what I ended up doing:

```python import httpx, sqlite_utils db = sqlite_utils.Database("/tmp/spatial.db") attractions = httpx.get("https://latest.datasette.io/fixtures/roadside_attractions.json?_shape=array").json() db["attractions"].insert_all(attractions, pk="pk")

Schema of that table is now:

CREATE TABLE [attractions] (

[pk] INTEGER PRIMARY KEY,

[name] TEXT,

[address] TEXT,

[latitude] FLOAT,

[longitude] FLOAT

)

db.init_spatialite() db["attractions"].add_geometry_column("point", "POINT")

db.execute(""" update attractions set point = GeomFromText( 'POINT(' || longitude || ' ' || latitude || ')', 4326 ) """) `` That last line took some figuring out - especially the need for the SRID of4326`, without which I got this error:

IntegrityError: attractions.point violates Geometry constraint [geom-type or SRID not allowed]

It would be good to both document this in more detail, but ideally also to come up with a more obvious pattern for inserting common types of spatial data.

Also related: - #398 - #79

 140912432 issue     
{
    "url": "https://api.github.com/repos/simonw/sqlite-utils/issues/399/reactions",
    "total_count": 0,
    "+1": 0,
    "-1": 0,
    "laugh": 0,
    "hooray": 0,
    "confused": 0,
    "heart": 0,
    "rocket": 0,
    "eyes": 0
}
    

Links from other tables