admin管理员组

文章数量:1344588

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:

/

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 plex to calculate precise ellipsoidal mathematics. SQL Server 2012 offers a promise 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: ,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.

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./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 plex to calculate precise ellipsoidal mathematics. SQL Server 2012 offers a promise 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./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.

• javascript
• sql-server
• google-maps
• gis
• sqlgeography

Share Improve this question Follow edited Jun 26, 2015 at 7:54 Bruce 1,68144 gold badges2020 silver badges2222 bronze badges asked May 29, 2015 at 17:48 ParoXParoX 5,9412525 gold badges8686 silver badges155155 bronze badges 4

• 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 Commented Jun 2, 2015 at 17:24
• Using the form under Live examples on the Vincenty solutions of geodesics on the ellipsoid page I get 9,030,706.728 m, which is pretty close to 9030715.95721209 (9 meters out of 9x10^9 meters) – geocodezip Commented Jun 4, 2015 at 0: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 Commented Jun 4, 2015 at 2: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 github./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 Commented Jun 5, 2015 at 17:42

Add a ment  | 

3 Answers 3

Sorted by: Reset to default 5 +50

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 mand line mands 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 mand 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 plex 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.

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.

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.

本文标签： javascriptGoogle Maps and SQL Server LINESTRING length inconsistentStack Overflow