2

I have 2 MultiLineString representing the a route (first is driving there and the second is the return way) .

MULTILINESTRING ((34.787315938035292 31.318235314177119, 34.788431415005249 31.336622682320776, 34.794144933818309 31.355201806839506, 34.794207563998924 31.371163684259038, 34.792967344484573 31.375581921229493, 34.789226146087763 31.390623411898762, 34.788274952156939 31.408575008023988, 34.786368903544009 31.42299930464733, 34.786328720608147 31.42193697540446, 34.786297827387109 31.422105913663092, 34.786280312670428 31.421992838029546, 34.786190597348 31.421582726711659, 34.786161861793758 31.421905927160644, 34.784898002487694 31.424062281290652, 34.773401914392345 31.434990008872095, 34.765590772924938 31.446642271859293, 34.77342394487551 31.466799593779264, 34.77975547041099 31.490976936335215, 34.782630232155611 31.506823501958817, 34.792679583505226 31.531054387260554, 34.800484354694987 31.551364145061932, 34.806546173040267 31.574946250797954, 34.813199698392914 31.597257951527482, 34.814700882755012 31.619577056013927, 34.809541545607935 31.657018778594132, 34.80638283046715 31.674769044083895, 34.815697398246741 31.69878758883678, 34.821170796592632 31.721351512377144, 34.822676820645704 31.740659141022942, 34.826406253975371 31.760366608188313, 34.832777976436191 31.795874550345438, 34.840133770521163 31.803493762964539, 34.857643693856282 31.809682398342275, 34.876558349351328 31.809001817509152, 34.899775788981991 31.810874151671349, 34.915550260013873 31.814641705371283, 34.92078885084598 31.819327945165384, 34.94872743329605 31.826006370096646, 34.968653641494242 31.831077555261356, 34.977436258261335 31.8348796713872, 34.988078880595516 31.834460104995458, 34.99298330536876 31.826840136006965, 35.000267546905079 31.826135241324245, 35.005568854286416 31.825923237904671, 35.017740547302338 31.8215162498958, 35.02155196628523 31.818288829834177, 35.026913523519241 31.813341112120234, 35.03143746024918 31.809795839880959, 35.038159534410212 31.80619724694477, 35.045420139243944 31.804745994112629, 35.053171381816789 31.804966181835955, 35.06486714082564 31.804141661709476, 35.075841806779032 31.800281337346696, 35.088383165072827 31.800341312456926, 35.106401658005012 31.801455597207628, 35.126101243739683 31.800681742893538, 35.146976345262694 31.797221642316991, 35.162196277950279 31.793296817012131, 35.171594439546396 31.797658570113885, 35.187671634314036 31.797928602082614, 35.188362774999014 31.796533416394119, 35.1887256549307 31.7961346714363, 35.190472346375131 31.794962708212374, 35.192562710517549 31.794612374668478, 35.192707852005682 31.793637348918882, 35.193583458825614 31.793129756791721, 35.194747974527196 31.793406487587923, 35.196095096006964 31.79164621288934, 35.196955729849186 31.790773244292048, 35.1978874456438 31.790160915552967, 35.198711620213004 31.789879413000829, 35.199295088482693 31.787229378909604, 35.19850752277577 31.785375424789216, 35.199416396367077 31.784687679081976, 35.199241688426945 31.782313977270359, 35.198391385845284 31.781402127099458, 35.201245278823734 31.776056353428189))

MULTILINESTRING ((35.200768733043091 31.77473722793188, 35.204546738625524 31.774719539653546, 35.208965075961345 31.776185106153822, 35.208640148891355 31.782029315838962, 35.209755124306163 31.782465782224509, 35.211377925506667 31.781691930524566, 35.213549710915274 31.780813498217512, 35.214769003020763 31.78168291327713, 35.214692303698442 31.780946053301573, 35.212572272348339 31.781205360240914, 35.208643904703017 31.783054725633296, 35.208298951212782 31.783020204739561, 35.203248270523765 31.782224483116469, 35.198547661079523 31.786334626536405, 35.199845310730879 31.787516017026629, 35.200144134813939 31.788041155898213, 35.197250692115119 31.790485693913038, 35.191107763219378 31.795067815242238, 35.181610401764608 31.801656830805612, 35.1687011405429 31.795676931971681, 35.159244819963 31.795794581845737, 35.119424750248832 31.798775201079778, 35.080393585902307 31.800856179659302, 35.063209196166355 31.805088407881986, 35.048056788398441 31.804031639631894, 35.032927570632985 31.808478772457441, 35.020924279147472 31.81877639146, 34.990129787588472 31.829259174717787, 34.988047951875991 31.839040689444193, 34.977978798901404 31.835101996624207, 34.9498888706375 31.825967830705292, 34.92736197558343 31.823111392882268, 34.91695636557349 31.815625240675068, 34.903688415937665 31.811906632219383, 34.882969413185968 31.810547012219338, 34.86002652341891 31.810052256918588, 34.8400240232646 31.803567891209664, 34.830159736601907 31.79428193887949, 34.832461959577664 31.781116935605244, 34.827114623267633 31.762582638502867, 34.822782721105142 31.743736272787874, 34.820540303486339 31.720919484741522, 34.814209114195862 31.695453086060326, 34.806275341573269 31.675224452093136, 34.81001464412418 31.653220249705704, 34.814807584458386 31.623882775804965, 34.813291228448193 31.60270186644005, 34.8080051217612 31.582810947810554, 34.803038127301519 31.560726326313713, 34.797198877391246 31.54151166321618, 34.786754234512 31.523229452759789, 34.782952679629339 31.504485052320216, 34.776143426121912 31.483992375769422, 34.77342018413583 31.467251749582854, 34.766455832382029 31.450437751509558, 34.765905289603033 31.442231443933725, 34.785259642730011 31.422175881020067, 34.789073528508013 31.401593669076053, 34.791297259262187 31.381621922859551, 34.795717357038875 31.365271563573277, 34.792079987685526 31.350441990935515, 34.786453724639145 31.329829087974357, 34.788396830251187 31.309436171497104, 34.790810764157918 31.289914211434404, 34.7976451665849 31.287943033661829, 34.80366544178834 31.286754464613146))

