{"url":"https://api.github.com/repos/simonw/datasette/issues/411","repository_url":"https://api.github.com/repos/simonw/datasette","labels_url":"https://api.github.com/repos/simonw/datasette/issues/411/labels{/name}","comments_url":"https://api.github.com/repos/simonw/datasette/issues/411/comments","events_url":"https://api.github.com/repos/simonw/datasette/issues/411/events","html_url":"https://github.com/simonw/datasette/issues/411","id":410384988,"node_id":"MDU6SXNzdWU0MTAzODQ5ODg=","number":411,"title":"How to pass named parameter into spatialite MakePoint() function","user":{"login":"dazzag24","id":1055831,"node_id":"MDQ6VXNlcjEwNTU4MzE=","avatar_url":"https://avatars.githubusercontent.com/u/1055831?v=4","gravatar_id":"","url":"https://api.github.com/users/dazzag24","html_url":"https://github.com/dazzag24","followers_url":"https://api.github.com/users/dazzag24/followers","following_url":"https://api.github.com/users/dazzag24/following{/other_user}","gists_url":"https://api.github.com/users/dazzag24/gists{/gist_id}","starred_url":"https://api.github.com/users/dazzag24/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dazzag24/subscriptions","organizations_url":"https://api.github.com/users/dazzag24/orgs","repos_url":"https://api.github.com/users/dazzag24/repos","events_url":"https://api.github.com/users/dazzag24/events{/privacy}","received_events_url":"https://api.github.com/users/dazzag24/received_events","type":"User","user_view_type":"public","site_admin":false},"labels":[{"id":3750195281,"node_id":"LA_kwDOBm6k_c7fh3BR","url":"https://api.github.com/repos/simonw/datasette/labels/spatialite","name":"spatialite","color":"20CE84","default":false,"description":""}],"state":"closed","locked":false,"assignees":[],"milestone":null,"comments":3,"created_at":"2019-02-14T16:30:22Z","updated_at":"2023-10-25T13:23:04Z","closed_at":"2019-05-05T12:25:04Z","assignee":null,"author_association":"NONE","active_lock_reason":null,"sub_issues_summary":{"total":0,"completed":0,"percent_completed":0},"issue_dependencies_summary":{"blocked_by":0,"total_blocked_by":0,"blocking":0,"total_blocking":0},"body":"Hi,\r\ndatasette version: \"0.26.2\"\r\nextensions: \r\n       spatialite: \"4.4.0-RC0\"\r\nsqlite version: \"3.22.0\"\r\n\r\nI 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:\r\n\r\n```\r\nconn.execute('SELECT InitSpatialMetadata(1)')\r\n\r\nconn.execute(\"SELECT AddGeometryColumn('airports', 'point_geom', 4326, 'POINT', 2);\")\r\n\r\nconn.execute('''UPDATE airports SET point_geom = GeomFromText('POINT('||\"longitude\"||' '||\"latitude\"||')',4326);''')\r\n\r\nconn.execute(\"SELECT CreateSpatialIndex('airports', 'point_geom');\")\r\n```\r\n\r\nI'm attempting to create a canned query and have this in my metadata.json file:\r\n```\r\n\"find_airports_nearest_to_point\":{\r\n    \"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;\"}\r\n```\r\nwhich doesn't seem to perform the templating of the name parameters correctly and I get no results.  \r\n\r\nHave also tired:\r\n```\r\nMakePoint( || :Long || , || :Lat || )\r\n```\r\nwhich returns this error:\r\n```\r\nnear \"||\": syntax error\r\n```\r\n\r\nHowever 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?\r\n\r\nThanks\r\nDarren","closed_by":{"login":"simonw","id":9599,"node_id":"MDQ6VXNlcjk1OTk=","avatar_url":"https://avatars.githubusercontent.com/u/9599?v=4","gravatar_id":"","url":"https://api.github.com/users/simonw","html_url":"https://github.com/simonw","followers_url":"https://api.github.com/users/simonw/followers","following_url":"https://api.github.com/users/simonw/following{/other_user}","gists_url":"https://api.github.com/users/simonw/gists{/gist_id}","starred_url":"https://api.github.com/users/simonw/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/simonw/subscriptions","organizations_url":"https://api.github.com/users/simonw/orgs","repos_url":"https://api.github.com/users/simonw/repos","events_url":"https://api.github.com/users/simonw/events{/privacy}","received_events_url":"https://api.github.com/users/simonw/received_events","type":"User","user_view_type":"public","site_admin":false},"reactions":{"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},"timeline_url":"https://api.github.com/repos/simonw/datasette/issues/411/timeline","performed_via_github_app":null,"state_reason":"completed","pinned_comment":null}