SQL最近位置查詢語句是什么

本篇內(nèi)容主要講解“SQL最近位置查詢語句是什么”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學(xué)習(xí)“SQL最近位置查詢語句是什么”吧!

前言

我已經(jīng)浪費了太多的時間在尋找定位軟件上了，因此這值得我去寫下如何去做。當(dāng)然，在地球表面計算距離意味著計算大圓距離，可以通過半正矢公式計算，也稱之為球面余弦定律公式。問題是：

給出一個具有經(jīng)緯度的位置表，其中哪個位置最靠近給出的定位？

<!--more-->

位置數(shù)據(jù)表

你是否想問在哪里我可以找到一張具有經(jīng)緯度的位置表？你可以在互聯(lián)網(wǎng)上搜索“郵政編碼免費下載”或者“免費郵編下載”。然后將其加載到MySQL表中。有很多不同類型的地理數(shù)據(jù)可以下載，附帶經(jīng)緯度位置。

這是SQL Server數(shù)據(jù)的美國郵政編碼數(shù)據(jù)包，如果你正好需要的話。

本文中的邏輯適用于MySQL，MariaDB， PostgreSQL，和微軟的SQL Server。Oracle的工作方式有一點不同；這里有一篇文章講述了如何在Oracle中實現(xiàn)。

請慎重的使用郵政編碼數(shù)據(jù)作為確定位置的方法。郵政編碼僅被設(shè)計用于幫助優(yōu)化郵政投遞。他們的數(shù)據(jù)用途有限，并且可能帶來錯誤的結(jié)果。例如，這是一篇地理學(xué)家寫的關(guān)于美國密西根州弗林特市水危機的文章。在很長一段時間，弗林特市的孩子似乎沒有鉛中毒，因為研究員只看他們家的郵政編碼去找出他們住在哪里。但是他們鉛中毒了，別和密歇根州政府犯相同的錯誤。

煩人卻必要的地理

緯度和經(jīng)度用度數(shù)表示。緯度描述了一個點在赤道以北或以南的距離。赤道上的點的緯度是零。北極的正（北）緯度為90度，并且南極是負(fù)（南）緯度-90度。相應(yīng)的，北半球的位置有著正緯度，并且南半球的位置有著負(fù)的緯度。

經(jīng)度描述了一個點從本初子午線向東的距離：地球表面從一個點到另一個點的任意直線。位于美國紐約市的帝國大廈的經(jīng)度為負(fù)（西），具體來說為-73.9857。印度阿格拉的泰姬陵經(jīng)度為正（東經(jīng)），具體為78.0422。英國倫敦附近的格林威治天文臺，根據(jù)定義，經(jīng)度為零。

因此，緯度是范圍內(nèi)的值[-90，90]。經(jīng)度是范圍（-180，180）內(nèi)的值。這些值有時以度、分和秒表示，而不是以度和小數(shù)表示。如果你打算做計算，先把分和秒轉(zhuǎn)換成小數(shù)。

在拿破侖時代，米是最早被定義的，所以從赤道到兩極有一千萬米。原來緯度上的米數(shù)是10000000/90或111.111公里。但是地球有點凸起，因此111.045公里/度被認(rèn)為是一個更好的近似值。

在這里我們?yōu)榱朔奖阌嬎悖覀兗僭O(shè)地球是一個球體。雖然這不是真的。它在赤道上有點凸起，但是定位問題，我們假設(shè)是球體就足夠了。

這個公式（111.045公里/度）在你向北或者向南移動的時候很好用。如果你在改變你的緯度而不是經(jīng)度。如果你在向東或者向西移動、在改變你的經(jīng)度、在赤道上，它也能起作用。但是在赤道的南北邊，經(jīng)度線越來越接近，所以如果你向西或向東移動一個刻度，你移動的距離就會小于111.045千米。當(dāng)你往東或往西走一度時，你實際移動的距離實際上是公里數(shù)。

111.045 * cos(latitude)

我們在一些英國殖民地里使用英里。海里是指緯度的一分鐘（1/60度）。所以每度有69法定英里或每度60海里。如果你正在處理這樣的應(yīng)用，如GPS控制耕牛隊，你可能會發(fā)現(xiàn)它有助于知道有552浪（長度單位，相當(dāng)于220碼、201米或?英里）每度。一些以美國為中心的應(yīng)用程序擾亂了經(jīng)度。對西半球的位置來說，它們是正的而不是負(fù)的。如果你在調(diào)試什么東西，要注意這個

大圓距離公式

任意兩點沿（球面）地球表面的距離是多少？用度數(shù)表示，用他們的經(jīng)緯度表示？這是由球余弦定理，或者半正矢公式?jīng)Q定的。這是MySQL語法中的：

DEGREES(ACOS(COS(RADIANS(lat1)) * COS(RADIANS(lat2)) *
             COS(RADIANS(long1) - RADIANS(long2)) +
             SIN(RADIANS(lat1)) * SIN(RADIANS(lat2))))

它是地球表面的距離。當(dāng)這些地方是你的公寓和當(dāng)?shù)爻?，或者是澳大利亞悉尼和冰島雷克雅未克的機場時，它也同樣適用。注意，這個結(jié)果是以度為單位的。這意味著如果我們想要以公里為單位的距離，我們必須將它乘以111.045，即每度公里的數(shù)值。

