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

问题描述

我有数据库表 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);

ST_DWithin

ST_DWithin 如果给定的几何图形在与他人的指定距离.以下查询搜索距离 POINT(-4.6314 54.0887) 5 英里半径内的几何:

SELECT name,long,lat,ST_Distance('POINT(-4.6314 54.0887)'::geography,ST_MakePoint(long,lat)) * 0.000621371 AS 距离来自建筑在哪里ST_DWithin('POINT(-4.6314 54.0887)'::geography,ST_MakePoint(long,lat),8046.72);-- 8046.72 米 = 5 英里；姓名 |长 |纬度 |距离---------------------+---------+---------+-------------------马恩岛机场 |-4.6283 |54.0804 |0.587728347062174(1 行)

ST_Distance

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

注意:使用 ST_Distance 的查询中的距离是实时计算的，因此不使用空间索引.所以，不建议在WHERE子句中使用这个函数！而是在 SELECT 子句中使用它.尽管如此，下面的示例显示了如何做到这一点:

SELECT name,long,lat,ST_Distance('POINT(-4.6314 54.0887)'::geography,ST_MakePoint(long,lat)) * 0.000621371 AS 距离来自建筑在哪里ST_Distance('POINT(-4.6314 54.0887)'::geography,ST_MakePoint(long,lat)) * 0.000621371 <= 5;姓名 |长 |纬度 |距离---------------------+---------+---------+-------------------马恩岛机场 |-4.6283 |54.0804 |0.587728347062174(1 行)

• 注意 ST_MakePoint 的参数顺序:它是经度,纬度..不是相反.

演示:db<>fiddle

Amazon Athena 等价物(以度为单位的距离):

SELECT *, ST_DISTANCE(ST_GEOMETRY_FROM_TEXT('POINT(-84.386330 33.753746)'),ST_POINT(long,lat)) AS 距离来自建筑在哪里ST_Distance(ST_GEOMETRY_FROM_TEXT('POINT(-84.386330 33.753746)'),ST_POINT(long,lat)) <= 5;

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

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

-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

解决方案

Why are you storing x,y in separated columns? I strongly suggest you to store them as geometry or geography to avoid unnecessary casting overhead in query time.

That being said, you can compute and check distances in miles using ST_DWithin or ST_Distance:

(Test data)

CREATE TABLE building (name text, long numeric, lat numeric);
INSERT INTO building VALUES ('Kirk Michael',-4.5896,54.2835);
INSERT INTO building VALUES ('Baldrine',-4.4077,54.2011);
INSERT INTO building VALUES ('Isle of Man Airport',-4.6283,54.0804);

ST_DWithin

ST_DWithin returns true if the given geometries are within the specified distance from another. The following query searches for geometries that are in 5 miles radius from POINT(-4.6314 54.0887):

SELECT name,long,lat,
  ST_Distance('POINT(-4.6314 54.0887)'::geography,
              ST_MakePoint(long,lat)) * 0.000621371 AS distance
FROM building
WHERE
  ST_DWithin('POINT(-4.6314 54.0887)'::geography,
              ST_MakePoint(long,lat),8046.72); -- 8046.72 metres = 5 miles;

        name         |  long   |   lat   |     distance      
---------------------+---------+---------+-------------------
 Isle of Man Airport | -4.6283 | 54.0804 | 0.587728347062174
(1 row)

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.

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 name,long,lat,
  ST_Distance('POINT(-4.6314 54.0887)'::geography,
              ST_MakePoint(long,lat)) * 0.000621371 AS distance
FROM building
WHERE 
  ST_Distance('POINT(-4.6314 54.0887)'::geography,
              ST_MakePoint(long,lat)) * 0.000621371 <= 5;

        name         |  long   |   lat   |     distance      
---------------------+---------+---------+-------------------
 Isle of Man Airport | -4.6283 | 54.0804 | 0.587728347062174
(1 row)

• Mind the parameters order with ST_MakePoint: It is longitude,latitude.. not the other way around.

Demo: db<>fiddle

Amazon Athena equivalent (distance in degrees):

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

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