issues: 410384988
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 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 410384988 | MDU6SXNzdWU0MTAzODQ5ODg= | 411 | How to pass named parameter into spatialite MakePoint() function | 1055831 | closed | 0 | 2 | 2019-02-14T16:30:22Z | 2022-01-20T21:29:41Z | 2019-05-05T12:25:04Z | NONE | Hi, datasette version: "0.26.2" extensions: spatialite: "4.4.0-RC0" sqlite version: "3.22.0" I have a table of airports with latitude and longitude columns. I've added spatialite (with KNN support). After creating the db using csvs-to-sqlit, I run these commands to setup the spatialite tables: ``` conn.execute('SELECT InitSpatialMetadata(1)') conn.execute("SELECT AddGeometryColumn('airports', 'point_geom', 4326, 'POINT', 2);") conn.execute('''UPDATE airports SET point_geom = GeomFromText('POINT('||"longitude"||' '||"latitude"||')',4326);''') conn.execute("SELECT CreateSpatialIndex('airports', 'point_geom');") ``` I'm attempting to create a canned query and have this in my metadata.json file: ``` "find_airports_nearest_to_point":{ "sql":"SELECT a.pos AS rank, b.id, b.name, b.country, b.latitude AS latitude, b.longitude AS longitude, a.distance / 1000.0 AS dist_km FROM KNN AS a JOIN airports AS b ON (b.rowid = a.fid) WHERE f_table_name = \"airports\" AND ref_geometry = MakePoint( :Long , :Lat ) AND max_items = 10;"} ``` which doesn't seem to perform the templating of the name parameters correctly and I get no results. Have also tired: ``` MakePoint( || :Long || , || :Lat || ) ``` which returns this error: ``` near "||": syntax error ``` However I cannot seem to find the correct combination of named parameter syntax (:Lat) or sqlite concatenation operator to make it work. Any ideas if using named parameters inside functions is supported? Thanks Darren | 107914493 | issue | {"url": "https://api.github.com/repos/simonw/datasette/issues/411/reactions", "total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | completed |