參考內(nèi)容

【知乎專欄PostgreSQL與PostGIS】

在PG10.X中需要用到存儲經(jīng)緯度和經(jīng)緯度查找，本篇用作記錄

剛開始百度的過程中總是看到各種PGSQL+PostGIS+OpenLayer或者什么GeoServer狼神的文章，對于我一個小白來說完全是一臉懵逼。在PGSQL中找到了幾何數(shù)據(jù)類型，而且我也只是想保存?zhèn)€位置點然后查詢附近的點或者計算下距離這種簡單的應(yīng)用，給我來一堆這么麻煩的東西顯然會懶得看。

帶著懶癌晚期患者的心情，開始慢慢入門了！

名詞解釋

PostgreSQL

嗯，你要是不知道就別看這篇文章了，沒啥意義

PostGIS

這個是當(dāng)時比較頭疼的，為啥會有這么個東西呢？難道數(shù)據(jù)庫本身就沒法用這玩意兒？？？難道PG就不能像MySQL一樣的簡單嗎？詳情見我另一篇文章【MySQL的GIS、GEO入門筆記】

1986年，加州大學(xué)伯克利分校的Michael Stonebraker教授領(lǐng)導(dǎo)了Postgres的項目，它是PostgreSQL的前身。隨后出現(xiàn)了PostGIS，PostGIS是對象－關(guān)系型數(shù)據(jù)庫系統(tǒng)PostgreSQL的一個擴展，它的出現(xiàn)讓人們開始重視基于數(shù)據(jù)庫管理系統(tǒng)的空間擴展方式，而且使PostGIS有望成為今后管理空間數(shù)據(jù)的主流技術(shù)。

“關(guān)系型數(shù)據(jù)庫+空間數(shù)據(jù)引擎”通常是近年來由GIS廠商研發(fā)的一種中間件解決方案。用戶將自己的空間數(shù)據(jù)交給獨立于數(shù)據(jù)庫之外的空間數(shù)據(jù)引擎，有空間數(shù)據(jù)引擎來組織空間數(shù)據(jù)在關(guān)系型數(shù)據(jù)庫中的存儲;當(dāng)用戶需要訪問數(shù)據(jù)的時候，再通知空間數(shù)據(jù)引擎，有引擎從關(guān)系型數(shù)據(jù)庫中取出數(shù)據(jù)，并轉(zhuǎn)化為客戶可以使用的方式。

PostGIS在對象關(guān)系型數(shù)據(jù)庫PostgreSQL上增加了存儲管理空間數(shù)據(jù)的能力，相當(dāng)于Oracle的spatial部分。PostGIS最大的特點是符合并且實現(xiàn)了OpenGIS的一些規(guī)范，是最著名的開源GIS數(shù)據(jù)庫。

1. 簡單理解：PostGIS是PGSQL的一個擴展，既然是擴展，那就需要進(jìn)行安裝，而不是直接就帶著的
2. PostGIS類似Oracle上的spatial（沒接觸過Oracle中的GIS）
3. PGSQL如果想用地理位置相關(guān)的功能，就要安裝PostGIS
4. 更詳細(xì)介紹，推薦參閱：【PostGIS教程一：PostGIS介紹】

SRID

沒錯，第一次接觸的時候我也是一臉懵逼，這是個啥？這么多怎么選？？？而且大多沒有深入的介紹，都是一些草草描述。以下摘自百度百科。。。

SRID：Spatial Reference System Identifier，理解為空間引用標(biāo)識符，或者說空間坐標(biāo)系？

空間引用標(biāo)識系統(tǒng)是由歐洲石油測繪組 (EPSG) 標(biāo)準(zhǔn)定義的，它是為繪圖、測繪以及大地測量數(shù)據(jù)存儲而開發(fā)的一組標(biāo)準(zhǔn)。該標(biāo)準(zhǔn)歸石油天然氣生產(chǎn)商 (OGP) 測繪和定位委員會所有。

幾何圖形實例默認(rèn) SRID 為零（就是幾何圖形！什么點線面體狼神的）

PGSQL中 geometry 實例的默認(rèn) SRID 為 0。利用 geometry 空間數(shù)據(jù)，執(zhí)行計算是不需要空間實例的指定 SRID 的；因此，實例可駐留在未定義的平面空間。

地域?qū)嵗仨毷褂弥С值?SRID

PGSQL支持基于 EPSG 標(biāo)準(zhǔn)的 SRID。必須使用 geography 實例的支持 PGSQL 的 SRID 執(zhí)行計算或?qū)⒎椒ㄓ糜诘赜?a target="_blank">空間數(shù)據(jù)。SRID 必須與 sys.spatial_reference_systems 目錄視圖中顯示的 SRID 中的一個匹配。如前所述，在使用 geography 數(shù)據(jù)類型對空間數(shù)據(jù)執(zhí)行計算時，結(jié)果將取決于在創(chuàng)建數(shù)據(jù)時使用的是哪個橢圓體，因為為每個橢圓體都分配了一個特定空間引用標(biāo)識符 (SRID)。

對 geography 實例使用方法時，PGSQL使用等于 4326 的默認(rèn) SRID，它將映射到 WGS84 空間引用系統(tǒng)。如果要使用 WGS84 （或 SRID 4326）之外的某個空間引用系統(tǒng)中的數(shù)據(jù)，您需要確定地域空間數(shù)據(jù)的特定 SRID。

WGS84、GCJ02

關(guān)于這個，相信接觸過位置開發(fā)的小伙伴都多少了解過，為什么坐標(biāo)定位會有偏移？那可能你的GCJ02（國測局02年制定的坐標(biāo)系，又被大家稱為火星坐標(biāo)系）沒有轉(zhuǎn)成WGS84導(dǎo)致的。更多的內(nèi)容請自行了解一下。這個對于位置的計算來說很重要。

總結(jié)來說就是：

1. 來自GPS的信號的坐標(biāo)基本都是WGS84坐標(biāo)系（Android和iOS都不會轉(zhuǎn)的，需要自己手動轉(zhuǎn)換）
2. 對于微信來說，來自騰訊地圖上的坐標(biāo)（不論是點選的還是怎么著的），都是加密后的GCJ02坐標(biāo)系
3. 對于微信公眾號來說，公眾號開啟的獲取用戶位置功能返回的就是WGS84的坐標(biāo)（惡不惡心？可能對于地圖服務(wù)商來說不準(zhǔn)提供WGS84的坐標(biāo)，但是手機GPS獲取的都是WGS84的坐標(biāo)，所以就會出現(xiàn)這種情況啦?。?/li>

開始入門

一、PostGIS的安裝

常規(guī)流程請參考：【PostGIS教程二：PostGIS安裝和創(chuàng)建空間數(shù)據(jù)庫】
首先進(jìn)行本環(huán)節(jié)前默認(rèn)你已經(jīng)安裝了PostgreSQL了！如果你的StackBuilder可以正常安裝，那么可以直接通過StackBuilder安裝的（我的不行，各種報錯），所以這里提供給你一個補救措施：手動安裝步驟

1. 有興趣的可以前往PostGIS官網(wǎng)下載頁面查看擴展安裝包的安裝說明：【傳送門】
2. 找到下載頁面上如圖的位置，下載離線的exe安裝文件

image.png

沒啥興趣的話，請直接打開這個官網(wǎng)的下載頁面,根據(jù)你的PGSQL版本下載對應(yīng)的.exe即可，一般都可以安裝最新版的PostGIS
下載后運行exe安裝即可。在設(shè)置安裝組件時，最好選擇"Create spatial database"創(chuàng)建一個新的模板庫，以便在創(chuàng)建數(shù)據(jù)庫時可以以此作為模板。當(dāng)然創(chuàng)建在哪里其實沒有多大影響，在當(dāng)前數(shù)據(jù)庫或者新建一個都可以。

二、啟用PostGIS擴展

在安裝好PostGIS擴展后，是需要在數(shù)據(jù)庫中啟用該擴展才可以生效的，官網(wǎng)描述：

PostGIS is an optional extension that must be enabled in each database you want to use it in before you can use it. Installing the software is just the first step. DO NOT INSTALL it in the database called postgres.

Connect to your database with psql or PgAdmin. Run the following SQL. You need only install the features you want:

就是說安裝完EXE擴展后，你還要在你需要開啟GIS特性的每一個數(shù)據(jù)庫中都根據(jù)你的需要運行如下SQL去開啟GIS特性。（但不要在postgres這個數(shù)據(jù)庫中開啟，這個是安裝PG時候自動創(chuàng)建的庫）

-- Enable PostGIS (as of 3.0 contains just geometry/geography)
-- 啟用PostGIS功能（僅包括geometry/geography相關(guān)）
CREATE EXTENSION postgis;

-- enable raster support (for 3+)
-- 啟用柵格擴展
CREATE EXTENSION postgis_raster;

-- Enable Topology
-- 啟用拓?fù)鋽U展
CREATE EXTENSION postgis_topology;

-- Enable PostGIS Advanced 3D
-- and other geoprocessing algorithms
-- sfcgal not available with all distributions
CREATE EXTENSION postgis_sfcgal;
-- fuzzy matching needed for Tiger
CREATE EXTENSION fuzzystrmatch;
-- rule based standardizer
CREATE EXTENSION address_standardizer;
-- example rule data set
CREATE EXTENSION address_standardizer_data_us;
-- Enable US Tiger Geocoder
CREATE EXTENSION postgis_tiger_geocoder;

通過執(zhí)行SELECT postgis_full_version();可以查看當(dāng)前啟用的PostGIS版本

三、快樂初體驗

在啟用了GIS的數(shù)據(jù)庫中如下開始體驗：

-- Create table with spatial column
-- 創(chuàng)建一個表mytable，包含了一個空間列g(shù)eom，格式是GEOMETRY(Point,26910)
-- 這里的26910是一個SRID，稍后會做解釋
CREATE TABLE mytable (
  id SERIAL PRIMARY KEY,
  geom GEOMETRY(Point, 26910),
  name VARCHAR(128)
);
 
