16.4. الدرس: الاستعلامات المكانية

لا تختلف الاستعلامات المكانية عن غيرها من استعلامات قواعد البيانات. يمكنك استخدام عمود الشكل الهندسي مثل أي عمود آخر في قاعدة البيانات. ومع تثبيت PostGIS في قاعدة بياناتنا، أصبحت لدينا دوال إضافية للاستعلام عن قاعدة البيانات.

هدف هذا الدرس: رؤية كيف تُطبَّق الدوال المكانية بطريقة مشابهة للدوال غير المكانية "العادية".

16.4.1. العوامل المكانية

عندما تريد معرفة النقاط الواقعة ضمن مسافة درجتين من نقطة (X,Y)، يمكنك فعل ذلك باستخدام:

select *
from people
where st_distance(geom,'SRID=4326;POINT(33 -34)') < 2;

النتيجة:

 id |     name     | house_no | street_id |   phone_no    |   geom
----+--------------+----------+-----------+---------------+---------------
  6 | Fault Towers |       34 |         3 | 072 812 31 28 | 01010008040C0
(1 row)

ملاحظة

اقتُطعت قيمة geom أعلاه لتوفير المساحة في هذه الصفحة. إذا أردت رؤية النقطة بإحداثيات مقروءة للإنسان، فجرّب شيئًا مشابهًا لما فعلته في القسم "View a point as WKT" أعلاه.

كيف نعرف أن الاستعلام أعلاه يُرجع جميع النقاط ضمن 2 درجة؟ لماذا ليس 2 متر؟ أو أي وحدة أخرى في الواقع؟

الإجابة

الوحدات المستخدمة في الاستعلام المثال هي الدرجات، لأن نظام الإحداثيات المرجعي (CRS) الذي تستخدمه الطبقة هو WGS 84. وهو نظام إحداثيات جغرافي، أي أن وحداته بالدرجات. أما نظام الإحداثيات المسقط، مثل إسقاطات UTM، فوحداته بالأمتار.

تذكّر أنك عند كتابة استعلام تحتاج إلى معرفة الوحدات التي يستخدمها نظام الإحداثيات المرجعي للطبقة. سيمكّنك ذلك من كتابة استعلام يُرجع النتائج التي تتوقعها.

16.4.2. الفهارس المكانية

يمكننا أيضًا تعريف فهارس مكانية. فالفهرس المكاني يجعل استعلاماتك المكانية أسرع بكثير. لإنشاء فهرس مكاني على عمود الشكل الهندسي استخدم:

CREATE INDEX people_geo_idx
  ON people
  USING gist
  (geom);

\d people

النتيجة:

Table "public.people"
   Column   |         Type          |                Modifiers
 -----------+-----------------------+----------------------------------------
  id        | integer               | not null default
            |                       | nextval('people_id_seq'::regclass)
  name      | character varying(50) |
  house_no  | integer               | not null
  street_id | integer               | not null
  phone_no  | character varying     |
  geom      | geometry              |
Indexes:
  "people_pkey" PRIMARY KEY, btree (id)
  "people_geo_idx" gist (geom)  <-- new spatial key added
  "people_name_idx" btree (name)
Check constraints:
  "people_geom_point_chk" CHECK (st_geometrytype(geom) = 'ST_Point'::text
  OR geom IS NULL)
Foreign-key constraints:
  "people_street_id_fkey" FOREIGN KEY (street_id) REFERENCES streets(id)

16.4.3. جرّب بنفسك: ★★☆

عدّل جدول المدن بحيث يكون عمود الشكل الهندسي فيه مفهرسًا مكانيًا.

الإجابة

CREATE INDEX cities_geo_idx
  ON cities
  USING gist (geom);

16.4.4. عرض توضيحي للدوال المكانية في PostGIS

لعرض الدوال المكانية في PostGIS، سننشئ قاعدة بيانات جديدة تحتوي على بعض البيانات (الوهمية).

