本节重点：职住数据与地理位置和坐标信息连接，计算基站点间直线距离，然后求每个街道的平均通勤距离
一、职住数据与地理位置和坐标信息连接
在教程（五）中，我们已经得到含有每个基站街道位置信息的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中街道面上，得到西安街道层面的平均通勤距离分布图（略）
网友评论