BETWEEN clause for finding coordinates

[mrMarcus]

Here's my query:

 

SELECT `id`
FROM `listings`
WHERE `latitude` BETWEEN '35.881083290234486' AND '41.86223796923285'
AND `longitude` BETWEEN '-90.79428164141848' AND '-101.78060976641848'
LIMIT 25

 

Here's the issue... Let's concentrate on the `longitude` part.

 

The above query returns no results; however, if I change the latter (-101.78060976641848) to a value below '-101.78060976641848' (or above, whichever way you look at it) such as '-99.999999999' the query returns results.

 

It seems that as long as both `longitude` values are under -100.xxxxx or above -100.xxxxxx then the query works, but if one is above and one is below then nothing is returned.

 

Both `latitude` and `longitude` are VARCHAR(20)

 

For example, this works:

 

SELECT `id`
FROM `listings`
WHERE `latitude` BETWEEN '35.881083290234486' AND '41.86223796923285'
AND `longitude` BETWEEN '-90.79428164141848' AND '-99.78060976641848'
LIMIT 25

 

As does this...

 

SELECT `id`
FROM `listings`
WHERE `latitude` BETWEEN '35.881083290234486' AND '41.86223796923285'
AND `longitude` BETWEEN '-100.79428164141848' AND '-103.78060976641848'
LIMIT 25

 

/stumped.

[fenway]

Why are you storing numbers as VARCHAR?

[mrMarcus]

Thanks for the reply.

 

Because I cannot get the negative values to work with any number based field type.

 

Suggestions on a field type to store coordinate values?

[mrMarcus]

I tried changing the field type to DECIMAL(10,6) and now I get no results returned at all:

 

SELECT `listing_id`
FROM `coords`
WHERE `lat` BETWEEN 40.6898 AND 46.26423
AND `lng` BETWEEN '-70.97494' AND '-91.9612'
LIMIT 25

 

Am I using the BETWEEN clause incorrectly (does it not work well with numeric values such as what I have)?  I know people talk about SPATIAL indexing and such, but this is just a small project and I thought I was complete until the issue with the negative numbers being on either side of -100

 

(created a different table for testing... exact same data)

[mrMarcus]

New query works... removed BETWEEN clause for longitude (`lng`)

 

SELECT `listing_id`
FROM `coords`
WHERE `lat` BETWEEN 34.40786104924253 AND 46.09689044689511
AND NOT `lng` > -77.41293398516848 AND `lng` >= -101.38559023516848
LIMIT 25

 

Solved... sorta.

[PFMaBiSmAd]

You do realize that expr BETWEEN min AND max expects the min value to be less than the max value and -101.xxxx is less (a larger negative value) than -77.yyyy (you would need to reverse the order of the operands.)

[mrMarcus]

You do realize that expr BETWEEN min AND max expects the min value to be less than the max value and -101.xxxx is less (a larger negative value) than -77.yyyy (you would need to reverse the order of the operands.)

 

When I did that mysql returned incorrect results, ie. records that weren't actually within the boundaries specified (both `lat` and `lng` coordinates).  The data is being pulled from a Google Map based on the viewport.  For example, when I switched the values as you stated, I would receive records that were waaaay out of the maps viewport.  Now this might be another issue altogether.

 

Thank you for your reply as it does make sense logically.