How to query for FLOAT values?

Question

How do I know if a pair of FLOAT(11,7) primary keys already exists?

I have these values lat: 50.0077953 and lng: -119.3953705

I do:

SELECT * FROM wp_geo_mashup_locations WHERE lat = '50.0077953' AND lng = '-119.395367'

Since this success, I do,

INSERT INTO wp_geo_mashup_locations (lat,lng) VALUES ('50.0077953', '-119.395367')

I get:

    Duplicate entry '50.0077934--119.3953705' for key 2

i'm using the type of decimal in the database column and only two numbers after the dot or comma,(well, depends on the situation, but that's in most cases)... — Jurijs Nesterovs, Jun 07 '13 at 13:07

score 0 · Answer 1 · edited May 23 '17 at 11:49

Well, if the INSERT fails, you know the pair already exists.

Alternatively, use some threshold when searching, eg:

lat BETWEEN 50.0077953-0.00005 AND 50.0077953+0.00005.

Note: you have play with this a bit till you figure out the right threshold (I don't know the resolution needed for long and lat coordinates).

Note: What is the ideal data type to use when storing latitude / longitudes in a MySQL database? - People suggest using spatial extensions, DECIMAL or FLOAT(10,6) / FLOAT(9,6) here.

1 Answers1