locked
Need help converting to miles RRS feed

  • Question

  • I have two points which are 1 mile apart from each other. When I run the code below I get a return value other that 1.00

    Any advice?

    DECLARE

     

    @Dominos geography;

    DECLARE

     

    @AceHardware geography;

    ---- Dominos Pizza

    --SET @Dominos = geography::STPointFromText('POINT(-81.281598 29.860768)', 4326);

    ---- Ace Hardware

    --SET @AceHardware = geography::STPointFromText('POINT(-81.276433 29.841076)', 4326);

    -- Dominos Pizza

    SET

     

    @Dominos = geography::STGeomFromText('POINT(-81.281598 29.860768)', 4326);

    -- Ace Hardware

    SET

     

    @AceHardware = geography::STGeomFromText('POINT(-81.276433 29.841076)', 4326);

    SELECT

     

    @Dominos.STDistance(@AceHardware) * 0.621371192237334 AS DistanceInMiles


    Online conversion: http://www.calculateme.com/Length/Kilometers/ToMiles.htm

    Tuesday, June 23, 2009 12:38 AM

Answers

  • Dont use this site to verify the Lat Longs of your addresses!

    http://www.batchgeocode.com/lookup/

    ------------------------------------

    But this works:

    Using:
    http://maps.google.com/maps?hl=en&tab=wl

    From:
    1686 A1A S, St Johns, Florida 32080 (McDonalds alias)

    To:
    1006 A1A S St. Augustine FL 32080 USA

    = 1.0 miles

    ------------------------------

    Using:
    http://www.getlatlon.com/

    1686 A1A S, St Johns, Florida 32080 (McDonalds)
    = 29.866246, -81.2826247

    1006 A1A S St. Augustine FL 32080 USA
    = 29.8804299, -81.2865786

    -------------------------
    Using:
    http://www.calculateme.com/Length/Meters/ToMiles.htm
    1 Meter = 0.000621371192237334 Miles

    http://www.calculateme.com/Length/Kilometers/ToMiles.htm
    1 Kilometer = 0.621371192237334 Miles

    ------------------

    DECLARE @McDonalds geography

    -- using: http://www.getlatlon.com/ to find the Lat Long
    SET @McDonalds = geography::STPointFromText('POINT(-81.2826247 29.866246)', 4326);
    SELECT
    Name,
    Address,
    City,
    -- using: http://www.calculateme.com/Length/Meters/ToMiles.htm
    Spatial.STDistance(@McDonalds) * 0.000621371192237334 AS DistanceInMiles
    FROM
    CustomerDev
    ORDER BY
    Spatial.STDistance(@McDonalds) ASC

    ------------------

    Resulting Query:

    Name:                  Address:           City:                DistanceInMiles:
    McDonalds            1686 A1A S       St. Johns          0.00811585999217824
    The Light House     1006 A1A S       St. Augustine    1.0314...

    -----------------

    As we say in America... "That's close enough for Government work!"

    Thanks!

    • Marked as answer by Rob Vig Wednesday, June 24, 2009 3:56 PM
    Wednesday, June 24, 2009 3:22 PM

