# Finding density centroids within polygons in PostGIS

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;
``````

• 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. Commented Mar 30, 2016 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. Commented Mar 30, 2016 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. Commented Mar 30, 2016 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. Commented Mar 30, 2016 at 14:43
• Can you self-answer this question and mark it as chosen? Commented May 9, 2017 at 15:21

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;
``````