CODEWITHSUNDEEP
sqlsql-server-2008
Rick Rat
Rick Rat

Reputation: 1732

Simple distance query is not working

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.

Upvotes: 2

Views: 235

Answers (6)

Amy B
Amy B

Reputation: 110111

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.

Upvotes: 2

user467105
user467105

Reputation:

-76.818238 is less than -76.383333.

Try:

 AND (Longitude BETWEEN -76.818238 AND -76.383333)

Upvotes: 2

Sachin Shanbhag
Sachin Shanbhag

Reputation: 55489

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

Upvotes: 0

remi bourgarel
remi bourgarel

Reputation: 9389

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

Upvotes: 0

Brad
Brad

Reputation: 15577

Flip your upper and lower negative limits:

-76.818238 is **less than** -76.383333

Upvotes: 2

David
David

Reputation: 73564

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.

Upvotes: 1

Related Questions