How can i load GeoJSON data into SQL Server 2016?Select independent objects using GEOJson on OpenlayersOpenlayers: select a feature programmatically from GeoJsonSQL Server Convert GeoJson format into SQL geometry/spatial dataLoading GEOJSON in Leaflet and separating attributesHow to implement Geography or geometry with spatial index implementation to find entities in the bounding box?Working with GeometryCollection in Leaflet layerGetting bounding boxes for all polygons in GeoJSON feature collection?Leaflet L.geoJSON from a js variable geojson object not showing on mapHow to append geojson file using featureselection

When blogging recipes, how can I support both readers who want the narrative/journey and ones who want the printer-friendly recipe?

How do I create uniquely male characters?

Can you lasso down a wizard who is using the Levitate spell?

Is it possible to make sharp wind that can cut stuff from afar?

least quadratic residue under GRH: an EXPLICIT bound

Closed subgroups of abelian groups

What makes Graph invariants so useful/important?

Patience, young "Padovan"

Is there really no realistic way for a skeleton monster to move around without magic?

Can an x86 CPU running in real mode be considered to be basically an 8086 CPU?

How can bays and straits be determined in a procedurally generated map?

Can I make popcorn with any corn?

XeLaTeX and pdfLaTeX ignore hyphenation

I see my dog run

Why is this code 6.5x slower with optimizations enabled?

Prevent a directory in /tmp from being deleted

Chess with symmetric move-square

Is Social Media Science Fiction?

How old can references or sources in a thesis be?

Is it possible to do 50 km distance without any previous training?

Could a US political party gain complete control over the government by removing checks & balances?

Can I interfere when another PC is about to be attacked?

How do we improve the relationship with a client software team that performs poorly and is becoming less collaborative?

Can town administrative "code" overule state laws like those forbidding trespassing?



How can i load GeoJSON data into SQL Server 2016?


Select independent objects using GEOJson on OpenlayersOpenlayers: select a feature programmatically from GeoJsonSQL Server Convert GeoJson format into SQL geometry/spatial dataLoading GEOJSON in Leaflet and separating attributesHow to implement Geography or geometry with spatial index implementation to find entities in the bounding box?Working with GeometryCollection in Leaflet layerGetting bounding boxes for all polygons in GeoJSON feature collection?Leaflet L.geoJSON from a js variable geojson object not showing on mapHow to append geojson file using featureselection






.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;








2















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?










share|improve this question






















  • How often will you need to do this?

    – DPSSpatial
    Feb 16 '18 at 16:36

















2















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?










share|improve this question






















  • How often will you need to do this?

    – DPSSpatial
    Feb 16 '18 at 16:36













2












2








2








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?










share|improve this question














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?







geojson sql-server json






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Feb 15 '18 at 21:49









DomenicoDomenico

111




111












  • How often will you need to do this?

    – DPSSpatial
    Feb 16 '18 at 16:36

















  • How often will you need to do this?

    – DPSSpatial
    Feb 16 '18 at 16:36
















How often will you need to do this?

– DPSSpatial
Feb 16 '18 at 16:36





How often will you need to do this?

– DPSSpatial
Feb 16 '18 at 16:36










2 Answers
2






active

oldest

votes


















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.






share|improve this answer
































    -1














    You can also use OGR2OGR convert GeoJSON to SQL SERVER






    share|improve this answer























      Your Answer








      StackExchange.ready(function()
      var channelOptions =
      tags: "".split(" "),
      id: "79"
      ;
      initTagRenderer("".split(" "), "".split(" "), channelOptions);

      StackExchange.using("externalEditor", function()
      // Have to fire editor after snippets, if snippets enabled
      if (StackExchange.settings.snippets.snippetsEnabled)
      StackExchange.using("snippets", function()
      createEditor();
      );

      else
      createEditor();

      );

      function createEditor()
      StackExchange.prepareEditor(
      heartbeatType: 'answer',
      autoActivateHeartbeat: false,
      convertImagesToLinks: false,
      noModals: true,
      showLowRepImageUploadWarning: true,
      reputationToPostImages: null,
      bindNavPrevention: true,
      postfix: "",
      imageUploader:
      brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
      contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
      allowUrls: true
      ,
      onDemand: true,
      discardSelector: ".discard-answer"
      ,immediatelyShowMarkdownHelp:true
      );



      );













      draft saved

      draft discarded


















      StackExchange.ready(
      function ()
      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fgis.stackexchange.com%2fquestions%2f271657%2fhow-can-i-load-geojson-data-into-sql-server-2016%23new-answer', 'question_page');

      );

      Post as a guest















      Required, but never shown

























      2 Answers
      2






      active

      oldest

      votes








      2 Answers
      2






      active

      oldest

      votes









      active

      oldest

      votes






      active

      oldest

      votes









      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.






      share|improve this answer





























        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.






        share|improve this answer



























          1












          1








          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.






          share|improve this 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.







          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited Apr 3 at 18:17

























          answered Feb 16 '18 at 1:29









          MickyTMickyT

          2,950914




          2,950914























              -1














              You can also use OGR2OGR convert GeoJSON to SQL SERVER






              share|improve this answer



























                -1














                You can also use OGR2OGR convert GeoJSON to SQL SERVER






                share|improve this answer

























                  -1












                  -1








                  -1







                  You can also use OGR2OGR convert GeoJSON to SQL SERVER






                  share|improve this answer













                  You can also use OGR2OGR convert GeoJSON to SQL SERVER







                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Aug 2 '18 at 18:25









                  GeospatialInformationTechGeospatialInformationTech

                  64737




                  64737



























                      draft saved

                      draft discarded
















































                      Thanks for contributing an answer to Geographic Information Systems Stack Exchange!


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid


                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.

                      To learn more, see our tips on writing great answers.




                      draft saved


                      draft discarded














                      StackExchange.ready(
                      function ()
                      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fgis.stackexchange.com%2fquestions%2f271657%2fhow-can-i-load-geojson-data-into-sql-server-2016%23new-answer', 'question_page');

                      );

                      Post as a guest















                      Required, but never shown





















































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown

































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown







                      Popular posts from this blog

                      រឿង រ៉ូមេអូ និង ហ្ស៊ុយលីយេ សង្ខេបរឿង តួអង្គ បញ្ជីណែនាំ

                      QGIS export composer to PDF scale the map [closed] Planned maintenance scheduled April 23, 2019 at 23:30 UTC (7:30pm US/Eastern) Announcing the arrival of Valued Associate #679: Cesar Manara Unicorn Meta Zoo #1: Why another podcast?Print Composer QGIS 2.6, how to export image?QGIS 2.8.1 print composer won't export all OpenCycleMap base layer tilesSave Print/Map QGIS composer view as PNG/PDF using Python (without changing anything in visible layout)?Export QGIS Print Composer PDF with searchable text labelsQGIS Print Composer does not change from landscape to portrait orientation?How can I avoid map size and scale changes in print composer?Fuzzy PDF export in QGIS running on macSierra OSExport the legend into its 100% size using Print ComposerScale-dependent rendering in QGIS PDF output

                      PDF-ში გადმოწერა სანავიგაციო მენიუproject page