用 PostgreSQL 做地理定位 • PostgreSQL 的藝術

前一章載入了 OpenStreetMap 的興趣點——英國的酒吧。本章要示範在單一 SQL 查詢裡替 IP 位址做地理定位並找出最近的酒吧！為此，我們使用 RhodiumToad 開發的出色擴充套件 ip4r。

載入地理定位資料#

第一步是找地理定位資料庫，有多家供應商提供。本例選用 MaxMind 的免費資料庫（GeoLite Free Downloadable Databases）。看過檔案內容後，先建立目標 schema，再用 pgloader 載入壓縮檔：

create extension if not exists ip4r;
create schema if not exists geolite;

create table if not exists geolite.location
(
   locid      integer primary key,
   country    text,
   region     text,
   city       text,
   postalcode text,
   location   point,
   metrocode  text,
   areacode   text
);

create table if not exists geolite.blocks
(
   iprange    ip4r,
   locid      integer
);

create index blocks_ip4r_idx on geolite.blocks using gist(iprange);

pgloader 命令描述了檔案格式，讓 pgloader 解析 CSV 並在記憶體中轉換成 PostgreSQL 期待的格式：

CSV 裡的位置是 latitude、longitude 兩個獨立欄位，轉換成單一 point 欄位。
IP 位址範圍原本是一對整數，轉換成更經典的表示法：(ip-range "16777216" "16777471") 得到 "1.0.0.0-1.0.0.255"。
命令會自動比對檔名找出要載入的檔案，不依賴實際目錄名（如 GeoLiteCity_20180327）——GeoLite 出新版時重跑一次 pgloader 就能載入新資料。

延伸範例：完整的 pgloader LOAD ARCHIVE 命令

/*
 * Loading from a ZIP archive containing CSV files.
 */

LOAD ARCHIVE
   FROM http://geolite.maxmind.com/download/geoip/database/GeoLiteCity_CSV/GeoLiteCity-latest.zip
   INTO postgresql://appdev@/appdev

     BEFORE LOAD EXECUTE 'geolite.sql'

     LOAD CSV
          FROM FILENAME MATCHING ~/GeoLiteCity-Location.csv/
               WITH ENCODING iso-8859-1
               (
                  locId,
                  country,
                  region     [ null if blanks ],
                  city       [ null if blanks ],
                  postalCode [ null if blanks ],
                  latitude,
                  longitude,
                  metroCode  [ null if blanks ],
                  areaCode   [ null if blanks ]
               )
          INTO postgresql://appdev@/appdev
          TARGET TABLE geolite.location
               (
                  locid,country,region,city,postalCode,
                  location point using (format nil "(~a,~a)" longitude latitude),
                  metroCode,areaCode
               )
          WITH skip header = 2,
               drop indexes,
               fields optionally enclosed by '"',
               fields escaped by double-quote,
               fields terminated by ','

  AND LOAD CSV
          FROM FILENAME MATCHING ~/GeoLiteCity-Blocks.csv/
               WITH ENCODING iso-8859-1
               (
                  startIpNum, endIpNum, locId
               )
          INTO postgresql://appdev@/appdev
          TARGET TABLE geolite.blocks
               (
                  iprange ip4r using (ip-range startIpNum endIpNum),
                  locId
               )
          WITH skip header = 2,
               drop indexes,
               fields optionally enclosed by '"',
               fields escaped by double-quote,
               fields terminated by ',';

執行 pgloader --verbose geolite.load 的摘要：geolite.location 匯入 928,138 列（46.4 MB，約 21 秒）、geolite.blocks 匯入 2,108,310 列（67.4 MB，約 31 秒），總計 2 分 30 秒。

pgloader 會在載入前先卸除索引，資料載入完再重建——而且與下一張表的載入平行進行。在規格好的伺服器上，這種平行處理帶來的效益非常可觀。

載入完成後，可用的資料表如下：

                     List of relations
 Schema  │   Name   │ Type  │ Owner  │ Size  │ Description
═════════╪══════════╪═══════╪════════╪═══════╪═════════════
 geolite │ blocks   │ table │ appdev │ 89 MB │
 geolite │ location │ table │ appdev │ 64 MB │
(2 rows)

在範圍中查找 IP 位址#

先看看主要資料長什麼樣。TABLE 命令是 SQL 標準，不妨直接用：

table geolite.blocks limit 10;

       iprange       │ locid
═════════════════════╪════════
 1.0.0.0/24          │ 617943
 1.0.1.0-1.0.3.255   │ 104084
 1.0.4.0/22          │     17
 1.0.8.0/21          │  47667
 1.0.64.0-1.0.81.255 │ 885221
 ...
