simonw/sqlite-utils
Make it easier to insert geometries, with documentation and maybe code
Ouverte
#399 ouverte le 5 févr. 2022
documentationhelp wantedpython-libraryspatialite
Métriques du dépôt
- Stars
- (2 065 étoiles)
- Métriques de merge PR
- (Merge moyen 9h 52m) (7 PRs mergées en 30 j)
Description
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:
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 of 4326, 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