iOS Question Add function to SQLite

[marcel]

Hi,

I am transfering my orginal xcode program to B4I and see what I am missing. How can I add the following SQLite function:

#pragma mark -
#pragma mark SQLLite Function

#define DEG2RAD(degrees) (degrees * 0.01745327) // degrees * pi over 180

static void distanceFunc(sqlite3_context *context, int argc, sqlite3_value **argv)
{
    // check that we have four arguments (lat1, lon1, lat2, lon2)
    assert(argc == 4);
    // check that all four arguments are non-null
    if (sqlite3_value_type(argv[0]) == SQLITE_NULL || sqlite3_value_type(argv[1]) == SQLITE_NULL || sqlite3_value_type(argv[2]) == SQLITE_NULL || sqlite3_value_type(argv[3]) == SQLITE_NULL) {
        //sqlite3_result_null(context);
        //return;
    }
    // get the four argument values
    double lat1 = sqlite3_value_double(argv[0]);
    double lon1 = sqlite3_value_double(argv[1]);
    double lat2 = sqlite3_value_double(argv[2]);
    double lon2 = sqlite3_value_double(argv[3]);
    // convert lat1 and lat2 into radians now, to avoid doing it twice below
    double lat1rad = DEG2RAD(lat1);
    double lat2rad = DEG2RAD(lat2);
    // apply the spherical law of cosines to our latitudes and longitudes, and set the result appropriately
    // 6378.1 is the approximate radius of the earth in kilometres
   
    //double dist = acos(sin(lat1rad) * sin(lat2rad) + cos(lat1rad) * cos(lat2rad) * cos(DEG2RAD(lon2) - DEG2RAD(lon1))) * 6378.1 * 1000;
   
    sqlite3_result_double(context, acos(sin(lat1rad) * sin(lat2rad) + cos(lat1rad) * cos(lat2rad) * cos(DEG2RAD(lon2) - DEG2RAD(lon1))) * 6378.1 * 1000);
}

 

[Erel]

You cannot add a function directly to the sqlite engine with B4i. You will need to apply the function to the query results.

 

[marcel]

You cannot add a function directly to the sqlite engine with B4i. You will need to apply the function to the query results.

I have a huge list of points in the Netherlands. So what would than the fastest way because I need to monitor them when people are driving... (Waypoints).

 

[Erel]

See the two answers here: http://stackoverflow.com/questions/3126830/query-to-get-records-based-on-radius-in-sqlite

A simpler (I think) approach is to calculate an approximate distance with:

"SELECT (CurrentLat - StoredLat) * (CurrentLat - StoredLat)  + (CurrentLon - StoredLon) * (CurrentLon - StoredLon) FROM table ORDER BY DESC"

Now take the top x results and calculate the exact distance to find the closest point.

 

[marcel]

Hi,

I am now using this from stackoverflow: http://stackoverflow.com/questions/...nearest-locations-with-latitude-and-longitude

I am only not certain which is the fasted list in B4A to store the results in.

I have inserted the B4A code if someone would like to have it.

Sub GetCloseItems(l as location, range As Float) As Map
    Dim multi As Float = 1.1
    Dim p1 As Location = calculateDerivedPosition(l,Main.range*multi,0)
    Dim p2 As Location = calculateDerivedPosition(l,Main.range*multi,90)
    Dim p3 As Location = calculateDerivedPosition(l,Main.range*multi,180)
    Dim p4 As Location = calculateDerivedPosition(l,Main.range*multi,270)
   

    Dim strSql As String = printf(" Latitude > %f and Latitude < %f and Longitude < %f and Longitude > %f",Array As Object(p3.Latitude,p1.Latitude,p2.Longitude,p4.Longitude))


'etc

End Sub


Sub toRadians(d As Double) As Double
    Return (d*cPI)/180
End Sub

Sub toDegrees(R As Double) As Double
    Return (R*180)/cPI
End Sub

Sub calculateDerivedPosition(loc As Location, range As Double, bearing As Double) As Location
     Dim EarthRadius As Int = 6371000
    Dim latA As Double = toRadians(loc.Latitude)
    Dim lonA As Double = toRadians(loc.Longitude)
    Dim angularDistance As Double = range / EarthRadius
    Dim trueCourse As Double = toRadians(bearing)

    Dim lat As Double= ASin(Sin(latA) * Cos(angularDistance) + Cos(latA) * Sin(angularDistance) * Cos(trueCourse))

    Dim dlon As Double = ATan2(Sin(trueCourse) * Sin(angularDistance) * Cos(latA), Cos(angularDistance) - Sin(latA) * Sin(lat))

    Dim lon As Double = ((lonA + dlon + cPI) Mod (cPI * 2)) - cPI

    lat = toDegrees(lat)
    lon = toDegrees(lon)

    Dim newloc As Location
    newloc.Initialize
    newloc.Latitude=lat
    newloc.Longitude=lon
    Return newloc
End Sub

Sub pointIsInCircle(pointForCheck As Location, center As Location,radius As Double)
        If (getDistanceBetweenTwoPoints(pointForCheck, center) <= radius) Then
            Return True
        Else
            Return False
        End If
End Sub

Sub getDistanceBetweenTwoPoints(p1 As Location, p2 As Location) As Double
        Dim R As Double = 6371000
        Dim dLat As Double = toRadians(p2.Latitude - p1.Latitude)
        Dim dLon As Double = toRadians(p2.Longitude - p1.Longitude)
        Dim lat1 As Double = toRadians(p1.Latitude)
        Dim lat2 As Double = toRadians(p2.Latitude)

        Dim a As Double = Sin(dLat / 2) * Sin(dLat / 2) + Sin(dLon / 2) * Sin(dLon / 2) * Cos(lat1) * Cos(lat2)
        Dim c As Double = 2 * ATan2(Sqrt(a), Sqrt(1 - a))
        Dim d As Double = R * c

        Return d
End Sub

 

[marcel]

Alternative simpeler solution this can be used:

("select * from table  ORDER BY abs(latitude - (?)) + abs( longitude - (?))",Array As String(l.Latitude,l.Longitude))

and select the top X