MySql空间数据库操作

标签： 无 分类： 未分类 创建时间：2025-07-27 03:01:30 更新时间：2025-09-26 04:44:39

1.前言

最近遇到了一个需求，就是要在mysql数据库中进行框选查询，开始的时候，我的数据库里面存储的是 geojson 字段，并不需要进行空间查询。

参考文章:
【1】.MySQL升级版本（Linux环境） 下载对应版本的mysql,然后覆盖安装。
【2】.MySql版本升级（8.0.19升级到8.0.22） 这里也是覆盖安装
【3】.MySQL GIS功能介绍 这里是关于一些空间函数的解释

2.geojson转为 geomtory

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
-- 删除现有的虚拟列
ALTER TABLE hzscreen_record DROP COLUMN geom_linestring;

-- 创建支持多种几何类型的虚拟列
ALTER TABLE hzscreen_record ADD COLUMN geom_geometry GEOMETRY AS (
    CASE 
        WHEN JSON_EXTRACT(xcgj, '$.geometry.type') = 'LineString' THEN
            ST_GeomFromGeoJSON(JSON_UNQUOTE(JSON_EXTRACT(xcgj, '$.geometry')))
        WHEN JSON_EXTRACT(xcgj, '$.features[0].geometry.type') = 'LineString' THEN
            ST_GeomFromGeoJSON(JSON_UNQUOTE(JSON_EXTRACT(xcgj, '$.features[0].geometry')))
        WHEN JSON_EXTRACT(xcgj, '$.features[0].geometry.type') = 'MultiLineString' THEN
            ST_GeomFromGeoJSON(JSON_UNQUOTE(JSON_EXTRACT(xcgj, '$.features[0].geometry')))
        ELSE NULL
    END
) STORED;

-- 最保险的办法，可以处理 NULL 的情况
ALTER TABLE hzscreen_record ADD COLUMN geom_geometry GEOMETRY AS (
    CASE 
        WHEN xcgj IS NOT NULL 
        AND JSON_VALID(xcgj) = 1
        AND (
            JSON_EXTRACT(xcgj, '$.geometry') IS NOT NULL 
            OR JSON_EXTRACT(xcgj, '$.features[0].geometry') IS NOT NULL
            OR JSON_EXTRACT(xcgj, '$.type') IN ('LineString', 'MultiLineString')
        ) THEN
            ST_GeomFromGeoJSON(
                JSON_UNQUOTE(
                    COALESCE(
                        JSON_EXTRACT(xcgj, '$.geometry'),
                        JSON_EXTRACT(xcgj, '$.features[0].geometry'),
                        CASE 
                            WHEN JSON_EXTRACT(xcgj, '$.type') IN ('LineString', 'MultiLineString') THEN
                                xcgj
                            ELSE NULL
                        END,
                        '{"type":"Point","coordinates":[0,0]}'
                    )
                )
            )
        ELSE 
            NULL
    END
) STORED;

3.创建索引

使用这个创建索引，会出现：SQL 错误 [1252] [42000]: All parts of a SPATIAL index must be NOT NULL

1
CREATE INDEX idx_geom_geometry ON hzscreen_record(geom_geometry);

【解决方案】
（1）添加约束

1
2
3
4
5
6
7
8
9
-- 更新NULL值为有效的JSON
UPDATE hzscreen_record 
SET xcgj = '{"type":"Feature","geometry":{"type":"Point","coordinates":[0,0]}}'
WHERE xcgj IS NULL OR JSON_VALID(xcgj) = 0;

-- 添加检查约束（MySQL 8.0.16+）
ALTER TABLE hzscreen_record 
ADD CONSTRAINT chk_xcgj_not_null 
CHECK (xcgj IS NOT NULL AND JSON_VALID(xcgj) = 1);

（2）处理 NULL 值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
-- 创建新的虚拟列，处理NULL情况
ALTER TABLE hzscreen_record ADD COLUMN geom_geometry GEOMETRY AS (
    CASE 
        WHEN xcgj IS NOT NULL 
        AND JSON_VALID(xcgj) = 1
        AND (
            JSON_EXTRACT(xcgj, '$.geometry') IS NOT NULL 
            OR JSON_EXTRACT(xcgj, '$.features[0].geometry') IS NOT NULL
        ) THEN
            ST_GeomFromGeoJSON(
                JSON_UNQUOTE(
                    COALESCE(
                        JSON_EXTRACT(xcgj, '$.geometry'),
                        JSON_EXTRACT(xcgj, '$.features[0].geometry')
                    )
                )
            )
        ELSE 
            ST_GeomFromText('POINT(0 0)', 4326)  -- 默认几何，确保不为NULL
    END
) STORED NOT NULL;

-- 现在应该可以创建索引了
CREATE INDEX idx_geom_geometry ON hzscreen_record(geom_geometry);

-- 检查虚拟列是否正常工作
SELECT 
    COUNT(*) as total_records,
    COUNT(geom_geometry) as records_with_geometry,
    MIN(ST_AsText(geom_geometry)) as sample_geometry
FROM hzscreen_record;

-- 查询操作
SELECT id, xcgj 
FROM hzscreen_record 
WHERE geom_geometry IS NOT NULL 
AND MBRIntersects(
    geom_geometry,
    ST_GeomFromText('POLYGON((0 -1, 1 -1, 1 1, 0 1, 0 -1))', 4326)
)
LIMIT 5;

参考文章:
【1】.SQL 错误 [1252] [42000]: All parts of a SPATIAL index must be NOT NULL 出现这个错误是因为要建立空间索引的字段不能为NULL值。改一下表定义，把空间字段值设为NOT NULL即可。 注意，要建立空间索引，表类型必须是MyISAM类型。
【2】.All parts of a SPATIAL index must be NOT NULL Indexed columns must be NOT NULL.

4.创建bbox

根据生成的geomtry，生成四至范围。

**本文已结束，感谢您的观看**
鼓励一下

往期推荐

文章目录
  1. 1. 1.前言
  2. 2. 2.geojson转为 geomtory
  3. 3. 3.创建索引
  4. 4. 4.创建bbox
小额赞助
本人提供免费与付费咨询服务，感谢您的支持！赞助请发邮件（ititchuan@gmail.com）通知，方便公布您的善意！
**光 3.01 元
Sun 3.00 元
ititchuan 3.00 元
捐赠者名单 合计：9.01 元
微信公众号
广告位
诚心邀请广大金主爸爸洽谈合作
每日一省
isNaN 和 Number.isNaN 函数的区别？

1.函数 isNaN 接收参数后，会尝试将这个参数转换为数值，任何不能被转换为数值的的值都会返回 true，因此非数字值传入也会返回 true ，会影响 NaN 的判断。

2.函数 Number.isNaN 会首先判断传入参数是否为数字，如果是数字再继续判断是否为 NaN ，不会进行数据类型的转换，这种方法对于 NaN 的判断更为准确。

2021-04-07 链接地址
每日二省
为什么0.1+0.2 ! == 0.3，如何让其相等?

一个直接的解决方法就是设置一个误差范围，通常称为“机器精度”。对JavaScript来说，这个值通常为2-52，在ES6中，提供了Number.EPSILON属性，而它的值就是2-52，只要判断0.1+0.2-0.3是否小于Number.EPSILON，如果小于，就可以判断为0.1+0.2 ===0.3。

2021-04-07 链接地址
每日三省
== 操作符的强制类型转换规则？

1.首先会判断两者类型是否**相同，**相同的话就比较两者的大小。

2.类型不相同的话，就会进行类型转换。

3.会先判断是否在对比 null 和 undefined，是的话就会返回 true。

4.判断两者类型是否为 string 和 number，是的话就会将字符串转换为 number。

5.判断其中一方是否为 boolean，是的话就会把 boolean 转为 number 再进行判断。

6.判断其中一方是否为 object 且另一方为 string、number 或者 symbol，是的话就会把 object 转为原始类型再进行判断。

2021-04-07 链接地址
每日英语
Happiness is time precipitation, smile is the lonely sad.
幸福是年华的沉淀，微笑是寂寞的悲伤。
2016 ——— 美文