基于高德大数据的人流迁徙分析——以武汉城市圈为例（一）

作者: LINDADADADA | 来源:发表于2020-04-01 23:15 被阅读0次

失踪人口回归！

人口迁徙联系.jpg

在这个新专题中，我想试着探讨一下怎么运用开源大数据来作分析，毕竟很多数据是很难拿到的，我们刚开始做一些小研究，可以先从学会利用开源数据开始。
好了那我们进入正题吧~本章我想来试试用高德迁徙大数据做人流方面的分析。为什么要用高德大数据呢，最近发现腾讯迁徙似乎看不了了，百度大数据只有近期的，没有历史数据，于是就选取了高德。
本章需要用到的同样是数据库、Navicat、ArcGIS，这些在手机信令专题中提到过的软件，以及一些基础操作讲过的我就不再重复了哦~

一、数据的获取、导入数据库

本次数据通过高德地图提供的大数据网站，中国主要城市迁徙意愿排行榜：https://trp.autonavi.com/migrate/page.do
在这里可以查询到某一时间对于某一城市的迁入、迁出城市指数，这是一个相对值

QQ截图20200401222309.png

QQ截图20200401222507.png

其实这批数据是我请Python大神同学帮忙爬取的，所以，也许等到我弄明白爬取的方法才能来补充这一部分了。。。
武汉城市圈（Wuhan Metropolitan Area），又称武汉“1+8”城市圈，是指以中国中部最大城市武汉为圆心，覆盖黄石、鄂州、黄冈、孝感、咸宁、仙桃、潜江、天门等周边8个大中型城市所组成的城市群。
本次数据以武汉城市圈为研究范围，分别以9个城市为搜索对象，爬取各城市从2018.6-2019年全年每一天的迁入、迁出指数（前30名城市的），得到18个原始数据表格，结构举例如下：鄂州市的迁入数据ezhou_in（这里我们只用真实的迁徙指数）

这里我已经把原始数据都通过Navicat 导入了数据库，方法可参照手机信令专题。当然也可以直接右键导入

QQ截图20200401223819.png

QQ截图20200401223948.png

数据导入中需要注意两个点：

1、编码

之前我也遇到过这个问题，一般编码我们默认用UTF-8，但有时候数据导入或导出数据库后中文变成乱码，这时我选择简体中文这一项就好了。

2、各字段数据类型

导入时要注意选择正确的数据类型，以免影响后期计算，例如代码可以用int，日期用date，迁徙指数用float。如果已经导入，也可以通过在表上右键选择“设计表”，或者通过代码转变数据类型。

image.png

二、数据在数据库中处理

2.1数据筛选处理

我先是筛选出2019年的数据，代码如下：

CREATE TABLE ezhou_in2 AS
select name,realindex as realidx_in,date from ezhou_in 
where date>= '2019-01-01' and date <= '2019-12-31'

我只选出几个有用的字段：迁入（出）的城市名称、真实迁徙指数（后简称迁徙指数）、日期，要注意日期范围的查询方式，这样每个表都重复一遍代码操作即可，筛选后如下：鄂州迁入

接着，为了研究武汉城市圈日常的迁徙特征，我需要排除春运、法定节假日的数据，代码如下，同样的重复每个表进行操作：

DELETE from ezhou_in2
where date='2019-01-01' or (date>='2019-01-21' and date<='2019-03-01')
or (date>='2019-04-05' and date<='2019-04-07') or (date>='2019-05-01' and date<='2019-05-03')
or (date>='2019-06-07' and date<='2019-06-09') or (date>='2019-09-13' and date<='2019-09-15')
or (date>='2019-10-01' and date<='2019-10-07')

那么对于日常的数据，我们可能还要分出工作日、周末进行研究，所以需要给日期挂上星期几的字段，这就要用到extract函数，代码如下，这几组代码依次运行哦：

ALTER TABLE ezhou_in2  ADD COLUMN day VARCHAR(50)

UPDATE ezhou_in2  
SET day=extract(dow from date)

ALTER TABLE ezhou_out2  ADD COLUMN day VARCHAR(50)

UPDATE ezhou_out2  
SET day=extract(dow from date)

QQ截图20200401231021.png

这样就加上了星期几的字段，可以用来进行进一步筛选。当然这里我打算先用所有数据试试，所以暂且不区分工作日和周末。

2.2处理成为OD格式的数据

