1

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?

0

1 Answer 1

3

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

6
  • My idea by executing the command line inside a query was that I'd be able to get the address of that point based on the actual data that's inside the database. On your samples the point location is hardcoded, right? In this logic, I'd still have to take the points out of the database together with an ID in order to update all points correctly. And also execute ogrinfo as many times as the number of lines on the database.. I can see a script following this logic of getting the points from the database and updating them line by line inside a loop. I'm wondering if I can avoid this loop though.
    – raylight
    Jul 1, 2021 at 4:02
  • Edited my answer. 2nd approach do what you expect: no WHERE to get addresses for each line
    – ThomasG77
    Jul 1, 2021 at 9:18
  • I'm not sure if I'm missing something... But after executing it I just see that the process is being executed but with no additional logs. Is it executed silently the number of lines of my database seconds?
    – raylight
    Jul 1, 2021 at 16:10
  • You can see what's happening by adding option --config CPL_DEBUG ON to get more verbose infos
    – ThomasG77
    Jul 1, 2021 at 16:20
  • 1
    You may update by chunk using rowid e.g stackoverflow.com/questions/39393095/… Although geocode works, if it fails, it may behave like a blackbox you are unable to control. If you only update some records addresses using chunk, it will be easier to locate if an issue happens
    – ThomasG77
    Jul 1, 2021 at 17:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.