All replies

  • Are you sure, that distance between points is 1 mile?
    http://maps.google.com/maps?f=d&source=s_d&saddr=81.281598W+29.860768N&daddr=81.276433W+29.841076N&hl=en&geocode=FaCjxwEdwr0n-w%3BFbRWxwEd79En-w&mra=ls&dirflg=w&sll=29.854416,-81.280804&sspn=0.029851,0.058322&ie=UTF8&ll=29.850918,-81.279001&spn=0.029852,0.058322&t=h&z=15

    Your code is correct in the base, don't forget than STDistance returns value in meters, so conversion to miles must be divided by 1000.
    Matej
    Tuesday, June 23, 2009 8:00 AM
  • Hi Rob,

    It's possible that when you geocoded the location of these stores, the results returned gave you the closest road intersection rather than the exact location.

    According to http://local.botw.org/Florida/Saint_Augustine/Dominos_Pizza/4246235.html, for example, the location of the Dominos Pizza should be south of the A1A S W Pope Road crossing.

    When you move the point accordingly in the Google Map given by matej.us, this calculates the distance by road as 1.2miles, which is probably about 1 mile as the crow flies.
    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Tuesday, June 23, 2009 8:56 AM
    Answerer
  • I used the name "Dominos" and "Ace Hardware" more as an alias than an exact location. I have since plotted an exact 1 mile distance and locations on opposite sides of the USA and have achieved the same discrepancy.

    Summary: The data I have geocoded is of the "geography" data type. I am using that data to call a web service which requires the "geometry" data type. I believe that is why I get results that are "off"...
    ------------
    I am using a mix of code from Chapter 7, 9 and 13 of the brilliant APress book "Beginning Spatial with SQL Server" http://www.apress.com/book/view/1430218290
    (Shameless plug I know - Thanks Alistair for your work, it's great, and very helpful)

    to call a web service to geocode my addresses which I believe is the "geography" data type.

    When I use that data with MS Maps, I believe it needs to be the "geometry" data type.

    I believe this is the reason I get minor discrepancies in my results; not sure.

    When I get the time, I need to look closer at the Microsoft Web Services:

    FindServiceSoap

    and the

    FindAddressSpecification

    to determine if my theory is correct.

    I am also taking a look at this for future projects:
    http://support.microsoft.com/default.aspx/kb/883992

    I appreciate all your thoughts my friends :>

    Robert
    • Edited by Rob Vig Wednesday, June 24, 2009 2:03 PM
    Wednesday, June 24, 2009 1:11 PM
  • Hi Rob,

    geometry and geography are terms that only apply to SQL Server 2008, so it's not really a question of whether the Mappoint web service is compatible with one or the other.
    The MapPoint Web Service, and almost all publicly-available geocoding services, return the coordinates of a geocoded address as a latitude/longitude coordinate pair measured using the WGS84 (EPSG:4326) spatial reference system. Since these are geographic coordinates, to calculate the distance between them in SQL Server you need to use the geography datatype.

    Can we start at the beginning and ask what the coordinates (or addresses, if they are yet to be geocoded) are of the two points that you know to be a mile apart, and how you know the distance between them to be exactly 1 mile? We can then work through to get the answer.

    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Wednesday, June 24, 2009 1:19 PM
    Answerer
  • Dont use this site to verify the Lat Longs of your addresses!

    http://www.batchgeocode.com/lookup/

    ------------------------------------

    But this works:

    Using:
    http://maps.google.com/maps?hl=en&tab=wl

    From:
    1686 A1A S, St Johns, Florida 32080 (McDonalds alias)

    To:
    1006 A1A S St. Augustine FL 32080 USA

    = 1.0 miles

    ------------------------------

    Using:
    http://www.getlatlon.com/

    1686 A1A S, St Johns, Florida 32080 (McDonalds)
    = 29.866246, -81.2826247

    1006 A1A S St. Augustine FL 32080 USA
    = 29.8804299, -81.2865786

    -------------------------
    Using:
    http://www.calculateme.com/Length/Meters/ToMiles.htm
    1 Meter = 0.000621371192237334 Miles

    http://www.calculateme.com/Length/Kilometers/ToMiles.htm
    1 Kilometer = 0.621371192237334 Miles

    ------------------

    DECLARE @McDonalds geography

    -- using: http://www.getlatlon.com/ to find the Lat Long
    SET @McDonalds = geography::STPointFromText('POINT(-81.2826247 29.866246)', 4326);
    SELECT
    Name,
    Address,
    City,
    -- using: http://www.calculateme.com/Length/Meters/ToMiles.htm
    Spatial.STDistance(@McDonalds) * 0.000621371192237334 AS DistanceInMiles
    FROM
    CustomerDev
    ORDER BY
    Spatial.STDistance(@McDonalds) ASC

    ------------------

    Resulting Query:

    Name:                  Address:           City:                DistanceInMiles:
    McDonalds            1686 A1A S       St. Johns          0.00811585999217824
    The Light House     1006 A1A S       St. Augustine    1.0314...

    -----------------

    As we say in America... "That's close enough for Government work!"

    Thanks!

    • Marked as answer by Rob Vig Wednesday, June 24, 2009 3:56 PM
    Wednesday, June 24, 2009 3:22 PM