Android Question Finding Locations with MySQL

ciginfo · Jul 31, 2013

Hello,
I don't understand all the things in Finding Locations with MySQL Here.
lat ant lng are latitude and longitude of a point in a row in a database.
What are 37 and -122. Are rounded latitude and longitude of a point defined, for example where I am?

B4X:

SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;

Thank you

eps · Jul 31, 2013

I don't know, but Erel recommended having Lat and Long as Strings.. That will take a lot of grief out of the whole process.

See here : http://www.b4x.com/android/forum/th...are-stored-in-sqlite-table.22946/#post-133054

and there's another thread, where Erel recommends this, but I can't seem to find it now. It makes sense to me, especially as it's just a whole string of characters that you need to send to the geo or google.navigation intent for mapintent, or are you doing something else?

ciginfo · Jul 31, 2013

I want to find the the closest locations wich are in a database (latitude and longitude) on a server, from me when I travel with my device. I note lat and long of my location and find in the database the closest location.
I have also to recuper the distance between the device and the location in the row of the database.
Distance =( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) )
If I am (latitude = 37 and longitude = -122) Is it Right?

Erel · Jul 31, 2013

How many points are in the database?

ciginfo · Aug 1, 2013

Many rows with lat and longitude and some more every day. I use this code to extract

B4X:

Sub ExtraitListePardistance
    ProgressDialogShow("Mise à jour des données")
    ExecuteRemoteQuery("SELECT id, nom,ouverture, adresse, telephone, mail,( 3959 * acos( cos( radians(43.493655) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(-1.474941) ) + sin( radians(43.493655) ) * sin( radians( latitude ) ) ) ) AS distance FROM kindabreak HAVING distance < 150 ORDER BY distance LIMIT 0 , 20",AFFICHE_LIST)
   
End Sub

Erel · Aug 1, 2013

Where have you found this SQL query?

ciginfo · Aug 3, 2013

I adapted this formula and it works fine.
I should like replace Longitude (-1.4746) and latitude (43.493) with a variable but I don't know the syntax.

B4X:

Sub ExtraitListePardistance
  Dim MyLatitude, MyLongitude as Double
MyLatitude = 43.4936
MyLongitude = -1.4746
    ExecuteRemoteQuery("SELECT id, nom,ouverture, adresse, telephone, mail,( 3959 * acos( cos( radians(MyLatitude) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(MyLongitude) ) + sin( radians(MyLatitude) ) * sin( radians( latitude ) ) ) ) AS distance FROM kindabreak HAVING distance < 150 ORDER BY distance LIMIT 0 , 20",AFFICHE_LIST)
 
End Sub

This code does not work. sin( radians('MyLatitude') or sin( radians("MyLatitude") neither.
What is the syntax?

NJDude · Aug 3, 2013

Try this:

B4X:

ExecuteRemoteQuery("SELECT id, nom,ouverture, adresse, telephone, mail,( 3959 * acos( cos( radians(43.493655) ) * cos( radians(" &  latitude & ") ) * cos( radians(" &  longitude & ") - radians(-1.474941) ) + sin( radians(43.493655) ) * sin( radians(" &  latitude & ") ) ) ) AS distance FROM kindabreak HAVING distance < 150 ORDER BY distance LIMIT 0 , 20",AFFICHE_LIST)

ciginfo · Aug 4, 2013

Yes, (" & MyLatitude & ") is the right syntax
Thank you

Android Question Finding Locations with MySQL

ciginfo

Well-Known Member

eps

Expert

ciginfo

Well-Known Member

Erel

B4X founder

ciginfo

Well-Known Member

Erel

B4X founder

ciginfo

Well-Known Member

NJDude

Expert

ciginfo

Well-Known Member

Similar Threads

Android Question Finding Locations with MySQL

Well-Known Member

Expert

Well-Known Member

B4X founder

Well-Known Member

B4X founder

Well-Known Member

Expert

Well-Known Member

Similar Threads

Privacy & Transparency

Privacy & Transparency