如何将我的geojson数据传输到mysql数据库

Question

我使用javascript geojson数据生成。现在我想将这个geojson文件的数据传输到mysql数据库。我知道mysql有一个用于geojson数据的函数，但我对这个主题很陌生，所以我不知道如何使用它。所以我的问题是如何将geojson数据传输到mysql数据库。我希望一列显示类型(点/多边形/…)，一列显示特征(如果有)(颜色，…)，另一列显示坐标。也许还有可能自动传输这些数据吗？

此刻我尝试了这个：

SET @point_o_from_geoJSON := ST_GeomFromGeoJSON( '{"type":"FeatureCollection","features":[{"type":"Feature","properties":{},"geometry":{"type":"Point","coordinates":[8.721926,49.856657]}},{"type":"Feature","properties":{},"geometry":{"type":"Polygon","coordinates":[[[8.428072,50.052724],[8.428072,50.190024],[8.807062,50.190024],[8.807062,50.052724],[8.428072,50.052724]]]}},{"type":"Feature","properties":{},"geometry":{"type":"Point","coordinates":[9.177812,50.151338]}}]}' ); # geometry from geoJSON 
select ST_AsText(@point_o_from_geoJSON) AS `point_o_from_geoJSON`;

但所有信息都在一列中：数据表

我试图用JSON_TABLE分割几何图形，但我很难处理每个几何图形的不同数量的坐标。那是我的尝试(来自https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html)：

SELECT *
FROM
JSON_TABLE(
'{"type":"FeatureCollection","features": 
[{"type":"Feature","properties":{},"geometry": 
{"type":"Point","coordinates":[8.721926,49.856657]}},
{"type":"Feature","properties":{},"geometry": 
{"type":"Polygon","coordinates":[[[8.428072,50.052724],[8.428072,50.190024],[8.807062,50.190024],[8.807062,50.052724],[8.428072,50.052724]]]}},
{"type":"Feature","properties":{},"geometry": 
{"type":"Point","coordinates":[9.177812,50.151338]}}]}',
'$[*]' COLUMNS(
top_ord For ordinality,
typeID VARCHAR(10) Path '$.type',
nested path '$.features[*]' columns (
itemtype varchar(10) path '$.type',
prop varchar(50) path '$.properties',
ord for ordinality,
nested path '$.geometry[*]' columns (
itemstype varchar(10) path '$.type',
ordi for ordinality,
nested path '$.coordinates[*]' columns (coord varchar(10) path '$')
)
)
) 
) as jt;

Answer 1

SELECT top_ord,
typeID,
features_ord,
itemtype,
CAST(properties AS CHAR) properties,
geometry_ord,
itemstype,
COALESCE(point_coordinate_ord, polygon_point_coordinate_ord) coordinate_ord,
CAST(COALESCE(point_coordinate, polygon_point_coordinate) AS DECIMAL(10,6)) point_coordinate
FROM JSON_TABLE(
'{"type":"FeatureCollection","features": 
[{"type":"Feature","properties":{},"geometry": 
{"type":"Point","coordinates":[8.721926,49.856657]}},
{"type":"Feature","properties":{},"geometry": 
{"type":"Polygon","coordinates":[[[8.428072,50.052724],[8.428072,50.190024],[8.807062,50.190024],[8.807062,50.052724],[8.428072,50.052724]]]}},
{"type":"Feature","properties":{},"geometry": 
{"type":"Point","coordinates":[9.177812,50.151338]}}]}',
'$' COLUMNS(
top_ord For ordinality,
typeID TEXT Path '$.type',
nested path '$.features[*]' columns (
features_ord for ordinality,
itemtype TEXT path '$.type',
properties JSON path '$.properties',
nested path '$.geometry' columns (
geometry_ord for ordinality,
itemstype TEXT path '$.type',
nested path '$.coordinates[*]' COLUMNS (
point_coordinate_ord FOR ORDINALITY,
point_coordinate TEXT PATH '$'
),
nested path '$.coordinates[*][*][*]' COLUMNS (
polygon_point_coordinate_ord FOR ORDINALITY,
polygon_point_coordinate TEXT PATH '$'
)
)
)
) 
) as jt
WHERE COALESCE(point_coordinate, polygon_point_coordinate) IS NOT NULL
ORDER BY 1,3,6,8;

coordinate_ord功能集合td>功能集合1：右；">49.856657>>.177812>：right；">50.151338

top_ord	项目样式	点坐标
8.721926
1	功能集合	8.428072
1	功能集合	2	功能	｛｝	1	多边形：右；">50.052724
1	功能集合	8.428072
1	功能集合	50.190024
1	功能集合	8.807062
1	功能集合	50.190024
1	功能集合	8.807062
1	功能集合	50.052724
1	功能集合	8.428072
1	功能集合	50.052724
1	功能集合	3	功能	｛｝	1点
1	功能集合	3	功能	｛｝	1	点