Is it possible to execute the command line inside SpatiaLite?

Question

Let's say I have a dataset filled with points on SpatiaLite and I don't have these points address location on this database. So, I make a new column on it for adding this address as the following:

ALTER TABLE my_table ADD COLUMN address TEXT

I'd like to execute commands available on the command line in my Operating System... Something like the following:

UPDATE 'my_table' SET address = externalCommand(getLocation, AsText(geometry))

In this pseudo example, getLocation is a script that I've created and externalCommand would be a way of accessing the command line with SpatiaLite. For example, my getLocation script is the following:

#!/bin/bash
  
point="$1"
latlong=($(echo "$point" | sed 's/)//g' | sed 's/^.*(//g'))
lat="${latlong[1]}"
long="${latlong[0]}"

curl -s "https://nominatim.openstreetmap.org/reverse?lat=$lat&lon=$long&zoom=10&format=json" | jq '.display_name'
sleep 0.5

This script is functional (it relies on curl, sed and jq), I'm using the OSM nominatim API in order to get the address of a specific latitude and longitude. If I execute this script with getLocation "POINT(-46.384794 -23.513442)", I see:

"São Paulo, Região Imediata de São Paulo, Região Metropolitana de São Paulo, Região Geográfica Intermediária de São Paulo, São Paulo, Região Sudeste, Brasil"

In my view, if I can execute the command line from inside SpatiaLite, I can also do stuff like that and use the UPDATE statement to get the address of points dynamically using an API like the OSM one... Is it possible? Can I execute commands available on the operating system in a query?

Answer 1

Approach 1: update each line of your database table using ogrinfo and WHERE statement (individual line update)

My first approach differs from your expectation. I do not try to run a command within Spatialite but to call Spatialite from the command line and provide the infos as variables to customize the SQL query.

You can try the following. My comments in the code explain the changes done

long=-1.5543
lat=47.2186
# Added -r option (for raw) to jq and assigned result to content variable
content=$(curl -s "https://nominatim.openstreetmap.org/reverse?lat=$lat&lon=$long&zoom=10&format=json" | jq -r '.display_name')

# Update using ogrinfo to Spatialite db yourfile.sqlite
# I've added a where condition you need to customized or you will update all records
ogrinfo -dialect SQLite -sql "UPDATE 'my_table' SET address = '"$content"' WHERE your_identifier_col = 'your_identifier_value'" yourfile.sqlite

Approach 2: update using Spatialite functions available through GDAL (no need for WHERE)

You can also play with existing geocoding functions available within GDAL (docs at https://gdal.org/user/sql_sqlite_dialect.html#ogr-geocoding-functions)

# Standalone demo, no update
# I change query template to call endpoint to call API to return JSON and then filter JSON
# Not really useful here but useful if consuming other services
ogrinfo --config OGR_GEOCODE_REVERSE_QUERY_TEMPLATE "http://nominatim.openstreetmap.org/reverse?format=json&lat={lat}&lon={lon}" -dialect -SQLite -sql "SELECT json_extract(ogr_geocode_reverse(2.0,49.0,'raw'), '$')" input.sqlite

Second choice, more simple as it use GDAL supported provider for Nominatim

ogrinfo --config OGR_GEOCODE_SERVICE OSM_NOMINATIM -dialect -SQLite -sql "SELECT ogr_geocode_reverse(2.0,49.0,'display_name')" input.sqlite

For your particular case, to update your field address using columns for lon and lat from your database table

# Update with display_name content from nominatim
ogrinfo --config OGR_GEOCODE_SERVICE OSM_NOMINATIM -dialect -SQLite -sql "UPDATE 'my_table' SET address = ogr_geocode_reverse(lon,lat,'display_name')" input.sqlite

You can also use ogr_geocode_reverse(geometry,'display_name') if your input coordinates are in your geometry and they are points instead of ogr_geocode_reverse(lon,lat,'display_name') You can also change delay adding option --config OGR_GEOCODE_DELAY 0.5(1 sed by default). Other options to change common configs to ogr_geocode/ogr_geocode_reverse behaviour https://gdal.org/doxygen/ogr__geocoding_8h.html#ac3e12320a8046248b992fd0ce4731903