3

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?

3
1

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.

-1

You can also use OGR2OGR convert GeoJSON to SQL SERVER

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.