The 1st route is around 95km and the 2nd is 124km (approx).

When doing STLength() on them I get the following results :

0.909006861532263
0.983569104560351

Any idea why ? And how can I get the correct length of the route ?

Interesting case - when I serve the line to OpenLayers and I do feature.geometry.getLength I get the following results :

111km
120km

Here is how I got the length using OpenLayers:

I've upload the feature from SQL to OpenLayers using OpenLayers.Format.WKT() The data in sql is in EPSG4326 (and so the OSM map)

And then I did the following :

layer.features[0].geometry.getLength()

enter image description here

Thing is that the length is still not correct. More over , How can I calculate it correct on SQL ? What do I need to do ? (The geometry is in EPSG4326 as I mentioned before)

6
  • Is data stored as a geography or a geometry? If the latter, I think it's treating the lat/lon values as 2D planar and that's the distance (in decimal degrees) that you're getting.
    – mkennedy
    Jun 24, 2013 at 18:28
  • data stored in geometry (EPSG4326) but when I convert it to geography (geography::STGeomFromText...) I get 94565.9363835203 and 102142.09338516
    – Alophind
    Jun 24, 2013 at 19:08
  • Those values are geodesic distances (on the WGS84 ellipsoid). I haven't the time today to check various other methods to see if I can get the 111km and 124km values.
    – mkennedy
    Jun 24, 2013 at 22:34
  • @mkennedy - Will you be able to do it ? Later on the user will have the ability to split / merge route and than I'll need to re calculate the length. As Stated below , I've converted the Geom to Geog and got the follwing results : 94565.9363835203 102142.09338516 still its not the real length.
    – Alophind
    Jun 25, 2013 at 4:47
  • I'm not going to be able to--it's just too busy in the next few weeks. But you listed the first route as about 95 km, so that looks about right. Are you sure the 2nd one is approx 124 km?
    – mkennedy
    Jun 25, 2013 at 6:03

2 Answers 2

3

Check this for some clarifications: https://stackoverflow.com/questions/2037354/sql-server-units-question. Basically, you need to use a spatial reference system to do the conversion. It seems to me that you haven't defined that and therefore you get the results in decimal degrees.

As you may know, this relation gives you an idea why you get the decimal values in your first results:

1 degree = 111.12 km [A minute (1/60th of a degree) equals 1.852 km]

this is very general, though. This changes with longitude and latitude, so basically your geographical position (where you are) in the world. That may be the reason of the 0.9 and 0.98 you got at the beginning.

The OSM basemap is spherical mercator (EPSG:900913, so meters), that's why you get the results in meters by default. In OpenLayers all measurements are based by default on the basemap projection, so in your case, meters because the OSM basemap. You have only EPSG:4326 in your code to be able to overlay your layer in decimal degrees (multilinestring features).

Are you working in SQL SERVER right? if so, you need to read this to do what you want: http://conceptdev.blogspot.de/2007/12/sql-server-2008-geography-vs-geometry.html

Hope this helps,

5
  • I've updated original question with how I do it in Openlayers , Still I dont have solution how to fix the length calculation - Didn't understand exactly what I need to do.
    – Alophind
    Jun 24, 2013 at 19:07
  • I edited my answer, please take a look at it and try the link I suggested you.
    – Gery
    Jun 24, 2013 at 20:15
  • I've read your link , When I run the following command : "select [Geom].STSrid from Table" I get that my SRID is 4326 which means that the length should be in metre and unit conversion factor is 1. so... Why am I getting wrong result? Btw , When converting the geom to geog I get : 94565.9363835203 102142.09338516
    – Alophind
    Jun 25, 2013 at 3:56
  • those values from the query are the same values coming from openlayers? If so, that should be the real length. How do you know in the first place that your lengths are 111 and 120 km? probably that's also the problem.
    – Gery
    Jun 25, 2013 at 5:31
  • The guy who sent me the coords told me that , I didnt count on it today and recorded a route using my iPhone and uploaded it to MSSQL , there was 2m difference between the route length on iPhone and what SQL gave me. I'll do more tests , Probably get geography is the correct length.
    – Alophind
    Jun 25, 2013 at 20:24
0

EPSG:900913 claims to have metres as units, but they only correspond to real metres at the equator. The more to the poles you come, you get differences to real metres. Look at Greenland on Openstreetmap, it is not really so large.

For real metres, you better use the UTM projection for your part of the world.

2
  • In my country we use EPSG 2039 but it doesn't exists in SQL Server...
    – Alophind
    Jun 25, 2013 at 20:23
  • in this case you could install postgis and do the calculations there, this software seems to have this in its spatial reference system (spatial_ref_sys): spatialreference.org/ref/epsg/2039/postgis, or you could you that info to put it in sql server
    – Gery
    Jun 25, 2013 at 20:40

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.