使所有建筑物距指定坐标在5英里范围内 [英] Getting all Buildings in range of 5 miles from specified coordinates

问题描述

我的数据库表Building具有以下列:name，lat，lng

I have database table Building with these columns: name, lat, lng

如何获取距指定坐标5英里范围内的所有Buildings，例如:

How can I get all Buildings in range of 5 miles from specified coordinates, for example these:

-84.38653999999998

-84.38653999999998

33.72024

我的尝试，但是不起作用:

My try but it does not work:

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，以避免查询时间的转换开销.

Why are you storing x,y in separated columns? I would sincerely recommend you to store them as geometry or geography to avoid the casting overhead in query time.

话虽如此，您可以使用以下方法计算距离(以英里为单位):

That being said, you can compute the distance in miles using something like this:

(测试数据)

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_DWithin

如果给定的几何形状在彼此之间的指定距离内，则

ST_DWithin 返回true，因此，我们需要在函数之前添加NOT.

ST_DWithin

ST_DWithin returns true if the given geometries are within the specified distance from another, therefore we need to add a NOT before the function.

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_Distance

函数 ST_Distance (带有geography类型参数)将返回距离以米为单位.使用此功能，您要做的就是最终将米转换为英里.

ST_Distance

The function ST_Distance (with geography type parameters) will return the distance in meters. Using this function all you have to do is to convert meters to miles in the end.

注意:使用ST_Distance的查询中的距离是实时计算的，因此不使用空间索引.因此，不建议在WHERE子句中使用此功能！可以在SELECT子句中使用它.不过，以下示例显示了如何完成此操作:

Attention: Distances in queries using ST_Distance are computed in real time and therefore do not use the spatial index. So, it is not recommended to use this function in the WHERE clause! Use it rather in the SELECT clause. Nevertheless the example below shows how it could be done:

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等效项(以度为单位的距离):

Amazon Athena equivalent (distance in degrees):

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;

这篇关于使所有建筑物距指定坐标在5英里范围内的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！