(10 rows)

ip4r 的輸出函式很聰明：能用 CIDR 表示的範圍就用 CIDR，不適用時就退回一般的 start-end 表示。ip4r 提供多個運算子，其中一些受剛建的 GiST 索引支援。純為樂趣，用一個系統目錄查詢把它們列出來——join 運算子類別（operator class）目錄，依運算子家族的概念找出 ip4r 型別搭配 GiST 存取方法的運算子：

select amopopr::regoperator
  from pg_opclass c
       join pg_am am on am.oid = c.opcmethod
       join pg_amop amop on amop.amopfamily = c.opcfamily
 where opcintype = 'ip4r'::regtype and am.amname = 'gist';

    amopopr
════════════════
 >>=(ip4r,ip4r)
 <<=(ip4r,ip4r)
 >>(ip4r,ip4r)
 <<(ip4r,ip4r)
 &&(ip4r,ip4r)
 =(ip4r,ip4r)
(6 rows)

（當然用 psql 的 \dx+ ip4r 也行，但這個查詢直接列出 GiST 索引會解的運算子。）>>= 讀作「包含（contains）」，正是我們要用的：

select iprange, locid
  from geolite.blocks
 where iprange >>= '91.121.37.122';

         iprange          │ locid
══════════════════════════╪═══════
 91.121.0.0-91.121.71.255 │    75

拜專用 GiST 索引之賜，這個查找不到一毫秒。

地理定位中繼資料#

在 MaxMind 的 schema 裡，有趣的資料其實在另一張表 geolite.location。換一個 IP——unix 的 host 命令說 google.us 的位址是 74.125.195.147，查查它來自哪裡：

select *
 from      geolite.blocks
      join geolite.location using(locid)
where iprange >>= '74.125.195.147';

─[ RECORD 1 ]───────────────────────────
locid      │ 2703
iprange    │ 74.125.191.0-74.125.223.255
country    │ US
region     │ CA
city       │ Mountain View
postalcode │ 94043
location   │ (-122.0574,37.4192)
metrocode  │ 807
areacode   │ 650

資料把 Google 的 IP 定位在 Mountain View，相當可信。而且 location 是同時含經緯度的 point 型別，可以直接畫上地圖。

緊急酒吧#

想做一個幫迷途羔羊找最近酒吧的應用嗎？既然能從瀏覽器的 IP 位址得知使用者位置，應該不難吧？我們的酒吧清單來自英國，所以挑一個英國的 IP：

$ host www.ox.ac.uk
www.ox.ac.uk has address 129.67.242.154
www.ox.ac.uk has address 129.67.242.155

查這個 IP 的地理位置：

select *
  from      geolite.location l
       join geolite.blocks using(locid)
 where iprange >>= '129.67.242.154';

─[ RECORD 1 ]─────────────
locid      │ 375290
country    │ GB
region     │ K2
city       │ Oxford
postalcode │ OX1
location   │ (-1.25,51.75)
iprange    │ 129.67.0.0/16

牛津大學看來確實在牛津。那麼剛踏出牛津大學，最近的十間酒吧是哪些？趁還沒渴壞趕快查：

   select pubs.name,
          round((pubs.pos <@> l.location)::numeric, 3) as miles,
          ceil(1609.34 * (pubs.pos <@> l.location)::numeric) as meters

     from geolite.location l
          join geolite.blocks using(locid)
          left join lateral
           (
               select name, pos
                 from pubnames
             order by pos <-> l.location
                limit 10
           ) as pubs on true

    where blocks.iprange >>= '129.67.242.154'
 order by meters;

        name        │ miles │ meters
════════════════════╪═══════╪════════
 The Bear           │ 0.268 │    431
 The Half Moon      │ 0.280 │    451
 The Wheatsheaf     │ 0.295 │    475
 The Chequers       │ 0.314 │    506
 The Old Tom        │ 0.315 │    507
 Turl Bar           │ 0.321 │    518
 St Aldate's Tavern │ 0.329 │    530
 The Mad Hatter     │ 0.337 │    542
 King's Arms        │ 0.397 │    639
 White Horse        │ 0.402 │    647
(10 rows)

用 PostgreSQL 加上幾個唾手可得的擴充套件，就能在單一 SQL 查詢內完成進階地理定位查找；查詢時間介於 1ms 到 6ms，這個技巧完全可以在正式環境直接用即時查詢服務使用者請求。