Matías Neiff's Blog

Bueno supongamos que tengamos una base de datos de nominatim, instalada y queremos a partir de un punto sacar las intersecciones, calle, barrio, municipio, depto y provincia. Aunque también sirve para extraer la calle si lo modifican un poco. Les dejo el SQL:

select calle, barrio, muni, depto, prov, placex.name->'name'as pais from (
select calle, barrio, muni, depto, placex.name->'name'as prov,placex.parent_place_id from (
select calle, barrio, muni, placex.name->'name'as depto,placex.parent_place_id from (
select calle, barrio, placex.name->'name'as muni,placex.parent_place_id from (
select distinct calles.name->'name' as calle, placex.name->'name' as barrio, placex.parent_place_id from (
select name, place_id,parent_place_id,rank_search from placex
WHERE ST_DWithin(st_geomfromtext('point(-76.5482 3.4477)',4326), geometry, 0.1)
and rank_search != 28 and rank_search >= 22
and (name is not null or housenumber is not null)
and class not in ('waterway','railway','tunnel','bridge')
and indexed_status = 0 
and (ST_GeometryType(geometry) not in ('ST_Polygon','ST_MultiPolygon') 
OR ST_DWithin(st_geomfromtext('point(-76.5482 3.4477)',4326), centroid,0.1))
ORDER BY ST_distance(st_geomfromtext('point(-76.5482 3.4477)',4326), geometry) ASC limit 2) as calles
inner join placex on placex.place_id = calles. parent_place_id)
as barrio
inner join placex on placex.place_id = barrio.parent_place_id) as muni
inner join placex on placex.place_id = muni.parent_place_id) as depto
inner join placex on placex.place_id = depto.parent_place_id) prov
inner join placex on placex.place_id = prov.parent_place_id;

Espero le sirva a alguien más.