Le portail francophone de la géomatique

La nouvelle guerre GPS et ses risques · el equipo presentando su proyecto frente a los demás equipos concursantes

				
					CREATE OR REPLACE TABLE routes AS 
FROM read_csv_auto('c:\...\routes.txt') ; 
				
			
https://icem7.fr/data/proxy_unzip.php?file=routes
&url=https://data.toulouse-metropole.fr/api/explore/v2.1/catalog/datasets/
tisseo-gtfs/files/fc1dda89077cf37e4f7521760e0ef4e9
				
					CREATE OR REPLACE MACRO get_gtfs(f, cache) AS 'https://icem7.fr/data/proxy_unzip.php?clear_cache=' || cache || '&file=' || f 
|| '&url=https://data.toulouse-metropole.fr/api/explore/v2.1/catalog/datasets/
tisseo-gtfs/file/fc1dda89077cf37e4f7521760e0ef4e9';

				
			
				
					-- 1er appel forçant le téléchargement du dernier gtfs
CREATE OR REPLACE TABLE routes AS FROM read_csv_auto(get_gtfs('routes', 1)) ; 
CREATE OR REPLACE TABLE trips  AS FROM read_csv_auto(get_gtfs('trips', 0)) ;
CREATE OR REPLACE TABLE shapes AS FROM read_csv_auto(get_gtfs('shapes', 0)) ;
CREATE OR REPLACE TABLE stops  AS FROM read_csv_auto(get_gtfs('stops', 0)) ;
CREATE OR REPLACE TABLE stop_times AS FROM read_csv_auto(get_gtfs('stop_times', 0)) ;
CREATE OR REPLACE TABLE calendar_dates AS FROM 
read_csv_auto(get_gtfs('calendar_dates', FALSE), 
              types=[VARCHAR,DATE,INT], dateformat='%Y%m%d') ;

				
			
				
					LOAD spatial ;
SELECT shape_id, shape_pt_sequence, 
ST_Point(shape_pt_lon,shape_pt_lat) AS geometry
FROM shapes ;
				
			
				
					WITH shapes_pt_geo AS (
 SELECT shape_id, shape_pt_sequence, 
 ST_Point(shape_pt_lon,shape_pt_lat) AS geometry
 FROM shapes
 ORDER BY shape_id, shape_pt_sequence
)
SELECT shape_id, 
ST_MakeLine(list(geometry)) AS geometry
FROM shapes_pt_geo
GROUP BY ALL ;
				
			
				
					CREATE OR REPLACE TABLE reseau_gtfs_toulouse_met AS
WITH shapes_pt_geo AS (
 SELECT shape_id, shape_pt_sequence, shape_dist_traveled, 
 ST_Point(shape_pt_lon,shape_pt_lat) AS geometry
 FROM shapes
 ORDER BY shape_id, shape_pt_sequence
),
shapes_lines_geo AS (
 SELECT shape_id, max(shape_dist_traveled)::int AS shape_length,
 ST_MakeLine(list(geometry)) AS geometry
 FROM shapes_pt_geo
 GROUP BY ALL
)
SELECT r.route_id,r.route_short_name,r.route_long_name,r.route_type,
	   s.shape_id,s.shape_length,s.geometry
FROM shapes_lines_geo s
LEFT JOIN (SELECT DISTINCT route_id, shape_id FROM trips) t 
	ON s.shape_id = t.shape_id
LEFT JOIN routes r ON r.route_id = t.route_id
ORDER BY r.route_type, r.route_id ;

				
			
				
					COPY reseau_gtfs_toulouse_met
TO 'C:/…/reseau_gtfs_toulouse_met.json'
WITH (FORMAT GDAL, DRIVER 'GeoJSON');

				
			
				
					SELECT route_short_name, route_long_name, shape_length,
ST_length(ST_Transform(geometry,'EPSG:4326','EPSG:2154', true))::int 
AS shape_length_calc
FROM reseau_gtfs_toulouse_met
ORDER BY shape_length DESC ;
				
			
				
					SELECT route_short_name, route_long_name, shape_length,
geometry.ST_Transform('EPSG:4326','EPSG:2154', true)
        .ST_Length()::int 
AS shape_length_calc
FROM reseau_gtfs_toulouse_met
ORDER BY shape_length DESC ;

				
			
				
					SELECT ST_Point(1.46158, 43.69875) AS home_location ;
				
			
				
					CREATE OR REPLACE VIEW arrets_proches AS 
SELECT ST_Point(stop_lon, stop_lat) AS geometry, 
ST_Distance(
 ST_Point(stop_lon, stop_lat).ST_Transform('EPSG:4326','EPSG:2154', true),
 ST_Point(1.46158,  43.69875).ST_Transform('EPSG:4326','EPSG:2154', true)
)::int AS distance_home, 
stop_id, stop_name FROM stops
WHERE distance_home 
				
			
				
					SELECT route_short_name, route_long_name, stop_name, arrival_time, 
trip_headsign, distance_home, trips.trip_id
FROM arrets_proches
JOIN stop_times ON arrets_proches.stop_id = stop_times.stop_id
JOIN trips ON stop_times.trip_id = trips.trip_id
JOIN calendar_dates ON trips.service_id = calendar_dates.service_id
JOIN routes ON routes.route_id = trips.route_id
WHERE arrival_time > localtime AND calendar_dates.date = current_date
ORDER BY arrival_time, distance_home ;

				
			
				
					WITH trips_proches AS (
	SELECT route_short_name || ' - ' || route_long_name AS ligne, 
	trip_headsign AS terminus, 
	stop_name, distance_home, arrival_time, trips.trip_id, geometry 
	FROM arrets_proches
	JOIN stop_times ON arrets_proches.stop_id = stop_times.stop_id
	JOIN trips ON stop_times.trip_id = trips.trip_id
	JOIN calendar_dates ON trips.service_id = calendar_dates.service_id
	JOIN routes ON routes.route_id = trips.route_id
	WHERE arrival_time > localtime AND calendar_dates.date = current_date
)
SELECT * EXCLUDE(trip_id),
FROM trips_proches
WHERE terminus IN ('Borderouge', 'Argoulets')
QUALIFY rank() over(PARTITION BY trip_id ORDER BY distance_home) = 1
ORDER BY arrival_time, distance_home ;

				
			
				
					CREATE OR REPLACE TABLE troncons_izards_ as