للبدء، أنشئ قاعدة بيانات جديدة (اخرج من صدفة psql أولًا):

createdb postgis_demo

تذكّر تثبيت امتدادات postgis:

psql -d postgis_demo -c "CREATE EXTENSION postgis;"

بعد ذلك، استورد البيانات المتوفرة في المجلد exercise_data/postgis/. ارجع إلى الدرس السابق للاطلاع على التعليمات، لكن تذكّر أنك ستحتاج إلى إنشاء اتصال PostGIS جديد بقاعدة البيانات الجديدة. يمكنك الاستيراد من الطرفية أو عبر DB Manager. استورد الملفات إلى جداول قاعدة البيانات التالية:

• points.shp إلى building

• lines.shp إلى road

• polygons.shp إلى region

حمّل طبقات قاعدة البيانات الثلاث هذه في QGIS عبر نافذة الحوار Add PostGIS Layers كالمعتاد. عند فتح جداول بياناتها الوصفية، ستلاحظ أنها تحتوي على الحقل id والحقل gid الذي أنشأه استيراد PostGIS.

بعد استيراد الجداول، يمكننا استخدام PostGIS للاستعلام عن البيانات. عد إلى الطرفية (سطر الأوامر) وادخل إلى موجّه psql بتشغيل:

psql postgis_demo

سنعرض بعض عبارات select هذه بإنشاء طرق عرض (views) منها، لتتمكن من فتحها في QGIS ورؤية النتائج.

التحديد حسب الموقع

احصل على جميع المباني في منطقة KwaZulu:

SELECT a.id, a.name, st_astext(a.geom) as point
  FROM building a, region b
    WHERE st_within(a.geom, b.geom)
    AND b.name = 'KwaZulu';

النتيجة:

 id | name |                  point
----+------+------------------------------------------
 30 | York | POINT(1622345.23785063 6940490.65844485)
 33 | York | POINT(1622495.65620524 6940403.87862489)
 35 | York | POINT(1622403.09106394 6940212.96302097)
 36 | York | POINT(1622287.38463732 6940357.59605424)
 40 | York | POINT(1621888.19746548 6940508.01440885)
(5 rows)

أو إذا أنشأنا منه طريقة عرض (view):

CREATE VIEW vw_select_location AS
  SELECT a.gid, a.name, a.geom
    FROM building a, region b
      WHERE st_within(a.geom, b.geom)
      AND b.name = 'KwaZulu';

أضف طريقة العرض كطبقة واعرضها في QGIS:

تحديد المناطق المجاورة

اعرض قائمة بأسماء جميع المناطق المجاورة لمنطقة Hokkaido:

SELECT b.name
  FROM region a, region b
    WHERE st_touches(a.geom, b.geom)
    AND a.name = 'Hokkaido';

النتيجة:

    name
--------------
 Missouri
 Saskatchewan
 Wales
(3 rows)

كطريقة عرض (view):

CREATE VIEW vw_regions_adjoining_hokkaido AS
  SELECT b.gid, b.name, b.geom
    FROM region a, region b
      WHERE st_touches(a.geom, b.geom)
      AND a.name = 'Hokkaido';

في QGIS:

لاحظ المنطقة المفقودة (Queensland). قد يعود ذلك إلى خطأ طوبولوجي. ويمكن لمثل هذه الشوائب أن تنبهنا إلى مشكلات محتملة في البيانات. ولحل هذا اللغز دون الانشغال بالحالات الشاذة التي قد تحتويها البيانات، يمكننا بدلًا من ذلك استخدام تقاطع مع نطاق عازل:

CREATE VIEW vw_hokkaido_buffer AS
  SELECT gid, ST_BUFFER(geom, 100) as geom
    FROM region
      WHERE name = 'Hokkaido';

ينشئ هذا نطاقًا عازلًا بمقدار 100 متر حول منطقة Hokkaido.

المنطقة الأغمق هي النطاق العازل:

التحديد باستخدام النطاق العازل:

