本節(jié)重點：職住數(shù)據(jù)與地理位置和坐標信息連接，計算基站點間直線距離，然后求每個街道的平均通勤距離

一、職住數(shù)據(jù)與地理位置和坐標信息連接

在教程（五）中，我們已經(jīng)得到含有每個基站街道位置信息的od表格如下圖：

QQ截圖20190106192658.png

執(zhí)行以下語句，去除職住基站相同的項，刪除home和work基站匹配不到位置信息的項，得到下表

CREATE TABLE hw_location2 AS
SELECT * FROM hw_location
WHERE home!=work

CREATE TABLE hw_location3 AS
SELECT * FROM hw_location2
WHERE h_qxmc!='' AND w_qxmc!=''

QQ截圖20190106194056.png

先在GIS中打開基站的屬性表，添加兩個字段，分別在計算幾何中計算基站的X和Y坐標（經(jīng)度和緯度），并將該基站shapefile存在英文路徑下
用Qgis將上述shp文件導入數(shù)據(jù)庫，具體方法見教程（二）
導入成功后，在數(shù)據(jù)庫中打開該表如下圖：

QQ截圖20181209181101.png

依次執(zhí)行以下語句，將之前的職住表hw_location3與基站XY信息連接，得到下表

CREATE TABLE hw_location5 AS
SELECT hw_location3.*, x AS h_x,y AS h_y
FROM hw_location3 LEFT JOIN jizhan on home=jzbm

CREATE TABLE hw_location6 AS
SELECT hw_location5.*, x AS w_x,y AS w_y
FROM hw_location5 LEFT JOIN jizhan on work=jzbm

QQ截圖20181209182624.png

二、計算基站點間直線距離

執(zhí)行以下語句，計算基站點間直線距離
其中：
起點的經(jīng)緯度：h_x, h_y
終點的經(jīng)緯度：w_x, w_y
4326是投影坐標系的空間引用識別號，WGS84坐標系
32649中最后兩位參數(shù)，根據(jù)城市所在經(jīng)度區(qū)位修改，例如：上海是51，南寧是48，西安這里用的是49
上述查詢的含義就是：兩個4326坐標系下的點對象轉(zhuǎn)換成32649坐標系后計算直線距離，這個距離與地面實際距離很接近

SELECT *,
    st_distance (
    st_transform (
    st_setsrid ( st_point ( h_x, h_y ), 4326 ),
    32649 
    ),
    st_transform (
    st_setsrid ( st_point ( w_x, w_y ), 4326 ),
    32649 
    )) AS dis INTO distance_result1 
FROM
    hw_location6

成功后得到新表如下，可見基站間距離已計算出來

QQ截圖20181209183301.png

關(guān)于計算兩點之間直線距離，可見施老師教程：
http://www.itdecent.cn/p/9e3cbf447c4f

三、求每個街道的平均通勤距離

依次執(zhí)行以下語句，基于居住街道匯總，并增加字段計算距離的加權(quán)平均，最終得到每個街道居住者的平均通勤距離如下表，同理可計算每個街道工作者的平均通勤距離

CREATE TABLE hw_dis_jd AS
SELECT h_jdmc ,SUM(dis*sum) AS dis_sum,SUM(sum) AS num_sum
FROM distance_result1
GROUP BY h_jdmc

ALTER TABLE hw_dis_jd 
ADD dis_avg numeric

UPDATE hw_dis_jd  
SET dis_avg = (dis_sum/num_sum)

QQ截圖20181209190928.png

最后可以將該表連接到GIS中街道面上，得到西安街道層面的平均通勤距離分布圖（略）