Geosorting on MySQL or PostgreSQL: find a simple but effective solution in Geo Spatial Search on a database

[dil_bert]

good day dear freaks - good day dear Barand - and Gizmola, 

 

this is a myql-question  i am trying to find a simple but effective solution in Geo Spatial Search on a datbase - how to perfomre 

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.  and yes on Android we also can use osmdroid to perform queries against Overpass API and display the results as overlays on an OSM map.

d. but if we have got - lets say 5000 records of openstreetmap-data in the myql-db then we can go ahead and perform a search on that table - see below 

 

regarding a and b : 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 this data 

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:

 

	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)
	

according the man-pages we have got the following: https://github.com/slimkrazy/python-google-places

 

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
	

so we re able to do the following snippet of code for a town-hotspot table: 

 

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])

To see the query_result of Colleges and Schools, we also can simply run the following snippet for a town-hotspot table: 

 

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: 

	           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
	

how to proceed: We could do now is order the above mentioned list according to their proximity to the following dataset:  (41.834527,-108.140625).

we can do it like so for the search of the town-hotspot table: 

$a = mysql_query("SELECT * FROM table ORDER BY proximity DESC");
while($b = mysql_fetch_assoc($a))
{
echo $b['url'].'<br />';
}

but we can also do this like so: we also can perform the Geosorting directly on the database - this may help to find a simple but effective solution in Geo Spatial Search on a datbase.

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:

 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 

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

 

 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)

This could be done like so - well i guess that this means to have lots of calculations.

what do you say?!

i guess that the mysql-search (the last example in this very very long posting ) is a pretty effective solution in Geo Spatial Search on a datbase.

 

 

Edited January 4, 2021 by dil_bert

[gizmola]

Pretty full article on using the various Geometry and Geospatial features of MySQL and it includes manual references.

Using the Postgis extension with Postgres.

It's also worth looking at MongoDB which has pretty much had a GeoJSON type since it's initial release, as well as indexing to support it. 

 

[dil_bert]

hi there - good day dear Gizmola, 

first of all: many thank😊

 

these links reveal lot of information and show very interstings pathways to the topic of geospatial approach. 

many thanks for these great hints: 

Looking from a backend perspective i am at the point to work with stored geographic data of many locations. Working with these data is a important step. working out functions that calculate the distance between between two locations (to show how far the location is from another). Its all about working out an algorithm that suppots these geospatial work.  Thank you for sharing these tutorials and study material.   Well i am very happy that you have shown some tutorials in the field of MySQL; 

i am working with openstreetmap-data for many months now - in the last time i thought that need to have

- not only requests to the endpoint of Openstreetmap API but more than that - 
- ability and capability to store the datasets in a db and to work with the data 
- to have internal procedures/functions to calculate & search locations in the database

I am very happy dear Gizmola that you show my MySQL tutorials and study material since i am a bit familiar with MySQL. on a sidenote: many many of my friends have installed PostGreSQL and suggest PostGIS. 

but i need to have a lean start here - i only want to work with POI - and i do not want to work with full maps and Shapefiles at the moment.  So i am happy to have these approaches and pathways that you have shown me. I look at all the datasets, materials and your shared stuff. Also many many thanks for sharing the study materials and tutorials regarding PostGIS and PostgreSQL. Thats a great addition to the MySQL-tutorial-stuff.  And here some (many many!!) thank to you, and all the others that do such a great support. You, dear Gizmola, but also the others here namely: requinix and Barand but also Kicken, gw1500se - all of you do a superb job here. 

This is a great place to be.  A place where programmers share ideas and help each other to grow. 

I love phpFreaks since it is an online community that supports sharing and discovering new ideas and pathways to solve issues in programming. 
I love this place for  discussing computer topics - not only php-realted but also lots of others more. Many thanks for your support - that you are here. 
Many thanks for this place where we can have debates, and make friends. 

 

In the next few days i will have a closer look at all the stuff  that you provided. 

 

have a great day

 

regards Dil_bert😊