10. Вправи з геометрією

Ось нагадування про всі функції, які ми бачили до цього часу. Вони мають бути корисними для виконання вправ!

• :command:`sum(expression)`агрегатна функція, що повертає суму для набору записів

• :command:`count(expression)`агрегатна функція, що повертає кількість записів у наборі

• ST_GeometryType(geometry) повертає тип геометрії

• ST_NDims(geometry) повертає кількість вимірів геометрії

• :command:`ST_SRID(geometry)`повертає номер ідентифікатора просторової прив’язки (SRID) геометрії

• ST_X(point) повертає координату X

• ST_Y(point) повертає координату Y

• ST_Length(linestring) повертає довжину лінії (linestring)

• :command:`ST_StartPoint(geometry)`повертає першу координату у вигляді точки

• ST_EndPoint(geometry) повертає останню координату у вигляді точки

• ST_NPoints(geometry) повертає кількість координат у лінії (linestring)

• ST_Area(geometry) повертає площу полігону

• ST_NRings(geometry) повертає кількість кілець (зазвичай 1, більше — якщо є дірки)

• ST_ExteriorRing(polygon) повертає зовнішнє кільце у вигляді лінії (linestring)

• ST_InteriorRingN(polygon, integer) повертає вказане внутрішнє кільце у вигляді лінії(linestring)

• ST_Perimeter(geometry) повертає сумарну довжину всіх кілець

• ST_NumGeometries(multi/geomcollection) повертає кількість частин у колекції

• ST_GeometryN(geometry, integer) повертає вказану частину колекції

• ST_GeomFromText(text) повертає geometry

• ST_AsText(geometry) повертає WKT text

• ST_AsEWKT(geometry) повертає EWKT text

• ST_GeomFromWKB(bytea) повертає geometry

• ST_AsBinary(geometry) повертає WKB bytea

• ST_AsEWKB(geometry) повертає EWKB bytea

• ST_GeomFromGML(text) повертає geometry

• ST_AsGML(geometry) повертає GML text

• ST_GeomFromKML(text) повертає geometry

• ST_AsKML(geometry) повертає KML text

• ST_AsGeoJSON(geometry) повретає JSON text

• ST_AsSVG(geometry) повертає SVG text

Також пам’ятайте про таблиці, які у нас є:

• nyc_census_blocks

  • blkid, popn_total, boroname, geom

• nyc_streets

  • name, type, geom

• nyc_subway_stations

  • name, geom

• nyc_neighborhoods

  • name, boroname, geom

10.1. Вправи

• Яка площа району «West Village»?

  SELECT ST_Area(geom)
    FROM nyc_neighborhoods
    WHERE name = 'West Village';
  

  1044614.5296486
  

  Примітка

  Площа наведена у квадратних метрах. Щоб отримати площу у гектарах, поділіть на 10 000. Щоб отримати площу в акрах, поділіть на 4047.

• Який тип геометрії у ‘Pelham St’? Яка її довжина?

  SELECT
     ST_GeometryType(geom),
     ST_Length(geom)
    FROM nyc_streets
    WHERE name = 'Pelham St';
  

  ST_MultiLineString
  50.323
  

• Яке GeoJSON-подання станції метро „Broad St“?

  SELECT
   ST_AsGeoJSON(geom)
  FROM nyc_subway_stations
  WHERE name = 'Broad St';
  

  {"type":"Point",
   "crs":{"type":"name","properties":{"name":"EPSG:26918"}},
   "coordinates":[583571.905921312,4506714.341192182]}
  

• Яка загальна довжина вулиць (у кілометрах) у Нью-Йорку? (Підказка: одиниці вимірювання просторових даних — метри, у кілометрі 1000 метрів.)

  SELECT Sum(ST_Length(geom)) / 1000
    FROM nyc_streets;
  

  10418.9047172
  

• **Яка площа Манхеттена в акрах? **(Підказка: і ``nyc_census_blocks ``, і ``nyc_neighborhoods `` містять поле ``boroname ``.)

  SELECT Sum(ST_Area(geom)) / 4047
    FROM nyc_neighborhoods
    WHERE boroname = 'Manhattan';
  

  13965.3201224118
  

  або…

  SELECT Sum(ST_Area(geom)) / 4047
    FROM nyc_census_blocks
    WHERE boroname = 'Manhattan';
  

  14601.3987215548
  

• Яка станція метро знаходиться найзахідніше?

  SELECT ST_X(geom), name
    FROM nyc_subway_stations
    ORDER BY ST_X(geom)
    LIMIT 1;
  

  Tottenville
  

• Яка довжина вулиці „Columbus Cir“ (також відома як Columbus Circle)?

  SELECT ST_Length(geom)
    FROM nyc_streets
    WHERE name = 'Columbus Cir';
  

  308.34199
  

• Яка довжина вулиць у Нью-Йорку за типами (підсумовано)?

  SELECT type, Sum(ST_Length(geom)) AS length
  FROM nyc_streets
  GROUP BY type
  ORDER BY length DESC;
  

                         type                       |      length
  --------------------------------------------------+------------------
   residential                                      | 8629870.33786606
   motorway                                         | 403622.478126363
   tertiary                                         | 360394.879051303
   motorway_link                                    | 294261.419479668
   secondary                                        | 276264.303897926
   unclassified                                     | 166936.371604458
   primary                                          | 135034.233017947
   footway                                          | 71798.4878378096
   service                                          |  28337.635038596
   trunk                                            | 20353.5819826076
   cycleway                                         | 8863.75144825929
   pedestrian                                       | 4867.05032825026
   construction                                     | 4803.08162103562
   residential; motorway_link                       | 3661.57506293745
   trunk_link                                       | 3202.18981240201
   primary_link                                     | 2492.57457083536
   living_street                                    | 1894.63905457332
   primary; residential; motorway_link; residential | 1367.76576941335
   undefined                                        |  380.53861910346
   steps                                            | 282.745221342127
   motorway_link; residential                       |  215.07778911517
  

  Примітка

  ORDER BY length DESC сортує результати за довжиною у спадному порядку. В результаті найбільш поширені типи опиняються першими у списку.