Haversine公式使用SQL Server查找最接近的场所-vb.net

我正在从表单中获取邮政编码。然后，我可以将这些邮政编码转换为lng，lat坐标，因为这些存储在表中。

SELECT lng, lat from postcodeLngLat WHERE postcode = ''CV1''

我还有另一张桌子，上面存放着一些场馆的家具。

SELECT v.lat, v.lng, v.name, p.lat, p.lng, p.postcode, ''HAVERSINE'' ASdistance FROM venuepostcodes v, postcodeLngLat p WHERE p.outcode = ''CB6'' ORDERBY distance

我要尝试做的是创建一个数据网格，该数据网格显示每个场所与邮政编码（在本例中为CV1）之间的距离。我知道Haversine公式应该可以达到我想要达到的目的，但是我不知道该从哪里开始将其合并到查询中。我认为公式需要转到我''HAVERSINE''在上面的查询中输入的位置。

有任何想法吗？

编辑

SELECT o.outcode AS lead_postcode, v.venue_name, 6371.0E * ( 2.0E *asin(casewhen 1.0E < (sqrt(square(sin(((RADIANS(CAST(o.lat ASFLOAT)))-(RADIANS(CAST(v.lat AS FLOAT))))/2.0E)) + (cos(RADIANS(CAST(v.lat ASFLOAT))) * cos(RADIANS(CAST(o.lat AS FLOAT))) *square(sin(((RADIANS(CAST(o.lng AS FLOAT)))-(RADIANS(CAST(v.lng ASFLOAT))))/2.0E))))) then 1.0E else (sqrt(square(sin(((RADIANS(CAST(o.lat ASFLOAT)))-(RADIANS(CAST(v.lat AS FLOAT))))/2.0E)) + (cos(RADIANS(CAST(v.lat ASFLOAT))) * cos(RADIANS(CAST(o.lat AS FLOAT))) *square(sin(((RADIANS(CAST(o.lng AS FLOAT)))-(RADIANS(CAST(v.lng ASFLOAT))))/2.0E))))) end )) AS distance FROM venuepostcodes v, outcodepostcodeso WHERE o.outcode = ''CB6'' ORDER BY distance

答案1

我认为您最好将其放入UDF并在查询中使用它：

SELECT v.lat, v.lng, v.name, p.lat, p.lng, p.postcode, udf_Haversine(v.lat, v.lng, p.lat, p.lng) AS distance FROM venuepostcodes v, postcodeLngLat p WHERE p.outcode = ''CB6'' ORDER BY distancecreate function dbo.udf_Haversine(@lat1 float, @long1 float, @lat2 float, @long2 float) returns float begin        declare @dlon float, @dlat float, @rlat1 float, @rlat2 float, @rlong1 float, @rlong2 float, @a float, @c float, @R float, @d float, @DtoR float        select @DtoR = 0.017453293        select @R = 3937 --3976        select             @rlat1 = @lat1 * @DtoR,            @rlong1 = @long1 * @DtoR,            @rlat2 = @lat2 * @DtoR,            @rlong2 = @long2 * @DtoR        select             @dlon = @rlong1 - @rlong2,            @dlat = @rlat1 - @rlat2        select @a = power(sin(@dlat/2), 2) + cos(@rlat1) * cos(@rlat2) * power(sin(@dlon/2), 2)        select @c = 2 * atn2(sqrt(@a), sqrt(1-@a))        select @d = @R * @c        return @d     end

