# Android ExampleSearching 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

B4X:
``````    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

##### Well-Known Member
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

##### Expert
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

#### DonManfred

##### Expert
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

##### Well-Known Member
I had made the same routine and got it sorted in following method:
B4X:
``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

##### Well-Known Member
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
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'
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
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,
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

##### Well-Known Member
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

##### Expert
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)

PHP:
``````    \$params = Array(\$lat, \$lon, \$radius);
\$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 .= "ORDER BY distance_in_km";
\$users = \$db->rawQuery(\$sql, \$params);
\$appresult["punkte"] = \$users;``````

#### BillMeyer

##### Well-Known Member
@DonManfred - For a brother in B4X - always a pleasure !!

#### woodpecker

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

B4X:
``````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

##### Expert
Doesn't this code require lat to be in radians
Yes.

Use this code for degrees:
B4X:
``````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

##### Member
Thanks!

Replies
0
Views
1K
Replies
15
Views
2K
Android Question How to capture coordinates.
Replies
1
Views
216
Replies
7
Views
5K
Replies
633
Views
784K