...
| Code Block | ||||
|---|---|---|---|---|
| ||||
CREATE TABLE public.osm_roadnames (id serial primary key, name_0 text, name_1 text, areaname_2 text, name text, km double precision); SELECT AddGeometryColumn('public','osm_roadnames','geom',4326,'MULTILINESTRING',2); insert into public.osm_roadnames (areaname_0, name_1, name_2, name,km,geom) SELECT name_0, name_1, name_2, osm_name, sum(km), ST_Multi(st_union(geom_way)) FROM sweden_2po_4pgr JOIN gadm.gadm ON gadm.objectid = sweden_2po_4pgr.gadm_link where osm_name is not null group by 1,2; CREATE INDEX osm_roadnames_gix ON public.osm_roadnames USING GIST (geom); vacuum analyze public.osm_roadnames; |
...
| Code Block | ||||
|---|---|---|---|---|
| ||||
select id
from public.sweden_osm2po_nodes
order by ST_Distance_Spheroid(ST_Transform(geom,4326), ST_GeomFromText('POINT(59.1214893 18.1033966)',4326), 'SPHEROID["WGS84",6378137,298.25728]' ) limit 1; |
Example how to create isocrone polygons based on traveltime
| Jira Legacy | ||||||||
|---|---|---|---|---|---|---|---|---|
|
| Code Block | ||||
|---|---|---|---|---|
| ||||
--Create a dataset with nodes
insert into distance_nodes (name,node,edge,cost,geom) (
SELECT '1h_walk_vh',id1 AS node, id2 AS edge, cost, geom
FROM pgr_drivingdistance(
'SELECT id, source, target, walk_cost as cost FROM public.sweden_2po_4pgr',
732011, 1, false, false --from VH an one hour away
) as di
JOIN sweden_osm2po_nodes pt
ON di.id1 = pt.id
);
--Create a isocrone polygon
INSERT INTO distance_polygons (name,max_cost, geom) (
SELECT 'Ola',0.25, ST_ConvexHull(ST_SetSRID(ST_MakePolygon(ST_AddPoint(foo.openline, ST_StartPoint(foo.openline))),4326)) as geom
from
(
SELECT ST_MakeLine(points ORDER BY id) AS openline FROM
(
SELECT ST_MakePoint(x, y) AS points, row_number() over() AS id from
pgr_alphashape($$SELECT node as id, ST_X(geom) AS x, ST_Y(geom) AS y
FROM public.distance_nodes WHERE cost < 0.25 and name ='test'$$)
) as a
) as foo )
|
...