SQL-SEVER如何计算地图上两点之间距离（直线距离）或（真实距离）

escaflowne945 2017-08-19 03:14:45
table1
name latitude longitude
2112 W Peterson Ave 41.991178 -87.683593
63rd St Beach 41.781016 -87.57612
900 W Harrison St 41.874675 -87.650019
Aberdeen St & Jackson Blvd 41.877726 -87.654787
Aberdeen St & Monroe St 41.880456 -87.653929
Ada St & Washington Blvd 41.88283 -87.661206
Adler Planetarium 41.866095 -87.607267
Albany Ave & 26th St 41.844475 -87.702013
Albany Ave & Bloomingdale Ave 41.914027 -87.705126
Artesian Ave & Hubbard St 41.889492 -87.688219

table2
from_station_name to_station_name
Aberdeen St & Jackson Blvd Aberdeen St & Monroe
Artesian Ave & Hubbard St Ada St & Washington Blvd

我想根据table1的经纬度计算table2 2点之间的距离应该怎么操作？
...全文
760 3 打赏 收藏 转发到动态 举报
写回复
用AI写文章
3 条回复
切换为时间正序
请发表友善的回复…
发表回复
cyg17173 2017-08-25
  • 打赏
  • 举报
 回复
厉害，长见识了。
道素 2017-08-20
  • 打赏
  • 举报
 回复
你可以用三角函数自己算，也可以利用数据库对象（至少2008） 

;with table1(name,latitude,longitude) AS (
    select '2112 W Peterson Ave',41.991178,-87.683593 union all
    select '63rd St Beach',41.781016,-87.57612 union all
    select '900 W Harrison St',41.874675,-87.650019 union all
    select 'Aberdeen St & Jackson Blvd',41.877726,-87.654787 union all
    select 'Aberdeen St & Monroe St',41.880456,-87.653929 union all
    select 'Ada St & Washington Blvd',41.88283,-87.661206 union all
    select 'Adler Planetarium',41.866095,-87.607267 union all
    select 'Albany Ave & 26th St',41.844475,-87.702013 union all
    select 'Albany Ave & Bloomingdale Ave',41.914027,-87.705126 union all
    select 'Artesian Ave & Hubbard St',41.889492,-87.688219
 )
  
 select x.name,y.name , GEOGRAPHY::Point(x.latitude , x.longitude , 4326).STDistance(GEOGRAPHY::Point(y.latitude , y.longitude , 4326))
 from table1 as x,table1 as y