-- Add a spatial index
-- 給geom列添加一個空間索引，索引類型為GIST
CREATE INDEX mytable_gix
  ON mytable
  USING GIST (geom);
 
-- Add a point
-- 插入一個數(shù)據(jù)點
INSERT INTO mytable (geom) VALUES (
  ST_GeomFromText('POINT(0 0)', 26910)
);
 
-- Query for nearby points
-- 查詢與給定點周圍1000米的點
SELECT id, name
FROM mytable
WHERE ST_DWithin(
  geom,
  ST_GeomFromText('POINT(0 0)', 26910),
  1000
);

講到這里，你基本就是已經(jīng)可以開始快樂的玩耍了。但接下來，對于一個剛接觸的小白白來說才是開始

應(yīng)用舉例：嘗試插入一批位置點并查詢附近的點的過程總結(jié)

這里就不過多描述從哪弄這些點了（推薦一下騰訊地圖開放平臺的地圖云？可以直接自己在可視化上搞點兒數(shù)據(jù)然后API接口拉下來即可）

關(guān)于SRID

很多盆友都會遇到SRID的問題，如何選擇SRID讓我頭疼了許久，也踩了許多坑。上面舉例中用的SRID是26910，有的例子中最開始用的SRID是26918，而對于我們GPS返回的坐標(biāo)來說，使用的是WGS84坐標(biāo)系，對應(yīng)的SRID應(yīng)該是4326的地理坐標(biāo)系。

所以建議在數(shù)據(jù)庫中保存GPS坐標(biāo)時使用4326的SRID進(jìn)行保存，這樣在地理位置計算上可以做到更加統(tǒng)一。

坐標(biāo)為"地理（geographics）"形式或者說是" 緯度（latitude）/經(jīng)度（longitude）"形式的數(shù)據(jù)非常常見。
與Mercator（墨卡托）、UTM（通用橫軸墨卡托）、Stateplane中的坐標(biāo)不同，地理坐標(biāo)不是笛卡爾平面坐標(biāo)（Cartesian coordinates）。地理坐標(biāo)并不表示平面上與原點的線性距離，相反，這些球坐標(biāo)描述了地球上的角坐標(biāo)。在球坐標(biāo)中，點由該點與參考子午線（經(jīng)度）的旋轉(zhuǎn)角度和該點與赤道的角度（緯度）指定。

你可以將地理坐標(biāo)看作近似的笛卡爾平面坐標(biāo)，并繼續(xù)進(jìn)行空間計算，然而，關(guān)于距離、長度和面積的測量將會是毫無意義的。由于球坐標(biāo)測量角度距離，因此單位以"度"表示。此外，索引和真/假測試（如相交和包含）可能會變得非常錯誤，因為越與極點或國際日期線接近的區(qū)域，點與點之間的距離變得越大。

更多關(guān)于SRID和地理（geography）的原理解釋可參閱知乎怪叔叔不睡覺的專欄：【PostGIS教程十二：地理】??強烈推薦參閱！??！茅廁頓開那種哦?。。?！

這里只給你做一個不是那么正確但是便于理解的建議：

1. 數(shù)據(jù)庫中存儲GPS位置（Point）或者范圍等內(nèi)容的列建議設(shè)置SRID為4326？
2. 這些列中存儲的GPS數(shù)據(jù)需要在存儲前轉(zhuǎn)換為WGS84坐標(biāo)系再存儲，不要存GCJ02的坐標(biāo)！
3. 由于4326中規(guī)定的度量單位是度（經(jīng)度、緯度的那個度），而不是我們常用的米，所以在計算點的位置關(guān)系的時候，根據(jù)需要進(jìn)行SRID的轉(zhuǎn)換（比如計算GPS點之間在地球上的距離等場景），比如需要將SRID4326的坐標(biāo)點Point轉(zhuǎn)換為geography格式，這時再通過ST_DWithin函數(shù)查詢周邊范圍的時候就是以米為單位了。

那有童鞋會問了，為什么不在存儲的時候就存儲成地理位置坐標(biāo)系（geography），而是存儲為SRID=4326的geometry呢？因為對于geography來說，相較于geometry會少很多空間計算的函數(shù)，在你深入了解號geograhy和geometry之間的曲別和聯(lián)系后，你就可以根據(jù)場景靈活的選擇使用哪種空間類型了。對于入門的我們來說，統(tǒng)一使用geometry不是什么壞處，畢竟它和geography之間可以直接轉(zhuǎn)換！

比如：ST_X()這個函數(shù)，只支持geometry類型，如果我們存儲的是geography的話，需要獲取這個點的X坐標(biāo)，需要先將其轉(zhuǎn)換為geometry。。。

推薦閱讀

如果想深入了解GIS相關(guān)的知識，再次推薦上面的知乎專欄哦：

空間數(shù)據(jù)庫專欄
專欄目錄