ST_ReverseGeocode

Answers

• Options

  mosheg Vertica Employee Administrator

  January 2023

  Not at the current version.
  Please open a support case to explain the business need to prioritize this.

  0
• Options

  elghali ✭

  January 2023

  Thank you Mosheg, basically we need to reverse geocode a location , feeding the function the lat/long and getting the address in human readable format

  example:

  SELECT ST_Reverse(ST_Point(-122.0856086, 37.4224082)) AS address;

  0
• Options

  Bryan_H Vertica Employee Administrator

  January 2023

  An issue is that Vertica needs a third-party provider for address lookup. You could write a Python UDSF that takes a Point or (x,y) coordinate as input and calls the OpenStreetMap API to get an address, which you would then return as a VARCHAR (or ROW if you'd like the address components):
  https://wiki.openstreetmap.org/wiki/Nominatim
  ArcGIS and others also provide reverse geocoding API that could be accessed by a UDX.

  0
• Options

  Bryan_H Vertica Employee Administrator

  January 2023

  I've posted a Python UDSF example using GeoPy and Nominatim at
  https://github.com/bryanherger/vertica-python-udx
  Please see the instructions and example under "UDSF example: reverseGeocode" in the README and sample implementation in the reversegeocode folder. It has not been tested at scale beyond the one-time example shown.

  2
• Options

  elghali ✭

  January 2023

  @Bryan_H said:
  I've posted a Python UDSF example using GeoPy and Nominatim at
  https://github.com/bryanherger/vertica-python-udx
  Please see the instructions and example under "UDSF example: reverseGeocode" in the README and sample implementation in the reversegeocode folder. It has not been tested at scale beyond the one-time example shown.

  Thank you for your time, this is very helpful

  0