B4J Question GPS position archive procedure and search for the 'nearest' points

[amorosik]

I'm trying to understand how to proceed to create an archive of information relating to scooters located in the city
In addition to the factory and model and serial number there will also be fields for the current geographical position, essentially two fields X=latitude and Y=longitude
I would then like to perform the search to show on the video the 10 scooters closest to the
How to select the 10 scooters 'closest' to a certain position among the 1000 available?

 

[Daestrum]

copilot said this - where mx,my is your location and x,y is the scooters location

SELECT
    location_id,
    x,
    y,
    -- Calculate distance using Haversine formula
    6371 * ACOS(
        COS(RADIANS(mx)) * COS(RADIANS(x)) * COS(RADIANS(y) - RADIANS(my)) +
        SIN(RADIANS(mx)) * SIN(RADIANS(x))
    ) AS distance
FROM
    your_table
ORDER BY
    distance
LIMIT 10;

 

[tchart]

copilot said this - where mx,my is your location and x,y is the scooters location

SELECT
    location_id,
    x,
    y,
    -- Calculate distance using Haversine formula
    6371 * ACOS(
        COS(RADIANS(mx)) * COS(RADIANS(x)) * COS(RADIANS(y) - RADIANS(my)) +
        SIN(RADIANS(mx)) * SIN(RADIANS(x))
    ) AS distance
FROM
    your_table
ORDER BY
    distance
LIMIT 10;

Nice but copilot is over complicating it.

For comparing small distances just use Pythagoras to calculate a distance between two x/y pairs. It may not be as accurate but it’s good enough for sorting points to find the closest 10 etc.

I know this from when I used to write software that warned of speeding cameras. The software had a radar display of the closest cameras.

 

[TILogistic]

see

Searching the nearest Coordinate in a Database

today i was in the requrement to write a search-routine in Database. given was a table with lat an lon values. Goal was to find the nearest Entry. Tablename: nw05 Fields: gidx (index), glat (Latitude), glon (longitude), gval (the value we want to get) Source of solution...

www.b4x.com

GPS - get nearest coordinates from SQLite database

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...

www.b4x.com