Search⌘ K
AI Features
Home
Courses
Mastering Advanced SQL Techniques with MySQL

Working with Documents in GeoJSON

Explore how to work with GeoJSON documents in MySQL by converting them into spatial entities using ST_GeomFromGeoJSON and reversing the process with ST_AsGeoJSON. Understand geometry types, feature collections, and coordinate reference systems for effective geospatial data handling and analysis.

We'll cover the following...

GeoJSON is a geospatial data interchange format based on JSON. It was developed for encoding various geographic data structures, including geometry types such as Point, LineString, Polygon, MultiPoint, MultiLineString, and MultiPolygon. Additionally, GeoJSON supports Feature objects, which are geometric objects with additional properties, and FeatureCollection objects, which are sets of Feature. An example of such geometry types is a GeoJSON document like the following:

Javascript (babel-node)
{
  "type": "FeatureCollection",
  "name": "e-scooter startup",
  "crs": { 
    "type": "name", 
    "properties": { 
      "name": "urn:ogc:def:crs:EPSG::3857" 
    } 
  },
  "features": [
    { "type": "Feature", "properties": { "id": 1 }, "geometry": { "type": "Point", "coordinates": [ 1489139.375061396276578, 6894445.784759958274662 ] } },
    { "type": "Feature", "properties": { "id": 2 }, "geometry": { "type": "Point", "coordinates": [ 1489403.643034972948954, 6893967.517177346162498 ] } },
    { "type": "Feature", "properties": { "id": 3 }, "geometry": { "type": "Point", "coordinates": [ 1489146.556256330804899, 6894019.221780870109797 ] } },
    { "type": "Feature", "properties": { "id": 4 }, "geometry": { "type": "Point", "coordinates": [ 1489609.025210089283064, 6894062.308950475417078 ] } },
    { "type": "Feature", "properties": { "id": 5 }, "geometry": { "type": "Point", "coordinates": [ 1490047.07810107152909, 6894109.704837041907012 ] } },
    { "type": "Feature", "properties": { "id": 6 }, "geometry": { "type": "Point", "coordinates": [ 1489577.805910007795319, 6893970.983343813568354 ] } },
    
    { "type": "Feature", "properties": { "id": 11 }, "geometry": { "type": "LineString", "coordinates": [ [ 1489354.26736818999052, 6894010.508232391439378 ], [ 1490104.755967538570985, 6894091.394225876778364 ], [ 1490128.938377961982042, 6893866.247646071948111 ], [ 1489620.273882847977802, 6893812.04569167457521 ], [ 1489536.052384476643056, 6894087.224844769574702 ], [ 1489502.69733561668545, 6894345.726473433896899 ], [ 1489172.482351903337985, 6894311.537548352964222 ], [ 1489110.77551151253283, 6894289.856766593642533 ], [ 1489198.332514769863337, 6894028.853509264998138 ], [ 1489266.710364932660013, 6894058.873053238727152 ] ] } },
    { "type": "Feature", "properties": { "id": 12 }, "geometry": { "type": "LineString", "coordinates": [ [ 1489348.598127036821097, 6894046.618688927032053 ], [ 1489553.731677525443956, 6894083.30924267321825 ], [ 1490864.585097720846534, 6894225.06820032838732 ] ] } },
    { "type": "Feature", "properties": { "id": 13 }, "geometry": { "type": "LineString", "coordinates": [ [ 1490872.92385993571952, 6894173.367874595336616 ], [ 1490440.975977199617773, 6894123.335301305167377 ], [ 1490452.650244300719351, 6894003.257125409319997 ], [ 1490562.721905538346618, 6894014.931392510421574 ] ] } },
    { "type": "Feature", "properties": { "id": 14 }, "geometry": { "type": "LineString", "coordinates": [ [ 1491049.70561889349483, 6893956.56005700584501 ], [ 1491048.037866450380534, 6893954.892304562963545 ], [ 1490452.650244300719351, 6893898.188721500337124 ], [ 1490522.695846906397492, 6893277.784812705591321 ], [ 1490556.050895766355097, 6893269.446050491183996 ] ] } },
    { "type": "Feature", "properties": { "id": 21 }, "geometry": { "type": "Polygon", "coordinates": [ [ [ 1489233.427302768686786, 6894117.403172097168863 ], [ 1489410.10989729152061, 6894135.680681872181594 ], [ 1489430.128122286405414, 6893949.424153659492731 ], [ 1489253.445527764270082, 6893926.794855839572847 ], [ 1489233.427302768686786, 6894117.403172097168863 ] ] ] } },
    { "type": "Feature", "properties": { "id": 22 }, "geometry": { "type": "Polygon", "coordinates": [ [ [ 1489104.179197909077629, 6894318.455779656767845 ], [ 1489107.660628343001008, 6894587.396280678920448 ], [ 1489173.807806587312371, 6894576.081631767563522 ], [ 1489170.326376153621823, 6894329.770428568124771 ], [ 1489104.179197909077629, 6894318.455779656767845 ] ] ] } },
    { "type": "Feature", "properties": { "id": 23 }, "geometry": { "type": "Polygon", "coordinates": [ [ [ 1489555.894796713022515, 6894069.533503631129861 ], [ 1489888.371403153752908, 6894105.218165579251945 ], [ 1489891.852833588141948, 6894079.977794932201505 ], [ 1489565.468730405904353, 6894046.033848201856017 ], [ 1489555.894796713022515, 6894069.533503631129861 ] ] ] } },
    { "type": "Feature", "properties": { "id": 24 }, "geometry": { "type": "Polygon", "coordinates": [ [ [ 1489903.602661302546039, 6894105.218165579251945 ], [ 1490084.637043867725879, 6894126.106748177669942 ], [ 1490086.377759084803984, 6894101.736735143698752 ], [ 1489905.343376519856974, 6894083.459225365892053 ], [ 1489903.602661302546039, 6894105.218165579251945 ] ] ] } },
    { "type": "Feature", "properties": { "id": 25 }, "geometry": { "type": "Polygon", "coordinates": [ [ [ 1489129.123571229632944, 6894032.060245564207435 ], [ 1489247.820845305686817, 6894044.279082601889968 ], [ 1489251.893790984991938, 6893999.476680131629109 ], [ 1489134.360215674620122, 6893987.83969247713685 ], [ 1489130.287269995547831, 6894031.478396176360548 ], [ 1489129.123571229632944, 6894032.060245564207435 ] ] ] } }
  ]
}

The GeoJSON document contains multiple attributes describing the represented spatial entities:

  • The document’s type is FeatureCollection, i.e., the document is a set of Feature (line 2).

  • The FeatureCollection operates under a specific coordinate reference system (crs), defined as the spatial reference system (SRS) with the ID 3857 (lines 4–9).

  • The various features within this collection are enumerated ...