CODEWITHSUNDEEP
androidsqlsqlitedistance
Andrea Giga Ravalli
Andrea Giga Ravalli

Reputation: 307

from sqlite to sql android

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!

Upvotes: 0

Views: 233

Answers (4)

Just Variable
Just Variable

Reputation: 877

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

Upvotes: 1

CL.
CL.

Reputation: 180192

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.

Upvotes: 0

Nazar Merza
Nazar Merza

Reputation: 3454

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)

Upvotes: 1

Khantahr
Khantahr

Reputation: 8548

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

Upvotes: 0

Related Questions