Simple distance query is not working

Question

I have latitude and longitude defined as decimal(9, 6) in my zip code table, per the zip code database company's instructions.

I take a lat/lon feed it to a function in my c# program and it gives me lat/lon in boundaries for getting a list of lat/lon from the database (radius distance)

The sql code does not work with the longitude.

SELECT TOP(10) * FROM USZipCode WHERE (Latitude BETWEEN 34.7600283409472 AND 37.6513716590528) AND (Longitude BETWEEN -76.383333 AND -76.818238)

But if I skip the longitude then it works, or at least returns records even if they are the wrong ones.

SELECT TOP(10) * FROM USZipCode WHERE (Latitude BETWEEN 34.7600283409472 AND 37.6513716590528)

The only thing I can think of; is that since the longitude is negative that the sql ain't working?

This is SQL SERVER 2008 R2 and I've also tried the Lat > x and Lat > y etc.

Answer 1

try

SELECT TOP(10) * FROM USZipCode WHERE (Latitude BETWEEN 34.7600283409472 AND 37.6513716590528) AND (Longitude BETWEEN -76.818238 AND -76.383333)

It IS because of negative numbers. -76.3 is a larger number than -76.8, so you need to reverse them.

Answer 2

Flip your upper and lower negative limits:

-76.818238 is **less than** -76.383333

Answer 3

If you want to compute with a radius it's more complicated, use this function

    CREATE FUNCTION [dbo].[getDistance]
(
@lat1 float,
@lon1 float,
@lat2 float,
@lon2 float
)
RETURNS float
AS
BEGIN
    -- Declare the return variable here
    DECLARE @result float

    -- Add the T-SQL statements to compute the return value here
    SET @result = (SQRT((69.1*(@lat2-@lat1)) * (69.1*(@lat2-@lat1)) + (53.0*(@lon2 - @lon1))*(53.0*(@lon2 - @lon1)))/0.621369949495) 

    -- Return the result of the function
    RETURN @result

END

And like this

SELECT * FROM myTable
WHERE dbo.getDistance(latitue,longitude,@lat,@long) <= @radius

Answer 4

Run this query and see if you have any records in this range -

SELECT TOP(10) * FROM USZipCode 
WHERE (Longitude BETWEEN -76.818238 AND -76.383333)

My guess is that you do not have any data in the longitude range specified and also -76.818238 is less than -76.383333

Answer 5

-76.818238 is less than -76.383333.

Try:

 AND (Longitude BETWEEN -76.818238 AND -76.383333)

Answer 6

The lower value needs to be first in the BETWEEN expression... Not the lower absolute value.

DECLARE @MyTable TABLE
(
  ID int PRIMARY KEY
)


DECLARE @i int
SET @i = -10
WHILE @i < 11
BEGIN
  INSERT INTO @MyTable (ID) SELECT @i
  SET @i = @i + 1
END

SELECT * FROM @MyTable
WHERE ID between 3 AND 6

  -- no records because there is no value which is greater than -3 and less than -6
SELECT * FROM @MyTable
WHERE ID between -3 AND -6

SELECT * FROM @MyTable
WHERE ID between -6 AND -3

SELECT * FROM @MyTable
WHERE ID between -3 AND 3

---

Fun fact, Sql Server's query optimizer detects the bad range in that second query and does not even perform IO on @MyTable.