1

I am having some issues converting a SQL table to GeoJSON.

This is my connection string:

ogr2ogr -f "GeoJSON" "test.geojson" "MSSQL:server=(LocalDb)\v11.0;database=SpatialDataExamples;tables=test;trusted_connection=yes;Integrated Security=true;DRIVER=SQL Server Native Client 11.0;GeometryType=wkb;" -sql "SELECT  * FROM test WHERE ID=1"

If I use the tables parameter, and no SQL, the geojson is exported correctly. If I specify some sql to filter the results, the geometry comes back as binary in the exported file.

I am trying to export a SQL polygon table, to individual documents that I can use in DocumentDB.

Exporting shapefiles directly produces geojson with coordinates in a right hand orientation as opposed to left hand as required by DocumentDB

Any suggestions?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ian Short
  • 59
  • 8

1 Answers1

1

So, this is what I have done, and it has worked.

  1. Upload shapefile to SQL Server using Shape2SQL

  2. Correct (orient the exterior ring in counter-clockwise fashion) the geometry using:

    UPDATE tablename SET geom = geom.MakeValid()
UPDATE tablename SET geom = geom.STUnion(geom.STStartPoint());

  3. Use ogr2ogr to output the entire layer to GeoJSON

  4. Add as layer in QGIS

  5. Use the following python script to loop through every feature, select and export to geojson file

    import qgis.core 
import qgis.utils
i = qgis.utils.iface
l = i.activeLayer()
j=0 
for feat in l.getFeatures():  
l.setSelectedFeatures([feat.id()]) 
qgis.core.QgsVectorFileWriter.writeAsVectorFormat(l,'c:/ConvertedShapefiles/shape'+str(j)+'.json', 'utf-8', l.crs(), 'GeoJson', True)
l.removeSelection()
j += 1
Ian Short
  • 59
  • 8