GEO地图数据分布处理

news/2025/2/9 6:09:15 标签: MySQL, 地图

需求

已知访问用户经纬度数据，根据经纬度统计每个省的访问总数

地区经纬度分布表

CREATE TABLE `area_geo` (
  `id` int(11) NOT NULL,
  `name` varchar(250) NOT NULL COMMENT '地区名',
  `ext_name` varchar(255) NOT NULL COMMENT '地区扩展名',
  `geo` geometry NOT NULL COMMENT '地区核心经纬度',
  `polygon` geometry NOT NULL COMMENT '地区经纬度范围',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

地区经纬度初始化数据

见文档顶端附件资源

定时任务&存储过程统计

基于地图数据统计的性能问题，考虑通过MySQL定时任务单独将地图数据进行统计结果保存，然后通过DBT脚本关联输出到数据仓库，创建访问数据存储表、存储过程、和定时任务

--开启定时器（或者通过MySQL配置永久性开启）
set global event_scheduler=1;
--查看开启情况，是否为ON
show variables like '%event_sche%';

--存储过程创建
DROP PROCEDURE IF EXISTS `area_geo_visit_data_pro`;
delimiter ==
CREATE PROCEDURE `area_geo_visit_data_pro`()
BEGIN
    DELETE FROM area_geo_visit_data;
	INSERT into area_geo_visit_data(event_time,module,label,cnt)
	SELECT
		CURDATE() as event_time,
		'VISIT_MAP' as module,
		c.name as label,
		SUM(b.cnt) as cnt
	FROM
		(
		SELECT
				a.location_longitude,
				a.location_latitude,
				COUNT(1) as cnt
		FROM
				matomo.matomo_log_visit a
		group by
			a.location_longitude,
			a.location_latitude
		)b ,
		matomo.area_geo c
	WHERE
		st_intersects(ST_GeomFromWKB(c.polygon),
		(st_geometryfromtext(concat('POINT(', CAST(b.location_longitude AS char) , ' ', CAST(b.location_latitude AS char) , ')'),0)))
	GROUP BY
		c.name ;
END
==
delimiter ;

--定时任务创建（统计频率可以根据需要设置，目前是与dbt脚本执行频率保持一致）
CREATE EVENT IF NOT EXISTS area_geo_visit_data_event 
ON SCHEDULE EVERY 24 HOUR 
ON COMPLETION PRESERVE
DO
CALL area_geo_visit_data_pro();