SQLite and spatial queries

[CidTek]

I need to find the closest row in a table using lat and lng. Will this be possible in Sqlite and B4A?

 

[alfcen]

Just completed a semiconductor database in B4A+SQLite, so, it should be possible.

Example:
sought longitude: xlon
sought latitude: ylat
table name: location
field name for longitude: long (stored in *.db as string)
field name for latitude: lat (stored in *.db as string)
field name for related info: description (stored in *.db as string)

An SQLite database file, if packed into the *.apk must be copied to an external location before initialization, such as:

Sub Process_Globals
   Dim SQL1 As SQL
   Dim Cursor As Cursor
End Sub

Sub Activity_Create(FirstTime As Boolean)
   If File.Exists(File.DirDefaultExternal,"mygeo.db") = False Then
   File.Copy(File.DirAssets,"mygeo.db",File.DirDefaultExternal,"mygeo.db")
   End If

   If SQL1.IsInitialized = False Then
        SQL1.Initialize(File.DirDefaultExternal, "mygeo.db", False)
   End If
End Sub

Then, a query goes something like:

Cursor = SQL1.ExecQuery("SELECT * FROM locations WHERE long LIKE '%" & xlon & "%' AND lat LIKE '%" & ylat & "%'")

For i = 0 To Cursor.RowCount - 1
    Cursor.Position = i
    ListView1.AddTwoLines2(Cursor.GetString("long") & " " & Cursor.GetString("lat"),Cursor.GetString("description"),i)
Next

Please don't tie me to that, it is just a rough guide. Please also make yourself familiar with SQL queries. Also, note that values in a databases maybe stored otherwise than in strings.

Please also refer to the SQL tutorial posted here:
Basic4android - Android programming with Gui designer

 

[alfcen]

Hi again,

When a database is updated, then this is not adequate:

If File.Exists(File.DirDefaultExternal,"mygeo.db") = False Then
   File.Copy(File.DirAssets,"mygeo.db",File.DirDefaultExternal,"mygeo.db")
End If

Please change the code to:

Sub Activity_Create(FirstTime As Boolean)
        If FirstTime Then File.Copy(File.DirAssets,"mygeo.db",File.DirDefaultExternal,"mygeo.db")
End Sub

This will update your database at every new installation and/or deployment (Compile & Run) via B4A.

Just noticed that as I updated my semiconductor database.
I should be demoted by the forum to "Newbie".

 

[CidTek]

Then, a query goes something like:

Cursor = SQL1.ExecQuery("SELECT * FROM locations WHERE long LIKE '%" & xlon & "%' AND lat LIKE '%" & ylat & "%'")

Please don't tie me to that, it is just a rough guide. Please also make yourself familiar with SQL queries. Also, note that values in a databases maybe stored otherwise than in strings.

That doesn't return an exact datamatch and I doubt if it can be used to find nearest lat/lon table rows by distance.

Wheras the typical way way in SQL (I've done several Firebird db apps) would be..

SELECT *, SQRT(POW(69.1 * (stop_lat - 43.80942), 2) + POW(69.1 * (-79.454326 - stop_lon) * COS(stop_lat / 57.3 ), 2 )) 
AS distance FROM Stops
ORDER BY distance ASC LIMIT 2

However (and this to me is where SQLite really drops the ball) there are no SQRT or POW math functions. :BangHead:

 

[alfcen]

So, you did not need an introduction of SQL in B4A:BangHead:

I would try and pass the computation results (with Sqrt(x) and Power(x,y)) to a variable and insert that variable with limited decimal digits into an SQL query, and/or use >= OR <= (don't know to what precision distances are in your DB).

 

[CidTek]

So, you did not need an introduction of SQL in B4A:BangHead:

I would try and pass the computation results (with Sqrt(x) and Power(x,y)) to a variable and insert that variable with limited decimal digits into an SQL query, and/or use >= OR <= (don't know to what precision distances are in your DB).

That means I would have to retrieve values from each row in the table to perform the calculations and then store the result locally until the 2 lowest values are found. The data represents all the transit stops in a medium large city, many of them only 20 meters apart (across the street).

 

[CidTek]

That means I would have to retrieve values from each row in the table to perform the calculations and then store the result locally until the 2 lowest values are found. The data represents all the transit stops in a medium large city, many of them only 20 meters apart (across the street).

Solved it.

sqrt((x1-x2)^2 + (y1-y2)^2)

I dropped the SQRT function since relative closest distance results still work without an accuarate distance. Then I manually square using multiplication instead of the POW function. I will then calculate the SQRT of the returned result to get the actual distance.

 

[Erik Bachmann]

And the full SQL is...?

Could you please post the full SQL ??