How to compare the value of numbers saved as VARCHAR in mySQL?

Navigation
2

I have a table named coordenadas_cache , with 6 fields: 

codigo (int, auto increment),
latitude_sul (VARCHAR(10)),
latitude_norte (VARCHAR(10)),
longitude_oeste (VARCHAR(11)),
longitude_leste (VARCHAR(11)),
endereco (VARCHAR(80))

In it are stored bounds, which are boundaries of locations / addresses of Google Maps, obtained through a JSON via HTTPS, when informing a Latitude / Longitude location.

I do in PHP a SQL query that compares the latitude and longitude of a point, like this: 

latitude_sul <= latitude_do_ponto<=latitude_norte
longitude_oeste <= longitude_do_ponto<=longitude_leste

If these conditions are valid, it means that the point is within the fence, and consequently at that address.

However, I am not able to return the value of the endereco field that contains the location, because I can not compare the two latitude and longitude strings with VARCHARs, according to the following query: 

SELECT coordenadas_cache.endereco FROM coordenadas_cache 
 WHERE latitude_sul <= latitude_do_ponto
   AND latitude_norte >= latitude_do_ponto
   AND longitude_leste <= longitude_do_ponto
   AND longitude_oeste >= longitude_do_ponto

Sample sent values: 

latitude_do_ponto = -29.123456
longitude_do_ponto = -53.123456

Is there any way to convert the VARCHARs and sent strings to INT during the execution of SELECT, so that the values can be compared?

    
asked by anonymous 04.04.2014 / 03:35

1 answer

3

You can use a decimal cast. So: 

SELECT coordenadas_cache.endereco 
  FROM coordenadas_cache 
 WHERE latitude_sul <= CAST('-29.123456' AS DECIMAL(10,5))
   AND latitude_norte >= CAST('-29.123456' AS DECIMAL(10,5))
   AND longitude_leste <= CAST('-53.123456' AS DECIMAL(10,5))
   AND longitude_oeste >= CAST('-53.123456' AS DECIMAL(10,5))

Or, using the variables you indicated: 

SELECT coordenadas_cache.endereco 
  FROM coordenadas_cache 
 WHERE latitude_sul <= CAST(latitude_do_ponto AS DECIMAL(10,5))
   AND latitude_norte >= CAST(latitude_do_ponto AS DECIMAL(10,5))
   AND longitude_leste <= CAST(longitude_do_ponto AS DECIMAL(10,5))
   AND longitude_oeste >= CAST(longitude_do_ponto AS DECIMAL(10,5))

The cast will convert the values using an accuracy of 5 decimal places.

    
04.04.2014 / 12:00
Twig and Namespaces Function not set with javascript