How to do arithmetic operations using parenthesis

user_947715 0
Viewed 226

I am trying to calculate the minimum distance between two points in (longitude / latitude) in a database.

In SQLite I can write (example):

SELECT POWER(latitude-40,2) + POWER(longitude-32,2) * 0.5
FROM example_table

(0.5 is a correction factor for better approximation)

When I try to do the same thing in SQLAlchemy:

db.query((models.example_table.latitude - 40) * (models.example_table.latitude - 40) + (models.example_table.longitude - 32) * (models.example_table.longitude - 32) * 0.5).first()

then the produced SQL is wrong as it uses '||' instead of '+':

INFO:sqlalchemy.engine.Engine:SELECT (example_table.latitude - ?) * (example_table.latitude - ?) || (example_table.longitude - ?) * (example_table.longitude - ?) * ? AS anon_1
FROM example_table
 LIMIT ? OFFSET ?
INFO:sqlalchemy.engine.Engine:[generated in 0.00078s] (43, 43, 32, 32, 0.5, 1, 0)
(-56.749055346000006,)

and of course the result is also wrong.

How can I fix this?

sqlalchemy sql python
1 Answers

It seems that the parenthesis works out of the box.

In my case the problem was that the 2 parameters (longitude, latitude) were modelled as String(s) (VARCHARs). Then, DBMS was trying then to concatenate strings and not numbers.

The solution was to cast the columns as numbers like this:

from sqlalchemy import cast, Numeric

db.query(
(cast(models.example_table.latitude, Numeric(10, 8)) - 43) *
(cast(models.example_table.latitude, Numeric(10, 8)) - 43) +
(cast(models.example_table.longitude, Numeric(10, 8)) - 32) *  
(cast(models.example_table.longitude, Numeric(10, 8)) - 32) * 
.5)

Many thanks to the people in the comments.

user_9477150
Related