Android Question GPS - get nearest coordinates from SQLite database

[Declan]

I am attempting the code from this post:

https://www.b4x.com/android/forum/threads/searching-the-nearest-coordinate-in-a-database.76833/#content

I have a table "stations" that has the following fields:
id (DB_TEXT)
name (DB_TEXT)
lon (DB_TEXT)
lat (DB_TEXT)

My Code:

Sub btnNearest_Click
   
    Dim lat As Float = -26.18586111 ' For example we want to find the nearest coordinate around this latitude
    Dim lon As Float = 28.03805556 ' and this longitude
    Dim c As Cursor
    Dim latdist As Double = 1.0 / 111111.0 * 3.0;
    Dim londist As Double = 1.0 / Abs(111111.0*Cos(lat)) * 3.0
    c = Starter.SQL0.ExecQuery2($"SELECT * FROM stations WHERE lat BETWEEN ? AND ? AND lon BETWEEN ? AND ?;"$,Array As String(lat - latdist, lat + latdist , lon - londist, lon + londist))
    For i = 0 To c.RowCount - 1
        c.Position = i
        LogColor("Nearest Lat: " & c.GetString("lat"), Colors.Blue)
        LogColor("Nearest Lon: " & c.GetString("lon"), Colors.Blue)
    Next
End Sub

How do I obtain just one single lat/lon which is the nearest to: lat/lon.

 

[rboeck]

Look here:
https://www.b4x.com/android/forum/threads/searching-the-nearest-coordinate-in-a-database.76833/

 

[Declan]

Look here:
https://www.b4x.com/android/forum/threads/searching-the-nearest-coordinate-in-a-database.76833/

Thanks, that is what I am basisng my code on.
I do not get the grips on your:

query1.Append("Select * from Kunden where (" & LatS & ") and (" & LongS & ") order by (abs(long -(" & SLong & ")) +( abs(Lat-" & SLat & ")*2.1)) Limit 21;")

From My above code, I want to get the nearest lat/lon from the DB of the coordidates:

    Dim lat As Float = -26.18586111 ' For example we want to find the nearest coordinate around this latitude
    Dim lon As Float = 28.03805556 ' and this longitude

 

[rboeck]

I think the "where" part of sql is the same logic as yours; what I tried is to calculate the absolute distance between current x and destination x, aswell for the y values and added them together. Because in my usecase the distance between two latitude values is 2.1 times lower, I made this 'quick and dirty'. Sadly it wouldn't work when the longitudes have a bigger delta as my "quick and dirty" solution doesn't equate for the different lengths of longitudes (as Earth isn't a perfect sphere and more shaped like a potatoe)

 

[klaus]

Attached you find a small test project.
In the original project you could find the locations in a given radius around a given location.
I added the function to get the nearest location.
In the query, I calculate the square of the distance between the given location and the locations in the database.
I use the square because there is no square root function in SQLite.

Original query for locations in the given radius:

Query = "SELECT City, Country, Longitude, Latitude, "
Query = Query & "(" & MyLat & " - Latitude) * (" & MyLat & " - Latitude) * " & ScaleLat & " + "
Query = Query & "(" & MyLon & " - Longitude) * (" & MyLon & " - Longitude) * " & ScaleLon & " AS Distance "
Query = Query & " FROM citylist WHERE Distance <= " & Radius2
Query = Query & " ORDER BY Distance ASC"

New query for the closest location:

    Query = "SELECT City, Country, Longitude, Latitude, "
    Query = Query & "min((" & MyLat & " - Latitude) * (" & MyLat & " - Latitude) * " & ScaleLat & " + "
    Query = Query & "(" & MyLon & " - Longitude) * (" & MyLon & " - Longitude) * " & ScaleLon & ") AS Distance "
    Query = Query & " FROM citylist"

 

[BillMeyer]

I have taken a re-look at this and have found what is possibly an easier way to get the distance between two points other than using the Haversine Formula.

The questions previously asked "how to find the closest place to a pre-defined location if you have a mySQL or SQLite database with lat/lon data" will have two possible answers, namely, "The Straight Distance between the two points" or Secondly, "The Driving (or road) distance between two points". These two can differ vastly and to address this issue with only a few lines of code, here is my contribution

    Dim loc1, loc2 As Location
    Dim Lat, Lon, DevLat, DevLon As String 'DevLat = Developers Latitude (My Office Lat and lon)

    Lat = "-25.676923"       ' Destination Lat
    Lon = "28.174359"        ' Destination Lon
    DevLat  = "-25.677337"  'DevLat = Developers Latitude (My Office Lat and lon)
    DevLon = "28.160202"    'DevLon = Developers Latitude (My Office Lat and lon)

 
    loc1.Initialize2(DevLat, DevLon)
    loc2.Initialize2(Lat, Lon)
    Log(Round2((loc1.DistanceTo(loc2)/ 1000),2) &" km's")  '1.42km's'

As you will notice with the above given information the distance between the two points is 1.42 km's in a straight line (I presume in a longer distance - following the curvature of the earths surface - this does make a difference) - on the other hand - driving there via a road is actually 2.0 km's !! So if you want to give someone the actual distance to reach the destination point, you can make use of Google's Matrix API and use a URL to extract the data you require. Google will look for your shortest route and return that to you as well as the estimated journey time to the destination.

Use this URL (just get your own API key): (Adjust units= to imperial or metric)

https://maps.googleapis.com/maps/ap...59&destinations=-25.677337,28.160202&key=Your API Key 'Remember to get your key from the Matrix API (you can also separate the destinations with a | (pipe) and the API will accept up to 100 destination locations.

The resultant JSON is:

{
"destination_addresses" : [ "678 Tottum St, Dorandia, Pretoria, 0188, South Africa" ],
"origin_addresses" : [
"243-247 Ben Viljoen St, Pretoria North, Pretoria, 0116, South Africa"
],
"rows" : [
{
"elements" : [
{
"distance" : {
"text" : "2.0 km",
"value" : 2013
},
"duration" : {
"text" : "5 mins",
"value" : 270
},
"status" : "OK"
}
]
}
],
"status" : "OK"
}

which can be extracted with the normal HTTP request and JobDone Sub which will include the Json Parser.

If you look at the above information "value" in both instances give in the first instance the distance in meters and in the second instance in seconds. Interesting !!

I trust that this will help for quick retrieval of the desired information that you, my friends, require.

 

[Declan]

Many thanks, I will try this later today.

 

[BillMeyer]

Many thanks, I will try this later today.

Pleasure My Boet - Anyday !!