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:
01 | select calle, barrio, muni, depto, prov, placex.name->'name'as pais from ( |
02 | select calle, barrio, muni, depto, placex.name->'name'as prov,placex.parent_place_id from ( |
03 | select calle, barrio, muni, placex.name->'name'as depto,placex.parent_place_id from ( |
04 | select calle, barrio, placex.name->'name'as muni,placex.parent_place_id from ( |
05 | select distinct calles.name->'name' as calle, placex.name->'name' as barrio, placex.parent_place_id from ( |
06 | select name, place_id,parent_place_id,rank_search from placex |
07 | WHERE ST_DWithin(st_geomfromtext('point(-76.5482 3.4477)',4326), geometry, 0.1) |
08 | and rank_search != 28 and rank_search >= 22 |
09 | and (name is not null or housenumber is not null) |
10 | and class not in ('waterway','railway','tunnel','bridge') |
12 | and (ST_GeometryType(geometry) not in ('ST_Polygon','ST_MultiPolygon') |
13 | OR ST_DWithin(st_geomfromtext('point(-76.5482 3.4477)',4326), centroid,0.1)) |
14 | ORDER BY ST_distance(st_geomfromtext('point(-76.5482 3.4477)',4326), geometry) ASC limit 2) as calles |
15 | inner join placex on placex.place_id = calles. parent_place_id) |
17 | inner join placex on placex.place_id = barrio.parent_place_id) as muni |
18 | inner join placex on placex.place_id = muni.parent_place_id) as depto |
19 | inner join placex on placex.place_id = depto.parent_place_id) prov |
20 | inner join placex on placex.place_id = prov.parent_place_id; |
Espero le sirva a alguien más.