[Geocoding] Reverse geocoding directly over postgreSQL tables

[Sarah Hoffmann]

Hi Toni,

On Tue, Mar 29, 2016 at 03:13:11PM +0200, Antonio Martos Ortega wrote:
> As I advanced in the title, I would like to make queries directly against
> my database, instead of using the URL get service provided by Nominatim.
> 
> I have my own Nominatim instance running in a *Debian* machine, and is
> supposed I'm working over *up-to-date versions*.
> 
> The thing is that I would like to *test multiple nodes at the same time* in
> order to get common information or relations between them. As I gave
> hundreds of tables in my Nominatim db, and doesn't look like querying them
> is trivial at all, I'm asking for some ideas.
> 
> At the end, I will probably just use the tool over *way's nodes*, so maybe
> there is a trick or some *specific tables* where I can match them.

The main table of interest for you would be the placex table. That is
the one big table that contains all places (e.g coutries, towns, streets,
house numbers) that Nominatim knows about. The geometry column already
has an index on it, so it is already suitable for reverse geocoding
(and in fact what Nominatim does).

If you are also interested in retriving the address of your result, you
will also need the place_addressline table. This essentially contains
for each place which other places in the placex table make up its
address. The only exception are POIs and housenumbers (places of
rank 30). For those, you need to look up the parent object first
(via the parent_place_id in placex) and use the address of the parent.

> I'm trying to get some examples from the source code published in Git
> <https://github.com/twain47/Nominatim>, but not sure where to find it
> either.

The best place to start looking in the code is website/reverse.php.
Starting around line 74, you can see the essentials for reverse
geocoding:

 - create a ReverseGeocode object and set a few parameters
 - call lookup and you get the id of the closest place
 - create a PlaceLookup object and set a few parameters
 - call lookupPlace with the id and you get a struct with lots
   of additional info about the place (including the address)

ReverseGeocode and PlaceLookup can be found in the lib/ directory
and might as well use these two classes as a basis for your own
implementation. However, be warned that they are not an official
API, so the interfaces might change slightly from time to time.

Kind regards

Sarah