[GIS] How to i load GeoJSON data into SQL Server 2016

geojsonjsonsql server

I have this GeoJSON file

{
"type": "FeatureCollection",
"crs": { "type": "name", "properties": { "name": "urn:ogc:def:crs:OGC:1.3:CRS84" } },
"features": [
        { "type": "Feature", 
          "properties": { "COD": "M093", "FOGLIO": "0001", "NUMERO": "8", "SHAPE_LENG": 41, "SHAPE_AREA": 92 }, 
          "geometry": { "type": "Polygon", "coordinates": [ [ [ 14.598541303917116, 41.207077405742481 ], [ 14.598633470050846, 41.207093720394617 ], [ 14.598638966363804, 41.207076029000419 ], [ 14.59864949316175, 41.207077896868881 ], [ 14.598656017060879, 41.207056865262551 ], [ 14.598637860420407, 41.20705365287025 ], [ 14.598644239819395, 41.207034773240196 ], [ 14.598632343145956, 41.207030995939746 ], [ 14.598544494019848, 41.207031457952098 ], [ 14.598541303917116, 41.207077405742481 ] ] ] } 
        },
        { "type": "Feature", 
          "properties": { "COD": "M093", "FOGLIO": "0001", "NUMERO": "373", "SHAPE_LENG": 114, "SHAPE_AREA": 544 }, 
          "geometry": { "type": "Polygon", "coordinates": [ [ [ 14.604357586854741, 41.207010550988301 ], [ 14.604395640269018, 41.207019000507003 ], [ 14.604393037206272, 41.207025689856785 ], [ 14.604413828409179, 41.207030117786921 ], [ 14.604518271504356, 41.207055409049211 ], [ 14.604583645117462, 41.206889192798513 ], [ 14.604514417202289, 41.206873628758352 ], [ 14.604507682734921, 41.206890905830868 ], [ 14.604500434473708, 41.206889300694492 ], [ 14.604306376797163, 41.206846187488196 ], [ 14.604305887228582, 41.206847447930919 ], [ 14.604287868784919, 41.206893697156083 ], [ 14.604392813074112, 41.206917782119795 ], [ 14.604377767880903, 41.206956414685905 ], [ 14.604356752352492, 41.207010361623965 ], [ 14.604357586854741, 41.207010550988301 ] ] ] } 
        }
    ]
}

I use this query

INSERT INTO Fabbricati (COD, FOGLIO, NUMERO, SHAPE_LENG, SHAPE_AREA, coordinates)
SELECT
    geography::STPolyFromText('POLYGON ((' + STRING_AGG(CAST(Long + ' ' + Lat as varchar(max)), ',') + '))',4326).ReorientObject() AS coordinates
FROM
    (
    SELECT 
        Long,
        Lat
    FROM
        OPENJSON(@GeoJSON, '$.features[0].geometry.coordinates[0]')
        WITH
            (
                Long varchar(100) '$[0]',
                Lat varchar(100) '$[1]'
            )
)d

How can i insert all features with all attributes?

Best Answer

Using this as a guide for reading the different geometry types, you can build up to something like the following.

-- Some sample polygons
WITH source AS (
SELECT *
FROM (VALUES 
    (1, N'{ "type": "Polygon",
        "coordinates": [
            [[30, 10], [40, 40], [20, 40], [10, 20], [30, 10]]
        ]
    }'),
    (2, N'{ "type": "Polygon",
            "coordinates": [
                [[30, 10], [40, 40], [20, 40], [10, 20], [30, 10]]
            ]
        }')
    ) j(id,polygon)
)
-- Actual Query
SELECT id, Geometry::STGeomFromText(CONCAT('POLYGON((',STUFF(coords,1,2,''),'))'),0)
FROM (
    SELECT id, j.coords
    FROM source s
        CROSS APPLY (SELECT  concat(', ',x , ' ' , y) FROM OPENJSON(s.polygon, '$.coordinates[0]')WITH (x int '$[0]', y int '$[1]' )FOR XML PATH('')) j (coords)
    ) p (id, coords)

With SQL Server 2017, you will be able to use a STRING_AGG. Of course you will need to come up with different build for each geometry type.

Related Question