dil_bert Posted October 24, 2020 Share Posted October 24, 2020 dear Community i have got a collection of four datasets: dataset 1; dataset 2; dataset 3; dataset 4 dataset-1 +-----------+-------+------------+-------+--------+------------------+----+------------+-------+--------------+ | 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 - ..? Quote Link to comment Share on other sites More sharing options...
Barand Posted October 24, 2020 Share Posted October 24, 2020 25 minutes ago, dil_bert said: is this a good idea!? or should i put all the data into one big table - ..? 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. 1 Quote Link to comment Share on other sites More sharing options...
dil_bert Posted October 25, 2020 Author Share Posted October 25, 2020 (edited) 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 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 [/code] - 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-. conclusion: 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 October 25, 2020 by dil_bert Quote Link to comment Share on other sites More sharing options...
dil_bert Posted October 25, 2020 Author Share Posted October 25, 2020 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: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. Quote Link to comment Share on other sites More sharing options...
dil_bert Posted October 25, 2020 Author Share Posted October 25, 2020 (edited) .... 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 October 25, 2020 by dil_bert Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.