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;