首先汇总武汉城市圈内各城市所有天的总迁徙指数：

CREATE TABLE ezhou_in_sum as
SELECT name,sum(realidx_in) as realidx_in FROM ezhou_in2
where name='武汉' or name='黄石' or name='黄冈' or name='鄂州' or name='咸宁' or name='孝感' or name='潜江' or name='天门' or name='仙桃'
GROUP BY name ORDER BY realidx_in DESC

这里我用到order by对realidx_in字段进行排序，加上DESC表示降序排序，这样把所有的汇总完：

QQ截图20200403134050.png

QQ截图20200403134204.png

2019共365天，这里汇总的是排除节假日60天后剩余的共305天的真实迁徙指数。汇总完会发现，由于我们爬取的只是前30位城市迁入迁出，可能对于某些城市来说武汉城市圈的另外某一城市未出现在该城市的前30榜单中，这就会导致数据有所缺失。由于排名前30外的迁徙指数非常小了，这里我将其记为0。例如天门没有出现在黄冈的数据表中，这里我手动添加一下（当然也可以最后再添）：

INSERT INTO huanggang_in_sum VALUES ('天门',0)

INSERT INTO huanggang_out_sum VALUES ('天门',0)

由于迁入、迁出是单向的数据，而我们最后OD图体现的城市两两之间是双向的总联系度，因此接下来我们要计算双向联系度total。

CREATE TABLE ezhou_total AS
SELECT ezhou_in_sum.name,realidx_in, realidx_out
from ezhou_in_sum LEFT JOIN ezhou_out_sum on ezhou_in_sum.name=ezhou_out_sum.name

ALTER TABLE ezhou_total  ADD COLUMN total float(8)

UPDATE ezhou_total  
SET total=realidx_in+realidx_out

这里用到left join函数来将迁入、迁出的两个表格数据，利用一个字段连接合到一个新表中。这里我已经能保证迁入、迁入两个表都包含了武汉城市圈的城市，如果两个表的城市不同（一个有一个没有），那么应该用full outer join。另外我还用到alter table来添加字段，用update来将计算结果添加到该字段中。
接下来还要构建一个拼接字段，例如：鄂州市 - 武汉市，方便最后与ArcGIS中构建的OD期望线进行连接：

ALTER TABLE ezhou_total  ADD COLUMN od VARCHAR(50)

UPDATE ezhou_total  
SET od='鄂州市' ||' - '|| name || '市' ||''

QQ截图20200403140547.png

最后得到的表格结构如上图，同样重复操作后得到9个城市的汇总表，然后合并这些汇总表：

CREATE TABLE whcsq_total AS
select od,realidx_in,realidx_out,total from wuhan_total
UNION ALL
select od,realidx_in,realidx_out,total from ezhou_total
UNION ALL
select od,realidx_in,realidx_out,total from huanggang_total
UNION ALL
select od,realidx_in,realidx_out,total from huangshi_total
UNION ALL
select od,realidx_in,realidx_out,total from xianning_total
UNION ALL
select od,realidx_in,realidx_out,total from xiaogan_total
UNION ALL
select od,realidx_in,realidx_out,total from qianjiang_total
UNION ALL
select od,realidx_in,realidx_out,total from tianmen_total
UNION ALL
select od,realidx_in,realidx_out,total from xiantao_total

这里用到union all函数，是可以一次性把多个结构相同的表合并的，得到一个新表whcsq_total如下图：

QQ截图20200403141004.png

然后我还需要把O的城市和D的城市分开来，方便最后在调节显示时构建字段查询。

CREATE TABLE whcsq_total2 AS
SELECT whcsq_total.*, split_part(od,' - ', 1) as o_ds,split_part(od,' - ', 2) as d_ds
FROM whcsq_total

这里就用到split_part基于' - '进行分列。

QQ截图20200403141359.png

如上表所示，将total字段降序排序后发现，会有两两重复的数据，例如'武汉市 - 孝感市'和'孝感市 - 武汉市'是一样的值。这是因为对于武汉市的表来说已经计算了武汉市和孝感市之间的总联系度，而对于孝感市的表中也重复计算了一次。
最后连接到GIS的期望线会由于两线重合不影响显示，但也可以事先把重复数据删除。如果直接连接到GIS就在GIS中连接数据库即可获得该表（具体操作参见手机信令专题中的GIS连接数据库），如果删除重复数据就进入到下一步：