FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

DECLARE @GeoJSON nvarchar(max) = N'{
  "type": "FeatureCollection",
  "features": [
    {
      "type": "Feature",
      "properties": {},
      "geometry": {
        "coordinates": [
          [
            [
              -3.564987562532224,
              40.292745296473385
            ],
            [
              -3.564987562532224,
              40.16285276280664
            ],
            [
              -3.4121841156292874,
              40.16285276280664
            ],
            [
              -3.4121841156292874,
              40.292745296473385
            ],
            [
              -3.564987562532224,
              40.292745296473385
            ]
          ]
        ],
        "type": "Polygon"
      }
    },
    {
      "type": "Feature",
      "properties": {},
      "geometry": {
        "coordinates": [
          [
            [
              -3.3271182792090315,
              40.4031979514057
            ],
            [
              -3.3271182792090315,
              40.36480015317514
            ],
            [
              -3.1790407121077635,
              40.36480015317514
            ],
            [
              -3.1790407121077635,
              40.4031979514057
            ],
            [
              -3.3271182792090315,
              40.4031979514057
            ]
          ]
        ],
        "type": "Polygon"
      }
    },
    {
      "type": "Feature",
      "properties": {},
      "geometry": {
        "coordinates": [
          [
            [
              -3.2262995101188494,
              40.623560335315744
            ],
            [
              -3.297187707135521,
              40.623560335315744
            ],
            [
              -3.297187707135521,
              40.554176446403545
            ],
            [
              -3.2262995101188494,
              40.554176446403545
            ],
            [
              -3.2262995101188494,
              40.623560335315744
            ]
          ]
        ],
        "type": "Polygon"
      }
    },
    {
      "type": "Feature",
      "properties": {},
      "geometry": {
        "coordinates": [
          [
            [
              -3.4610182069080224,
              40.50628355201741
            ],
            [
              -3.5303311106570447,
              40.50628355201741
            ],
            [
              -3.5303311106570447,
              40.46794459511571
            ],
            [
              -3.4610182069080224,
              40.46794459511571
            ],
            [
              -3.4610182069080224,
              40.50628355201741
            ]
          ]
        ],
        "type": "Polygon"
      }
    },
    {
      "type": "Feature",
      "properties": {},
      "geometry": {
        "coordinates": [
          [
            [
              -3.921003840881866,
              40.36480015317514
            ],
            [
              -4.02654848977366,
              40.26870991819703
            ],
            [
              -4.0092202638360845,
              40.15201714283356
            ],
            [
              -3.8044321391217295,
              40.12311370342616
            ],
            [
              -3.7335439421050864,
              40.2699118900496
            ],
            [
              -3.7760768603145323,
              40.4031979514057
            ],
            [
              -3.809158018922062,
              40.51706243744249
            ],
            [
              -3.9336061870190804,
              40.4031979514057
            ],
            [
              -3.9745638119609055,
              40.38400178830193
            ],
            [
              -3.9950426244325,
              40.3660004156724
            ],
            [
              -3.979289691762574,
              40.35399682894311
            ],
            [
              -3.9887414513645467,
              40.33118412644623
            ],
            [
              -4.024973196506011,
              40.32037541696292
            ],
            [
              -4.037575542643282,
              40.3503953361741
            ],
            [
              -4.0753825810510875,
              40.304759781098966
            ],
            [
              -4.0753825810510875,
              40.25308234032204
            ],
            [
              -4.062780234915181,
              40.23865367996609
            ],
            [
              -4.070656701250783,
              40.20016221759744
            ],
            [
              -3.921003840881866,
              40.36480015317514
            ]
          ]
        ],
        "type": "Polygon"
      }
    }
  ]
}'

CREATE TABLE ImportDataGeo (
    Id INT IDENTITY(1, 1) PRIMARY KEY,
    Geometry GEOGRAPHY,
    GeoType NVARCHAR(50)
);

-- Import the GeoJSON
INSERT INTO ImportDataGeo (Geometry, GeoType)
SELECT    
		geography::STPolyFromText('POLYGON (' + coordinates + ')', 3035).ReorientObject() AS Geometry,
		geometryType AS GeoType
FROM OPENJSON(@GeoJSON, '$.features')
        WITH (
                geometryType NVARCHAR(50) '$.geometry.type',
				coordinates NVARCHAR(MAX) '$.geometry.coordinates[0][0]'
        )

-- View the imported data
SELECT * FROM ImportDataGeo;

Id	Geometry	GeoType
1	NULL	Polygon
2	NULL	Polygon
3	NULL	Polygon
4	NULL	Polygon
5	NULL	Polygon

(5 rows affected)

(5 rows affected)