# PostGIS - Find the "median" distance between two group of geometries depending on polygon area

I want to evaluate land distribution and concentration regarding to road network. Specificly, I'm trying to calculate the median distance between a shape of roads (multilinestring) and a shape of polygons (polygon) which will "split" the total polygon area in two. That is more or less a geographic median regarding a set of references geometries.

As I absolutly have no idea how to proceed, I tried some alternative approach. I first calculate the nearest neighbor between each polygon centroid and road network and the minimal distance between those two. Then I calculate the median of those distances.

``````WITH nearest_distances AS
(SELECT
ORDER BY
)
SELECT
percentile_const(0.5) WITHIN GROUP (ORDER BY nearest_distances.distance)
FROM nearest_distances;
``````

I a second time, I cross-check my median result with a query giving the area of polygons covered by a buffer on road network set on the result of the median.

``````SELECT
``````

This is totally experimental as I needed quick estimation but I wonder if there any cleaner way to perform this. Idealy, I would split my polygons along a median line dividing polygon surface in two parts regarding the road network.

I see 3 ways of doing it, but no simple way:

First way: you can try to do a dichotomous research by modifying the buffer size (your last request) until you got an area close enough to half of your total area size. It's easy, but it can be really long if you have a lot of polygons and you want to be precise.

Second way: you need to first cut your polygons to have "atomic" entities, a.k.a. polygons small enough to not be cutted, the size depending of the wanted precision. You can for example rasterize (ST_AsRaster, using an integer as ID for your polygons, each pixel having the value of the ID that correspond to the polygon) and transform the raster back into geom (ST_PixelAsPolygons). Then you can select the distance to have the wanted number of pixels inside your buffer (half of the pixels).

Third way: (same as second but less clean) ST_Segmentize to add a lot of points to your polygons, then ST_Subdivide to cut your polygons in really small parts (it will depends of the parameters to the functions). You can then considers your small polygons like the pixels in the second way, but you have the corresponding area). I'm not entirely sure if this works. It's less clean because it should be harder to control the size and shapes of your polygons, and they will not be homogeneous.

• Generally speaking, you can also look into how to cut polygons by a grid to have a mix between 2 and 3, like for example gis.stackexchange.com/questions/16374/… Commented Nov 25, 2019 at 17:28
• I thought about using rasters, wich seems the better way. But as I'm not confortable working with it... Anyway, more than "shaping" the shape, the core question is about the function to calculate the distance regarding to a set of geometries, even with number of pixels/minipolygons. Commented Nov 26, 2019 at 11:32
• This answer is for having your polygons cut in 2 equivalent surfaces. If you don't care about the shape of your geometries, and you want to consider your polygon as a whole(atomic), you can simply try to compute the weighted median of your polygons, using st_distance(line, st_centroid(poly)) as your value and st_area(poly) as your weight. Commented Nov 27, 2019 at 18:32
• I don't think it exists in postgresql though, you would have to use plsql with a for loop. Commented Nov 27, 2019 at 18:35
• Well that is a good start but as it go further than SQL I guess I'm not competent enought to perform this. I'll let future generation work on it. Commented Nov 29, 2019 at 11:25