+-------------------------------+-------------------------------+-------------------+
| name                          | name                          |                   |
+-------------------------------+-------------------------------+-------------------+
| 2112 W Peterson Ave           | 2112 W Peterson Ave           | 0                 |
| 2112 W Peterson Ave           | 63rd St Beach                 | 24989.18554505447 |
| 2112 W Peterson Ave           | 900 W Harrison St             | 13236.42633898655 |
| 2112 W Peterson Ave           | Aberdeen St & Jackson Blvd    | 12825.80807843675 |
| 2112 W Peterson Ave           | Aberdeen St & Monroe St       | 12541.77246959468 |
| 2112 W Peterson Ave           | Ada St & Washington Blvd      | 12176.80710314554 |
| 2112 W Peterson Ave           | Adler Planetarium             | 15267.58780325237 |
| 2112 W Peterson Ave           | Albany Ave & 26th St          | 16366.04284240029 |
| 2112 W Peterson Ave           | Albany Ave & Bloomingdale Ave | 8753.349267141703 |
| 2112 W Peterson Ave           | Artesian Ave & Hubbard St     | 11300.9932993255  |
| 63rd St Beach                 | 2112 W Peterson Ave           | 24989.18554505447 |
| 63rd St Beach                 | 63rd St Beach                 | 0                 |
| 63rd St Beach                 | 900 W Harrison St             | 12079.08225850775 |
| 63rd St Beach                 | Aberdeen St & Jackson Blvd    | 12573.29349314966 |
| 63rd St Beach                 | Aberdeen St & Monroe St       | 12797.09289354806 |
| 63rd St Beach                 | Ada St & Washington Blvd      | 13335.58411339007 |
| 63rd St Beach                 | Adler Planetarium             | 9797.606557514406 |
| 63rd St Beach                 | Albany Ave & 26th St          | 12613.83851193379 |
| 63rd St Beach                 | Albany Ave & Bloomingdale Ave | 18249.44346716921 |
| 63rd St Beach                 | Artesian Ave & Hubbard St     | 15227.18075140567 |
| 900 W Harrison St             | 2112 W Peterson Ave           | 13236.42633898655 |
| 900 W Harrison St             | 63rd St Beach                 | 12079.08225850775 |
| 900 W Harrison St             | 900 W Harrison St             | 0                 |
| 900 W Harrison St             | Aberdeen St & Jackson Blvd    | 521.0501060881504 |
| 900 W Harrison St             | Aberdeen St & Monroe St       | 719.4700370303674 |
| 900 W Harrison St             | Ada St & Washington Blvd      | 1297.211644458833 |
| 900 W Harrison St             | Adler Planetarium             | 3674.929895108262 |
| 900 W Harrison St             | Albany Ave & 26th St          | 5467.152545063558 |
| 900 W Harrison St             | Albany Ave & Bloomingdale Ave | 6326.034750908474 |
| 900 W Harrison St             | Artesian Ave & Hubbard St     | 3572.396895157217 |
| Aberdeen St & Jackson Blvd    | 2112 W Peterson Ave           | 12825.80807843675 |
| Aberdeen St & Jackson Blvd    | 63rd St Beach                 | 12573.29349314966 |
| Aberdeen St & Jackson Blvd    | 900 W Harrison St             | 521.0501060881504 |
| Aberdeen St & Jackson Blvd    | Aberdeen St & Jackson Blvd    | 0                 |
| Aberdeen St & Jackson Blvd    | Aberdeen St & Monroe St       | 311.4754015856785 |
| Aberdeen St & Jackson Blvd    | Ada St & Washington Blvd      | 777.9936783201285 |
| Aberdeen St & Jackson Blvd    | Adler Planetarium             | 4151.093347602499 |
| Aberdeen St & Jackson Blvd    | Albany Ave & 26th St          | 5386.618240517426 |
| Aberdeen St & Jackson Blvd    | Albany Ave & Bloomingdale Ave | 5805.843124822277 |
| Aberdeen St & Jackson Blvd    | Artesian Ave & Hubbard St     | 3067.247056668563 |
| Aberdeen St & Monroe St       | 2112 W Peterson Ave           | 12541.77246959468 |
| Aberdeen St & Monroe St       | 63rd St Beach                 | 12797.09289354806 |
| Aberdeen St & Monroe St       | 900 W Harrison St             | 719.4700370303674 |
| Aberdeen St & Monroe St       | Aberdeen St & Jackson Blvd    | 311.4754015856785 |
| Aberdeen St & Monroe St       | Aberdeen St & Monroe St       | 0                 |
| Aberdeen St & Monroe St       | Ada St & Washington Blvd      | 659.0673399593265 |
| Aberdeen St & Monroe St       | Adler Planetarium             | 4189.202578108436 |
| Aberdeen St & Monroe St       | Albany Ave & 26th St          | 5648.931443443651 |
| Aberdeen St & Monroe St       | Albany Ave & Bloomingdale Ave | 5652.764312717465 |
| Aberdeen St & Monroe St       | Artesian Ave & Hubbard St     | 3017.841162928049 |
| Ada St & Washington Blvd      | 2112 W Peterson Ave           | 12176.80710314554 |
| Ada St & Washington Blvd      | 63rd St Beach                 | 13335.58411339007 |
| Ada St & Washington Blvd      | 900 W Harrison St             | 1297.211644458833 |
| Ada St & Washington Blvd      | Aberdeen St & Jackson Blvd    | 777.9936783201285 |
| Ada St & Washington Blvd      | Aberdeen St & Monroe St       | 659.0673399593265 |
| Ada St & Washington Blvd      | Ada St & Washington Blvd      | 0                 |
| Ada St & Washington Blvd      | Adler Planetarium             | 4848.140783322759 |
| Ada St & Washington Blvd      | Albany Ave & 26th St          | 5443.141452579562 |
| Ada St & Washington Blvd      | Albany Ave & Bloomingdale Ave | 5028.903647179581 |
| Ada St & Washington Blvd      | Artesian Ave & Hubbard St     | 2360.983353734794 |
| Adler Planetarium             | 2112 W Peterson Ave           | 15267.58780325238 |
| Adler Planetarium             | 63rd St Beach                 | 9797.606557514406 |
| Adler Planetarium             | 900 W Harrison St             | 3674.929895108262 |
| Adler Planetarium             | Aberdeen St & Jackson Blvd    | 4151.093347602499 |
| Adler Planetarium             | Aberdeen St & Monroe St       | 4189.202578108436 |
| Adler Planetarium             | Ada St & Washington Blvd      | 4848.140783322759 |
| Adler Planetarium             | Adler Planetarium             | 0                 |
| Adler Planetarium             | Albany Ave & 26th St          | 8225.850089600086 |
| Adler Planetarium             | Albany Ave & Bloomingdale Ave | 9711.046167673499 |
| Adler Planetarium             | Artesian Ave & Hubbard St     | 7204.751369015672 |
| Albany Ave & 26th St          | 2112 W Peterson Ave           | 16366.04284240029 |
| Albany Ave & 26th St          | 63rd St Beach                 | 12613.83851193379 |
| Albany Ave & 26th St          | 900 W Harrison St             | 5467.152545063558 |
| Albany Ave & 26th St          | Aberdeen St & Jackson Blvd    | 5386.618240517426 |
| Albany Ave & 26th St          | Aberdeen St & Monroe St       | 5648.931443443651 |
| Albany Ave & 26th St          | Ada St & Washington Blvd      | 5443.141452579562 |
| Albany Ave & 26th St          | Adler Planetarium             | 8225.850089600086 |
| Albany Ave & 26th St          | Albany Ave & 26th St          | 0                 |
| Albany Ave & 26th St          | Albany Ave & Bloomingdale Ave | 7729.526423100472 |
| Albany Ave & 26th St          | Artesian Ave & Hubbard St     | 5129.544814880507 |
| Albany Ave & Bloomingdale Ave | 2112 W Peterson Ave           | 8753.349267141704 |
| Albany Ave & Bloomingdale Ave | 63rd St Beach                 | 18249.44346716921 |
| Albany Ave & Bloomingdale Ave | 900 W Harrison St             | 6326.034750908475 |
| Albany Ave & Bloomingdale Ave | Aberdeen St & Jackson Blvd    | 5805.843124822277 |
| Albany Ave & Bloomingdale Ave | Aberdeen St & Monroe St       | 5652.764312717465 |
| Albany Ave & Bloomingdale Ave | Ada St & Washington Blvd      | 5028.903647179581 |
| Albany Ave & Bloomingdale Ave | Adler Planetarium             | 9711.046167673499 |
| Albany Ave & Bloomingdale Ave | Albany Ave & 26th St          | 7729.526423100472 |
| Albany Ave & Bloomingdale Ave | Albany Ave & Bloomingdale Ave | 0                 |
| Albany Ave & Bloomingdale Ave | Artesian Ave & Hubbard St     | 3065.048933135047 |
| Artesian Ave & Hubbard St     | 2112 W Peterson Ave           | 11300.9932993255  |
| Artesian Ave & Hubbard St     | 63rd St Beach                 | 15227.18075140567 |
| Artesian Ave & Hubbard St     | 900 W Harrison St             | 3572.396895157217 |
| Artesian Ave & Hubbard St     | Aberdeen St & Jackson Blvd    | 3067.247056668563 |
| Artesian Ave & Hubbard St     | Aberdeen St & Monroe St       | 3017.841162928049 |
| Artesian Ave & Hubbard St     | Ada St & Washington Blvd      | 2360.983353734794 |
| Artesian Ave & Hubbard St     | Adler Planetarium             | 7204.751369015672 |
| Artesian Ave & Hubbard St     | Albany Ave & 26th St          | 5129.544814880507 |
| Artesian Ave & Hubbard St     | Albany Ave & Bloomingdale Ave | 3065.048933135047 |
| Artesian Ave & Hubbard St     | Artesian Ave & Hubbard St     | 0                 |
+-------------------------------+-------------------------------+-------------------+
wenyiyi 2017-08-20
  • 打赏
  • 举报
 回复
