Created
February 8, 2020 10:31
Star
You must be signed in to star a gist
Extraction des intersections de rues pour alimenter le géocodeur addok
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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