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

根据国家海洋局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

最后看看地图