Jump to content

a retrieval out of 4 x 700 records of national data-sets


Recommended Posts

dear Community

i have got a collection of four datasets: 

dataset 1; dataset 2; dataset 3; dataset 4

	|     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 

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

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

Link to comment
Share on other sites

hello dear Barand 


first of all - glad to hear from you - this is a thing that "attracts especially you" - i thougth - and yes: i am glad to get feedback from you"


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.

very good idea - 


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:

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:

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

with this I have mapped the data relationally, but can also query them nonrelationally if necessary and
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: 

@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:

  "version": 0.6,
  "generator": "Overpass API 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:

type of school
legal form

- 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

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 

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

well the find nearby solution - which is table based should look like so: 


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



well Barand - if we take the two attributes 

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: i remember that we once have had a similar question years ago...  . you gave me brilliant and very helpful answers there too... 

how to find this thread!?`is there a solution in this forum - to find alll the threads where you and i were discussing things.!?  just another sql - task ... probably were able to solve even this one...




Edited by dil_bert
Link to comment
Share on other sites

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. 

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.

Link to comment
Share on other sites


.... here an example for an  Overpass-API -request :: see the Hospitals in 10 Km  distance to berlin - alexanderplatz .



also:  Berliner Aleanderplatz:

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


see the  dataset: 

	[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;



	@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 http://www.alexianer-berlin-hedwigkliniken.de/st_hedwig_krankenhaus
	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 http://www.schlosspark-klinik.de
	119398219 way 13.3172076 52.4803471 Sankt Gertrauden-Krankenhaus 10713 Berlin Paretzer Straße 12 http://www.sankt-gertrauden.de
	132056990 way 13.2918016 52.4904830 Martin-Luther-Krankenhaus 14193 Berlin Caspar-Theyß-Straße 27-31 http://www.pgdiakonie.de/martin-luther-krankenhaus
	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 http://www.vivantes.de/kau
	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 http://www.franziskus-berlin.de



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: https://tablepress.org/demo/


love to hear from you again 

Edited by dil_bert
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.