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

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

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…

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

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

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:


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

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:

[cöde]

                    +-------------------+
    +---------------+                   +---------------+
    |               |   search-form     |               |
    |               +--+--------------+-+               |
    |                  |              |                 |

±------±-----+ ±------±—+ ±----±------+ ±----±-----+
| | | | | | | |
| dataset 1 | | dataset 2 | | dataset 3 | | dataset 4 |
| | | | | | | |
| | | | | | | |
±-------------+ ±-----------+ ±------------+ ±-----------+

[/cöde]

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:

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