如何将我的GeoJSON数据传输到MySQL数据库?

huangapple go评论65阅读模式
英文:

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

fiddle

huangapple
  • 本文由 发表于 2023年4月4日 17:21:49
  • 转载请务必保留本文链接:https://go.coder-hub.com/75927655.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定