i have some database things to solve:
how to perform a geosort in dataset (eg.MySQL, Postgresql or others) to order by proximity and limit the search to a defined range.
in other words: How to do that with (avoiding a) full-scaled table scan for such kind of queries, using (traditional) indexes on the latitude and longitude columns.
to begin with the beginning; we can do such nearby searches that are based on openstreetmap-searches like so - and work on the overpass-api-level
performing a overpass-turbo.eu request:
[out:csv(::id,::type,::lon,::lat,amenity,name,"addr:postcode","addr:city","addr:street","addr:housenumber","contact:website",website,"contact:email")]
[timeout:600];
rel[boundary=administrative][admin_level=6][name="München"] -> .city;
(nwr[amenity=hospital][name](around.city:2000);
nwr[amenity=school][name](around.city:2000););
out center;
so we can use the following processes:
a. Overpass API ( https://wiki.openstreetmap.org/wiki/Overpass_API ) to search for nearby POIs.
b. Nominatim API ( https://wiki.openstreetmap.org/wiki/Nominatim )
c. on Android we also can use osmdroid to perform queries against Overpass API and display the results as overlays on an OSM map.
for more infos see the following links: a, https://wiki.openstreetmap.org/wiki/Overpass_API
by the way: The overpass-turbo interface is also really a very nice, handy and powerful aproach for that kind of search. we can put key=amenity and value=toilets in the search box.
It defaults to searching in the overpass-turbo.
what is aimed: i am trying to find all places nearby a certain location - let us do this in lat_lng.
What we have is the following dataset:
a location (location='location').
assumed we ve got a key for the Google places API Web Service and Google Maps Geocoding API.
Accoring to https://github.com/slimkrazy/python-google-places this should be enough.
This is a code-approach:
Code: Select all
YOUR_API_KEY = 'API_KEY'
google_places = GooglePlaces(YOUR_API_KEY)
query_result = google_places.nearby_search(
lat_lng='42.323417, 3.3456666666',
radius=200,
types=[types.TYPE_SCHOOL] or [types.TYPE_COLLEGE])
for place in query_result.places (##town-hotspot):
## we search in table "town-hotspot"
place.get_details()
print '%s %s %s' % (place.name, place.geo_location, place.types)
Code: Select all
from googleplaces import GooglePlaces, types, lang
YOUR_API_KEY = 'Key-API-KEY-Key-API-KEY-Key-API-KEY- foo bar '
google_places = GooglePlaces(OUR_API_KEY)
# You may prefer to use the text_search API, instead.
query_result = google_places.nearby_search(
location='Rome, Italy', keyword='Fish and Chips',
radius=20000, types=[types.TYPE_FOOD])
# If types param contains only 1 item the request to Google Places API
# will be send as type param to fullfil:
# http://googlegeodevelopers.blogspot.com.au/2016/02/changes-and-quality-improvements-in_16.html
if query_result.has_attributions:
print query_result.html_attributions
for place in query_result.places:
# Returned places from a query are place summaries.
print place.name
print place.geo_location
print place.place_id
Code: Select all
query_result = google_places.nearby_search(
## first of all we need the geo-coordinates:
lat_lng='42.323417, 3.3456666666',
## after the coordinates we need to set a value of radius
radius=100,
## after the coordinates and the setting of a value of radius we need to choose a type
types=[types.TYPE_SCHOOL] or [types.TYPE_COLLEGE])
Code: Select all
query_result.raw_response in tabel but wait: we can do it like so with the town-hotspot
Order table by proximity to specific latitude/longitude (using MySQL+PHP)
imagine if we have a MySQL table that looks like so:
Code: Select all
GEODATA more data of town-hotspot
--------------------------------------|-----
id | param| lat | lng | db-field-data
--------------------------------------|-----
1 | a | 41.339563 | -126.3557893 |field 1
--------------------------------------|-----
2 | b | 39.150682 | -107.066214 |field 2
--------------------------------------|-----
3 | c | 49.897893 | -99.444765 |field 3
--------------------------------------|-----
4 | d | 41.327433 | -106.34535 |field 4
--------------------------------------|-----
4 | e | 4=.553133 | -101.24563 |field 5
we can do it like so:
Code: Select all
$a = mysql_query("SELECT * FROM table ORDER BY proximity DESC");
while($b = mysql_fetch_assoc($a))
{
echo $b['url'].'<br />';
}
again: what is aimed: requirement: i want to display nearby places - good would be sorted in ascending order of distance from the current user.
The places table is:
Code: Select all
CREATE TABLE town-hotspot (
id int(11) NOT NULL ,
category varchar(400),
category_tag varchar (200),
[amenity:] name varchar(400),
address varchar(4000) ,
street varchar(200),
city varchar(200) ,
state varchar(200) ,
zipcode varchar(40) ,
country varchar(200) ,
telephone varchar (100),
fax Varchar (100),
website varchar (200),
mailadress varchar (200),
latitude decimal(111,2) ,
longitude decimal(222,2) ,
PRIMARY KEY (id)
)
note: i have approx 5000 records in a table.
Having this above mentioned set of data - note a whole bunch of data - with all that latitude, longitude, address, city and country and others more.
which options do i have to find nearby places in the table called "town-hotspot": well i could do the following
1) well we could do a calculation of a min, max range of lat, lng in lets say 10-25 miles radius from a certain coordinate
with a certain relation to Category and category tag...
this would look like so
Code: Select all
SELECT * FROM town-hotspot
WHERE (latitude between MINLAT and MAXLAT )
and (longitude between MINLNG and MAXLNG)
and category = CATEGORY varchar(400)
and category_tag = CATEGORY_TAG varchar (200)
what do you say?!