Question
__Erik__ on Thu, 06 Apr 2017 19:41:03
Hello.
I'm very familiar with T-SQL but not much experienced with spacial datatype. I have an interesting challenge I'm facing now.
I have gps coordinates (lon/lat) and I have to calculate the distance betwen points. I know it's easy (except performance wise...) to implement using FirstGeoFieldSTDistance(SecondGeoField). The thing I don't know how to perform is to calculte this on top of a shape (which I don't know yet how I will implements, probably again a bunch of lon/lat.
Here's an example, I would like to know the distance between point 1 and 2 but I want two different values. The distance within the shape and the distance outside the shape.
How can I accomplish this?
Thanks for any help.
Sponsored
Replies
Lin Leng on Tue, 11 Apr 2017 09:48:43
Hi __Erik__,
If I understand this correctly, did you mean you wish to calculate the distance between 1,c and c,2?
If so, that could be done by using STIntersection and STLength. For example:
DECLARE @g1 geometry = 'LINESTRING(-1 2, 2 2)'; DECLARE @g2 geometry = 'POLYGON((1 1, 1 3, 3 3, 3 1, 1 1))'; SELECT @g1.STLength(), @g1.STIntersection(@g2).STLength(), @g1.STLength()- @g1.STIntersection(@g2).STLength();
If you have any other questions, please let me know.
Regards,
Lin
__Erik__ on Tue, 11 Apr 2017 14:27:16
Hi Lin,
Correct, I want to know distance between 1-c and c-2.
I'm using Geography datatype (converted from Long/Lat) while you're referring to Geometry, can this still be accomplished? Shall I change my datatype from Geography to Geometry considering that the coordinates I'm dealing with are local to a single state in the US (which means that the rounding portion of the earth doesn't have much of an impact on the distance I guess...)
Thanks!
Lin Leng on Mon, 17 Apr 2017 10:16:59
Hi __Erik__,
>>I'm using Geography datatype (converted from Long/Lat) while you're referring to Geometry, can this still be accomplished?
Yes it works with geography data type as well. Just change @g1 and @g2 with your real data see how it goes.
If you have any other questions, please let me know.
Regards,
Lin
locoEtl on Mon, 17 Apr 2017 10:22:09
Hi, this is a typical calculation in any Navigation scenario, the principle is referred to as Great Circle Navigation where a great circle is the shortest distance between two lat/long points, have a look at
http://www.movable-type.co.uk/scripts/latlong.html