Android Question SQLite query for calculating distance

[Idris Sardi]

I have a table called "pois", I want to run sql query which will show all locations nearest to the phone gps location in 500 m, I copied the mysql code somewhere and used it for sqlite and it does not work, maybe anyone can help me to translate the red highlighted query to sqlite query version ?
Thank you
the code is as follows :


Sub GPS_LocationChanged (Location1 As Location)

Loc1 = Location1.Latitude
Loc2 = Location1.Longitude

Dim Cursor As Cursor
Qry = "Select place_id,place_name,lat,lon,poi_catid, ( 6371 * acos( cos( radians( " & Loc1 & " ) ) * cos( radians( lat ) ) * cos( radians( lon ) - radians( " & Loc2 & ") ) + sin( radians( " & Loc1 & " ) ) * sin( radians( lat ) ) ) ) as distance FROM pois HAVING distance < 0.5 ORDER BY distance"
Cursor = SQL1.ExecQuery(Qry)
For i = 0 To 15
Cursor.Position = i
ToastMessageShow(Cursor.GetString("place_name"),True)
Next
Cursor.Close
End Sub

=======================================
Error message says :
android.database.sqlite.SQLiteException: no such function: acos (code 1):

 

[mc73]

From what I understand acos is not supported in sqlite. Well, that's a pity, if you can create a custom sqlite function, then it'll be resolved. Otherwise, one way is to expand the acos to Taylor series, and include it in your query. I think this is a but complicated, and probably it won't be that fast.

 

[Erel]

You can fetch the points from the database and then create this calculations in your code.

 

[Idris Sardi]

I am new to sqlite and I'm not that smart to make sqlite function,
is there any alternative database running lightly on android rather than sqlite which supports "acos"?

 

[Erel]

No. And you do not need it.

Use Location.DistanceTo to calculate the distance in your B4A code.

 

[Idris Sardi]

I know the DistanceTo, but the case is like foursquare, you turn on the GPS application and the code should search records on sqlite table which contains location names with longitude and latitude nearest (e.g 500 meters from mobile phone) and show them on the screen, e.g :
- restaurant A - 8 meters
- restaurant B - 20 meters
- hospital - 50 meters
- etc - 16 meters
--[click to check in]---

 

[mc73]

The expansion of acos(x) is acos(x)=1/2*Pi-x-1/6*x^3-3/40*x^5-... I think that the bigger powers will play insignificant role, so you can adjust your query accordingly.

 

[warwound]

I am new to sqlite and I'm not that smart to make sqlite function,
is there any alternative database running lightly on android rather than sqlite which supports "acos"?

I do have a wrapper for the android Spatialite library.
It's a 'more or less(!)' complete implementation - you can certainly use spatialite's geo-spatial queries to make easy work of your task.

There's some spatial query examples on this page: https://www.gaia-gis.it/fossil/libspatialite/wiki?name=spatialite-android-tutorial.

You'd create a table with POINT type columns for your POI locations and then construct a query for all POIs within ?? distance of another POINT.
You're welcome to a copy of my b4a library BUT i can't promise any technical support or even sample projects at the moment.

Martin.

 

[Idris Sardi]

Thank you very much warwound, I'm going to check it out and be back after some research...

 

[Idris Sardi]

I think I have to wait your tutorial example regarding this with basic4android to get the nearest locations based database and GPS location,
thank you anyway...

 

[warwound]

OK.

SpatiaLite is an old library i created and i've now reopened the project and am looking at updating it - implementing more of the features of the native android java SpatiaLite library in my b4a wrapper.

Once that's done i aim to upload it to the forum along with some examples - that's likely to be a week or more away though.

Martin.

 

[Idris Sardi]

I have installed spatialite on my PC and tried some sql queries on command line, it worked fine, but when I do this, it won't work :

Select place_id,place_name,lat,lon, ( 6371 * acos( cos( radians( -6.845830100000001 ) ) * cos( radians( lat ) ) * cos( radians( lon ) - radians( 107.4736016) ) + sin( radians( -6.845830100000001 ) ) * sin( radians( lat ) ) ) ) as dist FROM pois group by place_name HAVING dist < 3 limit 10;

If I remove HAVING dist < 3 then this works fine :

Select place_id,place_name,lat,lon, ( 6371 * acos( cos( radians( -6.845830100000001 ) ) * cos( radians( lat ) ) * cos( radians( lon ) - radians( 107.4736016) ) + sin( radians( -6.845830100000001 ) ) * sin( radians( lat ) ) ) ) as dist FROM pois group by place_name HAVING dist < 3 limit 10;

What do you think? what is the problem?

 

[Idris Sardi]

