Post reply

Create GEOJSON with TSQL

  • July 29, 2019 at 9:56 pm

    #3666966

    I'm trying to create an export file in GeoJson, but I'm having a hard time reading and creating a file.

    Can anyone point me in the right direction?

    I've updated the code. I was able to create and insert the created GeoJSON into a table.

    Hoping someone can take a look and see if there is another more elegant way to do this and how to directly create a file of the json.

    v/r

    --GeoJson
    DECLARE @featureList nvarchar(max) = 
    (
       SELECT
       'Feature' as type,
       'Polygon' as 'geography.type',
       "geo_location".STAsText() as 'geography.geo_location',
       "geo_name" as 'properties.geo_name',
       "geo_parent_name" as 'properties.geo_parent_name'
       FROM dbo.myGeoData
       FOR JSON PATH
    )

    DECLARE @featureCollection nvarchar(max) = 
    (
    SELECT 'featureCollection' as 'type',
    JSON_QUERY(@featureList) AS 'features'
    FOR JSON PATH, WITHOUT_ARRAY WRAPPER
    )

    ---SELECT @featureCollection

    INSERT INTO [geo_json_test] (geo_name, geo_parent_name, geo_location, geo_type)
    SELECT "geo_name", "geo_parent_name", "geo_location", "geo_type"
    FROM
    OPENJSON(@featureCollection, '$features')
    WITH(
    "geo_name" varchar(50) '$.properties.geo_name',
    "geo_parent_name" varchar(50) '$.properties.geo_parent_name', 
    "geo_location" nvarchar(max) '$.geography.geo_location', 
    "geo_type" varchar(max) '$.geography.type'
    )

    • This topic was modified 5 years, 6 months ago by  delizat4g.
    • This topic was modified 5 years, 6 months ago by  delizat4g.

    Code-Blooded

  • July 30, 2019 at 10:10 pm

    #3667372

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply