Skip to content

Instantly share code, notes, and snippets.

@cquest
Created February 8, 2020 10:31
Show Gist options
  • Save cquest/c0a84e6757d15e66e6ae429e91a74a9e to your computer and use it in GitHub Desktop.
Save cquest/c0a84e6757d15e66e6ae429e91a74a9e to your computer and use it in GitHub Desktop.
Extraction des intersections de rues pour alimenter le géocodeur addok
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment