MySql 8.0.32 空间扩展计算面积

-- 建表
CREATE TABLE `pro_pool_fence` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `code` varchar(255) DEFAULT NULL COMMENT '围栏code',
  `name` varchar(256) DEFAULT NULL COMMENT '名称',
  `province` varchar(32) DEFAULT NULL COMMENT '省份',
  `province_code` bigint(20) DEFAULT NULL COMMENT '省份编码',
  `city` varchar(32) DEFAULT NULL COMMENT '城市',
  `city_code` bigint(20) DEFAULT NULL COMMENT '城市编码',
  `region` varchar(32) DEFAULT NULL COMMENT '行政区',
  `region_code` bigint(20) DEFAULT NULL COMMENT '行政区编码',
  `mall` varchar(64) DEFAULT NULL COMMENT '商圈',
  `mall_code` bigint(20) DEFAULT NULL COMMENT '商圈编码',
  `polygon` longtext COMMENT '围栏数据',
  `geo_polygon` geometry DEFAULT NULL,
  `geo_point` geometry DEFAULT NULL,
  `center_longitude` double DEFAULT NULL COMMENT '围栏重心经度',
  `center_latitude` double DEFAULT NULL COMMENT '围栏重心纬度',
  `type` int(11) DEFAULT NULL COMMENT '围栏类型: 1. 社区围栏 2. 小区围栏 3. 写字楼围栏',
  `status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '围栏状态',
  `sub_project_id` bigint(20) DEFAULT NULL COMMENT '子项目id',
  `project_id` bigint(20) DEFAULT NULL COMMENT '项目id',
  `project_name` varchar(255) DEFAULT NULL COMMENT '项目名称',
  `is_delete` int(11) NOT NULL DEFAULT '0' COMMENT '逻辑删除',
  `source` int(11) NOT NULL DEFAULT '1' COMMENT '来源 1 自有 2 后台新建',
  `remark` varchar(256) DEFAULT NULL COMMENT '备注',
  `area` double DEFAULT NULL COMMENT '面积',
  `perimeter` double DEFAULT NULL COMMENT '周长',
  `grouping_level` varchar(255) DEFAULT NULL COMMENT '等级',
  `gmt_create` datetime DEFAULT NULL COMMENT '创建时间',
  `gmt_create_uid` bigint(20) DEFAULT NULL COMMENT '创建人id',
  `gmt_create_by` varchar(50) DEFAULT NULL COMMENT '创建人名称',
  `gmt_modified` datetime DEFAULT NULL COMMENT '更新时间',
  `gmt_modified_uid` bigint(20) DEFAULT NULL COMMENT '更新人id',
  `gmt_modified_by` varchar(50) DEFAULT NULL COMMENT '更新人名称',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_poi_id` (`code`) USING BTREE,
  KEY `idx_poi_name` (`name`) USING BTREE,
  KEY `idx_city_region_mall_name` (`city`,`region`,`mall`) USING BTREE,
  KEY `idx_province_code` (`province_code`) USING BTREE,
  KEY `idx_city_code` (`city_code`) USING BTREE,
  KEY `idx_region_code` (`region_code`) USING BTREE,
  KEY `idx_mall_code` (`mall_code`) USING BTREE,
  KEY `idx_province` (`province`) USING BTREE,
  KEY `idx_city` (`city`) USING BTREE,
  KEY `idx_region` (`region`) USING BTREE,
  KEY `idx_mall` (`mall`) USING BTREE,
  KEY `idx_project_id` (`project_id`) USING BTREE,
  KEY `idx_is_delete` (`is_delete`) USING BTREE,
  KEY `idx_perimeter` (`perimeter`) USING BTREE,
  KEY `idx_area` (`area`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='POI数据表';
-- 从围栏数据更新为geometry类型，5.7执行没问题，8执行会报3617 - Latitude 114.440952 is out of range in function st geomfromtext. It must be within [-90.00000000000000000000]
update pro_pool_fence set geo_polygon = ST_GeomFromText(CONCAT('POLYGON((', REPLACE(REPLACE(polygon, ',', ' '),';',', '), '))'), 4326);
-- 查看转换数据
select CONCAT('POLYGON((', REPLACE(REPLACE(polygon, ',', ' '),';',', '), '))') from pro_pool_fence;
-- 更新SRID
UPDATE pro_pool_fence SET geo_polygon = ST_SRID(ST_GeomFromText(ST_AsText(geo_polygon)), 4326);
-- 转换数据
UPDATE pro_pool_fence
SET geo_polygon = ST_Transform(geo_polygon, 3857);
-- 计算面积
select id, ST_SRID(geo_polygon),geo_polygon, area,ST_Area(geo_polygon) from pro_pool_fence;