T-SQL 看看钓鱼岛离到底我们有多远

本文通过地理空间数据查询的方式,展示了中国各省份到钓鱼岛的具体距离。利用SQL查询语言,从数据库中获取了钓鱼岛及其周边岛屿的坐标,并计算了这些地点与钓鱼岛之间的直线距离。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

根据国家海洋局2012年9月10日公布的钓鱼岛和赤尾屿坐标,看看

钓鱼岛到底离我们有多远?

DECLARE @a geography= (SELECT geom FROM dbo.CHN_adm1 WHERE NAME_1='diaoyudao')

SELECT name_2,'->钓鱼岛' AS name,geom.STDistance(@a)/1000 AS diatance FROM dbo.CHN_adm1

ORDER BY diatance


name_2 name diatance
钓鱼岛 ->钓鱼岛 0
台湾                                                                                                                                                                                                                                                              ->钓鱼岛 117.306172874411
福建                                                                                                                                                                                                                                                              ->钓鱼岛 294.969757379859
浙江                                                                                                                                                                                                                                                              ->钓鱼岛 308.485996128841
上海                                                                                                                                                                                                                                                              ->钓鱼岛 568.346193561972
江西                                                                                                                                                                                                                                                              ->钓鱼岛 571.765298911414
江苏                                                                                                                                                                                                                                                              ->钓鱼岛 643.820073366621
广东                                                                                                                                                                                                                                                              ->钓鱼岛 648.943953956207
安徽                                                                                                                                                                                                                                                              ->钓鱼岛 652.479962389492
湖北                                                                                                                                                                                                                                                              ->钓鱼岛 855.553259540661
湖南                                                                                                                                                                                                                                                              ->钓鱼岛 910.426542784629
香港 ->钓鱼岛 954.246069836247
河南                                                                                                                                                                                                                                                              ->钓鱼岛 1006.72415995095
澳门 ->钓鱼岛 1051.29532425219
山东                                                                                                                                                                                                                                                              ->钓鱼岛 1096.07252836702
广西                                                                                                                                                                                                                                                              ->钓鱼岛 1137.98540709723
海南                                                                                                                                                                                                                                                              ->钓鱼岛 1372.21568345665
贵州                                                                                                                                                                                                                                                              ->钓鱼岛 1376.23875160608
河北                                                                                                                                                                                                                                                              ->钓鱼岛 1397.74916612305
重庆                                                                                                                                                                                                                                                              ->钓鱼岛 1423.20596455824
山西                                                                                                                                                                                                                                                              ->钓鱼岛 1457.12170556581
西沙                                                                                                                                                                                                                                                              ->钓鱼岛 1462.14033103937
陕西                                                                                                                                                                                                                                                              ->钓鱼岛 1473.17926524538
辽宁                                                                                                                                                                                                                                                              ->钓鱼岛 1474.46588891973
天津                                                                                                                                                                                                                                                              ->钓鱼岛 1548.55560222355
四川                                                                                                                                                                                                                                                              ->钓鱼岛 1597.05392741201
北京                                                                                                                                                                                                                                                              ->钓鱼岛 1671.54543016804
吉林                                                                                                                                                                                                                                                              ->钓鱼岛 1702.89537051446
云南                                                                                                                                                                                                                                                              ->钓鱼岛 1736.44962579353
甘肃                                                                                                                                                                                                                                                              ->钓鱼岛 1782.39026659417
内蒙古                                                                                                                                                                                                                                                             ->钓鱼岛 1786.75641081831
宁夏                                                                                                                                                                                                                                                              ->钓鱼岛 1937.88942040656
黑龙江                                                                                                                                                                                                                                                             ->钓鱼岛 2037.49739498445
青海                                                                                                                                                                                                                                                              ->钓鱼岛 2251.80327133802
西藏                                                                                                                                                                                                                                                              ->钓鱼岛 2423.16738116083
新疆                                                                                                                                                                                                                                                              ->钓鱼岛 3096.59834428733                                                                                                                                       

 

看看最近和最远的城市

SELECT * FROM (SELECT TOP 10 name_3,name_4 ,'钓鱼岛' AS name,geom.STDistance(@a)/1000 AS diatance FROM dbo.CHN_adm2 
ORDER BY diatance) a
UNION ALL
SELECT * FROM (SELECT TOP 10 name_3,name_4 ,'钓鱼岛' AS name,geom.STDistance(@a)/1000 AS diatance FROM dbo.CHN_adm2 
ORDER BY diatance DESC) b


name_3 name_4 name diatance
台湾                                                                                                                                                                                                                                                              台北 钓鱼岛 117.306172874401
台湾                                                                                                                                                                                                                                                              宜兰 钓鱼岛 147.936433119571
台湾                                                                                                                                                                                                                                                              基隆 钓鱼岛 152.170720900614
台湾                                                                                                                                                                                                                                                              台北市 钓鱼岛 174.078538058202
台湾                                                                                                                                                                                                                                                              花莲 钓鱼岛 200.216779266358
台湾                                                                                                                                                                                                                                                              桃园 钓鱼岛 201.439842966968
台湾                                                                                                                                                                                                                                                              新竹 钓鱼岛 218.266150540915
台湾                                                                                                                                                                                                                                                              台中 钓鱼岛 231.38860859258
台湾                                                                                                                                                                                                                                                              苗栗 钓鱼岛 235.943784283145
台湾                                                                                                                                                                                                                                                              南投 钓鱼岛 252.3431887982
新疆                                                                                                                                                                                                                                                              克孜勒苏 钓鱼岛 4425.75270905956
新疆                                                                                                                                                                                                                                                              喀什 钓鱼岛 4328.29400628259
新疆                                                                                                                                                                                                                                                              博尔塔拉 钓鱼岛 4120.56919284722
新疆                                                                                                                                                                                                                                                              克拉玛依 钓鱼岛 4016.29404788444
新疆                                                                                                                                                                                                                                                              伊犁 钓鱼岛 3997.95066851308
新疆                                                                                                                                                                                                                                                              阿克苏 钓鱼岛 3978.18136112323
新疆                                                                                                                                                                                                                                                              石河子 钓鱼岛 3934.76545893642
新疆                                                                                                                                                                                                                                                              塔城 钓鱼岛 3890.71484746074
新疆                                                                                                                                                                                                                                                              和田 钓鱼岛 3823.66699770644
新疆                                                                                                                                                                                                                                                              乌鲁木齐 钓鱼岛 3693.76597078457

最后看看地图

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值