ImportError: this is MySQLdb version (1, 2, 5, ''final'', 1), but _mysql is version (1, 4, ...

(flask-demo) ➜ flask-demo git:(master) ✗ pip install mysqlclient==1.2.5 DEPRECATION: Python 2.7 will reach the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 won''t be maintained after that date. A future version of pip will drop support for Python 2.7. More details about Python 2 support in pip, can be found at https://pip.pypa.io/en/latest/development/release-process/#python-2-support Looking in indexes: http://pypi.douban.com/simple ERROR: Could not find a version that satisfies the requirement mysqlclient==1.2.5 (from versions: 1.3.0, 1.3.1, 1.3.2, 1.3.3, 1.3.4, 1.3.5, 1.3.6, 1.3.7, 1.3.8, 1.3.9, 1.3.10, 1.3.11rc1, 1.3.11, 1.3.12, 1.3.13, 1.3.14, 1.4.0rc1, 1.4.0rc2, 1.4.0rc3, 1.4.0, 1.4.1, 1.4.2, 1.4.2.post1, 1.4.3, 1.4.4) ERROR: No matching distribution found for mysqlclient==1.2.5 (flask-demo) ➜ flask-demo git:(master) ✗ pip install MySQL-python DEPRECATION: Python 2.7 will reach the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 won''t be maintained after that date. A future version of pip will drop support for Python 2.7. More details about Python 2 support in pip, can be found at https://pip.pypa.io/en/latest/development/release-process/#python-2-support Looking in indexes: http://pypi.douban.com/simple Requirement already satisfied: MySQL-python in /Users/wang/.virtualenvs/flask-demo/lib/python2.7/site-packages (1.2.5) (flask-demo) ➜ flask-demo git:(master) ✗ pip install mysqlclient==1.3.0 DEPRECATION: Python 2.7 will reach the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 won''t be maintained after that date. A future version of pip will drop support for Python 2.7. More details about Python 2 support in pip, can be found at https://pip.pypa.io/en/latest/development/release-process/#python-2-support Looking in indexes: http://pypi.douban.com/simple Collecting mysqlclient==1.3.0 Downloading http://pypi.doubanio.com/packages/6a/91/bdfe808fb5dc99a5f65833b370818161b77ef6d1e19b488e4c146ab615aa/mysqlclient-1.3.0.tar.gz (76kB) |████████████████████████████████| 81kB 1.9MB/s Building wheels for collected packages: mysqlclient Building wheel for mysqlclient (setup.py) ... done Created wheel for mysqlclient: filename=mysqlclient-1.3.0-cp27-cp27m-macosx_10_14_intel.whl size=44252 sha256=9227ae6803ea4590b6d18684e525b4b14958d2403aee820c6181ab1578cca342 Stored in directory: /Users/wang/Library/Caches/pip/wheels/03/17/c0/a1134f10bd894aec20af7d555f675e02a83552609d128e0756 Successfully built mysqlclient Installing collected packages: mysqlclient Found existing installation: mysqlclient 1.4.4 Uninstalling mysqlclient-1.4.4: Successfully uninstalled mysqlclient-1.4.4 Successfully installed mysqlclient-1.3.0

MySQL Crash Course #16# Chapter 24. Using Cursors + mysql 循环

 mysql中游标的使用案例详解（学习笔记）这篇讲得相当直白好懂了。

索引：

1. cursor 基础讲解
2. mysql 循环
3. 书上的整合代码

cursor 基础讲解

 cursor 有点类似于 JDBC 中的 ResultSet ，允许我们在执行 SELECT 之后，一行一行地 FETCH 数据。

它只能被用在存储过程中！如果把存储过程比作函数，cursor 只能在这个函数体中（存储过程的内部）定义、打开、关闭，一旦存储过程执行完毕，它将不再存在（可以把 cursor 理解为一个局部变量）。

定义一个 cursor ，这个时候并没有执行 SELECT 语句：

CREATE PROCEDURE processorders()
BEGIN
   DECLARE ordernumbers CURSOR
   FOR
   SELECT ordernum FROM orders;
END;

打开 cursor，SELECT 语句就是在这个时候被执行的，数据被缓存到 cursor 中，在这之后可以 FETCH 数据：

OPEN ordernumbers;

关闭 cursor ，释放 cursor 所占用的所有内存和资源， 当然，即便不手动关闭，在存储过程结束时（执行到 END） cursor 也会自动关闭：

CLOSE ordernumbers;

接下来就是 FETCH 数据了，每次 FETCH 一次都是下一行！可以结合上面几个知识点做个小实验，上面是实验用的表：

DROP PROCEDURE IF EXISTS hello;

DELIMITER //

CREATE PROCEDURE hello()
BEGIN
   -- 声明局部变量，用于临时存储 FETCH 结果
   DECLARE o INT;

   -- 声明游标
   DECLARE ordernumbers CURSOR
   FOR
   SELECT order_num FROM orders;

   -- 打开游标（执行SELECT，缓存数据）
   OPEN ordernumbers;

   -- 取出第一个订单号放到 o 里，并打印
   FETCH ordernumbers INTO o;
   SELECT o;
   
   -- 取出第二个订单号放到 o 里，并打印
   FETCH ordernumbers INTO o;
   SELECT o;
   
   -- 关闭游标（释放内存、资源）
   CLOSE ordernumbers;
   
   -- 重新打开游标（重新执行SELECT，缓存数据）
   OPEN ordernumbers;
   
   -- 取出第 ? 个订单号放到 o 里，并打印
   FETCH ordernumbers INTO o;
   SELECT o; -- 可以猜到，依然是第一个订单号

END // 

DELIMITER ;

CALL hello();

mysql 循环

 mySQL 本身的循环其实挺简单的，但是书上写的循环有点特殊。

基本循环示例：

while ↓

DROP PROCEDURE IF EXISTS hello;

DELIMITER //

CREATE PROCEDURE hello()
BEGIN
    DECLARE v1 INT DEFAULT 5;

    WHILE v1 > 0 DO
    SET v1 = v1 - 1;
    SELECT v1;
    END WHILE;
END // 

DELIMITER ;

CALL hello();

repeat ↓

DROP PROCEDURE IF EXISTS hello;

DELIMITER //

CREATE PROCEDURE hello()
BEGIN
    DECLARE v1 INT DEFAULT 5;

    REPEAT
    SET v1 = v1 - 1;
    SELECT v1;
    UNTIL v1 = 0 END REPEAT;
END // 

DELIMITER ;

CALL hello();

loop ↓

DROP PROCEDURE IF EXISTS hello;

DELIMITER //

CREATE PROCEDURE hello()
BEGIN
    DECLARE v1 INT DEFAULT 5;

    label1: LOOP
    
    SET v1 = v1 - 1;
    SELECT v1;
    IF v1 > 0 THEN
    ITERATE label1; -- 继续循环 相当于continue
    END IF;
    LEAVE label1; -- 相当于 break
    
    END LOOP;
END // 

DELIMITER ;

CALL hello();

PS. 为了确保可以直接 COPY 到命令行执行，上面的代码缩进有点问题。。。

书上的循环示例：

CREATE PROCEDURE processorders()
BEGIN

   -- Declare local variables
   DECLARE done BOOLEAN DEFAULT 0;
   DECLARE o INT;

   -- Declare the cursor
   DECLARE ordernumbers CURSOR
   FOR
   SELECT order_num FROM orders;

   -- Declare continue handler
   DECLARE CONTINUE HANDLER FOR SQLSTATE ''02000'' SET done=1;

   -- Open the cursor
   OPEN ordernumbers;

   -- Loop through all rows
   REPEAT

      -- Get order number
      FETCH ordernumbers INTO o;

   -- End of loop
   UNTIL done END REPEAT;

   -- Close the cursor
   CLOSE ordernumbers;

END;

 首先要看懂这句话

DECLARE CONTINUE HANDLER FOR SQLSTATE ''02000'' SET done=1;

意思就是出现 SQLSTATE ''02000'' 这句话时就执行 SET done=1

而 SQLSTATE ''02000'' 等价于 not found ，它是在 FETCH 不到数据的时候出现的，因此拿不到数据的时候 done 变为真就结束循环了。。。

 书上的整合代码

CREATE PROCEDURE processorders()
BEGIN

   -- Declare local variables
   DECLARE done BOOLEAN DEFAULT 0;
   DECLARE o INT;
   DECLARE t DECIMAL(8,2);

   -- Declare the cursor
   DECLARE ordernumbers CURSOR
   FOR
   SELECT order_num FROM orders;
   -- Declare continue handler
   DECLARE CONTINUE HANDLER FOR SQLSTATE ''02000'' SET done=1;

   -- Create a table to store the results
   CREATE TABLE IF NOT EXISTS ordertotals
      (order_num INT, total DECIMAL(8,2));

   -- Open the cursor
   OPEN ordernumbers;

   -- Loop through all rows
   REPEAT

      -- Get order number
      FETCH ordernumbers INTO o;

      -- Get the total for this order
      CALL ordertotal(o, 1, t);

      -- Insert order and total into ordertotals
      INSERT INTO ordertotals(order_num, total)
      VALUES(o, t);

   -- End of loop
   UNTIL done END REPEAT;

   -- Close the cursor
   CLOSE ordernumbers;

END;

这些活到底谁来做好呢 ？ 是上层应用程序 还是 数据库 ？ 。 。。 待更新。。

今天关于MySQL 大圆距离和Haversine 公式的介绍到此结束，谢谢您的阅读，有关Haversine公式与PHP、Haversine公式使用SQL Server查找最接近的场所-vb.net、ImportError: this is MySQLdb version (1, 2, 5, ''final'', 1), but _mysql is version (1, 4, ...、MySQL Crash Course #16# Chapter 24. Using Cursors + mysql 循环等更多相关知识的信息可以在本站进行查询。