Mastering Advanced SQL Techniques with MySQL/

...

/

Working with Documents in GeoJSON

Working with Documents in GeoJSON

Discover what you can do with GeoJSON in MySQL and try it yourself.

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:

Press + to interact
{
  "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 ...