from sqlite to sql android

Question

I have this query

SELECT
   id,
   ssid,
   bssid,
   capabilities,
   level,
   frequency,
   geolat,
   geolong,
   nickname,
   (
      " + rangeDouble + " * ACOS( (SIN( " + pi + " * " + geoLat + " / 180 ) * SIN( " + pi + " * lat / 180 ) ) + ( COS( " + pi + " * " + geoLat + " / 180 ) * COS( " + pi + " * lat / 180 ) * COS( ABS( ( " + pi + " * " + geoLng + " / 180 ) - ( " + pi + " * longg / 180 ) ) ) ) )
   )
   AS distanza 
FROM
   wifiTable 
HAVING
   distanza < " + rangeDouble + " 
ORDER BY
   distanza LIMIT 0,
   20",
   null
)

distanza is like distance in English.

How can I transform this query from SQL to SQLite? Because I don't know how to use "lat" obtained by SELECT in java code!

sqlite not have ACOS, COS & SIN! – Andrea Giga Ravalli Nov 08 '12 at 17:04 — Andrea Giga Ravalli, Nov 08 '12 at 17:04

score 1 · Accepted Answer · edited May 23 '17 at 12:12

1

Cursor cursor = getReadableDatabase(). rawQuery("YOUR QUERY HERE"); please refer here to know more about using sqlite

Edit you need to have different columns to store your sin and COS values which you have to calculate and keep inside program and when you need to show it just call a function calculate it and show Similiar question is mentioned here hope this will help

edited May 23 '17 at 12:12

Community

1
1

answered Nov 08 '12 at 16:55

Just Variable

892
10
19

sqlite not have ACOS, COS & SIN! – Andrea Giga Ravalli Nov 08 '12 at 17:00

score 1 · Answer 2 · answered Nov 08 '12 at 17:50

First learn how queries work with Sqlite under Android. In the very least you can use raw query, as was mentioned already by Deepak Samuel Rajan:

Cursor cursor = getReadableDatabase(). rawQuery("YOUR QUERY HERE");

Then, with regard to your specifc problem, it really consists of two parts: 1) communicating with the database to extract the information you need. 2) perform some sort of mathematical calculations on the data.

It is best if you separate these two concerns, then solving of your problem becomes a lot easier.

For example, say x and y coordinates are stored in a database, and you want to calculate distance based on them:

 // get x and y coordinates from databas
 cursor = from query
 x = retrieve from cursor
 y = retrieve from cursor

 // calculate distance: part of your code
  distance = sqrt(x^2 + y^2)

score 0 · Answer 3 · answered Nov 08 '12 at 16:53

0

SQLite supports most features of SQL so you shouldn't need to change anything if your syntax is right.

answered Nov 08 '12 at 16:53

Khantahr

8,156
4
37
60

sqlite not have ACOS, COS & SIN! – Andrea Giga Ravalli Nov 08 '12 at 17:04

score 0 · Answer 4 · answered Nov 08 '12 at 17:55

In theory, SQLite supports user-defined functions, but in practice, Android's built-in SQL interface does not give you access to that.

If you don't want to do the calculations in your own program, you have to use the NDK to compile your own copy of SQLite.

from sqlite to sql android

4 Answers4