SQL里居然还能算这个
SQL Server 2019数据库基础入门视频教程
本课程使用sql server 2019版本的数据库进行讲解。主要讲解SQL Server的基本操作和SQL主要语法，注重基础性和实用性。软件公司里面各个岗位包括实施、运维、客服、测试、程序员等都涉及数据库的使用，学会本课程足...
SQL Server根据地图坐标经纬度计算距离
实战1-亲测,返回结果是米（m） 如果嫌麻烦直接跳转至：“参考方案一” 注意：该方法仅支持 SQL Server 2008 和该版本以上的数据库 1、准备-工具 百度坐标拾取器： http://api.map.baidu.com/lbsapi/getpoint/index.html 百度地图：https://map.baidu.com/ 2、准备-SQL语句，新增一张表 USE [DBTase] GO /****** Object: Table [dbo].[distance_.
sqlserver之FOR XML PATH，坐标定位，sql语句
1.FOR XML PATH碰到一个这样的需求，需要查询出电影的所有场次，于是就接触到了FOR XML PATH。想要得到的就是这样的效果。首先看一下sql语句。SELECT DISTINCT show_time + '|' FROM api_film_show tt WHERE tt.cinema_id = t1.cinema_id FOR XML PATH ...
SQL Server 2008 数据挖掘算法
数据挖掘能干什么？ 分类 分类可以把大量数据（在数据挖掘中也称为事例CASE）分成多个类别，而分类的依据就是这些事例中的属性。聚类 分类有一个同胞兄弟就是聚类，聚类相对分类更加自动，聚类也将大量实例分成多个类别，不过这次是根据属性值的相似程度自动汇聚成不同的类别。通常把分类成为有监督的算法，而聚类则被称为无监督的算法，主要原因就是分类在执行前就已经有了明确的类别，而聚类在分析前还不知道有多少分类...
rw_《计算机是怎样跑起来的》4-11 章
2016.07.15 - 08.19 《计算机是怎样跑起来的》4-11 章。 - 矢泽久雄 / 著 - 胡屹 / 译 个人学习笔记。[知识的范围（编程）]4 程序像河水一样流淌着问题 & 重点07.15 初级问题。Flow Chart的中文意思是什么？ [1] 流程图。[来自度娘]中级问题。请说出自然界中河流的三种流动方式。 [1] 向着一个方向流；流着流着产生支流；卷成旋涡。[读后
应用实例

27,580

社区成员

68,556

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章