Extraction des intersections de rues pour alimenter le géocodeur addok

osm_junctions2addok.sh

wget https://download.geofabrik.de/europe/france/ile-de-france-latest-free.shp.zip -N -nv
unzip ile-de-france-latest-free.shp.zip

ogr2ogr -f pgdump /vsistdout/ gis_osm_roads_free_1.shp -nln osm_roads --config PG_USE_COPY YES -lco GEOMETRY_NAME=geometry | psql

psql -tA -c "
SELECT row_to_json(p) FROM
(
    SELECT j.*, c.nom as city, c.insee as citycode
    FROM (SELECT 'junction_' || st_geohash(st_centroid(unnest(ST_ClusterWithin(st_intersection(r1.geometry, r2.geometry),0.0001)))) as id,
        'poi' as type,
        'junction' as poi,
        format('%s / %s', r1.name, r2.name) as name,
        st_y(st_centroid(unnest(ST_ClusterWithin(st_intersection(r1.geometry, r2.geometry),0.0005)))) as lat,
        st_x(st_centroid(unnest(ST_ClusterWithin(st_intersection(r1.geometry, r2.geometry),0.0005)))) as lon,
        '' as context,
        0 as rank
    FROM osm_roads r1
    JOIN osm_roads r2
        on (st_intersects(r1.geometry, r2.geometry))
    WHERE
        r1.name is not null and length(r1.name) > 3
        and r2.name is not null and length(r2.name) > 3
        and r1.name < r2.name
    GROUP BY r1.name, r2.name) as j
    JOIN communes c ON (ST_intersects(st_setsrid(st_makepoint(lon,lat),4326), wkb_geometry))
) as p" > junctions.json