英文:
How can I transfer my geojson data to a mysql database?
问题
I generated with javascript geojson data. Now I want to transfer the data of this geojson file to a mysql database. I know that mysql has a function for geojson data but I'm really new in this topic so I don't know how to use it. So my question is how I can transfer my geojson data to a mysql database. I would like that one column shows the type (Point/Polygon/...), one the features if there are some (color,...) and one column for the coordinates. And perhaps is there also a possibility to transfer these data automatically?
At the moment I tried this:
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`;
But then all the information are in one column: data table
I tried to split the geometries with JSON_TABLE but I am struggling with the different numbers of coordinates of each geometry. That was my try (from 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;
英文:
I generated with javascript geojson data. Now I want to transfer the data of this geojson file to a mysql database. I know that mysql has a function for geojson data but I'm really new in this topic so I don't know how to use it.
So my question is how I can transfer my geojson data to a mysql database. I would like that one column shows the type (Point/Polygon/...), one the features if there are some (color,...) and one column for the coordinates.
And perhaps is there also a possibility to transfer these data automatically?
At the moment I tried this:
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`;
But then all the information are in one column: data table
I tried to split the geometries with JSON_TABLE but I am struggling with the different numbers of coordinates of each geometry. That was my try (from 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;
答案1
得分: 0
以下是翻译好的代码部分:
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;
希望这对您有所帮助。如有其他问题,请随时提出。
英文:
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 | typeID | features_ord | itemtype | properties | geometry_ord | itemstype | coordinate_ord | point_coordinate |
---|---|---|---|---|---|---|---|---|
1 | FeatureCollection | 1 | Feature | {} | 1 | Point | 1 | 8.721926 |
1 | FeatureCollection | 1 | Feature | {} | 1 | Point | 2 | 49.856657 |
1 | FeatureCollection | 2 | Feature | {} | 1 | Polygon | 1 | 8.428072 |
1 | FeatureCollection | 2 | Feature | {} | 1 | Polygon | 2 | 50.052724 |
1 | FeatureCollection | 2 | Feature | {} | 1 | Polygon | 3 | 8.428072 |
1 | FeatureCollection | 2 | Feature | {} | 1 | Polygon | 4 | 50.190024 |
1 | FeatureCollection | 2 | Feature | {} | 1 | Polygon | 5 | 8.807062 |
1 | FeatureCollection | 2 | Feature | {} | 1 | Polygon | 6 | 50.190024 |
1 | FeatureCollection | 2 | Feature | {} | 1 | Polygon | 7 | 8.807062 |
1 | FeatureCollection | 2 | Feature | {} | 1 | Polygon | 8 | 50.052724 |
1 | FeatureCollection | 2 | Feature | {} | 1 | Polygon | 9 | 8.428072 |
1 | FeatureCollection | 2 | Feature | {} | 1 | Polygon | 10 | 50.052724 |
1 | FeatureCollection | 3 | Feature | {} | 1 | Point | 1 | 9.177812 |
1 | FeatureCollection | 3 | Feature | {} | 1 | Point | 2 | 50.151338 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论