SELECT code_insee, street, fromleft, fromright, 
ST_GeomFromWKB(geometry) AS geometry
FROM 'https://static.data.gouv.fr/resources/filaire-voiries-toulouse-metropole-format-geoparquet/20231219-050942/filaire-de-voirie-toulouse-met-geo.parquet'
WHERE motdir LIKE 'IZARDS%'
ORDER BY fromleft, fromright ;

				
			
				
					CREATE OR REPLACE TABLE frontiere_izards AS 
SELECT DISTINCT a.geometry,a.fromleft,a.fromright 
FROM izards_troncons a 
CROSS JOIN izards_troncons b 
WHERE ST_Equals(a.geometry, b.geometry) AND a.code_insee  b.code_insee
ORDER BY a.fromleft, a.fromright ;
				
			
				
					CREATE OR REPLACE TABLE buffer_izards AS 
SELECT ST_Union_Agg(geometry)
.ST_Transform('EPSG:4326','EPSG:2154',true)
.ST_Buffer(100) -- buffer de 100 mètres
.ST_Transform('EPSG:2154','EPSG:4326',true) AS geom,
quadkey_min_geo(geom) AS quadkey
FROM frontiere_izards ;

				
			
				
					FORCE INSTALL spatial FROM 'http://nightly-extensions.duckdb.org' ;
				
			
				
					CREATE OR REPLACE MACRO quadkey_min_geo(geom) AS (
 WITH t1 AS (
	SELECT 
	unnest(split(ST_Quadkey(ST_XMin(geom), ST_YMin(geom),12),'')) AS a, 
	unnest(split(ST_Quadkey(ST_XMin(geom), ST_YMax(geom),12),'')) AS b, 
	unnest(split(ST_Quadkey(ST_XMax(geom), ST_YMin(geom),12),'')) AS c, 
	unnest(split(ST_Quadkey(ST_XMax(geom), ST_YMax(geom),12),'')) AS d 
 ) 
 SELECT string_agg(a,'') AS quadkey FROM t1 WHERE a = b AND b = c AND c = d
) ;

				
			
				
					SELECT geometry FROM 'https://static.data.gouv.fr/resources/ban-format-parquet/20231228-103716/adresses-france-2023.parquet'
WHERE quadkey LIKE '120222030220%' ;
				
			
				
					SELECT ST_GeomFromWKB(geometry) AS geom FROM 'https://static.data.gouv.fr/resources/ban-format-parquet/20231228-103716/adresses-france-2023.parquet' a
JOIN buffer_izards b 
     ON ST_Within(ST_GeomFromWKB(geometry), b.geom)
WHERE a.quadkey LIKE '120222030220%' ; -- 1 s
				
			
				
					SELECT ST_GeomFromWKB(geometry) AS geom FROM 'https://static.data.gouv.fr/resources/ban-format-parquet/20231228-103716/adresses-france-2023.parquet' a
JOIN buffer_izards b ON ST_Within(ST_GeomFromWKB(geometry), b.geom)
WHERE a.quadkey LIKE (b.quadkey || '%') ;  -- 10 s
				
			
				
					SELECT ST_union_agg(geometry) AS geometry FROM frontiere_izards
UNION SELECT geom FROM buffer_izards
UNION (SELECT ST_GeomFromWKB(geometry) AS geom FROM 'https://static.data.gouv.fr/resources/ban-format-parquet/20231228-103716/adresses-france-2023.parquet' a
 JOIN buffer_izards b ON ST_Within(ST_GeomFromWKB(geometry), b.geom)
 WHERE a.quadkey LIKE '120222030220%')
UNION SELECT ST_ExteriorRing(geom) FROM st_read('https://geo.api.gouv.fr/communes?code=31555&format=geojson&geometry=contour')
UNION SELECT ST_ExteriorRing(geom) FROM st_read('https://geo.api.gouv.fr/communes?code=31282&format=geojson&geometry=contour') ;
				
			
				
					COPY (
WITH ban AS (
 SELECT *, ST_AsWKB(ST_point(lon, lat)) AS geometry, 
 ST_QuadKey(ST_point(lon, lat), 12) AS quadkey 
 FROM 'c:\...\adresses-france.csv.gz' 
)
SELECT * EXCLUDE(lon,lat,x,y) FROM ban
ORDER BY quadkey
) TO 'c:\...\adresses-france.parquet' ;
				
			
gpq convert c:\...\adresses-france.parquet c:\...\adresses-france-geo.parquet 
--from=parquet --to=geoparquet --compression=zstd

Communauté	Ressources	L'emploi	Le marché	L'association	Fils RSS
Forum Blogs Wiki Planet Sites amis	Agenda Biblio Fiches AMO-CNIG Paris PCGIS Téléchargements	Offres d'emploi Banque de CV Formations Démarche Métiers	Geo-Entreprises Geo-Communiqué Appels d'offres	L'association Qui sommes-nous ? Nous aider Bienfaiteurs Partenaires Historique Statuts	Tout le site Job Géomatique Services GeoBlogs ...	Faire un don Mentions légales

Le portail francophone de la géomatique

Canaux

Éléments récents