Android Example 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: http://stackoverflow.com/questions/...ite-by-nearest-latitude-longitude-coordinates

B4A Code to do the search in a already opened SqLite Database

    Dim lat As Float = 51.6916667 ' For example we want to find the nearest coordinate around this ltitude
    Dim lon As Float = 11.3375 ' 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 = sql.ExecQuery2($"SELECT * FROM nw05 WHERE glat BETWEEN ? AND ? AND glon BETWEEN ? AND ?;"$,Array As String(lat - latdist, lat + latdist , lon - londist, lon + londist))
    For i = 0 To c.RowCount - 1
        c.Position = i
        Log(c.GetString("gval"))
    Next

 

[rboeck]

Hi, what i miss: How is the distance sorted? What i find, is only the query which selects a given range of coordinates, but how do you find the nearest? And btw. what is in the columns gval?

Regards,
Reinhard

 

[DonManfred]

How is the distance sorted?

Dim latdist As Double = 1.0 / 111111.0 * 3.0;
Dim londist As Double = 1.0 / Abs(111111.0*Cos(lat)) * 3.0

3km radius

 

[DonManfred]

what is in the columns gval?

just any result column from the dataset found. Not interesting for this exaqmple.
It was from interested for my customer.

 

[klaus]

3km radius

I would say 3km square.

 

[DonManfred]

I would say 3km square.

you are probably right!
I´m not really fit at coordinate-system.

I have just this comment in mind and interpreted a radius on it:

I use this in Objective-C to obtain all places of interest that are within 3km around the current location

Thank you for correcting me

 

[rboeck]

I had made the same routine and got it sorted in following method:

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

LatS and LongS are strings with a between clause form to; by adding the difference in x and y distance and correcting the Lat factor i got useable results in order from near to far.

 

[BillMeyer]

Manfred,

To get the closest place to your current location in a MySQL database (this is what I have used - may help you - should easily convert to SQLite) you use a "thing" called the Haversine formula. (wikipedia)

Here is an example MySQL procedure that I call from my b4a app. I hope this helps you.

The parameters are:

Latitude - Your Current Position Latitude
Longitude - Your current Position Longitude
RadiusIn - The radius in km from your current position you wish to search
NumRecs - The number of MySQL records you want returned

This particular one returns the number of records or less that you specify within the radius you have specified in km's from your current position. I use it for a Closest Pharmacy lookup.

PROCEDURE ListingSystem2.GetPharmacies(IN Latitude VARCHAR(255), IN Longitude VARCHAR(255), IN RadiusIn INT, IN NumRecs INT)
COMMENT 'Procedure to get Closest Pharmacies using Haversine formula'
SELECT g.listing_refid,g.lat,g.lng,g.address_content,g.address_city, g.address_state, g.address_postcode, g.phone, g.email, g.status, Round(p.distance_unit * DEGREES(ACos(Cos(RADIANS(p.latpoint)) * Cos(RADIANS(g.lat)) * Cos(RADIANS(p.longpoint) - RADIANS(g.lng)) + Sin(RADIANS(p.latpoint)) * Sin(RADIANS(g.lat)))),2) As distance_in_km
FROM yellowpages_listings As g
JOIN (Select Latitude As latpoint, Longitude As longpoint, RadiusIn As radius, 111.045 As distance_unit) As p
WHERE g.lat
BETWEEN p.latpoint - (p.radius / p.distance_unit)
AND p.latpoint + (p.radius / p.distance_unit)
AND g.lng
BETWEEN p.longpoint - (p.radius / (p.distance_unit * Cos(RADIANS(p.latpoint))))
AND p.longpoint + (p.radius / (p.distance_unit * Cos(RADIANS(p.latpoint))))
AND g.status = 'T'
ORDER BY distance_in_km
LIMIT NumRecs

The table looks like this:

CREATE TABLE ListingSystem2.yellowpages_listings (
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
listing_refid VARCHAR(255) DEFAULT NULL,
lat VARCHAR(255) DEFAULT NULL,
lng VARCHAR(255) DEFAULT NULL,
address_postcode VARCHAR(255) DEFAULT NULL,
address_content TEXT DEFAULT NULL,
address_city VARCHAR(255) DEFAULT NULL,
address_state VARCHAR(255) DEFAULT NULL,
phone VARCHAR(255) DEFAULT NULL,
email VARCHAR(255) DEFAULT NULL,
status ENUM('T','F','E') NOT NULL DEFAULT 'F',
PRIMARY KEY (id)
)
ENGINE = INNODB
AUTO_INCREMENT = 0
AVG_ROW_LENGTH = 230
CHARACTER SET utf8
COLLATE utf8_general_ci;

Have Fun !!

 

[tigrot]

Do you need absolute distance or driving distance? I have an hospital quite near my home. If I walk it's only m.500 far from my home, driving it's almost 2Km. because of a one way road(urbanistic disaster).

 

[DonManfred]

I hope this helps you

I was just in the need to get better results and i tried to use the query used in the procedure and i think the results are great.
Thank you again for your Code!

I used it inside a PHP-Script (my Bridge to the DB)

    $params = Array($lat, $lon, $radius);
    $sql = "SELECT *, Round(p.distance_unit * DEGREES(ACos(Cos(RADIANS(p.latpoint)) * Cos(RADIANS(g.Rechts)) * Cos(RADIANS(p.longpoint) - RADIANS(g.Hoch)) + Sin(RADIANS(p.latpoint)) * Sin(RADIANS(g.Rechts)))),2) As distance_in_km ";
    $sql .= "FROM ".$tabellenname." As g ";
    $sql .= "JOIN (Select ? As latpoint, ? As longpoint, ? As radius, 111.045 As distance_unit) As p ";
    $sql .= "WHERE g.Rechts BETWEEN p.latpoint - (p.radius / p.distance_unit) AND p.latpoint + (p.radius / p.distance_unit)";
    $sql .= " AND g.Hoch BETWEEN p.longpoint - (p.radius / (p.distance_unit * Cos(RADIANS(p.latpoint))))";
    $sql .= "AND p.longpoint + (p.radius / (p.distance_unit * Cos(RADIANS(p.latpoint))))";
    $sql .= "ORDER BY distance_in_km";
    $users = $db->rawQuery($sql, $params);
    $appresult["punkte"] = $users;

 

[BillMeyer]

@DonManfred - For a brother in B4X - always a pleasure !!

 

[woodpecker]

Doesn't this code require lat to be in radians, it doesn't seem to give the correct result if lat is in degrees??

Dim lat As Float = 51.6916667 ' For example we want to find the nearest coordinate around this ltitude
    Dim lon As Float = 11.3375 ' 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

 

[klaus]

Doesn't this code require lat to be in radians

Yes.

Use this code for degrees:

Dim lat As Float = 51.6916667 ' For example we want to find the nearest coordinate around this ltitude
Dim lon As Float = 11.3375 ' 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 * CosD(lat)) * 3.0

 

[woodpecker]

Thanks!