我使用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;
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;
top_ord | 项目样式 | coordinate_ord | 点坐标 | 8.721926 | 功能集合1:右;">49.856657||
---|---|---|---|---|---|---|
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点 | .177812|
1 | 功能集合 | 3 | 功能 | {} | >1 | 点 | :right;">50.151338