CODEWITHSUNDEEP
mysqlgeolocationgeocoding
Rocco The Taco
Rocco The Taco

Reputation: 3777

MySQL query to pull longitude within certain range

I have a query that used a simple WHERE logitude != '0' I though would be sufficient to pull only records that have been successfully geo-coded.

I cannot control the quality or if a address is successfully geocoded as the table is read only. I can only control my query.

The longitude is normally written out with up to 6 decimal places: -81.729345

Data Type of Latitude and Longitude columns are Decimal, Length is 9, Decimals is 6.

I guess I need to make sure I only select records within a certain range of longitude?

I tried this: Longitude BETWEEN '-89.69%' AND '-72.07%' with and without the %

But it returns all records, so I know its wrong as some of the records are geo-coded incorrectly (example: 14.359164).

How do I select only within that range and allow for any number of decimal places?

Here is a snippet of the latitude/longitude columns:

26.219295   -81.722585
26.280398   -81.629169
26.134763   -81.795894
26.1255 -81.788166
26.403096   -81.787194
26.1219 -81.793919
26.247832   -81.82119
26.22877    -81.795777
26.25278    -81.78444
26.319679   -81.731197
26.02094    -81.685416
26.364607   -81.817356
25.9351 -81.723069
26.392275   -81.818925
40.903837   14.359164

Upvotes: 0

Views: 47

Answers (1)

O. Jones
O. Jones

Reputation: 108651

latitude and longitude columns often have the FLOAT or DOUBLE data type. Rarely they have a DECIMAL(10,4) data type, and sometime in the face of all reason and logic they have a VARCHAR(15) or some such data type.

If yours are FLOAT or DOUBLE, your comparisons are done with arithmetic, not text pattern matching. So you want this sort of WHERE clause.

WHERE Longitude BETWEEN -89.69 AND -72.07

This will catch -89.69, -72.07, and all values in between.

This will also work with DECIMAL data types. If you have varchar Longitude data your best bet is to change it to FLOAT. Failing that, you want

 WHERE  CAST(longitude AS DECIMAL(10,4))  BETWEEN -89.69 AND -72.07

But this will have terrible performance.

The % wildcard only works with character datatypes and only with LIKE comparisons.

Upvotes: 1

Related Questions