7

I want to find the highest density of OpenStreetMap nodes within a dataset of census geometries. I want to tie the census data to the location of the node which is most central in terms of population. This is required for a drive time analysis. I am aware that street nodes are not completely representative of population but it is the best we have and I need to run drive times.

I have a dataset of census geometries and assigned the id of the polygon geometries to the nodes that are covered by each polygon. I use st_coveredby to assign the blkgrp (US Census block groups) id to each OSM node.

I would like to run a query which takes all nodes within a 1000m buffer into account and calculates the density for that node based on the relative distance to all the other nodes in the 1000m buffer. The query will only look at nodes that are within the same block group.

So far I get the count using st_dwithin as well as an aggregate for the distances (either sum or avg)...

Now I just need to work out how to aggregate the distance to get the best density value for the points. I can then find the point with highest density and move the census area centroid to that location.

UPDATE osm_nodes_oh
  SET c_grp = Q.c
  FROM (  
    SELECT
      t1.id i,
      count(t2.id) c,
      sum(t1.geom <#> t2.geom) d_sum,
      avg(t1.geom <#> t2.geom) d_avg
    FROM osm_nodes_oh t1, osm_nodes_oh t2
    WHERE st_dwithin(t1.geom, t2.geom, 1000)
      AND t1.blkgrp = t2.blkgrp
    GROUP BY t1.id) Q
  WHERE osm_nodes_oh.id = Q.i;

enter image description here

  • So, have you solved this now? Your query looks correct, though from your explanation, you might need to add an ST_Intersects between the census polygons and points, as well as ST_DWithin, as you are only interested in points within 1000 that are also inside the same census polygon. – John Powell Mar 30 '16 at 13:54
  • I used st_coveredby instead of st_intersect to assign the blkgrp property which is the id from the census polygons. st_intersect would work just as well though. – Dennis Bauszus Mar 30 '16 at 14:35
  • I am almost there... the last step of course is to find the point with the highest count inside each of the census geometries. Howver, this is just a workaround. Ideally I would like to assign a value which takes the relative distance of points into account and not just counts the points that are within 1000m. – Dennis Bauszus Mar 30 '16 at 14:38
  • Are you aware of ST_ClusterWithin? I don't complety understand your question, but I am wondering if this might not help solve it. Requires Postgis 2.2. – John Powell Mar 30 '16 at 14:43
  • 1
    Can you self-answer this question and mark it as chosen? – Evan Carroll May 9 '17 at 15:21
1

I finally got there. Using a count was obviously nonsense for the densities. Luckily getting an inverse distance squared is simple with PostGIS.

UPDATE osm_nodes_oh
  SET d_grp = Q.d
  FROM (  
    SELECT
      t1.id i,
      round(sum((1000 - (t1.geom <#> t2.geom)) ^ 2) - 999999) d
      FROM osm_nodes_oh t1, osm_nodes_oh t2
      WHERE st_dwithin(t1.geom, t2.geom, 1000)
    AND t1.blkgrp = t2.blkgrp
      GROUP BY t1.id) Q
  WHERE osm_nodes_oh.id = Q.i;

Now I can extract the locations of the nodes with the highest inverse distance score from the nodes table.

SELECT DISTINCT ON (blkgrp)
  blkgrp,
  d_grp,
  geom
  INTO centres_oh
  FROM osm_nodes_oh
  ORDER BY blkgrp, d_grp DESC;

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.