locked
Calculate distance between two points (latitude/longitude) but also within a shape RRS feed

  • Question

  • 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.

    

    Thursday, April 6, 2017 7:41 PM

All replies

  • 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


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 11, 2017 9:48 AM
  • 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!

    Tuesday, April 11, 2017 2:27 PM
  • 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

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 17, 2017 10:16 AM
  • 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

    Monday, April 17, 2017 10:22 AM