105 lines
4 KiB
SQL
105 lines
4 KiB
SQL
-- ساخت گراف مسیریابی از planet_osm_line برای استفاده در API ماتریس (pgRouting).
|
||
--
|
||
-- پیش‌نیاز:
|
||
-- 1) PostGIS و pgRouting روی سرور نصب باشند.
|
||
-- نصب pgRouting (نسخه را با نسخه PostgreSQL خود هماهنگ کنید):
|
||
-- Ubuntu/Debian: sudo apt install postgresql-16-pgrouting
|
||
-- یا برای نسخه 15: sudo apt install postgresql-15-pgrouting
|
||
-- 2) جداول OSM (planet_osm_line) در دیتابیس gis وجود داشته باشند.
|
||
--
|
||
-- اجرا با کاربر postgres (نه root):
|
||
-- sudo -u postgres psql -d gis -f scripts/setup-routing-graph.sql
|
||
--
|
||
-- پس از به‌روزرسانی OSM این اسکریپت را دوباره اجرا کنید.
|
||
|
||
\set ON_ERROR_STOP 1
|
||
|
||
-- 1) فعال‌سازی افزونه‌ها
|
||
CREATE EXTENSION IF NOT EXISTS postgis;
|
||
CREATE EXTENSION IF NOT EXISTS pgrouting;
|
||
|
||
-- 2) schema و جدول یال‌ها
|
||
CREATE SCHEMA IF NOT EXISTS routing;
|
||
|
||
DROP TABLE IF EXISTS routing.ways CASCADE;
|
||
CREATE TABLE routing.ways (
|
||
id BIGINT PRIMARY KEY,
|
||
the_geom GEOMETRY(LineString, 4326),
|
||
cost DOUBLE PRECISION,
|
||
reverse_cost DOUBLE PRECISION
|
||
);
|
||
|
||
-- 3) پر کردن از جاده‌های OSM (فقط جاده‌های قابل عبور ماشین + پشتیبانی یک‌طرفه).
|
||
-- way ممکن است 4326، 3857 یا SRID=0 باشد؛ برای طول متری به WGS84 تبدیل می‌کنیم.
|
||
-- ستون oneway در صورت نبود اضافه می‌شود (مقادیر از تگ OSM در import پر می‌شوند).
|
||
DO $$
|
||
BEGIN
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM information_schema.columns
|
||
WHERE table_schema = 'public' AND table_name = 'planet_osm_line' AND column_name = 'oneway'
|
||
) THEN
|
||
ALTER TABLE planet_osm_line ADD COLUMN oneway text;
|
||
END IF;
|
||
END $$;
|
||
|
||
WITH prep AS (
|
||
SELECT
|
||
osm_id,
|
||
ST_Transform(ST_SetSRID(way, CASE WHEN ST_SRID(way) = 0 THEN 4326 ELSE ST_SRID(way) END), 4326) AS geom,
|
||
ST_Length(
|
||
ST_Transform(ST_SetSRID(way, CASE WHEN ST_SRID(way) = 0 THEN 4326 ELSE ST_SRID(way) END), 4326)::geography
|
||
) AS len,
|
||
LOWER(TRIM(COALESCE(oneway, ''))) AS ow
|
||
FROM planet_osm_line
|
||
WHERE way IS NOT NULL
|
||
AND highway IN (
|
||
'motorway', 'motorway_link', 'trunk', 'trunk_link',
|
||
'primary', 'primary_link', 'secondary', 'secondary_link',
|
||
'tertiary', 'tertiary_link', 'unclassified', 'residential',
|
||
'living_street', 'service', 'road', 'construction'
|
||
)
|
||
)
|
||
INSERT INTO routing.ways (id, the_geom, cost, reverse_cost)
|
||
SELECT
|
||
osm_id,
|
||
geom,
|
||
CASE
|
||
WHEN ow IN ('-1', 'reverse') THEN -1
|
||
ELSE len
|
||
END,
|
||
CASE
|
||
WHEN ow IN ('yes', '1', 'true') THEN -1
|
||
WHEN ow IN ('-1', 'reverse') THEN len
|
||
ELSE len
|
||
END
|
||
FROM prep
|
||
WHERE len > 0
|
||
ON CONFLICT (id) DO NOTHING;
|
||
|
||
-- 4) در pgRouting 3.x ستون‌های source و target باید از قبل وجود داشته باشند؛ سپس pgr_createTopology آن‌ها را پر می‌کند.
|
||
ALTER TABLE routing.ways ADD COLUMN IF NOT EXISTS source BIGINT;
|
||
ALTER TABLE routing.ways ADD COLUMN IF NOT EXISTS target BIGINT;
|
||
|
||
-- تلرانس ۰٫۰۰۰۰۵ درجه (~۵٫۵ متر) برای اتصال بهتر انتهای خطوط در داده OSM
|
||
SELECT pgr_createTopology(
|
||
'routing.ways',
|
||
0.00005,
|
||
'the_geom',
|
||
'id'
|
||
);
|
||
|
||
-- 5) اندکس برای کوئری‌های snap و مسیریابی
|
||
CREATE INDEX IF NOT EXISTS idx_routing_ways_source ON routing.ways(source);
|
||
CREATE INDEX IF NOT EXISTS idx_routing_ways_target ON routing.ways(target);
|
||
CREATE INDEX IF NOT EXISTS idx_routing_ways_vertices_the_geom ON routing.ways_vertices_pgr USING GIST(the_geom);
|
||
|
||
-- 6) دسترسی خواندن برای کاربر جستجو (در صورت وجود)
|
||
DO $$
|
||
BEGIN
|
||
IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'search_user') THEN
|
||
GRANT USAGE ON SCHEMA routing TO search_user;
|
||
GRANT SELECT ON ALL TABLES IN SCHEMA routing TO search_user;
|
||
ALTER DEFAULT PRIVILEGES IN SCHEMA routing GRANT SELECT ON TABLES TO search_user;
|
||
END IF;
|
||
END
|
||
$$;
|