0

I have 3 columns:

  1. latitude
  2. longitude
  3. place name.

If I pass place name in search query I can get latlang values. But if I passed latitude or longitude value, I didn't get place name. The cursor always returns count 0.

working well:

Select * from tablename where placename='chennai'

not working:

Select * from tablename where latitude='12.2383'

Help me

ArtKorchagin
  • 4,801
  • 13
  • 42
  • 58
Gopi
  • 11
  • 5

2 Answers2

1

The question is how you have defined latitude. If it is a string, and exactly that value, then fine. Otherwise, you are probably comparing floating point values and that's a problem -- you are off by a miniscule amount. You may think the value in the database is 12.2383, but it might really be 12.2383000001. And you might think the value you are passing in is 12.2383, but it might really be 12.2382999997.

Here are a few ways to fix this:

where abs(latitude - 12.2383) < 0.00001

where cast(latitude as decimal(8, 4)) = cast(12.2383 as decimal(8, 4))

where latitude between 12.2382 - 0.000001 and 12.2382 + 0.000001

The latter approach is preferable because it allows the use of indexes.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Column 1 and 2 are double values, so I'm passing double value in query. – Gopi Dec 10 '15 at 04:13
  • @ggopi . . . Apart from having no idea what columns 1 and 2 are, I think you miss the point of the answer. – Gordon Linoff Dec 10 '15 at 04:15
  • Abs also not working. Other 2 are not preferable. Becase I want exact location. So have to use correct and single value. – Gopi Dec 10 '15 at 04:17
  • @ggopi . . . There is really no such thing as an exact match when you use floating point arithmetic. You should define the values as decimals (numeric) instead. Although using a different language, this answer might provide some guidance: http://stackoverflow.com/questions/4915462/how-should-i-do-floating-point-comparison (I'm sure there are other similar answers as well). – Gordon Linoff Dec 10 '15 at 04:22
0

Why would a numeric value be quoted

try

Select * from tablename where latitude=12.2383
Scary Wombat
  • 44,617
  • 6
  • 35
  • 64