Brasil/Metodologia/Importação de endereços/Agro/Itapetininga

Testes com o município de Itapetininga (cód. IBGE 3522307), polígono 298289. Carga IBGE Agro2017 e dados OSM de outubro de 2018.

create view vw_Itapetininga_roads AS 
  SELECT p.* 
  FROM  planet_osm_roads p
  WHERE way && (select way from vw_planet_osm_polygon_city_ibge where cod_ibge=3522307); -- 813

SELECT geohash, st_distance(ag.geom,r.way,true) as dist, r.osm_id, r.osm_id2, 
       nom_tipo_seglogr,  nom_titulo_seglogr, nom_seglogr, r.tags
FROM vw_ibge_agro2017_full ag INNER JOIN vw_Itapetininga_roads r ON  ST_DWithin(ag.geom,r.way,100,true)
WHERE ag.ibge_id=3522307  order by 1,2 desc;
-- 1290

Exemplo

   geohash   |    dist     |  osm_id   | osm_id2 | nom_tipo_seglogr | nom_titulo_seglogr |          nom_seglogr           |                                                                           tags                                                                           
-------------+-------------+-----------+---------+------------------+--------------------+--------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------
 6gv8fg3771r | 40.82871976 |  -3019523 |       2 | ESTRADA          |                    | DA FAZENDA                     | {"name": "Aracaçu", "boundary": "administrative", "admin_level": 9}

 6gv8rv5hx1x | 96.53051601 | 202622354 |       1 | RUA              |                    | RODOVIA SP 127                 | {"ref": "SP-127", "name": "Rodovia Professor Francisco da Silva Pontes", "oneway": "yes", "highway": "motorway", "surface": "asphalt", "maxspeed": 110}

 6gv8u97x7p2 | 15.15822044 | 235883719 |       1 | ENTRADA          |                    | MUNICIPAL DO RETIRO            | {"name": "Estrada Municipal Espigão - Bom Retiro", "highway": "secondary"}

 6gvbbbnus03 | 81.79760418 | 200999480 |       1 | RODOVIA          |                    | SANTIAGO FRANCA SP139          | {"ref": "SP-139", "name": "Rodovia Santiago França", "highway": "primary", "maxspeed": 60}

 6gvbbbs05wp | 19.78976427 | 200999480 |       1 | RODOVIA          |                    | SANTIAGO FRANCA                | {"ref": "SP-139", "name": "Rodovia Santiago França", "highway": "primary", "maxspeed": 60}
...