sql - 使所有建筑物距指定坐标在5英里范围内

我的数据库表Building具有以下列:name，lat，lng
如何获得距指定坐标5英里范围内的所有Buildings，例如:

-84.38653999999998

33.72024

我的尝试，但不起作用:

SELECT ST_CONTAINS(
  SELECT ST_BUFFER(ST_Point(-84.38653999999998,33.72024), 5),
  SELECT ST_POINT(lat,lng) FROM "my_db"."Building" LIMIT 50
);

https://docs.aws.amazon.com/athena/latest/ug/geospatial-functions-list.html

最佳答案

为什么将x，y存储在分开的列中？我真诚地建议您将它们存储为geometry或geography，以避免查询时间的转换开销。
话虽如此，您可以使用以下方式计算距离(以英里为单位):
(测试数据)

CREATE TEMPORARY TABLE building (name text, lat numeric, long numeric);
INSERT INTO building VALUES ('foo',7.52,51.96);
INSERT INTO building VALUES ('bar',7.62,51.94);
INSERT INTO building VALUES ('far away ... ',10.62,59.94);

ST_D内
如果给定的几何形状在彼此之间的指定距离内，则 ST_DWithin 返回true，因此我们需要在函数之前添加NOT。

SELECT *,
  ST_Distance(
    ST_GeographyFromText('POINT(7.62 51.93)'),
    ST_MakePoint(lat,long)) * 0.000621371 AS distance
FROM building
WHERE
  NOT ST_DWithin(
    ST_GeographyFromText('POINT(7.62 51.93)'),
    ST_MakePoint(lat,long),8046.72) -- 8046.72 meters = 5 miles;

     name      |  lat  | long  |     distance
---------------+-------+-------+------------------
 far away ...  | 10.62 | 59.94 | 566.123267141404
(1 Zeile)

ST_距离
函数 ST_Distance (带有geography类型参数)将返回以米为单位的距离。使用此功能，您要做的就是最终将米转换为英里。
注意:使用ST_Distance的查询中的距离是实时计算的，因此不使用空间索引。因此，不建议在WHERE子句中使用此功能!而是在SELECT子句中使用它。不过，以下示例显示了如何完成此操作:

SELECT *, ST_Distance(ST_GeographyFromText('POINT(7.62 51.93)'),
      ST_MakePoint(lat,long)) * 0.000621371 AS distance
FROM building
WHERE
  ST_Distance(ST_GeographyFromText('POINT(7.62 51.93)'),
  ST_MakePoint(lat,long)) * 0.000621371 > 5

     name      |  lat  | long  |     distance
---------------+-------+-------+------------------
 far away ...  | 10.62 | 59.94 | 566.123267141404
(1 Zeile)

Amazon Athena等效项(以度为单位的距离):

SELECT *, ST_DISTANCE(ST_GEOMETRY_FROM_TEXT('POINT(-84.386330 33.753746)'),
      ST_POINT(lat,long)) AS distance
FROM building
WHERE
  ST_Distance(ST_GEOMETRY_FROM_TEXT('POINT(-84.386330 33.753746)'),
  ST_POINT(lat,long)) > 5;

关于sql - 使所有建筑物距指定坐标在5英里范围内，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/51889155/