hello dear community
first of all i sm happy with this great forum -
i have got a collection of four datasets:
dataset 1; dataset 2; dataset 3; dataset 4
dataset-1
four datasets with about 500 to 700 records
the datasets are derived from germany - from a search on the planet file of openstreetmap
see the sets
all the four sets reside in single spreadsheets
i want to recieve a retrieval - for the surroundint of postal codes see the example...
if we take the postal coded 8000 which belongs to the area of munich by the way: i want to recieve the sets of data that belong to this area...
is this possible - is this a good idea!? or should i put all the data into one big table - ..?
question: is this a good idea!? or should i put all the data into one big table - ..?
we could do it like so:
One big table with column indicating the type of data (1=hospital, 2=bakery, 3=pharmacy, 4=grocery etc) plus a second table to store those types and type numbers.
well - it is a solution that could be done with openstreetmap instantly - it is a solution that delivers a distance Matrix to help users find nearby locations on an interactive ...in other words: a solution that - discover the nearest entity ::
So it is like this: I obtained the data via openstreetmap and saved it in a table - but of course I can also use MySQL or sqlite
By the way: this should be used for a web application:
so: if you have a point - so we take, for example, Berlin's Aleanderplatz:
the above I could also query data relating to the tables directly via the Overpass API: https://wiki.openstreetmap.org/wiki/Overpass_API
At first I thought to work without the OSM map display and only allow a tabular search. The query via Overpass-Api could be
in my opinion yes also set up and implemented directly in the web frontent.
so - let's say the tables are as follows:
For now, however, I want to save the data locally (each data record has a unique ID) and, by the way, also keep the timestamp with it,
at which the data record was last updated. If the timestamp is smaller than the last run then you know
that the data record no longer exists and then I can either cancel it and no longer select it or delete it.
Concerning. the representation in a map: the map could be operated via a WebGIS plugin (for example Leaflet),
then load the data from Overpass from the local DB and integrate it separately as a marker on the map.
to the implementation via query on the OSM endpoint: here you can also use Overpass - for querying POIs;
Since homogenization is not possible due to the differences between the POIs, I save
Transferred from the backend as JSON to the frontend. I can also search through the attribute values (attr_val) regardless of the field name.
With the right indices, this works efficiently and without problems.
... so if I have 2 records ...
- here for example related to "schools"
once the school with lon, lat would look like so:
or - you can also do it like this:
The above would be the first record:
And if I then ..... then also a data set with the following attributes:
- then I can work well with these two data sets. Because I then mapped the data relationally using the method mentioned above,
And in addition, I can also query non-relationally if necessary: And yes: I can also read the data from the backend as JSON and then use it
work. I can then search the DB for attribute values (attr_val) regardless of the field name.
we can query the data via the Overpass API: https://wiki.openstreetmap.org/wiki/Overpass_API
I would save them locally (each record has a unique ID) and keep the timestamp when the record was last updated. If the timestamp is smaller than the last run then you know that the data record no longer exists and you can either cancel it and no longer select it or delete it.
I would run the map via a WebGIS plugin (I recommend Leaflet), then load the data from Overpass from your DB and integrate it separately as a marker on the map.
What are the advantages of individual tables? If the distinction is only the categorization, you could simply work with filters and the like.
here - in this case it has to be clarified what exactly speaks for individual tables?
If the distinction is just the categorization, I could just work with filters and the like - me
would then have to add one more attribute to the values ...
.... so the
I would just have to - if I put everything in a large table then carry the attribute category: clinic (hosp), bakery, milk or shoe shop, school
I would also have to carry the exact geographic
..[...]....
etc. etx.
well the find nearby solution - which is table based should look like so:
it seems to be a iterative nature of finding a better nearby solution forms this task-.
conclusion:
well - if we take the two attributes
would we be able to do this table based solution - and find the nearest entity of all the types ...
look forward to hear from you
by the way iin openstreetmap-solutions it is like so:
- to construct a query, that helps to get "hospitals" which is a special phrase according to this link
Here's an example Overpass-API query that returns hospitals within a 10 Km radius of that location.
by the way: we could also compute a bounding box from the coordinate and restrict the search to that area,
if that were more suitable for the certain goal.
... here an example for an Overpass-API -request :: see the Hospitals in 10 Km distance to berlin - alexanderplatz .
also: Berliner Aleanderplatz:
see the dataset:
this gives us back
and for the other datasets we could construct the requests simiiar
but wait - i love to have a table solution - and no web-frontend where the visitor can run requests to the osm-endpoint.
i love the table-solution since you can browse the tables as well
see a Demo: << link removed by moderator>>
first of all i sm happy with this great forum -
i have got a collection of four datasets:
dataset 1; dataset 2; dataset 3; dataset 4
dataset-1
Code:
+-----------+-------+------------+-------+--------+------------------+----+------------+-------+--------------+
| id | @type | name | plz | city | addr:street | hn |addr:email |etc/etx| special-data |
+-----------+-------+--------------------+---------------------------+----+------------+-------+--------------+
| 268915924 | node | clinic 1 | 12055 | Berlin | Zwiestädter Str. | 23 |=ZÄHLENWENN | | |
| 268915972 | node | clinic 2 | 80004 | München| Hedwigstraße | 53 |=ZÄHLENWENN |etc/etx| special-data |
| 268916030 | node | clinic 3 | 70545 | Stgt | Donaustraße | 48 |=ZÄHLENWENN | | |
| 268916196 | node | clinic 4 | 20222 | Hamburg| Winsstraße | 31 | | | |
+-----------+-------+------------+-------+--------+------------------+----+------------+-------+--------------+
four datasets with about 500 to 700 records
the datasets are derived from germany - from a search on the planet file of openstreetmap
see the sets
Code:
dataset_1 _ hospitals
dataset_2_ backery
dataset_3_ pharmacy
dataset_4_ grocery
all the four sets reside in single spreadsheets
i want to recieve a retrieval - for the surroundint of postal codes see the example...
if we take the postal coded 8000 which belongs to the area of munich by the way: i want to recieve the sets of data that belong to this area...
Code:
dataset_1 _ hospitals
dataset_2_ backery
dataset_3_ pharmacy
dataset_4_ grocery
is this possible - is this a good idea!? or should i put all the data into one big table - ..?
question: is this a good idea!? or should i put all the data into one big table - ..?
we could do it like so:
One big table with column indicating the type of data (1=hospital, 2=bakery, 3=pharmacy, 4=grocery etc) plus a second table to store those types and type numbers.
well - it is a solution that could be done with openstreetmap instantly - it is a solution that delivers a distance Matrix to help users find nearby locations on an interactive ...in other words: a solution that - discover the nearest entity ::
So it is like this: I obtained the data via openstreetmap and saved it in a table - but of course I can also use MySQL or sqlite
By the way: this should be used for a web application:
so: if you have a point - so we take, for example, Berlin's Aleanderplatz:
Code:
DD COORDINATES; 52.519664588 13.407998368
DMS COORDINATES; 52 ° 31'10.79 "N 13 ° 24'28.79" E
the above I could also query data relating to the tables directly via the Overpass API: https://wiki.openstreetmap.org/wiki/Overpass_API
At first I thought to work without the OSM map display and only allow a tabular search. The query via Overpass-Api could be
in my opinion yes also set up and implemented directly in the web frontent.
so - let's say the tables are as follows:
Code:
dataset_1_ Clinics / hospital (hosp)
dataset_2_ Bakeries
dataset_3_ Dairy Shops
dataset_4_ Shoe stores
dataset_5_ schools
For now, however, I want to save the data locally (each data record has a unique ID) and, by the way, also keep the timestamp with it,
at which the data record was last updated. If the timestamp is smaller than the last run then you know
that the data record no longer exists and then I can either cancel it and no longer select it or delete it.
Concerning. the representation in a map: the map could be operated via a WebGIS plugin (for example Leaflet),
then load the data from Overpass from the local DB and integrate it separately as a marker on the map.
to the implementation via query on the OSM endpoint: here you can also use Overpass - for querying POIs;
Since homogenization is not possible due to the differences between the POIs, I save
- the POI with its coordinates in a table (poiID, lat, lng).
- I save the attributes in an extra table (poiID, attr_name, attr_val).
Transferred from the backend as JSON to the frontend. I can also search through the attribute values (attr_val) regardless of the field name.
With the right indices, this works efficiently and without problems.
... so if I have 2 records ...
- here for example related to "schools"
once the school with lon, lat would look like so:
Code:
@id @type lon :, lat:
268915924 node,
name: max müller school
or - you can also do it like this:
The above would be the first record:
Code:
{
"version": 0.6,
"generator": "Overpass API 0.7.56.6 474850e8",
"osm3s": {
"timestamp_osm_base": "2020-10-15T13:56:02Z",
"copyright": "The data included in this document is from www.openstreetmap.org. The data is made available under ODbL."
},
"elements": [
{
"type": "node",
"id": 268915924,
"lat": 53.5955976,
"lon": 9.9249389,
"tags": {
"amenity": "school",
"name": "
name max müller Schule
"
}
},
{
"type": "node",
"id": 268915924
"lat": 53.6874870,
"lon": 9.6838098,
"tags": {
"amenity": "school",
"name": "Birkenallee-Schule"
}
},
{
"type": "node",
"id": 268980540,
"lat": 53.6927053,
"lon": 9.9905466,
"tags": {
"amenity": "school",
"name": "Gemeinschaftsschule Ossenmoorpark mit Außenstelle am Aurikelstieg",
}
},
{
And if I then ..... then also a data set with the following attributes:
Code:
Surname
School_ID
type of school
address
place
district
district
phone
FAX
legal form
Internet
E-mail
is_rebbz
FID
- then I can work well with these two data sets. Because I then mapped the data relationally using the method mentioned above,
And in addition, I can also query non-relationally if necessary: And yes: I can also read the data from the backend as JSON and then use it
work. I can then search the DB for attribute values (attr_val) regardless of the field name.
we can query the data via the Overpass API: https://wiki.openstreetmap.org/wiki/Overpass_API
I would save them locally (each record has a unique ID) and keep the timestamp when the record was last updated. If the timestamp is smaller than the last run then you know that the data record no longer exists and you can either cancel it and no longer select it or delete it.
I would run the map via a WebGIS plugin (I recommend Leaflet), then load the data from Overpass from your DB and integrate it separately as a marker on the map.
What are the advantages of individual tables? If the distinction is only the categorization, you could simply work with filters and the like.
here - in this case it has to be clarified what exactly speaks for individual tables?
If the distinction is just the categorization, I could just work with filters and the like - me
would then have to add one more attribute to the values ...
.... so the
Code:
dataset_1_ Clinics / hospital (hosp)
dataset_2_ Bakeries
dataset_3_ Dairy Shops
dataset_4_ Shoe stores
dataset_5_ schools
I would just have to - if I put everything in a large table then carry the attribute category: clinic (hosp), bakery, milk or shoe shop, school
I would also have to carry the exact geographic
Code:
@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
..[...]....
Code:
74400076 Backery -43.9590472 -19.9455820 French Baguette Rio
72219634 School -43.9590472 -19.8885820 Paulo Freire-School Rio Rio
etc. etx.
well the find nearby solution - which is table based should look like so:
Code:
+-------------------+
+---------------+ +---------------+
| | search-form | |
| +--+--------------+-+ |
| | | |
+-------+------+ +-------+----+ +-----+-------+ +-----+------+
| | | | | | | |
| dataset 1 | | dataset 2 | | dataset 3 | | dataset 4 |
| | | | | | | |
| | | | | | | |
+--------------+ +------------+ +-------------+ +------------+
it seems to be a iterative nature of finding a better nearby solution forms this task-.
conclusion:
well - if we take the two attributes
Code:
a. type of entity: (POI) - bakery - grocery - hospital - . etc. etc
b, geo-position: lon - lat
would we be able to do this table based solution - and find the nearest entity of all the types ...
look forward to hear from you
by the way iin openstreetmap-solutions it is like so:
- to construct a query, that helps to get "hospitals" which is a special phrase according to this link
Here's an example Overpass-API query that returns hospitals within a 10 Km radius of that location.
Code:
[out:json][timeout:25];
nwr(around:10000,40.40,-79.93)["amenity"="hospital"];
out center;
by the way: we could also compute a bounding box from the coordinate and restrict the search to that area,
if that were more suitable for the certain goal.
... here an example for an Overpass-API -request :: see the Hospitals in 10 Km distance to berlin - alexanderplatz .
also: Berliner Aleanderplatz:
Code:
DD COORDINATES; 52.519664588 13.407998368 DMS COORDINATES; 52°31'10.79" N 13°24'28.79" E
see the dataset:
Code:
[out:csv(::id,::type,::lon, ::lat, "name","addr:postcode","addr:city","addr:street","addr:housenumber","contact:website"," contact:email=*")][timeout:600];
nwr(around:10000,52.519664588 13.407998368)["amenity"="hospital"];
out center;
this gives us back
Code:
@id @type @lon @lat name addr:postcode addr:city addr:street addr:housenumber contact:website contact:email=*
775745348 node 13.3640559 52.5037269 Evangelische Elisabeth Klinik 10785 Berlin Lützowstraße 24-26
1257340412 node 13.3717015 52.4778100 Klinik für Kinder- und Jugendmedizin
4610129 way 13.3703633 52.5554121 Jüdisches Krankenhaus Berlin 13347 Berlin Heinz-Galinski-Straße 1
4637750 way 13.3752301 52.5573670 DRK Kliniken Berlin | Mitte
4750929 way 13.3428630 52.5421508 Charité Campus Virchow Klinikum
24054786 way 13.3967186 52.5260055 St. Hedwig-Krankenhaus 10115 Berlin Große Hamburger Straße 5-11 [url=http://www.alexianer-berlin-hedwigkliniken.de/st_hedwig_krankenhaus]http://www.alexianer-berlin-hedwigkliniken.de/st_hedwig_krankenhaus[/url]
24931203 way 13.3713474 52.5356575 Bundeswehrkrankenhaus Berlin 10115 Berlin Scharnhorststraße 13
32424425 way 13.3465511 52.4620791 Auguste-Viktoria-Klinikum 12157 Berlin Rubensstraße 125
41447057 way 13.4297195 52.5406155 "Vivantes Klinikum im Friedrichshain, Standort Prenzlauer Berg" Fröbelstraße 15
46839073 way 13.4954295 52.5133024 Sana Klinikum Lichtenberg 10365 Berlin Fanningerstraße 32 https://www.sana-kl.de/
48992251 way 13.3902674 52.4590154 Vivantes Wenckebach-Klinikum 12099 Berlin Wenckebachstraße 23
49876720 way 13.5095722 52.5280791 Evangelisches Krankenhaus Königin Elisabeth Herzberge 10365 Berlin Herzbergstraße 79 https://www.keh-berlin.de/
53038853 way 13.2772940 52.5202588 DRK Kliniken Westend 14050 Berlin Spandauer Damm 130
93478285 way 13.2814545 52.4792516 Klinik am Roseneck
99146927 way 13.4141205 52.5709842 Maria Heimsuchung Caritas Klinikum Pankow
114201147 way 13.2897238 52.5222616 Schlosspark-Klinik 14059 Berlin Heubnerweg 2 [url=http://www.schlosspark-klinik.de]http://www.schlosspark-klinik.de[/url]
119398219 way 13.3172076 52.4803471 Sankt Gertrauden-Krankenhaus 10713 Berlin Paretzer Straße 12 [url=http://www.sankt-gertrauden.de]http://www.sankt-gertrauden.de[/url]
132056990 way 13.2918016 52.4904830 Martin-Luther-Krankenhaus 14193 Berlin Caspar-Theyß-Straße 27-31 [url=http://www.pgdiakonie.de/martin-luther-krankenhaus]http://www.pgdiakonie.de/martin-luther-krankenhaus[/url]
137033906 way 13.3637902 52.5545152 Evangelisches Geriatriezentrum Berlin 13347 Berlin Reinickendorfer Straße 61
138144499 way 13.4578379 52.4343190 Ida-Wolff-Krankenhaus 12351 Berlin Juchaczweg 21
138150297 way 13.4589236 52.4372389 Vivantes Klinikum - Neukölln 12351 Berlin Rudower Straße 48
168967363 way 13.3729563 52.4781040 St. Joseph Krankenhaus 12101 Berlin Wüsthoffstraße 15
229099728 way 13.3297666 52.4896219 Friedrich von Bodelschwingh-Klinik
305989452 way 13.4085942 52.4939881 Vivantes Klinikum am Urban 10967 Berlin Dieffenbachstraße 1 [url=http://www.vivantes.de/kau]http://www.vivantes.de/kau[/url]
326445437 way 13.3283008 52.5830005 Krankenhaus des Maßregelvollzugs Berlin (KMV) 13403 Berlin Olbendorfer Weg 70
326448280 way 13.3274384 52.5812119 Vivantes Humboldt-Klinikum - Standort Oranienburger Straße 13437 Berlin Oranienburger Straße 285
378933899 way 13.4507369 52.5554951 Park Klinik Weißensee
391245035 way 13.3878055 52.5350773 Lazarus-Kranken- und Diakonissenhaus
567540939 way 13.4691308 52.5505504 Alexianer St. Joseph Krankenhaus Berlin-Weißensee
583306346 way 13.3778632 52.5257624 Charité Universitätsmedizin Berlin
700483283 way 13.4394766 52.5255323 Klinikum im Friedrichshain 10249 Berlin Landsberger Allee 49 https://www.vivantes.de/fuer-sie-vor-ort/klinikum-im-friedrichshain-landsberger-allee/
7715945 relation 13.3452165 52.5060328 Franziskus-Krankenhaus Berlin 10787 Berlin Budapester Straße 15-19 [url=http://www.franziskus-berlin.de]http://www.franziskus-berlin.de[/url]
and for the other datasets we could construct the requests simiiar
but wait - i love to have a table solution - and no web-frontend where the visitor can run requests to the osm-endpoint.
i love the table-solution since you can browse the tables as well
see a Demo: << link removed by moderator>>
Last edited by a moderator: