根據經緯度座標獲得省市區縣行政區劃城市名稱，自建資料庫 java python php c# .net 均適用

xiangyuecn發表於2022-02-16
資料庫JavaPythonPHPC#

目錄

在LBS應用中，根據座標來解析獲得對應是哪個城市是一個很常見的功能，比如App裡面通過手機定位自動選擇城市；本文介紹的是通過自己建的資料庫，利用SQL空間查詢來進行座標解析得到對應的省市區，絕大部分支援空間資料型別(Spatial)的資料庫均支援，包括但不限於：MySQL、SQL Server、Oracle、PostgreSQL等；開發語言不限，只要能進行資料庫查詢就都支援，用Java、Python、PHP、C#均能很簡單的實現。

省市區邊界資料線上預覽、下載：https://xiangyuecn.gitee.io/areacity-jsspider-statsgov/
GitHub地址：https://github.com/xiangyuecn/AreaCity-JsSpider-StatsGov

通過座標來獲取地址：百度地圖提供的介面叫 “地址逆解析”，高德地圖提供的介面叫 “地理逆編碼”，它們開放平臺均提供了前後端介面，發個http請求就可以拿到資料，相關的介面呼叫請自行閱讀平臺開發文件，使用起來很方便。

當然這篇文章不會去介紹怎麼搞街道門牌號這些完整地址，也不講怎麼樣呼叫人家的介面，只介紹座標對應的省市區名稱的獲取，自建資料庫寫SQL進行空間查詢，完全自己實現，比調開放平臺介面相對會複雜些。

由於全國區縣每年都會有比較多的變更，所以省市區邊界資料需要經常去同步維護，好在這上面這個開源庫有在持續的長期維護，新資料釋出後更新相對容易很多。由於開源庫更新維護資料比較及時，所以只要開源庫沒有被關閉，本文介紹的提取方法就一直適用； 比那些上傳到下載平臺就萬年不更新的資料優秀很多。

查詢效果展示：
資料庫查詢效果

從這裡隨便拿的一個座標：
座標位置

直觀的效果如上圖，在百度地圖裡面隨便點選一下（或App定位獲得的座標）得到一個座標，然後到資料庫裡面利用空間查詢SQL就能查詢到座標所在的城市。

步驟一、下載省市區邊界資料

到開源庫裡面下載最新的 ok_geo.csv.7z 檔案（13M大小，解壓130M+），點此下載。下載好後解壓得到 ok_geo.csv，此檔案內包含了最新全國所有的省市區縣座標邊界向量資料。

注：這個檔案只包含三級（省市區）資料不含第四級（鄉鎮街道），如需鄉鎮座標邊界資料，可以請點此下載 ok_geo4_*.csv 檔案（90MB+壓縮包 匯出後300M+）。

步驟二、解析CSV檔案匯入資料庫

下載好的檔案 ok_geo.csv 是純文字檔案，可以自行編寫指令碼進行解析，然後導進資料庫中，自行解析處理比較複雜，請參考開源庫內文件；開源庫內提供了一個格式轉換工具，此工具支援將CSV資料匯入資料庫，因此我們直接在下載資料時順帶把工具下載好，點此下載。

此轉換工具除了支援將 ok_geo.csv 匯入資料庫外，還支援匯出：sql、shp、geojson，和座標系轉換；還可執行自定義 JavaScript 指令碼，擴充套件出豐富功能；軟體是Windows版，如果需要在MacOs中用，可以用虛擬機器。

匯入操作

轉換工具執行匯入資料庫操作：

  1. 點選 選擇ok_geo.csv檔案 按鈕，選擇解壓出來的CSV檔案；
  2. 資料庫設定中選擇要匯入的資料庫型別，這裡選的是MySQL，再填寫資料庫連線，包括：埠、資料庫名稱、賬號密碼；
  3. 點選匯入資料庫按鈕，等待一會，大約3分鐘左右，所有資料就都匯入到了資料庫按今天日期新建的表裡面。

注：csv檔案內的邊界資料預設是高德地圖GCJ-02火星座標系，如果需要其他座標系，比如百度的BD-09或GPS的WGS-84，可以通過高階指令碼中的座標系轉換外掛進行轉換，選擇好對應的外掛後，點選應用就可以了，在匯入資料庫時會自動進行座標系轉換。

注：這個工具限制每次操作只可匯出一個城市和它的下一級資料，匯出少量資料還是很輕鬆的，所以我們可以多操作幾次，將需要的城市資料全部匯入資料庫；比如要深圳的所有區縣資料：第一遍匯入全國所有的省，第二遍在城市名字首中填寫廣東省 （結尾帶一個空格）匯入廣東所有的市，第三遍在城市名字首中填寫廣東省 深圳市 （結尾帶一個空格）匯入深圳所有的區。如果在金鑰輸入框中填寫了金鑰，此工具就沒有這些限制了，開源庫裡面會不定期發放金鑰搞福利，點選一次操作即可匯出全國所有的省市區三級資料。

表結構和空間欄位（MySQL版，其他資料庫類似）：

CREATE TABLE Areacity_Geo_20220216 (
  id int NOT NULL, --城市id
  pid int NOT NULL, --上級城市id
  deep int NOT NULL, --層級：0省、1市、2區
  name varchar(250) NOT NULL, --城市名稱：`深圳市`
  ext_path varchar(255) NOT NULL, --省市區三級完整名稱：廣東省 深圳市 羅湖區
  geo geometry NOT NULL, --城市中心座標，空間資料格式
                --，ST_AsText轉成WKT文字後：`POINT EMPTY`、`POINT (123.456 34.567)`
  polygon geometry NOT NULL --城市邊界範圍圖形，空間資料格式
                --，ST_AsText轉成WKT文字後：`POLYGON EMPTY`、`POLYGON ((123.456 34.567,...))`、`MULTIPOLYGON (((123.456 34.567,...)),...)`
)

對空間欄位的查詢，需要用`ST_AsText()`方法才能查詢出字串文字(WKT: Well Known Text)，否則查詢出來的是二進位制資料
-- MySQL版：
SELECT id, name, ST_AsText(geo) AS geo, ST_AsText(polygon) AS polygon FROM 表名
-- SQL Server版：
SELECT id, name, geo.STAsText() AS geo, polygon.STAsText() AS polygon FROM 表名

步驟三、在程式中根據座標解析獲得城市

省市區邊界匯入到了資料庫後，我們就可以在在 Java、Python、PHP、C# 等程式中對資料庫進行查詢，通過SQL的空間計算函式ST_Intersects來查詢一個座標在哪些邊界範圍內，就能得到對應的省市區資訊了。

空間查詢SQL語句

比如要查詢座標`lng:113.929976 lat:22.529497`是在哪個城市
-- MySQL版：
SELECT id,deep,name FROM 表名 WHERE ST_Intersects(polygon, ST_GeomFromText('POINT(113.929976 22.529497)',0))=1
-- SQL Server版：
SELECT id,deep,name FROM 表名 WHERE polygon.STIntersects(geometry::STGeomFromText('POINT(113.929976 22.529497)',0))=1

查詢結果例子（MySQL版，其他資料庫類似）
MySQL查詢效果

程式程式碼連線上資料庫，通過以上SQL查詢到資料庫資料後，就獲得了省市區資訊，可以通過deep欄位來區分哪條資料是省(deep=0)、市(deep=1)、區縣(deep=2)。

通過以上三步，我們就完全是自己實現了根據經緯度座標來解析獲得對應是哪個城市這一功能。

【END】

相關文章