2

I have an Azure SQL table:

-- Create the table in the specified schema
CREATE TABLE dbo.geoTest_DEV
(
    fAssetId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    fAssetGeoId [NVARCHAR] (50),
    fAssetType [NVARCHAR](255),
    fAssetStatus [NVARCHAR](255),
    fAssetDesc [NVARCHAR](255),
    fGeoType [NVARCHAR](50),
    fGeoCoord [GEOGRAPHY]
);
GO

I have set up an Azure Blob Storage container as an external data source for this database. This holds .geojson files to be loaded into the Azure SQL table:

CREATE DATABASE SCOPED CREDENTIAL uploadGeo
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<bigLongString>';

CREATE EXTERNAL DATA SOURCE geoStorage
    WITH (
        TYPE = BLOB_STORAGE,
        LOCATION = 'https://mycoolthing.blob.core.windows.net',
        CREDENTIAL = uploadGeo
    );

Here is the schema of geotest.geojson: NOTE: There are multiple polygons in the actual file. This is just a sample of a single feature.

{
  "type": "FeatureCollection",
  "features": [
    {
      "type": "Feature",
      "properties": {
        "geometryId": "1",
        "assetType": "residential building",
        "assetStatus": "active",
        "assetDesc": "Bills place"
      },
      "geometry": {
        "type": "Polygon",
        "coordinates": [
          [
            [
              -121.39254742860793,
              47.668080416965564
            ],
            [
              -121.39254742860793,
              47.66804784193947
            ],
            [
              -121.39257156848907,
              47.66804603221521
            ],
            [
              -121.39257156848907,
              47.668002598814054
            ],
            [
              -121.39236772060394,
              47.668002598814054
            ],
            [
              -121.39236772060394,
              47.668080416965564
            ],
            [
              -121.39254742860793,
              47.668080416965564
            ]
          ]
        ]
      }
    }
  ]
}

I can't quite figure out how to get each of the properties, the geometry type AND the geometry coordinates objects into the SQL table in a single call.

Here is where I'm stuck:

DECLARE @geoJson NVARCHAR(MAX)
SELECT @geoJson = BulkColumn
FROM OPENROWSET (
    BULK 'geoTest.geojson',
    DATA_SOURCE = 'geoUploads',
    SINGLE_CLOB) as JSON

INSERT INTO dbo.geoSQL_DEV (fGeoCoord)
SELECT
  geography::STPolyFromText('POLYGON ((' + STRING_AGG(CAST(Long + ' ' + Lat as varchar(max)), ',') + '))',4326).ReorientObject() AS fGeoCoord
FROM
    (
    SELECT 
        Long,
        Lat
    FROM
        OPENJSON(@geoJson, '$.features[0].geometry.coordinates[0]')
        WITH
            (
                Long varchar(100) '$[0]',
                Lat varchar(100) '$[1]'
            )
)d

GO

DECLARE @geoJson1 NVARCHAR(MAX)
SELECT @geoJson1 = BulkColumn
FROM OPENROWSET (
    BULK 'geoTest.geojson',
    DATA_SOURCE = 'geoUploads',
    SINGLE_CLOB) as JSON

INSERT INTO dbo.geoSQL_DEV (fAssetGeoId, fAssetType, fAssetStatus, fAssetDesc, fGeoType)
SELECT
  fAssetGeoId,
    fAssetType,
    fAssetStatus,
    fAssetDesc, 
    fGeoType
FROM
OPENJSON(@geoJson1, '$.features')
    WITH (
        fAssetGeoId [NVARCHAR] (50) '$.properties.geometryId',
        fAssetType [NVARCHAR](300) '$.properties.assetType',
        fAssetStatus [NVARCHAR](300) '$.properties.assetStatus',
        fAssetDesc [NVARCHAR](300) '$.properties.assetDesc',
        fGeoType [NVARCHAR](300) '$.geometry.type'
    )

GO

SELECT * FROM dbo.geoSQL_DEV

ALMOST THERE! This results in:

enter image description here

I just need to figure out how to insert each fGeoCoord in the correct record instead of in the single record as shown.

ANY IDEAS?

Your Answer

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

Browse other questions tagged or ask your own question.