Can I use direct SQL queries instead of using Nominatim or other geo decoders?

Question

I looked at source code of Nominatim and it's look like that mostly of it's functionality is just preparing SQL query and formating it's result.

And possible other geo(de)coders works in same way. Can I use direct SQL queries to get data from DB?

Where I can find examples of them? I would like to get place name from coordinates.

score 3 · Accepted Answer · answered Mar 23 '17 at 19:04

Yes, it is possible.

I tried it a few years ago and came up with a simple, crude geocoder you can find on Github: https://github.com/plechi/mini-geocoder

You need a PostgreSQL-Server with installed PostGIS and HSTORE extensions.

Create a new Postgres Database.

Enable PostGIS and HSTORE extensions:

CREATE EXTENSION postgis;  
CREATE EXTENSION hstore;

After that you need Osmosis and a dataset from Openstreetmap, look in the OSM-Wiki for details: http://wiki.openstreetmap.org/wiki/Downloading_data

Create the database schema from Osmosis (run commands in the terminal):

psql -d <database> -f <osmosis-folder>/script/pgsnapshot_schema_0.6.sql

Import the data (also execute in the terminal):

osmosis --read-xml file="<downloaded_datafile>.osm" --write-apidb host="<dbhost>" database="<dbname>" user="<dbuser>" password="<dbpassword>"

Theoretically, you could query the resulting database (examine the schema for details).

For my geocoder, I created a "optimization table" for easier queries:

CREATE TABLE geocode_optimized
AS SELECT
     w.tags -> 'addr:street'                            AS street,
     w.tags -> 'addr:housenumber'                       AS housenumber,
     w.tags -> 'addr:postcode'                          AS postcode,
     w.tags -> 'addr:city'                              AS city,
     w.tags -> 'addr:country'                           AS country,
     AVG(ST_X(n.geom))                                  AS longitude,
     AVG(ST_Y(n.geom))                                  AS latitude,
     to_tsvector(concat_ws(' ', w.tags -> 'addr:street',
                           w.tags -> 'addr:housenumber',
                           w.tags -> 'addr:postcode',
                           w.tags -> 'addr:city',
                           w.tags -> 'addr:country'
                 ))                                     AS full_text,
     st_makepoint(AVG(ST_X(n.geom)), AVG(ST_Y(n.geom))) AS point
   FROM ways w
     INNER JOIN way_nodes wn ON w.id = wn.way_id
     INNER JOIN nodes n ON n.id = wn.node_id
   WHERE exist(w.tags, 'addr:housenumber') AND exist(w.tags, 'addr:street')
   GROUP BY housenumber, street, postcode, city, country;

CREATE INDEX idx_geocode_full_text ON geocode_optimized USING GIN (full_text);

Geocode (address to coordinates):

SELECT 
   street, 
   housenumber, 
   postcode, 
   city, 
   country, 
   longitude, 
   latitude 
FROM geocode_optimized 
WHERE full_text @@ plainto_tsquery('YOUR ADDRESS')

Reverse geocode (coordinates to address)

SELECT 
    street, 
    housenumber, 
    postcode, 
    city, 
    country, 
    longitude, 
    latitude, 
    CAST (st_distance_sphere(st_makepoint(longitude,latitude), st_makepoint('<longitude>','<latitude>')) AS FLOAT) as distance 
FROM geocode_optimized;

As mentioned, this is quite crude and it is probably not the most performant solution.

Can I use direct SQL queries instead of using Nominatim or other geo decoders?

1 Answers1