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;
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 |