We have a land protocol where we receive a fishnet of 1x1 km cells. Some cells are randomly choosed. We need to put 4 points in each cell and these points have to be on a road too. The minimum distance between points have to be 500m for every points of every cells IF POSSIBLE or if it is not, we want the maximum possible distance.
In a first try we divided every cell in four 500x500 m cells with ST_CreateFishnet then we put points at the centroid of the sub-cells then on the closest road (ST_ClosestPoint). We get some good results but in the example below you can see than point 5 is too close from 6 and could be moved on the left road.
WITH r1 AS ( -- only sub-cells which intersects random cells SELECT id_maille, ROW_NUMBER() OVER() AS id_grille, fishnet_500.geomgrille FROM fishnet_500 JOIN t_mailles ON ST_Intersects(ST_Buffer(t_mailles.geom,-200), fishnet_500.geomgrille) -- buffer < 0 to not select neightbours ) , r2 AS ( -- cut roads in every cells SELECT id_maille, id_grille, ST_Intersection((ST_Dump(roads.geom)).geom, r1.geomgrille) as geomroute FROM roads JOIN r1 ON ST_Intersects(roads.geom, r1.geomgrille) ) -- select point on each road the closest to cell centroid SELECT r2.id_maille, r2.id_grille, ST_ClosestPoint(ST_Union(r2.geomroute),ST_Centroid(r1.geomgrille)) as geomipa FROM r2 JOIN r1 ON r2.id_grille = r1.id_grille GROUP BY r2.id_maille, r2.id_grille, r1.geomgrille ORDER BY r2.id_maille, r2.id_grille
If you want to give it a try I put the 3 layers (fishnet with random cells, sub-fisnet and roads) in an archive that you can find here.
I guess we can't avoid a recursive algorithm that tries many possibilities but i'm not sure.