16

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.

Bruce
  • 1,647
  • 4
  • 20
  • 22
ParoX
  • 5,685
  • 23
  • 81
  • 152
  • Only thing I have been able to find out is that the semi minor axis tends to be expressed to two more decimal places, which has exactly zero influence on the overall result. I sincerely have no idea how MSSQL determines it's distance. – David Mulder Jun 02 '15 at 17:24
  • Using the form under [Live examples on the Vincenty solutions of geodesics on the ellipsoid page](http://movable-type.co.uk/scripts/latlong-vincenty.html) I get 9,030,706.728 m, which is pretty close to 9030715.95721209 (9 meters out of 9x10^9 meters) – geocodezip Jun 04 '15 at 00:02
  • Yeah, thats also the same distance I got using the jsbin solution I posted at the bottom of my post. It's very close, but I need it to be more exact. – ParoX Jun 04 '15 at 02:59
  • I don't think that the functions provided in the Maps API are intended for heavy-duty GIS applications that require the extreme precision you're looking for. For these kinds of applications, you're better off finding some other library that meets your specific needs and using that. You could try https://github.com/chrisveness/geodesy, although I'm not sure if it will give you more accurate calculations than the libraries other people have mentioned. – not_a_bot Jun 05 '15 at 17:42

3 Answers3

5

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.

Community
  • 1
  • 1
David Mulder
  • 26,123
  • 9
  • 51
  • 114
  • If accuracy isn't an issue then google maps built in function and MS SQL SRID 104001 will work as a solution that matches close enough. In my specific application accuracy does matter but seems approach 2 may be what is needed. – ParoX Jun 09 '15 at 15:03
4

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.

jwd630
  • 4,529
  • 1
  • 20
  • 22
  • GeographicLib returns 9030706.727997527 which is the same as the Vincenty formula. I am starting to think MSSQL is inaccurate... – ParoX Jun 05 '15 at 22:49
  • Can you use the Vincenty formula on the server instead of the MSSQL formula? Another suggestion is to use the javascript version on the client as the polyline changes, and sync up the end points of the line with the back end either on a timer or after the drawing is complete. – brenzy Jun 06 '15 at 10:53
  • Given what I think may be a misunderstanding about the projection used by Google Maps you may want to check out [Geodesic lines, circles, envelopes in Google Maps](http://geographiclib.sourceforge.net/scripts/geod-google.html) and the related instructions which may help explain the differences that may account for your discrepancy. – jwd630 Jun 07 '15 at 18:24
  • Im aware of the projection that google maps uses, the javascript methods I have been using are reliant on 4326. Google maps geodesic option for polyline actually does not have any effect in their internal calculations for the length of a polyline (weirdly). They simply take every long/lat point in the line and calculate based on a spherical model of the earth. Thats why google and MSSQL are so far off, where as Vincenty uses an ellipsoidal model of the earth, and much closer to MSSQL, but I can't find the damn formula Microsoft is using, I just know its probably ellipsoidal – ParoX Jun 08 '15 at 03:47
  • My solution is seemingly going to be to dumb down the MSSQL length function by using a spherical model of the earth so it matches google maps and store it as a column with the geometry. The alternative is to write a SQL procedure that uses vincenty. I have to make one them match, one way or another and I'd prefer the most accurate way, but it seems this will be most costly. – ParoX Jun 08 '15 at 03:51
0

The only way you can be sure is to get the Microsoft programmer on the phone, disassemble the code, or just send a query to a SQL Server database and accept it's results.

This may be of help to you: Using SQL Spatial Types in a .NET Application

If you want to try your hand at disassembling, I believe the method is in the Microsoft.SqlServer.Types.dll file.

Community
  • 1
  • 1
Brian Pressler
  • 6,653
  • 2
  • 19
  • 40
  • Option 2 seems easier than Option 1. Option 3 is not viable for a solution. Any recommendations on how to disassemble it? I assume its written in a .NET language which is notoriously bad at preventing disassembly. – ParoX Jun 02 '15 at 23:41
  • If you are trying to match what SQL Server does, then I assume you are using SQL Server in the back end for something else. If not... why do you care if it matches SQL Server? And if you're already connecting to a SQL Server, why is it not viable to put your geometry queries in procedures on the SQL Server or Web Services that query SQL Server? – Brian Pressler Jun 03 '15 at 18:03
  • I am creating a LRS system using a javascript interface, I need to know the distance of a polyline about 10 times a second as it changes, this can be done easily with pure javascript (I've done it). This cannot be done with a backend call to SQL Server because there is too much overhead and it would spam the server. However, the distances that the javascript function calculates must match SQL server or else there will be discrepancies between the view and model. – ParoX Jun 03 '15 at 21:33
  • I was able to decompile `Microsoft.SqlServer.Types.dll` no problem, but I saw it referenced a function `GLNativeMethods.GeodeticPointDistance` in `SqlServerSpatial120.dll`. Loading the spatial dll did not result in high level code as it doesnt seem to be .NET. I loaded it into IDA and was able to get the assembly but its hard to grasp. I did manage to find a string where it references what measurement it uses. I posted it in the original question as an edit – ParoX Jun 04 '15 at 03:44
  • Was able to get C from the code, but its really obfuscated. I was hoping I would find a unique number that would help me find the formula. Interestingly the unique number `16384` does not appear in the functions, suggesting it may not be Vincenty . Here are the useful functions: http://pastebin.com/t1g5UCFS – ParoX Jun 04 '15 at 05:01