This is a little more complex than the basics so I need some help.
I have a cte that returns nodes from a spatial database based on being within a certain distance. The nodes are all related as nodes on a network (stops on a tram line) and as such have their memberships defined as an array of values (1 or more).
From this list of 'reachable' nodes, I'd like to return only the closest 2 based on membership in each network_id.
with con as (
select
stops.id as stop_id,
stops.vertex_id,
stops.network_type,
stops.rel_id,
pois.id as poi_id,
pois.geom as poi_geom,
stops.geom as geom,
min(st_distance(stops.geom, pois.geom))/(1.3*60) as cost_m
from pois inner join stops on st_dwithin(stops.geom, pois.geom, 20*60/1.4)
where pois."name" = 'Esselunga' and stops.vertex_id is not null and 'tram' = any(network_type)
group by stop_id, vertex_id, network_type, rel_id, poi_id, poi_geom, stops.geom)
select
*
from con;
which results, in part, with:
| stop_id | vertex_id | network_type | rel_id | poi_id | poi_geom | geom | cost_m |
|---|---|---|---|---|---|---|---|
| 1,279,667,257 | 77 | {tram} | {912,391,920,038} | 1,384,317,060 | POINT (1024933.2459064544 5696278.830865822) | POINT (1024966.6640175906 5696839.282259776) | 7.1980361932 |
| 1,279,667,260 | 54 | {tram} | {912,392} | 1,384,317,060 | POINT (1024933.2459064544 5696278.830865822) | POINT (1024907.5644999285 5696872.79552085) | 7.6220460598 |
| 1,479,139,754 | 539 | {tram} | {929,848} | 1,384,317,060 | POINT (1024933.2459064544 5696278.830865822) | POINT (1024814.8464960468 5696489.456395822) | 3.0977270588 |
| 1,726,477,317 | 564 | {tram} | {929,849,661,028} | 1,384,317,060 | POINT (1024933.2459064544 5696278.830865822) | POINT (1024912.8967035374 5695543.442483473) | 9.4316650464 |
| 1,726,477,333 | 601 | {tram} | {661,028} | 1,384,317,060 | POINT (1024933.2459064544 5696278.830865822) | POINT (1025406.4984576639 5696208.824533349) | 6.1333642029 |
| 1,726,477,343 | 602 | {tram} | {661,028} | 1,384,317,060 | POINT (1024933.2459064544 5696278.830865822) | POINT (1024914.3772527649 5696138.056783953) | 1.8209356776 |
| 1,726,512,175 | 583 | {tram} | {661,000} | 1,384,317,060 | POINT (1024933.2459064544 5696278.830865822) | POINT (1025718.5158584084 5696266.686870917) | 10.0687672846 |
| 1,726,512,203 | 542 | {tram} | {661,000} | 1,384,317,060 | POINT (1024933.2459064544 5696278.830865822) | POINT (1024929.7059466472 5695426.267220704) | 10.9303973626 |
| 1,726,512,229 | 582 | {tram} | {661,000} | 1,384,317,060 | POINT (1024933.2459064544 5696278.830865822) | POINT (1025347.7996901686 5696196.188528069) | 5.4193718868 |
| 1,726,512,262 | 541 | {tram} | {661,000,929,848} | 1,384,317,060 | POINT (1024933.2459064544 5696278.830865822) | POINT (1024885.167018381 5696083.592349491) | 2.5778368624 |
| 1,730,563,196 | 561 | {tram} | {929,849} | 1,384,317,060 | POINT (1024933.2459064544 5696278.830865822) | POINT (1024871.0739708462 5696167.884575985) | 1.6304965103 |
| 1,730,563,209 | 540 | {tram} | {929,848} | 1,384,317,060 | POINT (1024933.2459064544 5696278.830865822) | POINT (1024283.986108352 5696837.377198968) | 10.9801707869 |
| 2,051,364,326 | 58 | {tram} | {912,392} | 1,384,317,060 | POINT (1024933.2459064544 5696278.830865822) | POINT (1025729.9261062149 5696333.534608023) | 10.2378986799 |
| 2,423,105,781 | 75 | {tram} | {920,038,912,391} | 1,384,317,060 | POINT (1024933.2459064544 5696278.830865822) | POINT (1025409.2257851884 5696548.384172874) | 7.0129005562 |
| 2,423,105,782 | 55 | {tram} | {912,392} | 1,384,317,060 | POINT (1024933.2459064544 5696278.830865822) | POINT (1025328.2297236873 5696596.009276302) | 6.4945023019 |
As you can see, there are overlaps in rel_id, and I want to return, for each poi_id, the lowest 2 costs.
I don't even know if it is possible to perform this or if it would be better to change the stops.rel_id when importing data into the database and perform a comparison with easier column types/organization.