請注意MS SQL Server需要使用一個float或double來表示RADIANS。RADIANS(30) 返回的是有問題的值，但是RADIANS(30)能正常工作。一般來說，MS SQL Server不會可靠的強制整integer類型的值轉(zhuǎn)換為float或者double類型，所有請小心，不要在你需要使用float的時候使用integer類型。此外，請記住美國郵政編碼雖然看起來像數(shù)字，但是其實是字符串。我住的地方郵政編碼是'01950'，這和1950是不一樣的。

查詢最近的位置

為了在數(shù)據(jù)庫中找到與給定點的最近的點，我們可以這樣寫查詢。讓我們使用經(jīng)度為-70.81、緯度為42.81的點。這個MySQL查詢按照距離的順序查找離給定點最近的15個點。 可以在這邊測試：http://sqlfiddle.com/#!9/21e06/1

SELECT zip, primary_city, latitude, longitude,
      111.045* DEGREES(ACOS(COS(RADIANS(latpoint))
                 * COS(RADIANS(latitude))
                 * COS(RADIANS(longpoint) - RADIANS(longitude))
                 + SIN(RADIANS(latpoint))
                 * SIN(RADIANS(latitude)))) AS distance_in_km
 FROM zip
 JOIN (
     SELECT  42.81  AS latpoint,  -70.81 AS longpoint
   ) AS p ON 1=1
 ORDER BY distance_in_km
 LIMIT 15

注意使用連接將latpoint和longpoint放入查詢中。這樣編寫查詢很方便，因為公式中多次引用了latpoint和longpoint。（MySQL不需要使用ON 1=1，但是PostgreSQL需要） (在SQL Server中, 使用 SELECT TOP(15) zip … 來替換LIMIT 15.)

非常好，我們做到了，對吧？別著急!這個查詢雖然是正確的，但是他很慢。

優(yōu)化

查詢速度很慢是因為它必須為每個可能的點對計算半正矢公式。因此，它使你的MySQL服務(wù)器做了很多數(shù)學(xué)運算，并強制它掃描整個位置表。如何優(yōu)化？如果我們能在表中的緯度和經(jīng)度列上使用索引，那就太好了。為此，我們引入一個約束。假設(shè)我們只關(guān)心郵政編碼表中距離（latpoint，longpoint）50公里以內(nèi)的點。讓我們找出如何使用索引來消除更遠(yuǎn)的點。

請記住，根據(jù)本文前面的背景信息，緯度是111.045公里。所以，如果緯度列上有一個索引，我們可以使用類似這樣的SQL子句來消除太北或太南的點，這些點可能不在50公里之內(nèi)。

latitude BETWEEN latpoint - (50.0 / 111.045)
             AND latpoint + (50.0 / 111.045)

這個WHERE語句允許MySQL在計算半正矢距離公式之前使用索引省略許多緯度點。它允許MySQL對緯度索引執(zhí)行范圍掃描。

最后，我們可以使用一個類似但更復(fù)雜的SQL子句來消除太東或太西的點。這個條款更復(fù)雜，因為經(jīng)度是離我們移動的赤道越遠(yuǎn)的距離越小。請看下面公式：

longitude BETWEEN longpoint - (50.0 / (111.045 * COS(RADIANS(latpoint))))
              AND longpoint + (50.0 / (111.045 * COS(RADIANS(latpoint))))

因此，將所有這些放在一起，這個查詢將查找（latpoint，longpoint）50公里范圍內(nèi)的最東邊15個點。

盡管這個查詢有點復(fù)雜，但它利用了緯度和經(jīng)度索引，并且工作效率很高。

請注意，作為整個查詢的一部分，我們加入了這個子查詢。

SELECT  42.81  AS latpoint,  -70.81 AS longpoint,
        50.0 AS radius,      111.045 AS distance_unit

這樣做的目的是使應(yīng)用軟件更容易提供查詢所需的參數(shù)。Latpoint和Longpoint是您需要附近位置的特定位置。radius指定搜索應(yīng)該走多遠(yuǎn)。最后，如果你想用公里表示距離，距離單位應(yīng)該是111.045。如果你想用英里表示距離，應(yīng)該是69.0。

極限對角線距離

但是，這個邊界查詢有可能返回距離（latpoint，longpoint）對角線超過50km的一些點：它只檢查一個邊界矩形，而不是對角線距離。讓我們增強查詢以消除超過50公里的點。

使用英里而不是公里

最后，許多人需要用英里而不是公里來計算他們的距離。這很簡單。只需將距離單位的值更改為69.0。

這是一個基于經(jīng)緯度的典型商店查找程序或位置查找程序的查詢。應(yīng)該能夠適應(yīng)你的使用，沒有太多的麻煩。

將此查詢適應(yīng)其他位置表定義

當(dāng)然，這個查詢是用一個特定的ZIP表定義（一個美國郵政編碼表）編寫的。該zip表包含名為zip、primary_city、latitude和longitude等字段。請注意，該表在查詢中由 FROM zip AS z引用。所以它的別名是z。

你的位置表很可能有不同的列。重寫此查詢來適應(yīng)你的查詢應(yīng)該很簡單。在查詢中查找稱為z.something的字段，并用表中的字段名替換這些字段。例如，如果你的表名為shop，并且有shopname、shoplat和shoplong字段，那么你把z.shopname替換為z.primary_city，以此類推。你將通過在查詢中包含FROM SHOP as z來引用表。

到此，相信大家對“SQL最近位置查詢語句是什么”有了更深的了解，不妨來實際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進入相關(guān)頻道進行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！