You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
42 lines
1.7 KiB
SQL
42 lines
1.7 KiB
SQL
-- Migration 000018: Place towns on an approximate Archimedean spiral (not collinear).
|
|
-- Order by level_min is unchanged — ring roads still follow progression Willowdale → … → Starfall → wrap.
|
|
-- Waypoints regenerate at server startup from town centers (see road_graph / 000016).
|
|
|
|
UPDATE towns SET world_x = 2620, world_y = 800 WHERE name = 'Willowdale';
|
|
UPDATE towns SET world_x = 2926, world_y = 1058 WHERE name = 'Thornwatch';
|
|
UPDATE towns SET world_x = 2899, world_y = 1584 WHERE name = 'Ashengard';
|
|
UPDATE towns SET world_x = 2399, world_y = 2056 WHERE name = 'Redcliff';
|
|
UPDATE towns SET world_x = 1535, world_y = 2126 WHERE name = 'Boghollow';
|
|
UPDATE towns SET world_x = 633, world_y = 1571 WHERE name = 'Cinderkeep';
|
|
UPDATE towns SET world_x = 131, world_y = 660 WHERE name = 'Starfall';
|
|
|
|
-- Keep road_waypoints (if populated by 000017) aligned with new town centers.
|
|
DELETE FROM road_waypoints;
|
|
|
|
INSERT INTO road_waypoints (road_id, seq, x, y)
|
|
SELECT
|
|
r.id,
|
|
gs.seq,
|
|
CASE
|
|
WHEN gs.seq = 0 THEN f.world_x
|
|
WHEN gs.seq = seg.nseg THEN t.world_x
|
|
ELSE f.world_x + (t.world_x - f.world_x) * (gs.seq::double precision / seg.nseg::double precision)
|
|
END,
|
|
CASE
|
|
WHEN gs.seq = 0 THEN f.world_y
|
|
WHEN gs.seq = seg.nseg THEN t.world_y
|
|
ELSE f.world_y + (t.world_y - f.world_y) * (gs.seq::double precision / seg.nseg::double precision)
|
|
END
|
|
FROM roads r
|
|
INNER JOIN towns f ON f.id = r.from_town_id
|
|
INNER JOIN towns t ON t.id = r.to_town_id
|
|
CROSS JOIN LATERAL (
|
|
SELECT GREATEST(
|
|
1,
|
|
FLOOR(
|
|
SQRT(POWER(t.world_x - f.world_x, 2) + POWER(t.world_y - f.world_y, 2)) / 20.0
|
|
)::integer
|
|
) AS nseg
|
|
) seg
|
|
CROSS JOIN LATERAL generate_series(0, seg.nseg) AS gs(seq);
|