Post reply

Zip Code search - Msg 3623 "A domain error occurred" (code inside)

  • January 3, 2010 at 1:32 pm

    #141397

    Hi,

    DECLARE @start_lat float

    DECLARE @MaxNorthLat float

    DECLARE @MaxSouthLat float

    SELECT @start_lat = (SELECT Z.Latitude FROM dbo.ZipCodes Z WHERE Z.ZipCode = @zip)

    SET @MaxNorthLat = @start_lat + (10*360/24859.82)

    SET @MaxSouthLat = @start_lat - (10*360/24859.82)

    DECLARE @radius int

    SET @radius = 10

    SELECT

    TotalRows = COUNT(*) OVER(),

    RowNum = ROW_NUMBER()OVER (ORDER BY P.Zip),

    P.ProviderID, P.LastName, P.FirstName

    , z1.ZipCode, z1.State, DistanceInMiles = dbo.fnDistance(z1.Latitude, z1.Longitude, z2.Latitude, z2.Longitude)

    FROM dbo.Providers P WITH (NOLOCK)

    INNER JOIN dbo.ZipCodes z1 ON z1.ZipCode = P.Zip

    JOIN dbo.ZipCodes z2

    on z1.Latitude between z2.Latitude - @radius and z2.Latitude + @radius

    and z1.Longitude between z2.Longitude - @radius and z2.Longitude + @radius

    AND z1.Latitude BETWEEN @MaxSouthLat AND @MaxNorthLat

    AND (@zip IS NULL OR z2.ZipCode = @zip)

    - dbo.fnDistance = SELECT @Distance = 3963.0 * ACOS(SIN(RADIANS(@Lat1)) * SIN(RADIANS(@Lat2)) + COS(RADIANS(@Lat1)) * COS(RADIANS(@Lat2)) * COS(RADIANS(@Long2 - @Long1)))

    -- this code results in "Msg 3623 : A domain error occurred"

    -- Error occurs with certain zip codes but not with others (e.g. '10014' generates error, but not '10023')

    Also, problem only happens in SQL 2005, not in SQL 2008. Help please!

    UPDATE: I found this, implemented it, and it solved the problem.

    -- http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-zipcode-latitude-longitude-pr

  • January 4, 2010 at 5:48 pm

    #1098713

    Thanks for posting back with the solution.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply