1

I have a table in SQL Server with an STSrid of 0. I would like to convert this table to GeoJSON using ogr2ogr. The problem is, despite converting to EPSG:4326, the resulting coordinates are not valid (verified using GeoJSONLint). The GeoJSON file's crs key lists the coordinate reference system as the desired CRS84, but the feature coordinates are far too large.

A sample feature appears as such:

{ "type": "Feature", "properties": { "TR": "00028" }, "geometry": { "type": "Point", "coordinates": [ 447366.18450000137, -920054.45910000056 ] } }

The output coordinates as seen above are identical to the input:

select TR, Shape.STX, Shape.STY from [table] where TR = '00028'

results in:

00028   447366.184500001    -920054.459100001

I'm entering the following ogr2ogr command:

ogr2ogr -f "GeoJSON" "sqlexport.json" "MSSQL:server=[server];database=[db];trusted_connection=yes;" -sql "select TR, Shape from [table]" -a_srs "EPSG:4326"

Edit: Despite SQL Server listing the STSrid as 0, ArcGIS reads it as GCS_North_American_1983:

Custom
Projection: Two_Point_Equidistant
False_Easting: 0.0
False_Northing: 0.0
Latitude_Of_1st_Point: 49.0
Latitude_Of_2nd_Point: 49.0
Longitude_Of_1st_Point: -110.0
Longitude_Of_2nd_Point: -77.0
Linear Unit: Meter (1.0)

Geographic Coordinate System: GCS_North_American_1983
Angular Unit: Degree (0.0174532925199433)
Prime Meridian: Greenwich (0.0)
Datum: D_North_American_1983
  Spheroid: GRS_1980
    Semimajor Axis: 6378137.0
    Semiminor Axis: 6356752.314140356
    Inverse Flattening: 298.257222101

Despite this, if I change the ogr2ogr command to the following:

ogr2ogr -f "GeoJSON" "sqlexport.json" "MSSQL:server=[server];database=[db];trusted_connection=yes;" -sql "select TR, Shape from [table]" -s_srs "EPSG:4140" -t_srs "EPSG:4326"

The coordinates still remain unchanged.

Improve this question
14
  • how do the coordinates look in SQL server when you use table.geometry.STX / .STY?
    – Inactivated Account
    Commented Nov 20, 2015 at 17:33
  • @mapBaker: see above edit. The input coordinates are identical to the GeoJSON output
    – baobobs
    Commented Nov 20, 2015 at 17:42
  • those coordinates don't look like WGS 84 / 4326... they look to be in UTM or something...
    – Inactivated Account
    Commented Nov 20, 2015 at 17:46
  • 2
    No, ArcGIS recognizes it as a custom projected coordinate reference system using the two point equidistant projection, and based on NAD83. On your 2nd ogr2ogr command EPSG:4140 is deprecated (no longer in use), NAD83 (CSRS98). The data is not in a geographic coordinate system. What happens if you use -t_srs 4326, but omit both -s_srs and -a_srs?
    – mkennedy
    Commented Nov 20, 2015 at 20:53
  • 1
    I gave the epsg:3857 example for showing you that ogr2ogr can do conversion. All you need to do is to use correct -s_srs and I am sure you can solve it by studying how to feed the two point equidistant parameters from the ArcGIS report into +proj4 string that you feed as -s_srs for ogr2ogr. Lat1 lon1 lat2 lon2 are the parameters you must adjust.
    – user30184
    Commented Nov 20, 2015 at 22:31

1 Answer 1

Reset to default
0

In order to determine the correct coordinate system for the input SQL Server data, I exported the data as a shapefile, and ran the following command on the prj file:

gdalsrsinfo [table].prj

This generated the following PROJ.4 string:

+proj=tpeqd +lat_1=49 +lon_1=-110 +lat_2=49 +lon_2=-77 +x_0=0 +y_0=0 +datum=NAD83 +units=m +no_defs

I then placed the PROJ.4 string in front of the -s_srs flag in the ogr2ogr command like so:

ogr2ogr -f "GeoJSON" "sqlexport.json" "MSSQL:server=[server];database[db];trusted_connection=yes;" -sql "select TR, Shape from [table]" -s_srs "+proj=tpeqd +lat_1=49 +lon_1=-110 +lat_2=49 +lon_2=-77 +x_0=0 +y_0=0 +datum=NAD83 +units=m +no_defs" -t_srs "EPSG:4326"
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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