基于手机信令的大数据分析教程（十）——数据库中计算每个街道的平均通勤距离

本节重点：职住数据与地理位置和坐标信息连接，计算基站点间直线距离，然后求每个街道的平均通勤距离

一、职住数据与地理位置和坐标信息连接

在教程（五）中，我们已经得到含有每个基站街道位置信息的od表格如下图：

QQ截图20190106192658.png

执行以下语句，去除职住基站相同的项，删除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坐标（经度和纬度），并将该基站shapefile存在英文路径下
用Qgis将上述shp文件导入数据库，具体方法见教程（二）
导入成功后，在数据库中打开该表如下图：

QQ截图20181209181101.png

依次执行以下语句，将之前的职住表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

二、计算基站点间直线距离

执行以下语句，计算基站点间直线距离
其中：
起点的经纬度：h_x, h_y
终点的经纬度：w_x, w_y
4326是投影坐标系的空间引用识别号，WGS84坐标系
32649中最后两位参数，根据城市所在经度区位修改，例如：上海是51，南宁是48，西安这里用的是49
上述查询的含义就是：两个4326坐标系下的点对象转换成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

关于计算两点之间直线距离，可见施老师教程：
https://www.jianshu.com/p/9e3cbf447c4f

三、求每个街道的平均通勤距离

依次执行以下语句，基于居住街道汇总，并增加字段计算距离的加权平均，最终得到每个街道居住者的平均通勤距离如下表，同理可计算每个街道工作者的平均通勤距离

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中街道面上，得到西安街道层面的平均通勤距离分布图（略）

最后编辑于：2019.01.06 19:46:32