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