19. 地理练习

以下是我们到目前为止看到的所有函数的提醒。它们应该对练习有所帮助！

• Sum(number) 将结果集中的所有数字相加

• ST_GeogFromText(text)

• ST_Distance(geography, geography) 返回地理位置之间的距离

• :command:`ST_Transform(geometry, srid)`在新的投影中返回几何图形

• :command:`ST_Length(geography)`返回行长度

• 如果对象在平面空间中不是不相交的，则返回true

• :command:`ST_Intersects(geography, geography)`如果对象在球体空间中不是不相交的，则返回true

当然！我们有以下表可用：

• nyc_streets

  • name, type, geom

• nyc_neighborhoods

  • name, boroname, geom

19.1. 练习

• How far is New York from Seattle? What are the units of the answer?

  注解

  New York = POINT(-74.0064 40.7142) and Seattle = POINT(-122.3331 47.6097)

  SELECT ST_Distance(
    'POINT(-74.0064 40.7142)'::geography,
    'POINT(-122.3331 47.6097)'::geography
    );
  

  3875538.57141352
  

• What is the total length of all streets in New York, calculated on the spheroid?

  SELECT Sum(
    ST_Length(Geography(
      ST_Transform(geom,4326)
    )))
  FROM nyc_streets;
  

  10421999.666
  

  注解

  The length calculated in the planar "UTM Zone 18" projection is 10418904.717, 0.02% different. UTM is good at preserving area and distance, within the zone boundaries.

• Does ‘POINT(1 2.0001)’ intersect with ‘POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))’ in geography? In geometry? Why the difference?

  SELECT ST_Intersects(
    'POINT(1 2.0001)'::geography,
    'POLYGON((0 0,0 2,2 2,2 0,0 0))'::geography
  );
  
  SELECT ST_Intersects(
    'POINT(1 2.0001)'::geometry,
    'POLYGON((0 0,0 2,2 2,2 0,0 0))'::geometry
  );
  

  true and false
  

  注解

  The upper edge of the square is a straight line in geometry, and passes below the point, so the square does not contain the point. The upper edge of the square is a great circle in geography, and passes above the point, so the square does contain the point.