Google Maps and SQL Server LINESTRING length inconsistent

Question

Google Maps and MSSQL seem to disagree on how to calculate the distance/length of a polyline/linestring using SRID 4326.

MSSQL:

SELECT geography::STGeomFromText('LINESTRING(-98.78 39.63,2.98 27.52)', 4326).STLength()

Result: 9030715.95721209

Then Google Maps:

http://jsbin.com/niratiyojo/1/

Result: 9022896.239500616

At first I thought it was just a different radius of earth measure so I played around with that and it turned out to be more.

I need my JavaScript interface to match what MSSQL would report to remain consistent and accurate. Where or how can I find how MSSQL calculates their STLength() and can it be replicated in JavaScript?

Update:

I realized if I do

SELECT GEOGRAPHY::STGeomFromText('LINESTRING(-98.78 39.63,2.98 27.52)', 104001).STLength() * 6378137

Then MSSQL returns 9022896.23950062

The new SRID in MSSQL :

New “Unit Sphere” Spatial Reference ID The default spatial reference ID (SRID) in SQL Server 2012 is 4326, which uses the metric system as its unit of measurement. This SRID also represents the true ellipsoidal sphere shape of the earth. While this representation is most accurate, it’s also more complex to calculate precise ellipsoidal mathematics. SQL Server 2012 offers a compromise in speed and accuracy, by adding a new spatial reference id (SRID), 104001, which uses a sphere of radius 1 to represent a perfectly round earth.

So the problem is that Google Maps does not use a true ellipsoidal sphere in calculations. I am looking for a javascript function that gets 9030715.95721209 as witnessed.

I tried Vincenty direct formula here: http://jsbin.com/noveqoqepa/1/edit?html,js,console and while it's closer I still cannot match MSSQL

Edit 2:

I was able to find the measurements it uses:

SridList._sridList.Add(4326, new SridInfo(4326, "EPSG", 4326, "GEOGCS[\"WGS 84\", DATUM[\"World Geodetic System 1984\", ELLIPSOID[\"WGS 84\", 6378137, 298.257223563]], PRIMEM[\"Greenwich\", 0], UNIT[\"Degree\", 0.0174532925199433]]", "metre", 1.0, 6378137.0,
6356752.314));

but seemingly plugging those into Vincenty yield no luck.

Answer 1

After going through all the different options your best option seems to be to use the same literal function on both the server and the client. This can be achieved in two ways:

Approach 1: Use the SQL function on the client

In this case you would trigger an AJAX query on the client to the server, which in turn queries the database for the specific calculation you want and return it to the client.

Approach 2: Use the Javascript function in SQL

This might sound quite impossible, but using xp_cmdshell it's possible to execute command line commands from sql, and you can run javascript from the terminal using something like node.js, so everything you're left with is implementing the Vincenty function to be called from the command line.

The big question here is how the performance will be. Starting and stopping a node instance every few seconds seems like a relatively bad idea, so it would be far more optimal to code a service in node to do this work, however I would not know what the best way would be for sql to interact with such a service. The simplest approach would probably be to have it do a http request to something like localhost:8888/?lat1=&lng1=&etc., but that's starting to be nearly as complex as approach 1.

Conclusion

Approach 1 still seems to be the most reasonable one, although approach 2 gives you a lot more flexibility to do exactly what you want. For a private project or a perfectionist project I think I would go with approach 2, for a 'we need to finish this and we do not have time for surprises or optimalization'-kinda project I think I would advise approach number 1.

Answer 2

You do realize that Google Maps uses Web Mercator, aka EPSG:3857, and not WGS EPSG:4326? The Web Mercator article and its references may help explain the differences.

Try the JavaScript implementation of GeographicLib and/or Proj4js. As this question/answer on gis.stackexchange will indicate, underlying implementations of spatial data conversions can be subtly different. You at least need something better than Vincenty direct.