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

Asked

Viewed 1,183 times

2

I have a table called 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 values (Bounds), which are limits of Google Maps' locations/addresses, obtained through a JSON via HTTPS, when informing a Latitude/Longitude location.

I do, in PHP an 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 unable to return the field value endereco which contains the location, because I cannot compare the two latitude and longitude strings with the 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

Example of values sent:

latitude_do_ponto = -29.123456
longitude_do_ponto = -53.123456

Is there any way to transform Varchars and strings sent to INT during SELECT execution, so that values can be compared?

php mysql

  • Of curiosity, isn’t it worth keeping in int already? If you multiply by 10000000 you will have coordinates from -213 to +213 with 7 decimal places (and if you need more houses have the bigint). Thus, it will have much more speed in the comparisons, and the base will occupy much less than half of the current space.

1 answer

3


You can use a cast to decimal. 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 else, 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.

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.