{"id":7912,"date":"2019-01-25T04:54:11","date_gmt":"2019-01-25T03:54:11","guid":{"rendered":"http:\/\/www.sigterritoires.fr\/?p=7912"},"modified":"2019-01-25T08:40:19","modified_gmt":"2019-01-25T07:40:19","slug":"how-to-develop-an-application-with-pgrouting-in-windows-4-catchment-areas-and-isochrones","status":"publish","type":"post","link":"https:\/\/www.sigterritoires.fr\/index.php\/en\/how-to-develop-an-application-with-pgrouting-in-windows-4-catchment-areas-and-isochrones\/","title":{"rendered":"How to develop an application with pgRouting in Windows (4): catchment areas and isochrones"},"content":{"rendered":"\n

pgRouting includes a\nfunction\ncalled\npgr_drivingDistance\n, that, together with three others functions , pgr_withPointsDD\n, pgr_alphaShape and pgr_pointsAsPolygon , can be used to establish which\npaths\nhave\na certain cost versus a starting or arrival\npoint . <\/p>\n\n\n\n

The area surrounding these paths\nis\ndesignated\nas\na\ncatchment area . pgr_drivingDistance , in\nconjunction\nwith pgr_pointsAsPolygon is often\nused\nto determine coverage requirements for a service or to determine optimal\nlocations for new facilities. <\/p>\n\n\n\n\n\n\n\n

For example , if you have\na\nfire\nstation, you must determine\nall\nparts of the city that can be reached\nwithin five minutes. A reverse\nproblem would be to find all parts of the city\nwhere\nthe residents could drive\nto\na hospital within five minutes.\n
\nThe\nfunction that calculates the paths for both problems\nis\ncalled\npgr_drivingDistance\nbecause it is often used\nwhen\nthe cost is measured as driving\ntime . However, the costs are not necessarily\n  limited to a time or a\ndistance.
\nFor\nthis type of application , knowing all routes towards\nor\nfrom a given point is not very useful.  It\nis here where the functions   pgr _alphaShape and\npgr_pointsAsPolygon come into play. The\npgr_alphaShape use as input a set of x, y\ncoordinates and returns a set of x, y which correspond to the vertices of a\npolygon that includes all the input points.
\npgr_pointsAsPolygon\nis\nsimilar\nto pgr_alphaShape , it uses as input a set of coordinates x, y, but instead of\nreturning another\nseries\nof points, returns a polygon   that  surrounds all input\npoints . Internally, it uses the pgr_alphaShape function .\n<\/p>\n\n\n\n

Using<\/strong> pgr_drivingDistance<\/strong>\n<\/p>\n\n\n\n

Let’s take the case of a\nfire\nstation and search the area covered in 5 minutes. The\norder sql corresponding to these\ncriteria\ncan be translated by the following: <\/p>\n\n\n\n

SELECT * FROM<\/em><\/strong> pgr_drivingDistance<\/em><\/strong>\n(‘SELECT<\/em><\/strong> gid<\/em><\/strong> as id, source, target,<\/em><\/strong> cost_s<\/em><\/strong>\nas cost,<\/em><\/strong> reverse_cost_s<\/em><\/strong> as<\/em><\/strong> reverse_cost<\/em><\/strong>\nFROM<\/em><\/strong> public.ways<\/em><\/strong> ‘, 25736, 300, true)<\/em><\/strong>\n\n\nIn the SELECT clause we use\nthe cost of a section in\nseconds\n(cost_s), and indicate the reverse_cost_s because even fire trucks cannot drive\nagainst traffic. 25736<\/em><\/strong> is\nthe node corresponding to the fire station, 300<\/em><\/strong>\nis\nthe forecasted cost (5 minutes x 60 seconds ), and the true<\/em><\/strong>\nvalue indicates that the graph is\ndirectional\n(takes into account the forbidden driving\nsense ). \n\n\n\n<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

The result of the query returns\n6948 nodes that are in within\n5 minutes from the fire station. By cons the result of the query\nhaving\nno geometry , we can not visualize them in QGis. <\/p>\n\n\n\n

You have to modify the query as\nfollows: <\/p>\n\n\n\n

SELECT * FROM<\/em><\/strong> pgr_drivingDistance<\/em><\/strong>\n(‘SELECT<\/em><\/strong> gid<\/em><\/strong> as id, source, target,<\/em><\/strong> cost_s<\/em><\/strong>\nas cost,<\/em><\/strong> reverse_cost_s<\/em><\/strong> as<\/em><\/strong> reverse_cost<\/em><\/strong>\nFROM<\/em><\/strong> public.ways<\/em><\/strong> ‘, 25736, 300, true) as JOIN<\/em><\/strong>\nways_vertices_pgr<\/em><\/strong> on di.node = ways_vertices_pgr.id<\/em><\/strong>\n\n\nWe perform a join with the node table (\nways_vertices_pgr ) and the result of the query includes\nthe geometries :   \n\n\n\n<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

We can see the\nresult in QGis:\n\n\n\n<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

How to create<\/strong>\nthe<\/strong>\ncatchment<\/strong>\narea<\/strong>\nwith<\/strong>\npgr_pointsAsPolygon<\/strong>\n\n\nTo create the catchment area, we use\nthe former query as sql\nof the function pgr_pointsAsPolygon , by\ncreating\na polygon in output that we can load in QGis:
SELECT 1 As id,<\/em><\/strong>ST_SetSRID<\/em><\/strong>(<\/em><\/strong>pgr_pointsAsPolygon<\/em><\/strong>(<\/em><\/strong>
$$ SELECT<\/em><\/strong>di.seq<\/em><\/strong>AS id, ST_X\n(<\/em><\/strong>v.the_geom<\/em><\/strong>) AS x, ST_Y (<\/em><\/strong>v.the_geom<\/em><\/strong>) As y<\/em><\/strong>
FROM<\/em><\/strong>pgr_drivingDistance<\/em><\/strong>(<\/em><\/strong>SELECT<\/em><\/strong>gid<\/em><\/strong>As id, source, target,<\/em><\/strong>
cost_s<\/em><\/strong>AS cost,<\/em><\/strong>reverse_cost_s<\/em><\/strong>AS<\/em><\/strong>reverse_cost<\/em><\/strong>
FROM<\/em><\/strong>public.ways<\/em><\/strong>\u00bb, 25736, 300, true<\/em><\/strong>
) AS di INNER JOIN<\/em><\/strong>public.ways_vertices_pgr<\/em><\/strong>AS v ON<\/em><\/strong>di.node<\/em><\/strong>= v.id $$<\/em><\/strong>
), 4326) As<\/em><\/strong>geom<\/em><\/strong>;<\/em><\/strong><\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

pgr_pointAsPolygon uses the id, x, and y points\ngenerated by pgr_drivingDistance . For x\nand y we use the functions St_x and St_y on the points geometry.\n\n\nOnce loaded in QGis, the\nresult is as follows :   \n\n\n\n<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

How to create the\nisochrones<\/strong> <\/strong><\/p>\n\n\n\n

The isochrones correspond to the reachable areas\nin\na\nseries\nof time steps. We could , in this\nexample\n, want to know which are\nthe areas we can reach\nin\n1, 2, 3, 4 and 5 minutes. <\/p>\n\n\n\n

Therefore we will proceed in\ntwo\nsteps\n. <\/p>\n\n\n\n

The first one, is to create a\ntable with our nodes resulting\nfrom pgr_drivingDistance for the fastest time step (5 minutes). Logically\n, in this table it will, also, be found the nodes\nreachable\nin\nthe shortest periods of time. <\/p>\n\n\n\n

We create this table ( dd_caserne )\nwith the following sql command  : <\/p>\n\n\n\n

CREATE TABLE<\/em><\/strong> dd_caserne<\/em><\/strong> AS<\/em><\/strong>\n
\nSELECT<\/em><\/strong> di.seq<\/em><\/strong> As id,<\/em><\/strong>\ndi.node<\/em><\/strong> ,<\/em><\/strong> di.edge<\/em><\/strong> ,<\/em><\/strong>\n
\ndi.agg_cost<\/em><\/strong> As<\/em><\/strong> time_access<\/em><\/strong>\n,<\/em><\/strong> v.the_geom<\/em><\/strong> As<\/em><\/strong> geom<\/em><\/strong>\n
\nFROM<\/em><\/strong> pgr_drivingDistance<\/em><\/strong> (SELECT<\/em><\/strong>\ngid<\/em><\/strong> As id, source, target,<\/em><\/strong>
\ncost_s<\/em><\/strong> AS cost,<\/em><\/strong> reverse_cost_s<\/em><\/strong>\nAS<\/em><\/strong> reverse_cost<\/em><\/strong>
\nFROM public.ways’, 25736, 300, true<\/em><\/strong>\n
\n) AS di INNER JOIN<\/em><\/strong> public.ways_vertices_pgr<\/em><\/strong>\nAS v ON<\/em><\/strong> di.node<\/em><\/strong> = v.id;<\/em><\/strong>\n\n\nThe resulting table is the following :\n\n\n\n\n<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Based on this table we will build\na\nseries\nof polygons with pgr_pointsAsPolygon for points at 60, 120 180 240 and 300\nseconds of access time by using generate_series\nSELECT<\/em><\/strong>i<\/em><\/strong>As<\/em><\/strong>time_access,\nST_SetSRID<\/em><\/strong>(<\/em><\/strong>pgr_pointsAsPolygon<\/em><\/strong>(<\/em><\/strong>
‘SELECT id, ST_X (<\/em><\/strong>geom<\/em><\/strong>) AS\nx, ST_Y (<\/em><\/strong>geom<\/em><\/strong>) As y<\/em><\/strong>
FROM<\/em><\/strong>dd_caserne<\/em><\/strong>
WHERE<\/em><\/strong>access_time<\/em><\/strong><= ‘||<\/em><\/strong>i :: text), 4326) As<\/em><\/strong>geom<\/em><\/strong>
FROM<\/em><\/strong>generate_series<\/em><\/strong>(60,300,60)\nAs<\/em><\/strong>i<\/em><\/strong>
ORDER BY<\/em><\/strong>i<\/em><\/strong>DESC<\/em><\/strong><\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\nThe\nresult includes\nthe 5 isochrones polygons , which we can, now,\ndisplay\nin\nQGis:\n\n\n\n<\/p>\n\n\n\n

\"\"<\/figure>\n","protected":false},"excerpt":{"rendered":"

pgRouting includes a function called pgr_drivingDistance , that, together with three others functions , pgr_withPointsDD , pgr_alphaShape and pgr_pointsAsPolygon , can be used to establish which paths have a certain cost versus a starting or arrival…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"give_campaign_id":0,"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"sfsi_plus_gutenberg_text_before_share":"","sfsi_plus_gutenberg_show_text_before_share":"","sfsi_plus_gutenberg_icon_type":"","sfsi_plus_gutenberg_icon_alignemt":"","sfsi_plus_gutenburg_max_per_row":"","_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[1260],"tags":[],"class_list":["post-7912","post","type-post","status-publish","format-standard","hentry","category-non-classe-en"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p6XU0A-23C","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/posts\/7912","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/comments?post=7912"}],"version-history":[{"count":0,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/posts\/7912\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/media?parent=7912"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/categories?post=7912"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/tags?post=7912"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}