-- Find 10 random Rite-Aid locations
with a as (
select
*
from
nyc_pharmacies
where
name ilike '%rite%'
limit
10
)
select
-- For each pharmacy we will find 10 random
-- buildings within 800 meters
a.name,
a.id as pharm_id,
a.geom as pharm_geom,
b.*
from
a
cross join lateral (
select
bin as building_id,
geom
from
nyc_building_footprints
where
st_dwithin(
st_centroid(geom) :: geography,
st_centroid(a.geom) :: geography,
800
)
order by
random()
limit
10
) b
create table rite_aid_tsp as with a as (
select
distinct b.pharm_id as id,
b.geom,
s.source
from
rite_aid_odm b
cross join lateral(
SELECT
source
FROM
ways
ORDER BY
the_geom <-> b.pharm_geom
limit
1
) s
), b as (
select
b.pharm_id as id,
s.source
from
rite_aid_odm b
cross join lateral(
SELECT
source
FROM
ways
ORDER BY
the_geom <-> b.geom
limit
1
) s
), c as (
select
a.id,
a.source,
-- Constructs an array with the way ID of the pharmacy as the first item
array_prepend(a.source, array_agg(distinct b.source)) as destinations
from
a
join b using (id)
-- Will return one row per pharmacy ID
group by
a.id,
a.source
)
select
*
from
c
create table rite_aid_tsp_odm as
-- Select all the values from the table created in the last step
select
a.*,
r.*
from
rite_aid_tsp a
cross join lateral (
select
*
from
-- This will create the cost matrix for each pharmacy
pgr_dijkstracostmatrix(
'select
gid as id,
source,
target,
cost_s * penalty as cost,
reverse_cost_s * penalty as reverse_cost
from ways
join configuration
using (tag_id)',
-- We can use the array to calculate the distances
-- between all locations in the array
(
select
destinations
from
rite_aid_tsp
where
id = a.id
),
directed := false
)
) r
create table solved_tsp as
select
s.id,
s.source,
tsp.*,
lead(tsp.node, 1) over (
partition by s.source
order by
tsp.seq
) as next_node
from
rite_aid_tsp s
cross join lateral (
select
*
from
pgr_TSP(
$$
select
*
from
rite_aid_tsp_odm
where
source = $$ || s.source || $$$$
)
) tsp
create table final_tsp_test as
with a as (
select
s.id,
s.source,
s.seq,
s.node,
s.next_node,
di.*,
ways.the_geom,
st_length(st_transform(ways.the_geom, 3857)) as length
from
solved_tsp s,
-- We cross join this to each row
pgr_dijkstra(
'select
gid as id,
source,
target,
cost_s,
cost_s * penalty as cost,
reverse_cost_s * penalty as reverse_cost
from ways
join configuration
using (tag_id)',
-- We create a route between the current node and the next node
s.node,
s.next_node,
true
) as di
join ways on di.node = ways.source
)
select
-- Union the geometries and find the sum of the cost and length for each route
st_union(st_transform(the_geom, 4326)) as route,
source,
sum(cost) as cost,
sum(length) as length
from
a
group by
source