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?