CREATE VIEW vw_hokkaido_buffer_select AS
  SELECT b.gid, b.name, b.geom
    FROM
    (
      SELECT * FROM
        vw_hokkaido_buffer
    ) a,
    region b
    WHERE ST_INTERSECTS(a.geom, b.geom)
    AND b.name != 'Hokkaido';

في هذا الاستعلام، تُستخدم طريقة عرض النطاق العازل الأصلية كما يُستخدم أي جدول آخر. إذ يُعطى الاسم المستعار a، ويُستخدم حقل الشكل الهندسي الخاص به a.geom لتحديد أي مضلع في الجدول region (الاسم المستعار b) يتقاطع معه. لكن Hokkaido نفسها مستبعدة من عبارة select هذه، لأننا لا نريدها؛ نريد فقط المناطق المجاورة لها.

في QGIS:

يمكن أيضًا تحديد جميع الكائنات ضمن مسافة معينة، دون الخطوة الإضافية المتمثلة في إنشاء نطاق عازل:

CREATE VIEW vw_hokkaido_distance_select AS
  SELECT b.gid, b.name, b.geom
    FROM region a, region b
      WHERE ST_DISTANCE (a.geom, b.geom) < 100
      AND a.name = 'Hokkaido'
      AND b.name != 'Hokkaido';

يحقق هذا النتيجة نفسها، دون الحاجة إلى خطوة النطاق العازل الوسيطة:

تحديد القيم الفريدة

اعرض قائمة بأسماء البلدات الفريدة لجميع المباني في منطقة Queensland:

SELECT DISTINCT a.name
  FROM building a, region b
    WHERE st_within(a.geom, b.geom)
    AND b.name = 'Queensland';

النتيجة:

  name
---------
 Beijing
 Berlin
 Atlanta
(3 rows)

أمثلة إضافية ...

CREATE VIEW vw_shortestline AS
  SELECT b.gid AS gid,
        ST_ASTEXT(ST_SHORTESTLINE(a.geom, b.geom)) as text,
        ST_SHORTESTLINE(a.geom, b.geom) AS geom
    FROM road a, building b
      WHERE a.id=5 AND b.id=22;

CREATE VIEW vw_longestline AS
  SELECT b.gid AS gid,
         ST_ASTEXT(ST_LONGESTLINE(a.geom, b.geom)) as text,
         ST_LONGESTLINE(a.geom, b.geom) AS geom
    FROM road a, building b
      WHERE a.id=5 AND b.id=22;

CREATE VIEW vw_road_centroid AS
  SELECT a.gid as gid, ST_CENTROID(a.geom) as geom
    FROM road a
      WHERE a.id = 1;

CREATE VIEW vw_region_centroid AS
  SELECT a.gid as gid, ST_CENTROID(a.geom) as geom
    FROM region a
      WHERE a.name = 'Saskatchewan';

SELECT ST_PERIMETER(a.geom)
  FROM region a
    WHERE a.name='Queensland';

SELECT ST_AREA(a.geom)
  FROM region a
    WHERE a.name='Queensland';

CREATE VIEW vw_simplify AS
  SELECT gid, ST_Simplify(geom, 20) AS geom
    FROM road;

CREATE VIEW vw_simplify_more AS
  SELECT gid, ST_Simplify(geom, 50) AS geom
    FROM road;

CREATE VIEW vw_convex_hull AS
  SELECT
    ROW_NUMBER() over (order by a.name) as id,
    a.name as town,
    ST_CONVEXHULL(ST_COLLECT(a.geom)) AS geom
    FROM building a
    GROUP BY a.name;

16.4.5. الخلاصة

رأيت كيف تستعلم عن الكائنات المكانية باستخدام دوال قاعدة البيانات الجديدة من PostGIS.

16.4.6. ما التالي؟

سنتناول فيما يلي بنى الأشكال الهندسية الأكثر تعقيدًا وكيفية إنشائها باستخدام PostGIS.