# How to calculate multiple distances between set of points and vector grid in PostGIS?

My set contains the following input data: A set of points and a hexagonal (vector) grid.

For each cell in the grid, I want to calculate the driving-distance to each point. I thought this would be a nice approach for calculation in a pgRouting enabled PostGIS database. At the end, I'd like to have the information stored in the attribute table of the Grid!

Unfortunately I'm not that much into PostGIS so I'm not sure about how to implement the algorithm correctly. What I did so far:

1. I have a street network that I loaded into PostGIS. I used pgr to create a topology. It seems to work!

[Edit 1]

I managed to simplify this a little. I am now using the heaxagon's centroids as points. I calculated the nearest network node for each centroid and wrote the source ID into a new table containing all centroids AND the source ID of the closest node from the network.

I did the same for the blue points so I have two tables to work with. I now want to route from each centroid to each blue node. Since I have very little understanding of PostGIS and Loops I thought someone could help me with the final step. The loop is meant to SELECT a centroid (source id) and route to all the blue point's source id. Then it should write the cost into the respective columnn. So It should look somewhat like this:

``````HEAXAGON ID | HEX SOURCE ID | COST A | COST B | COST C | COST D | ... | COST J
1           | 2341          | 250    | 600    | 564    | 993    | ... | 1200
2           | 6521          | 260    | 756    | 604    | 870    | ... | 1190
...         | ...           | ...    | ...    | ...    | ...    | ... | ...
4331        | 63525         | 1500   | 1874   | 120    | 451    | ... | 145
``````

I have 10 Points and 4331 Hexagons, so this would need like 43310 Dijsktra Operations. I hope you can help me with an algorithm that solves this since I'm completely desperate right now!

• gis.stackexchange.com/questions/74564/… could that be useful in your case ? – simplexio Dec 18 '15 at 12:42
• Hey, I tried to use your algo, but it didnt seem to work for me. Probably i got some of the relations wrong! Maybe you can have a look at my "Edit". Thanks! – Erik Dec 21 '15 at 11:06
• so let see if i understand this right. you have hexa, road and target tables ? and you have created topology for roads. – simplexio Dec 28 '15 at 8:55