13. الربط المكاني¶
الربط المكاني هو الخبز اليومي لقواعد البيانات المكانية. فهو يتيح لك دمج معلومات من جداول مختلفة باستخدام العلاقات المكانية مفتاحًا للربط. ويمكن التعبير عن كثير مما نعدّه "تحليل GIS القياسي" في صورة عمليات ربط مكاني.
في القسم السابق، استكشفنا العلاقات المكانية عبر عملية من خطوتين: استخرجنا أولًا نقطة محطة المترو 'Broad St'، ثم استخدمنا تلك النقطة لطرح أسئلة أخرى مثل "في أي حي تقع محطة 'Broad St'؟"
باستخدام الربط المكاني، يمكننا الإجابة عن السؤال في خطوة واحدة، فنسترجع معلومات عن محطة المترو والحي الذي يحتويها:
SELECT
subways.name AS subway_name,
neighborhoods.name AS neighborhood_name,
neighborhoods.boroname AS borough
FROM nyc_neighborhoods AS neighborhoods
JOIN nyc_subway_stations AS subways
ON ST_Contains(neighborhoods.geom, subways.geom)
WHERE subways.name = 'Broad St';
subway_name | neighborhood_name | borough
-------------+--------------------+-----------
Broad St | Financial District | Manhattan
كان بإمكاننا ربط كل محطة مترو بالحي الذي يحتويها، لكننا في هذه الحالة أردنا معلومات عن محطة واحدة فقط. يمكن استخدام أي دالة تعطي علاقة صح/خطأ بين جدولين لإجراء ربط مكاني، لكن أكثرها استخدامًا: ST_Intersects و ST_Contains و ST_DWithin.
13.1. الربط والتلخيص¶
يوفّر الجمع بين JOIN و GROUP BY نوع التحليل الذي يُجرى عادةً في نظام GIS.
على سبيل المثال: "ما عدد السكان والتركيبة العرقية لأحياء Manhattan؟" هنا لدينا سؤال يجمع بين معلومات السكان من التعداد وحدود الأحياء، مع قصر النتائج على حي Manhattan وحده.
SELECT
neighborhoods.name AS neighborhood_name,
Sum(census.popn_total) AS population,
100.0 * Sum(census.popn_white) / Sum(census.popn_total) AS white_pct,
100.0 * Sum(census.popn_black) / Sum(census.popn_total) AS black_pct
FROM nyc_neighborhoods AS neighborhoods
JOIN nyc_census_blocks AS census
ON ST_Intersects(neighborhoods.geom, census.geom)
WHERE neighborhoods.boroname = 'Manhattan'
GROUP BY neighborhoods.name
ORDER BY white_pct DESC;
neighborhood_name | population | white_pct | black_pct
---------------------+------------+-----------+-----------
Carnegie Hill | 18763 | 90.1 | 1.4
North Sutton Area | 22460 | 87.6 | 1.6
West Village | 26718 | 87.6 | 2.2
Upper East Side | 203741 | 85.0 | 2.7
Soho | 15436 | 84.6 | 2.2
Greenwich Village | 57224 | 82.0 | 2.4
Central Park | 46600 | 79.5 | 8.0
Tribeca | 20908 | 79.1 | 3.5
Gramercy | 104876 | 75.5 | 4.7
Murray Hill | 29655 | 75.0 | 2.5
Chelsea | 61340 | 74.8 | 6.4
Upper West Side | 214761 | 74.6 | 9.2
Midtown | 76840 | 72.6 | 5.2
Battery Park | 17153 | 71.8 | 3.4
Financial District | 34807 | 69.9 | 3.8
Clinton | 32201 | 65.3 | 7.9
East Village | 82266 | 63.3 | 8.8
Garment District | 10539 | 55.2 | 7.1
Morningside Heights | 42844 | 52.7 | 19.4
Little Italy | 12568 | 49.0 | 1.8
Yorkville | 58450 | 35.6 | 29.7
Inwood | 50047 | 35.2 | 16.8
Washington Heights | 169013 | 34.9 | 16.8
Lower East Side | 96156 | 33.5 | 9.1
East Harlem | 60576 | 26.4 | 40.4
Hamilton Heights | 67432 | 23.9 | 35.8
Chinatown | 16209 | 15.2 | 3.8
Harlem | 134955 | 15.1 | 67.1
ما الذي يحدث هنا؟ من الناحية المفاهيمية (إذ إن ترتيب التقييم الفعلي تحسّنه قاعدة البيانات داخليًا) يحدث ما يلي:
يُنشئ البند
JOINجدولًا افتراضيًا يتضمن أعمدة من جدولي الأحياء والتعداد.يرشّح البند
WHEREالجدول الافتراضي ليقتصر على الصفوف الواقعة في Manhattan.تُجمَّع الصفوف المتبقية حسب اسم الحي وتُمرَّر إلى الدالة التجميعية Sum() لجمع قيم السكان.
بعد قليل من الحساب والتنسيق (مثل
GROUP BYوORDER BY) على الأرقام النهائية، يُخرج استعلامنا النسب المئوية.
ملاحظة
يجمع البند JOIN بين عنصرين من عناصر FROM. نستخدم افتراضيًا INNER JOIN، لكن هناك أربعة أنواع أخرى من الربط. لمزيد من المعلومات، راجع تعريف join_type في توثيق PostgreSQL.
يمكننا أيضًا استخدام اختبارات المسافة مفتاحًا للربط، لإنشاء استعلامات تلخيصية من نوع "كل العناصر ضمن نصف قطر". لنستكشف الجغرافيا العرقية لمدينة New York باستخدام استعلامات المسافة.
أولًا، لنحصل على التركيبة العرقية الأساسية للمدينة.
SELECT
100.0 * Sum(popn_white) / Sum(popn_total) AS white_pct,
100.0 * Sum(popn_black) / Sum(popn_total) AS black_pct,
Sum(popn_total) AS popn_total
FROM nyc_census_blocks;
white_pct | black_pct | popn_total
------------------+------------------+------------
44.0039500762811 | 25.5465789002416 | 8175032
إذن، من بين 8 ملايين شخص في New York، سُجّل نحو 44% منهم على أنهم "بيض" و 26% على أنهم "سود".
غنّى Duke Ellington ذات مرة: "You / must take the A-train / To / go to Sugar Hill way up in Harlem." وكما رأينا سابقًا، تضم Harlem أعلى نسبة من السكان الأمريكيين من أصل أفريقي في Manhattan بفارق كبير (80.5%). فهل ينطبق الشيء نفسه على قطار A الذي غنّى عنه Duke؟
لاحظ أولًا أن محتويات الحقل routes في الجدول nyc_subway_stations هي ما يهمّنا للعثور على قطار A. والقيم الموجودة فيه معقّدة بعض الشيء.
SELECT DISTINCT routes FROM nyc_subway_stations;
A,C,G
4,5
D,F,N,Q
5
E,F
E,J,Z
R,W
ملاحظة
تحذف الكلمة المفتاحية DISTINCT الصفوف المكررة من النتيجة. ودون الكلمة المفتاحية DISTINCT، يعيد الاستعلام أعلاه 491 نتيجة بدلًا من 73.
إذن للعثور على قطار A، نريد أي صف في routes يحتوي على 'A'. يمكننا ذلك بعدة طرق، لكننا سنستفيد اليوم من أن strpos(routes,'A') تعيد عددًا غير صفري فقط إذا كان 'A' موجودًا في الحقل routes.
SELECT DISTINCT routes
FROM nyc_subway_stations AS subways
WHERE strpos(subways.routes,'A') > 0;
A,B,C
A,C
A
A,C,G
A,C,E,L
A,S
A,C,F
A,B,C,D
A,C,E
لنلخّص التركيبة العرقية ضمن مسافة 200 متر من خط قطار A.
SELECT
100.0 * Sum(popn_white) / Sum(popn_total) AS white_pct,
100.0 * Sum(popn_black) / Sum(popn_total) AS black_pct,
Sum(popn_total) AS popn_total
FROM nyc_census_blocks AS census
JOIN nyc_subway_stations AS subways
ON ST_DWithin(census.geom, subways.geom, 200)
WHERE strpos(subways.routes,'A') > 0;
white_pct | black_pct | popn_total
------------------+------------------+------------
45.5901255900202 | 22.0936235670937 | 189824
إذن لا تختلف التركيبة العرقية على امتداد قطار A اختلافًا جذريًا عن تركيبة مدينة New York ككل.
13.2. ربط متقدّم¶
رأينا في القسم الأخير أن قطار A لا يخدم سكانًا تختلف تركيبتهم العرقية كثيرًا عن بقية المدينة. فهل هناك قطارات تخدم سكانًا ذوي تركيبة عرقية مختلفة عن المتوسط؟
للإجابة عن هذا السؤال، سنضيف عملية ربط أخرى إلى استعلامنا، كي نتمكن من حساب تركيبة عدة خطوط مترو في آن واحد. ولذلك سنحتاج إلى إنشاء جدول جديد يسرد جميع الخطوط التي نريد تلخيصها.
CREATE TABLE subway_lines ( route char(1) );
INSERT INTO subway_lines (route) VALUES
('A'),('B'),('C'),('D'),('E'),('F'),('G'),
('J'),('L'),('M'),('N'),('Q'),('R'),('S'),
('Z'),('1'),('2'),('3'),('4'),('5'),('6'),
('7');
يمكننا الآن ربط جدول خطوط المترو باستعلامنا الأصلي.
SELECT
lines.route,
100.0 * Sum(popn_white) / Sum(popn_total) AS white_pct,
100.0 * Sum(popn_black) / Sum(popn_total) AS black_pct,
Sum(popn_total) AS popn_total
FROM nyc_census_blocks AS census
JOIN nyc_subway_stations AS subways
ON ST_DWithin(census.geom, subways.geom, 200)
JOIN subway_lines AS lines
ON strpos(subways.routes, lines.route) > 0
GROUP BY lines.route
ORDER BY black_pct DESC;
route | white_pct | black_pct | popn_total
-------+-----------+-----------+------------
S | 39.8 | 46.5 | 33301
3 | 42.7 | 42.1 | 223047
5 | 33.8 | 41.4 | 218919
2 | 39.3 | 38.4 | 291661
C | 46.9 | 30.6 | 224411
4 | 37.6 | 27.4 | 174998
B | 40.0 | 26.9 | 256583
A | 45.6 | 22.1 | 189824
J | 37.6 | 21.6 | 132861
Q | 56.9 | 20.6 | 127112
Z | 38.4 | 20.2 | 87131
D | 39.5 | 19.4 | 234931
L | 57.6 | 16.8 | 110118
G | 49.6 | 16.1 | 135012
6 | 52.3 | 15.7 | 260240
1 | 59.1 | 11.3 | 327742
F | 60.9 | 7.5 | 229439
M | 56.5 | 6.4 | 174196
E | 66.8 | 4.7 | 90958
R | 58.5 | 4.0 | 196999
N | 59.7 | 3.5 | 147792
7 | 35.7 | 3.5 | 102401
كما في السابق، تُنشئ عمليات الربط جدولًا افتراضيًا لكل التركيبات الممكنة ضمن قيود JOIN ON، ثم تُمرَّر تلك الصفوف إلى عملية تلخيص GROUP. ويكمن السحر المكاني في الدالة ST_DWithin، التي تضمن ألا تدخل في الحساب إلا كتل التعداد القريبة من محطات المترو المعنية.
13.3. قائمة الدوال¶
ST_Contains(geometry A, geometry B): تعيد true إذا وفقط إذا لم تقع أي نقطة من B خارج A، ووقعت نقطة واحدة على الأقل من داخل B في داخل A.
ST_DWithin(geometry A, geometry B, radius): تعيد true إذا كان الشكلان الهندسيان ضمن المسافة المحددة أحدهما من الآخر.
ST_Intersects(geometry A, geometry B): تعيد TRUE إذا كانت الأشكال الهندسية/الجغرافية "متقاطعة مكانيًا" (تشترك في أي جزء من الحيّز)، و FALSE إذا لم تكن كذلك (أي منفصلة).
round(v numeric, s integer): دالة رياضية في PostgreSQL تقرّب القيمة إلى s من المنازل العشرية
strpos(string, substring): دالة نصية في PostgreSQL تعيد عددًا صحيحًا يمثّل موقع سلسلة فرعية محددة.
sum(expression): دالة تجميعية في PostgreSQL تعيد مجموع السجلات في مجموعة من السجلات.