给定两个区域的经度和纬度，如何以米为单位找出它们之间的距离。如何在SQL中查询..？ [英] Given longitude and latitude of two areas, how to find the distance between them in meters. How to query in SQL..?

问题描述

我基本上有两张带有ID，Area.Longitude和纬度列的表格。我需要找出每个区域相距20米以上的距离。

表1 b
$ b

  ## [ID] [区域] [纬度] [经度] ## 
 
 ID1区域1 51.51141557 -0.138341652 
 
 ID2区域2 51.50950278 -0.156438192 
 
 ID3 Area3 51.5071583 -0.153418937 
  

表2

  ## [ID] [区域] [纬度] [经度] ## 
 ID1区域1 51.50819747 -0.141020749 
 
 ID2 Area2 51.50781548 -0.14294574 
 
 ID3 Area3 51.51286329 -0.14765827 
  

我想比较表1（区域1）和表格2（区域2），找出它们之间的差异（以经度和纬度给出），并以米为单位显示结果（它们之间的距离）。

结果##

  ## [ID] [区域] [以米为单位的距离] ## 
 
 ID1区域1 5米
 
 ID2区域2 10米
 
 ID3区域3 20米
  

如何编写sql查询来实现此目的。请帮助

解决方案

这是我们用于计算距离的项目中的oracle函数。你可以修改相应的语法以适应mysql / sql-server

 创建或替换
 FUNCTION CALC_DISTANCE（Lat1 IN NUMBER， 
 Lon1 IN NUMBER，
 Lat2 IN NUMBER，
 Lon2 IN NUMBER）返回数字是
  - 将度数转换为弧度
 DEGTORAD NUMBER：= 57.29577951; 
 --Radius NUMBER：= 6387.7; - 对于km 
半径NUMBER：= 6387700  - 对于米
 
 BEGIN 
 RETURN（NVL（Radius，0）* ACOS（（sin（NVL（Lat1,0） / DegToRad）* SIN（NVL（Lat2,0）/ DegToRad））+ 
（COS（NVL（Lat1,0）/ DegToRad）* COS（NVL（Lat2,0）/ DegToRad）* 
 Cos（Nvl（Lon2,0）/ Degtorad-Nvl（Lon1,0）/ Degtorad））））; 
 END; 
  

---

要使用此函数以米计算，您可能可以使用，

  SELECT 
 CALC_DISTANCE（'51 .51141557'，' -  0.138341652'，
 '51 .50819747 '，'-0.141020749'）AS DISTANCE 
 FROM DUAL; 
  

I basically have two table with ID, Area.Longitude and latitude column. I need to find the difference in distances that are more than 20 meters apart for each area.

Table 1

##[ID]  [Area]  [Latitude]  [Longitude]##

ID1 Area1   51.51141557 -0.138341652

ID2 Area2   51.50950278 -0.156438192

ID3 Area3   51.5071583  -0.153418937

Table 2

##[ID]  [Area]  [Latitude]  [Longitude]##
ID1 Area1    51.50819747    -0.141020749

ID2 Area2    51.50781548    -0.14294574

ID3 Area3    51.51286329    -0.14765827

I want to compare table 1( Area 1) and Table 2 ( Area 2) find the difference between them (given in longitude and latitude) and display the results in meters (their distance between them)

Result##

##[ID]  [Area]  [Distance apart in Meter]##

ID1 Area1   5 meter

ID2 Area2   10 meter

ID3 Area3   20 meter

How do I write an sql query for to achieve this. Please help

解决方案

This is the oracle function We use in our projects to calculate distance. You please modify syntax accordingly to suit mysql / sql-server

create or replace
FUNCTION CALC_DISTANCE (Lat1 IN NUMBER,
                                     Lon1 IN NUMBER,
                                     Lat2 IN NUMBER,
                                     Lon2 IN NUMBER) RETURN NUMBER IS
 -- Convert degrees to radians
 DEGTORAD NUMBER := 57.29577951;
 --Radius NUMBER := 6387.7;  -- For km
   Radius NUMBER := 6387700  -- For metres

BEGIN
  RETURN(NVL(Radius,0) * ACOS((sin(NVL(Lat1,0) / DegToRad) * SIN(NVL(Lat2,0) / DegToRad)) +
        (COS(NVL(Lat1,0) / DegToRad) * COS(NVL(Lat2,0) / DegToRad) *
         Cos(Nvl(Lon2,0) / Degtorad - Nvl(Lon1,0)/ Degtorad))));
END;

---

To use this function to get in meters, you can probably use,

SELECT
CALC_DISTANCE ('51.51141557' ,'-0.138341652',
              '51.50819747', '-0.141020749') AS DISTANCE
FROM DUAL;

这篇关于给定两个区域的经度和纬度，如何以米为单位找出它们之间的距离。如何在SQL中查询..？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！