Sorry, I fixed it, I had incorrect field types for lon and lat, they were text type, I changed them to REAL now this works fine :

Select place_id,place_name,lat,lon, ( 6371 * acos( cos( radians( -6.845830100000001 ) ) * cos( radians( lat ) ) * cos( radians( lon ) - radians( 107.4736016) ) + sin( radians( -6.845830100000001 ) ) * sin( radians( lat ) ) ) ) as dist FROM pois group by place_name HAVING dist < 3 limit 10;

My problem now is HOW DO WE RUN THIS on basic4android !!!

 

[Erel]

SQLite doesn't support these functions (it is not related to Basic4android). You will need to implement these calculations in your code instead.

 

[warwound]

@Erel Irdis has installed SpatiaLite on his PC and is trying to work out how to use it.

@Irdis Sardi

First you might find the SpatiaLiteGUI useful.

Second you ought to use the built in spatial functions.
Look here: http://www.gaia-gis.it/gaia-sins/spatialite-sql-3.0.0.html, specifically 'SQL functions on type Point' and 'SQL functions for distance relationships'.

Try to create a column in your SpatiaLite table that is of type POINT - search the documentation for the AddGeometryColumn syntax.
Now you can use the built in PtDistWithin function in your SELECT query's WHERE clause.

A 'pseudo' query would have a syntax such as:

SELECT point_column, other_column FROM my_table WHERE PtDistWithin(point_column, MakePoint(my_lat, my_long, 4326))

4326 is the Spatial Reference ID (SRID) for the WGS84 project that is typically used in latitude, longitude coordinates.

The extra work that creating a proper geometry column requires now makes your queries so much simpler!

Android's implementation of SQLite has no way to add your own functions - SpatiaLite supports custom functions but as you can see - you don't need them, you just need a table where geometry data is stored in geometry type columns.

I guess you might want a copy of my SpatiaLite library? PM me and i'll sort it out.

Martin.

 

[Harris]

When you have a table with location lat / lon - this is what I use

Sub FindNearest( ) As Boolean
    Dim i As Int
    Dim lastloc, lloc As Double
    Dim fnd As Boolean
    Dim Ca As Cursor
    Log(" GPS Status: " & GPSServMod.fSats)
    fnd = False
    cl = "No Location Found"
    If GPSServMod.gLoc.fAccuracyValid = False Then
        Log(" GPS not Locked " & GPSServMod.fSats)
        DoEvents
        Return fnd
    Else
        lat1 = GPSServMod.gLoc.fLatitude
        lon1 = GPSServMod.gLoc.fLongitude
        lat2 = lat1
        lon2 = lon1
        m = .5
    End If
    Ca = SQL2.ExecQuery2( "SELECT * FROM Location WHERE latitude <= ? and latitude >= ? and longitude >= ? and longitude <= ? order by city", Array As String(lat1 + m, lat2 - m, lon1 - m, lon2 + m ) )
    lloc = 100000000.0
    If Ca.RowCount > 0 Then
        For i = 0 To Ca.RowCount - 1
            Ca.Position = i
            lastloc = Geo1.distanceToMidpoint(lat2, lon2, Ca.GetDouble("latitude"),Ca.GetDouble("longitude"))
            If lastloc <= lloc Then
                cl = Ca.GetString("city")&", "&Ca.GetString("region")
                lloc = lastloc
            End If
        Next
        CurrLoc = cl
        Log(" Found Many Locations: " & Ca.RowCount)
        fnd = True
    Else
        m = m + .5
    End If
    Ca.Close
    Return fnd
End Sub

 

[agorad]

When you have a table with location lat / lon - this is what I use


hello

I'm trying your code example
but I have error

six tell me what is GPSServMod

a library that do not know?

Parsing code. 00:09
Compiling code. error
Error compiling program.
Error description: Unknown member: GLOC
Occurred on line: 202
If GPSser.gLoc.fAccuracyValid = False Then
Word: GLOC

 

[Harris]

gLoc is a Type that is populated by Location_changed event.
The posted code is an Example of how to get locations near you from a table. It will not run (or compile) on its' own. It is part of my GPSServmod attached.

Basically, the query returns locations within the radius specified by m (0.5).

 

[persianpowerman1]

Buddy.. why dont you do the calculations in your code... wont that be smoother.. and quicker?

 

[dibesw]

gLoc is a Type that is populated by Location_changed event.
The posted code is an Example of how to get locations near you from a table. It will not run (or compile) on its' own. It is part of my GPSServmod attached.

Basically, the query returns locations within the radius specified by m (0.5).

HI Harris
what is DefCM in GPSServMod?
THANKS!