float in database are strings
c2ofh opened this issue · 4 comments
In the schema of the object the coordinates columns are correctly defined as float
t.float "latitude"
t.float "longitude"
However, when I address the coordinates, the values are returned as string
.
Expected behavior
object.latitude
=> 51.5209106
object.latitude.class
=> Float
Actual behavior
object.latitude
=> "51.5209106"
object.latitude.class
=> String
When I use the near method the sql fails to ececute
ActiveRecord::StatementInvalid (PG::UndefinedFunction: ERROR: operator does not exist: numeric - character varying)
LINE 1: ...s.*, 6371.0 * 2 * ASIN(SQRT(POWER(SIN((48.1351253 - business...
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
: SELECT objects.*, 6371.0 * 2 * ASIN(SQRT(POWER(SIN((48.1351253 - objects.latitude) * PI() / 180 / 2), 2) + COS(48.1351253 * PI() / 180) * COS(objects.latitude * PI() / 180) * POWER(SIN((11.5819805 - objects.longitude) * PI() / 180 / 2), 2))) AS distance, MOD(CAST((ATAN2( ((objects.longitude - 11.5819805) / 57.2957795), ((objects.latitude - 48.1351253) / 57.2957795)) * 57.2957795) + 360 AS decimal), 360) AS bearing FROM "objects" WHERE (objects.latitude BETWEEN 47.685464497040634 AND 48.58478610295936 AND objects.longitude BETWEEN 10.90820632087435 AND 12.255754679125651 AND (6371.0 * 2 * ASIN(SQRT(POWER(SIN((48.1351253 - objects.latitude) * PI() / 180 / 2), 2) + COS(48.1351253 * PI() / 180) * COS(objects.latitude * PI() / 180) * POWER(SIN((11.5819805 - objects.longitude) * PI() / 180 / 2), 2)))) BETWEEN 0.0 AND 50) ORDER BY distance ASC LIMIT $1
Environment info
- Geocoder version: 1.6.7
- Rails version: 5.2.6
- Database (if applicable): PostgreSQL
- Lookup (if applicable): google
Solution
I Monkey-Patched Geocoder::Sql
to convert those values to a float, because I can't figure out, why this isn't a float.
I've changed all #{lat_attr}
and #{lon_attr}
to CAST(#{lat_attr} AS float)
and CAST(#{lon_attr} AS float)
Then it works, but i usually hate monkey patches :)
Have you checked your Postgres schema to make sure the columns were actually created as floats? Maybe something went wrong with the database migration?
@alexreisner the schema is float, but I will try to convert it to text an back to float by migration.
Hot this helps to get rid of the monkey patch.
I would like to contribute to solving this problem.
My understanding is that the point where it is converted to a string is around lib/geocoder/cli.rb:75, am I correct?
# concatenate args with coords that might have been removed
# before option processing
query = (args + coords).join(" ")
if query == ""
If you have any additional information on how to solve this problem, I would appreciate it.
@c2ofh any updates on this?