14. تمارين الربط المكاني

إليك تذكيرًا ببعض الدوال التي رأيناها. تلميح: ستكون مفيدة في التمارين!

• sum(expression): دالة تجميعية تعيد المجموع لمجموعة من السجلات

• count(expression): دالة تجميعية تعيد حجم مجموعة من السجلات

• ST_Area(geometry) تعيد مساحة المضلعات

• ST_AsText(geometry) تعيد WKT بصيغة text

• ST_Contains(geometry A, geometry B) تعيد true إذا كان الشكل الهندسي A يحتوي الشكل الهندسي B

• ST_Distance(geometry A, geometry B) تعيد أقصر مسافة بين الشكل الهندسي A والشكل الهندسي B

• ST_DWithin(geometry A, geometry B, radius) تعيد true إذا كانت المسافة بين الشكل الهندسي A والشكل الهندسي B تساوي radius أو أقل

• ST_GeomFromText(text) تعيد geometry

• ST_Intersects(geometry A, geometry B) تعيد true إذا كان الشكل الهندسي A يتقاطع مع الشكل الهندسي B

• ST_Length(linestring) تعيد طول الخط

• ST_Touches(geometry A, geometry B) تعيد true إذا كانت حدود الشكل الهندسي A تلامس الشكل الهندسي B

• ST_Within(geometry A, geometry B) تعيد true إذا كان الشكل الهندسي A يقع داخل الشكل الهندسي B

وتذكّر أيضًا الجداول المتاحة لدينا:

• nyc_census_blocks

  • name، popn_total، boroname، geom

• nyc_streets

  • name، type، geom

• nyc_subway_stations

  • name، routes، geom

• nyc_neighborhoods

  • name، boroname، geom

14.1. التمارين

• ما محطة المترو الموجودة في 'Little Italy'؟ وعلى أي خط مترو تقع؟

  SELECT s.name, s.routes
  FROM nyc_subway_stations AS s
  JOIN nyc_neighborhoods AS n
  ON ST_Contains(n.geom, s.geom)
  WHERE n.name = 'Little Italy';
  

     name    | routes
  -----------+--------
   Spring St | 6
  

• ما جميع الأحياء التي يخدمها القطار 6؟ (تلميح: يحتوي العمود routes في الجدول nyc_subway_stations على قيم مثل 'B,D,6,V' و 'C,6')

  SELECT DISTINCT n.name, n.boroname
  FROM nyc_subway_stations AS s
  JOIN nyc_neighborhoods AS n
  ON ST_Contains(n.geom, s.geom)
  WHERE strpos(s.routes,'6') > 0;
  

          name        | boroname
  --------------------+-----------
   Midtown            | Manhattan
   Hunts Point        | The Bronx
   Gramercy           | Manhattan
   Little Italy       | Manhattan
   Financial District | Manhattan
   South Bronx        | The Bronx
   Yorkville          | Manhattan
   Murray Hill        | Manhattan
   Mott Haven         | The Bronx
   Upper East Side    | Manhattan
   Chinatown          | Manhattan
   East Harlem        | Manhattan
   Greenwich Village  | Manhattan
   Parkchester        | The Bronx
   Soundview          | The Bronx
  

  ملاحظة

  استخدمنا الكلمة المفتاحية DISTINCT لحذف القيم المكررة من مجموعة النتائج حيث توجد أكثر من محطة مترو في الحي الواحد.

• بعد أحداث 9/11، أُغلق حي 'Battery Park' لعدة أيام. كم عدد الأشخاص الذين اضطُرّوا إلى الإخلاء؟

  SELECT Sum(popn_total)
  FROM nyc_neighborhoods AS n
  JOIN nyc_census_blocks AS c
  ON ST_Intersects(n.geom, c.geom)
  WHERE n.name = 'Battery Park';
  

  17153
  

• ما الحي ذو الكثافة السكانية الأعلى (شخص/km2)؟

  SELECT
    n.name,
    Sum(c.popn_total) / (ST_Area(n.geom) / 1000000.0) AS popn_per_sqkm
  FROM nyc_census_blocks AS c
  JOIN nyc_neighborhoods AS n
  ON ST_Intersects(c.geom, n.geom)
  GROUP BY n.name, n.geom
  ORDER BY popn_per_sqkm DESC LIMIT 2;
  

        name       |  popn_per_sqkm
  -------------------+------------------
   North Sutton Area | 68435.13283772678
   East Village      | 50404.48341332535