根据百度新闻引擎检索得到各城市的地面塌陷事故,进行统计探索分析和空间冷热点分析后所做的可视化效果
后台:django + mysql
塌陷新闻events的表结构如下:
序号 |
字段名 |
标识符 |
类型及长度 |
有无空值 |
主键 |
索引序号 |
1 |
新闻ID |
ID |
int |
无 |
Y |
1 |
2 |
时间 |
Date |
date |
无 |
|
|
3 |
省 |
Province |
char(10) |
无 |
|
|
4 |
市 |
City |
char(10) |
无 |
|
|
5 |
县 |
District |
char(10) |
有 |
|
|
6 |
伤亡人数 |
Casualty |
int |
无 |
|
|
7 |
事故原因 |
Reason |
char(20) |
无 |
|
|
8 |
链接 |
Link |
date |
无 |
|
|
9 |
地址 |
Adress |
char(50) |
有 |
|
|
其中,事故原因的分类包括:
- 建设工程 (地铁施工、管线施工……)
- 自身结构隐患
- 环境因素 (暴雨……)
- 管理缺陷
- 原因不明
如样例新闻可提取为:
对表格的统计分析则通过视图的方式,视图的结构包括:
- events_reason
- month-countAll
- month-countLy
- month-countAvgm
- month-countAvgy
- ranking-list
- region-count
CREATE VIEW `events_reason` AS SELECT
Reason as Reason,
COUNT(*) as ReasonCounts
FROM original_events
GROUP BY Reason
序号 |
字段名 |
标识符 |
类型及长度 |
有无空值 |
1 |
事故原因 |
Reason |
date |
无 |
2 |
事故数量 |
ReasonCounts |
int |
无 |
CREATE VIEW `month-countAll` AS
select
DATE_FORMAT(NewsT,'%m') as month,
count(*) as AllCounts
FROM original_events
GROUP BY DATE_FORMAT(NewsT,'%m')
ORDER BY DATE_FORMAT(NewsT,'%m')
序号 |
字段名 |
标识符 |
类型及长度 |
有无空值 |
1 |
月份 |
month |
int |
无 |
2 |
历史总量 |
AllCounts |
int |
无 |
CREATE VIEW `month-countLy` AS
select
DATE_FORMAT(NewsT,'%m') as month,
count(*) as LyCounts
FROM original_events
GROUP BY DATE_FORMAT(NewsT,'%m')
ORDER BY DATE_FORMAT(NewsT,'%m')
序号 |
字段名 |
标识符 |
类型及长度 |
有无空值 |
1 |
月份 |
month |
int |
无 |
2 |
数量 |
LyCounts |
int |
无 |
CREATE VIEW `month-countAvgm` AS
SELECT
DATE_FORMAT(NewsT,'%m') as month,
count(*)
/
(SELECT count(DATE_FORMAT(NewsT,'%Y'))
FROM original_events)
as AvgmCounts
FROM original_events
GROUP BY DATE_FORMAT(NewsT,'%m')
ORDER BY DATE_FORMAT(NewsT,'%m')
序号 |
字段名 |
标识符 |
类型及长度 |
有无空值 |
1 |
月份 |
month |
int |
无 |
2 |
平均数量 |
AvgmCounts |
float(3) |
无 |
CREATE VIEW `month-countAvgy` AS
SELECT
count(*)
/
(
SELECT
count(distinct DATE_FORMAT(NewsT,'%Y'))
FROM original_events
)
as Avgycounts
FROM original_events
序号 |
字段名 |
标识符 |
类型及长度 |
有无空值 |
1 |
平均全年总数 |
Avgycounts |
float(3) |
无 |
CREATE VIEW `ranking-list` AS
SELECT
District as district,
count(*) as DistrictCounts
FROM original_events
GROUP BY District
序号 |
字段名 |
标识符 |
类型及长度 |
有无空值 |
1 |
区域 |
district |
char(10) |
无 |
2 |
数量 |
DistrictCounts |
int |
无 |
CREATE VIEW `region-count` AS
SELECT
Province as province,
count(*) as RegionCounts
FROM original_events
GROUP BY Province
序号 |
字段名 |
标识符 |
类型及长度 |
有无空值 |
1 |
省份 |
province |
char(10) |
无 |
2 |
数量 |
RegionCounts |
int |
无 |