You are not logged in.

#1 2021-01-27 23:57:00

tagtheworld
Member
Registered: 2012-04-25
Posts: 218

Umgebungs-Suche: Lösungen in DB-Abfrage oder durch Overpass-API

Tach Community, smile


vorweg - euch allen noch ein gutes neues Jahr!:)

...hier eine Frage die man auf zwei Weisen bearbeiten kann - entweder auf einer Datenbankebene - oder auch auf einer Abfrageebene (vgl. Around-Filter: Einsatz multipler Parameter bei der nearby-Abfrage  (vgl. hier: https://forum.openstreetmap.org/viewtopic.php?id=71531  )

Zunächst ein Themenaufriss:  Diese Umgebungssuche lässt sich auf verschiedenen Wegen erreichen.  Das Ziel über ein Webfrontend einen Ort einzugeben und dann aus 3 oder vier Kategorien ( Hospital, School, x, y ) die jeweils nähsten Einträge noch zu finden und aufzulisten - diese Aufgabenstellung - sie lässt sich mittels mehrer Ansätze verfolgen:

a das ganze als MySQL-Frage (oder PostgreSQL, wenn man so will) oder
b. mittels Overpass-API

Ich versuche, eine einfache, aber effektive Lösung für eine Nearby-Suche, gelöst über eine Datenbank zu finden - wie man sie durchführt;

Anm zum Ziel.: Es sollen die Daten / Abfragen dann auch online in einem Webfrontend verwendet werden.

vgl. etwa dies Angaben etwa:

@id            @type    @lon           @lat          name     addr:postcode                   addr:city    addr:street    addr:housenumber    contact:website     contact:email=*
35332685    hosp    -43.9485880    -19.8175998    Hospital Risoleta Tolentino Neves        Belo Horizonte    Rua das ..
35332689    hosp    -43.9518360    -19.9178800    Prontocor                                Rio
53254282    hosp    -43.9319894    -19.9260406    Hospital Semper                          Rio
75600076    hosp    -43.9590472    -19.9505820    Hospital Luxemburgo                      Rio
78719634    hosp    -43.9528392    -19.9239539    Hospital Vera Cruz                       Belo Horizonte    Avenida Barbacena    653
...[...]...
74400076    Backery   -43.9590472    -19.9455820    French Baguette                        Rio
72219634    School    -43.9590472    -19.8885820    Paulo Freire-School Rio                Rio

etc. etx.

Also - Ich habe einige Datenbank-Dinge zu lösen: Gesucht ist eine - sagen wir "Geosorting-Lösung" in einem Datensatz durch (z. B. MySQL, Postgresql oder andere), um nach Nähe zu ordnen und die Suche auf einen definierten Bereich zu beschränken. Mit anderen Worten: Wie geht man diese Aufgabenstellung an - unter Vermeidung eines vollständig skalierten Scans der DB für solche Abfragen unter Verwendung (traditioneller) Indizes für die Breiten- und Längengradspalten? Um mal mit dem Anfang beginnen; Wir können solche Suchvorgänge in der Nähe durchführen, die auf solchen Openstreetmap-Suchvorgängen basieren - und auf der Overpass-API-Ebene arbeiten und sich etwa auch via overpass-turbo.eu-Anfrage ausführen lassen:

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;


Einschub: Betrachten wir mal eien Ansatz auf einer Datenbankebene: Es ginge ja theoretisch auch eine Suchfunktion über mehrere Tabellen - und dann Filter für die Spalten - also eine Tabelle in der man noch die Kategorien

a. type
b. lon lat /(gps-daten eben) einfügt

man kann in der gps-daten-spalte nach den Eingaben filtern. Ist ggf. halt am einfachsten und funktioniert.  Also ich denke, dass dann die Tabelle ja so aussehen koennte:

vgl dies Angaben etwa:
@id            @type    @lon           @lat          name     addr:postcode                   addr:city    addr:street    addr:housenumber    contact:website     contact:email=*
35332685    hosp    -43.9485880    -19.8175998    Hospital Risoleta Tolentino Neves        Belo Horizonte    Rua das Gabirobas    1 
35332689    hosp    -43.9518360    -19.9178800    Prontocor                                Rio
53254282    hosp    -43.9319894    -19.9260406    Hospital Semper                          Rio
75600076    hosp    -43.9590472    -19.9505820    Hospital Luxemburgo                      Rio
78719634    hosp    -43.9528392    -19.9239539    Hospital Vera Cruz                       Belo Horizonte    Avenida Barbacena    653
74400076    Backery   -43.9590472    -19.9455820    French Baguette                        Rio
72219634    School    -43.9590472    -19.8885820    Paulo Freire-School Rio                Rio
etc. etx.

by the way: Also es ist so: ich habe die Daten gewonnen über openstreetmap und gespeichert in einer Tabelle - kann dazu aber natürlich auch MySQL oder sqlite verwenden By the way: das soll für eine Webanwendung verwendet werden. Also: wenn man einen Punkt hat - also nehmen wir zum Beispiel den Berliner Aleanderplatz:

DD COORDINATES; 52.519664588 13.407998368
DMS COORDINATES; 52°31'10.79" N 13°24'28.79" E

die o.g. Daten die die Tabellen betreffen die könnte ich auch über die Overpass API jeweils direkt abfragen: https://wiki.openstreetmap.org/wiki/Overpass_API Vorerst dachte ich, ohne OSM Kartendarstellung zu arbeiten und lediglich eine Tabellarische Suche zu ermoeglichen. Die Abfrage via Overpass-Api liesse sich m.E. ja auch direkt im Webfrontent einrichten und umsetzen lassen.

also - sagen wir es sind die Tabellen wie folgt:

dataset_1_ Kliniken / hospital (hosp)
dataset_2_ Bäckereien
dataset_3_ Milchläden
dataset_4_ Schuhläden
dataset_5_ Schulen

Vorerst jedoch will ich die Daten also lokal speichern (jeder Datensatz hat eine eindeutige ID) und nebenbei bemerkt auch noch den Timestamp mitführen, an dem der Datensatz zum letzten mal aktualisiert wurde.

Zu der Umsetzung via Abfrage am OSM-Endpoint: hier kann man auch Overpass verwenden - für die Abfrage von POI; Da hierbei aufgrund der Unterschiede zwischen den POIs keine Homogenisierung möglich ist speichere ich den POI mit seinen Koordinaten in einen Table (poiID, lat, lng). Die Attribute speichere ich in einen extra Table (poiID, attr_name, attr_val). damit habe ich die Daten relational abgebildet, kann sie aber nach Bedarf auch nonrelational abfragen und aus dem Backend als JSON ins Frontend übergeben. Ich kann auch die Attribut-Werte (attr_val) unabhängig vom Feldnamen durchsuchen. Mit den passenden Indizes funktioniert das performant und problemlos.

....also wenn ich 2 Datensätze habe....- hier zum Beispiel bezogen auf "Schulen"

...einmal die Schule mit lon, lat

@id        @type    lon:, lat:
268915924    node,
name   max müller Schule


bzw. - man kann das auch so umsetzen, wenn man das nearby - thema komplett in einer OSM-Streetmap-Lsg. abbilden wollte:

[out:json][timeout:25];

nwr(around:10000,40.40,-79.93)["amenity"="hospital"];

out center;

wie oben aber beschrieben wollte ich zunächst mit Tabellen bzw. einer Datenbank arbeiten - und eine tabellarische Lsg ergo zunächst mal ins Auge fassen..

Ende des Einschubs: Abgesehen vom Datenbank-Ansatz kann ich mir noch vorstellen, das mit Overpass-Api und einem  Around-Filter zu lösen:vgl etwa:  Einsatz multipler Parameter bei der nearby-Abfrage  (vgl. hier: https://forum.openstreetmap.org/viewtopic.php?id=71531  )

Man kann also die folgenen Prozesse verwenden:

a. Overpass API ( https://wiki.openstreetmap.org/wiki/Overpass_API ) um die naheliegenden POIs zu suchen.

b. Nominatim API ( https://wiki.openstreetmap.org/wiki/Nominatim )

c. und ja; auf Android koennten wir auch osmdroid einsetzen um Abragen via Overpass API durchzuführen und die Ergebnisse  overlays auf einer OSM map darzustellen.

d. aber was tun wenn wir - sagen wir 5000 Einträge eines openstreetmap-Datensatzes. Wenn es so ist, dann kann man ja auch so ansetzen, in der MySQL-DB eine Abfrage abzusetzen.

bezogen auf a und b: hier der Link: https://wiki.openstreetmap.org/wiki/Overpass_API

by the way: Das overpass-turbo interface ist ziemlich nett und auch leistungsfähig. Man kann da ja auch sehr einfach
eine Distanzsuche anstellen - etwa über eine Suche wie..

key=amenity and value=toilets

Was ist beabsichtigt: So etwas wie eine Nearby-Suche, also die Suche nach Plätzen (orten) die in der Nähe einer bestimmten Postion sich befinden: 

Wir haben ein Datensatz:

a location (location='location').

Also um mal ein Beispiel zu geben - aus der Google-Map-Welt:

..wenn man das auf der Google places API mal ansieht und die Web Service Google Maps Geocoding API nähme: vgl. https://github.com/slimkrazy/python-google-places 

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)

...nach den man-pages haben wir das Folgende: 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 … 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

das würde den folgenden Code ergeben für eine Tabelle bezogen von Nearby-Hotspots:

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

Bezogen auf ein konkretes Beispiel: Das Ergebnis ist die Abfrage von "College" und "School", Dann kommt
man zu der Abfrage:


query_result.raw_response in tabel


also mittels einer DB-Lösung könnte das formalisiert so aussehen - die Tabelle "town-hotspot"
Wir können hier ordnen und strukturieren bzg. latitude/longitude (unter Einsatz von z.B. MySQL+PHP)

Das könnte dann etwa so aussehen:

GEODATA                  noch mehr DATEN
--------------------------------------|-----
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


hier ließe sich eine Ordnung herstellen - in Bezug auf Distanzen  z.B. zu einem Referenzdatensatz:  (41.834527,-108.140625).
wenn man hiervon ausgeht, kann man etwa so auch in der Town-hotspot-Tabelle ansetzen:

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

Aber ich denke, dass man das Geosorting auch direkt in der Datenbank ausgeführt werden kann.
Das sollte m.E. möglich sein, mittels eines effektiven Ansatzes einer Geo Spatial-Suche in einer Datenbank.

Also worauf es ankommt; nearby places zu finden: gut wäre es in einer "aufsteigenden" Reihenfolge bezogen auf die Distanz zu bekommen.
Die Datenbank könnte ungefähr so aussehen.

CREATE TABLE town-hotspot (
id int(11) NOT NULL ,
category varchar(400),
category_tag varchar (200),
[amenity:] name varchar(400),
address varchar(400) ,
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)
)

Anm.; ich habe ca. 5000 records in der Datenbank.

Daten wie  latitude, longitude, Addresse, Stadt, Land etc. etx.

Wie finde ich "nearby-places" in der DB "town-hotspot":

a. Ein Ansatz ist es mittels min, max range of lat, lng in einem Radius von x Km und das in
Relation zu Kategorie bzwe Category und category tag..

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)


Es ist also die Frage  wie ich das am besten umsetze  - wenn man das nearby - thema komplett in einer OSM-Streetmap-Lsg. abbilden wollte.

[out:json][timeout:25];

nwr(around:10000,40.40,-79.93)["amenity"="hospital"];

out center;

Wie oben aber beschrieben wollte ich zunächst mit Tabellen arbeiten - und eine tabellarische Lsg ergo zunächst mal ins Auge fassen..
Es sollen die Daten / Abfragen dann auch online in einem Webfrontend verwendet werden.

Wie würdet ihr  das angehen?


Viele Grüße Tag_the_World smile


Zusatz: ich hab noch ein paar Aufsätze gefunden - die zum Thema passen: hier drei englische Aufsätze, die das Thema nochmals auf der DB-Ebene interessant beleuchten.  Ich lese mich übers Wochenende mal ein.


a. Playing with Geometry/Spatial Data Types in MySQL https://medium.com/sysf/playing-with-ge … 5b83880331

In this tutorial, we are going to understand how to store and use spatial data types like coordinates and objects. But, we will mainly focus on Points (2D Cartesian Coordinate) and Geographic Locations (Geodetic Coordinates)

Looking from a backend perspective, we would be needing to store geographic data of these locations like Latitude and Longitude. Then we would need to write functions that calculate the distance between the user and the location (to show how far the location is from him/her). Using the same function, we can design an algorithm that finds closest places near to the user or within a given radius from him/her.
There are many tutorials and study material on the internet that helps you solve this problem by using simple data types like float for latitude and longitude and MySQL’s capability to create internal procedures/functions to calculate & search locations. But in this tutorial, we are going to talk about MySQL’s built-in spatial data types.
MySQL is one of the databases that follow a subset of this standard. You can read about OGC from opengeospatial.org.


b. Working with PostgreSQL and PostGIS: How To Become A GIS Expert
https://www.percona.com/blog/2020/04/15 … is-expert/


c. Geospatial Support in MongoDB https://www.baeldung.com/mongodb-geospatial-support

2. Storing Geospatial Data
First, let's see how to store geospatial data in MongoDB.
MongoDB supports multiple GeoJSON types to store geospatial data. Throughout our examples, we'll mainly use the Point and Polygon types.

2.1. Point
This is the most basic and common GeoJSON type, and it's used to represent one specific point on the grid.
Here, we have a simple object, in our places collection, that has field location as a Point:


Wie würdet ihr vorgehen: Diese Umgebungssuche lässt sich auf verschiedenen Wegen erreichen.  Das Ziel über ein Webfrontend einen Ort einzugeben und dann aus 3 oder vier Kategorien ( Hospital, School, x, y ) die jeweils nähsten Einträge noch zu finden und aufzulisten - diese Aufgabenstellung - sie lässt sich mittels mehrer Ansätze verfolgen:

a das ganze als MySQL-Frage (oder PostgreSQL, wenn man so will) oder
b. mittels Overpass-API

Ich versuche, eine einfache, aber effektive Lösung für eine Nearby-Suche, gelöst über eine Datenbank zu finden - wie man sie durchführt;
smile

... und am WE lese ich mich mal in die engl. Aufsätze ein... - vg

Last edited by tagtheworld (2021-01-28 01:37:18)


https://wiki.openstreetmap.org/wiki/User:Tagtheworld - interested in all things concerning osm especially those related to the languages Perl, PHP, Python and the databases MySQL and Postgresql.

Offline

Board footer

Powered by FluxBB