Android Question How can I get nearby places stored in a db based on my location?

[Mashiane]

Hi

I have a database of place names, these have latitude and longitude details. If I were to be able to determine where I am using the GPS library to get my latitude and longitude, how can I then use my location and get nearby places from the database based on the stored lat lng details? Can someone advise please? perhaps also specifying a range of kms?

 

[Erel]

How large is the database?

 

[cimperia]

From a Google search:
https://developers.google.com/maps/articles/phpsqlsearch_v3

The solution is based on a MySQL query, should not be too difficult to adapt to other databases.

 

[Mashiane]

On average, 1000 records to filter from.

 

[eurojam]

The question is, how to proceed a spatial query with a spatial operator in the where clause. If your database is serverside and mysql, oracle or postgresql there are spatial extension available, like POSTGIS. If there is no spatial extension available you can use some mathematics in your where clause:

The shortest geodesic distance between two given points P1=(lat1, lon1) and P2=(lat2, lon2) on the surface of a sphere with radius R is the great circle between both points:
dist = arccos(sin(lat1) · sin(lat2) + cos(lat1) · cos(lat2) · cos(lon1 - lon2)) · R
so the sql for the question finding points in the database (colums Lat, Lon) within a distance d=1000 km from myPoint=(mylat, mylon) would be:

SELECT * FROM Places WHERE acos(sin(mylat) * sin(Lat) + cos(mylat) * cos(Lat) * cos(Lon - (mylon))) * 6371 <= 1000;

I don't know how fast this wil be....may be you can tell me....

 

[Mashiane]

Thanks guys, will check this out.

 

[warwound]

There is the b4a Spatialite library.
Spatialite is a version of SQLite with built in support for geospatial objects and queries.
It'd make easy work of selecting rows within a certain radius of a point - or rows that are within a defined rectangle etc.

Spatialite has one major disadavantge compared to the standard SQLite library - it'll add a 'few' MBs to your compile .apk size.