Skip to content

Instantly share code, notes, and snippets.

@oeon
Last active February 27, 2022 09:44
Show Gist options
  • Save oeon/6747878 to your computer and use it in GitHub Desktop.
Save oeon/6747878 to your computer and use it in GitHub Desktop.
basic steps with pgRouting to make alpha shape of isochrone
  1. createdb with postgres > create postgis/pgrouting extension > load road data with ogr2ogr or shp2pgsql or SPIT in QGIS

  2. create vertices_tmp table with id/geom columns

select pgr_createTopology('roads', 0.5, 'geom', 'id');

  1. from 29788 = to the nearest 'vertices_tmp' node id to Station 15, select #all nodes less than 10 units (minutes) of cost
SELECT id, id1 AS node, id2 AS edge, cost, the_geom
INTO "20_10min_nodes"
  FROM pgr_drivingdistance(
    'SELECT id, source, target, time_min as cost FROM roads',
    9798, 10, false, false
  ) as di
  JOIN vertices_tmp pt
  ON di.id1 = pt.id;
  1. create alpha shape from selected nodes table
CREATE TABLE "20_10min_alpha" AS
SELECT ST_MakePolygon(ST_AddPoint(foo.openline, ST_StartPoint(foo.openline)))::geometry, 2 as alphaPoly
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 id::integer, st_x(the_geom)::float as x, st_y(the_geom)::float as y  FROM "20_10min_nodes"')
) as a) as foo;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment