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     3 2019-02-14T16:30:22Z 2023-10-25T13:23:04Z 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

Links from other tables