Distance to nearest polling station on a map

[tdamsma]

Inspired by @hpfast presentation on distance to nearest polling station in Utrecht, I had a quick go at this issue using pgroute. Just wanted to share my findings:

I loaded the Rotterdam metro area (300k nodes), and defined 33 facilities (facility being the polling station or some other Point of Interest). Using the function pgr_drivingdistance it is possible to do the entire query in one go (define distance to multiple sources), reducing query time for my case to just under 10 seconds on my laptop.

The query is as follows:

--DROP MATERIALIZED VIEW distances;
CREATE MATERIALIZED VIEW distances AS (

WITH 
--first map the facilities to the nearest node on the network
node_facility AS (
    SELECT
		facilities.id AS facilities_id,
        (SELECT 
            nodes.id AS node_id
        FROM ways_vertices_pgr AS nodes
        ORDER BY facilities.geom <#> nodes.the_geom
        LIMIT 1)
        FROM facilities
    ),
--aggregate all nodes in opne array
nodes AS (
    SELECT array_agg(node_id) AS nodes FROM node_facility
    ),
--then calculate the distance from every point on the network to each of the 
--nodes. Stop searching after max distance (10km in this example) is reached.
distance AS (
    SELECT *
    FROM nodes, pgr_drivingDistance('
        SELECT gid AS id,
            source::int4,
            target::int4,
            length_m::float8 AS cost
        FROM ways',
        nodes.nodes,
        10000::FLOAT,
        false,
        false))
--use the result to select the single nearest facility
SELECT DISTINCT ON (nodes.id) 
    nodes.id, 
    nodes.the_geom, 
	node_facility.facilities_id,
    distance.agg_cost 
  FROM ways_vertices_pgr AS nodes
  JOIN distance ON distance.node = nodes.id
  JOIN node_facility ON node_facility.node_id = distance.from_v
  ORDER BY nodes.id, agg_cost ASC
  )

[hpfast]

there we go, that's more like it :)

thanks for sharing this.

[tdamsma]

Almost forgot, based on: http://gis.stackexchange.com/questions/156916/driving-time-to-the-nearest-facility-using-pgrouting/233269#233269

Another thing came to mind: did you consider using nearest neighbour interpolation instead of inverse distance? I think that might be slightly more representative and that would produce nice discontinuous jumps in the gradient where highways and rivers cut through the network

[hpfast]

yeah, the contours here are based on linear interpolation in fact. First we tried inverse distance, which shows the road network in great detail but in too much detail, resulting in hard-to-read contours: [image: Inline image 1] nearest neighbour does indeed produce the nicest contour lines, as in they most resemble a real terrain especially in areas with few road nodes (and at barriers as you mention). But then the contours didn't always extend to the edge of the area and so were hard to work with to make polygons, so because of time constraints linear interpolation was a good compromise between the two.

…

On Thu, Mar 23, 2017 at 4:26 PM, Thijs Damsma ***@***.***> wrote: Almost forgot, based on: http://gis.stackexchange.com/ questions/156916/driving-time-to-the-nearest-facility-using- pgrouting/233269#233269 Another thing came to mind: did you consider using nearest neighbour interpolation instead of inverse distance? I think that might be slightly more representative and that would produce nice discontinuous jumps in the gradient where highways and rivers cut through the network — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#3 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ADfUalrF50SriRwTMaaOxUJP266q-eVgks5roo8VgaJpZM4Mmz75> .

[hpfast]

I think a nice alternative way of visualising this would be to color the road network instead of the whole surface. I know GRASS's network tools can split up the network into segments based on isochrones, and I think pgrouting can too. Another approach would be to make voronoi polygons around all the adresses, and then group those by nearest facility to visualize the catchments.

…

On Thu, Mar 23, 2017 at 4:48 PM, Hans Fast ***@***.***> wrote: yeah, the contours here are based on linear interpolation in fact. First we tried inverse distance, which shows the road network in great detail but in too much detail, resulting in hard-to-read contours: [image: Inline image 1] nearest neighbour does indeed produce the nicest contour lines, as in they most resemble a real terrain especially in areas with few road nodes (and at barriers as you mention). But then the contours didn't always extend to the edge of the area and so were hard to work with to make polygons, so because of time constraints linear interpolation was a good compromise between the two. On Thu, Mar 23, 2017 at 4:26 PM, Thijs Damsma ***@***.***> wrote: > Almost forgot, based on: http://gis.stackexchange.com/q > uestions/156916/driving-time-to-the-nearest-facility-using-p > grouting/233269#233269 > > Another thing came to mind: did you consider using nearest neighbour > interpolation instead of inverse distance? I think that might be slightly > more representative and that would produce nice discontinuous jumps in the > gradient where highways and rivers cut through the network > > — > You are receiving this because you were mentioned. > Reply to this email directly, view it on GitHub > <#3 (comment)>, > or mute the thread > <https://github.com/notifications/unsubscribe-auth/ADfUalrF50SriRwTMaaOxUJP266q-eVgks5roo8VgaJpZM4Mmz75> > . >