Database structure for route encyclopedia
A writing on the topic “what form of record keeping would I choose for an encyclopedia of urban / suburban transport routes” in a free style.
Most of all, when writing this text, I was guided by the remnants of knowledge from the field of programming, at the same time reinterpreting for myself the notion “normal form of writing in the database”.
1. I would like to have a system in which one could look at the bus / trolley bus route in a foreign city, where I am going for the first time. In the most basic case, I just need to find out how it goes, at which intersection it turns off and where the final one is, let us talk to the conductor or driver about the stops. In thekosher luxury case - how much is the fare, the interval of movement, all the juicy details of the route with such detail that you can replace the driver.
2. Route encyclopedia should be, in fact, some kind of DBMS. This means that all routes need to be entered into the database. It is necessary to determine the form of the record in such a database, which can be easily processed, and it is desirable that if something changes in the DBMS, the old database can continue to be used. It is necessary that the record was less unnecessary and more necessary.
3. What do we need in the record? In fact, we need id, route route and metadata.
With id, everything seems to be clear: an identifier for yourself, okay. The route of the route is a set of geographical points, which% of the bus% passes in a sequential order with passengers, and some of these points have stops (landing-disembarking). Each route starts from the initial stop and ends at the final one. Even if the route is a roundabout, you can find some place where the route conditionally begins (from where, say, production of cars for the route usually begins). It turns out one broken line that goes from the starting point to the ending point (with direction). Everything else is not a highway. Also, the highway is not the turn of the transport (if before this, all passengers are expelled at the final one. And if they are not expelled, then, in principle, you can consider the turn as part of the route. But still, the "final" before the U-turn and the "initial" after - should be, in real life, at least formally).
Metadata are those that relate to one route in all its variations, which do not depend on which route any particular bus% will go along with this number. (A clear representative of this type of meta-information is the actual route number). There are metadata that relate only to a specific route (they mainly describe the time-cost-spatial parameters of the trip). It means that they should be shown together with the corresponding route, and not attributed to the metadata of the previous type.
If you look at it like this, the database structure appears like this: id; metadata independent of the road; several sets of tracks with metadata that relate only to them.
3+. A little detail. The statistical majority of routes, whether rail or trackless, will nevertheless require exactly two routes per id - “there” and “back”. This fact should not force us in any way to limit the choice of the form of a record in the database, but it should be taken into account when developing the interface and search. For example, if the user requests a specific, but specific, variation of the route, it is often more convenient for him to immediately see both the direct and reverse tracks, especially since this does not visually clutter up the map.
(If you draw all the routes of the route on the map at once, even if only at one end, it clutters the map with repeatedly induced lines and distracts attention.)
3 ++. There are cases of routes-polymorphs, when, for example, every second% bus leaving% has a different route route (for example, it calls at an array or station). There is such a route 365 in Moscow - I myself was not, but they told me: although it is considered one route, in fact, four routes hide under this number, albeit with the same starting and ending point. And surely a sign on the windshield of the bus always warns about following a specific one. In fact, there are four different routes: “365”, “365 with a stop in A”, “365 through B”, “365 through A and B”. Since the route is, in fact, four different (and when you search from the vicinity of the initial stop to point A, only two of the four results should fall out) - accordingly, I propose and store them under four different id, especially since such routes are overwhelmingly minority. Again, usability does not suffer much, because we can easily and naturally when outputting data to the user, output to him something like a list of “similar routes”.
4. Now that the storage model of the route is chosen, you can think about the meta-data. In addition, some of them depend on a specific route, and some only on a specific route, they can be further divided into two groups - those that require manual filling and those that (in order to store all data in a standardized, same form) users are better not to give. For example, all geographic data (country-subject of federation-region-city) should be stored in a standard form, otherwise it will not be possible to quickly and smoothly fasten tags (for more details in paragraph 10). It is convenient to take the information in the form in which it gives, for example, the google maps API. (There are alternatives like GEOS, they chose Google Maps for example only).
Further, I will always mean by the word "city" at the same time a complete set of meta-categories about it - country-district-region-city.
5. You should not forcibly record a route to any one city from the catalog, otherwise suburban routes raise a lot of questions. Even more questions put routes between the two twin cities, like Dnepropetrovsk and Dneprodzerzhinsk (only closer). But at least we need to tie up routes to something, otherwise we risk getting tram 30 from Moscow time, St. Petersburg, Kiev - and Helsinki, at the request of the “tram thirtieth number”. It happens even worse: within the same Moscow, you can find bus number 1 from Zelenograd, bus number 1 from Korolev and a couple more of the first buses! You need to have a special field for each id, which would tell you where this or that bus comes from - something like “the city where the route is assigned / approved” (semi-auto - select from the list or click on the map with the google API address). Figuratively speaking, the field can be called the “home port”.
To make my thought easier to understand, example:
- there are routes with the same number in Nerezinovaya: 1 (Korolev); 1c; 1 (Zelenograd); 1 (Khimki). For the first route in this tricky field we will write down “Korolev”, for the second - “Moscow”, for the 3rd and 4th - “Zelenograd” and “Khimki”, respectively. When a user searches for a road from point A to point B, instead of answering “bus 1”, we will submit “bus 1 (Korolev)”, even if A and B are entirely in Moscow. Of course, if the “from” and “to” points of the search are both in the “city of registry”, then this field can be not displayed on the screen. Among other uses, this field is the basis for cataloging and a means of combating the same routes in the same city.
Another example: bus 70 for the city of Sevastopol is suburban, it passes a considerable part of Sevastopol, then travels around the suburbs and turns out to be in Foros. If someone searches for a route within Foros, or from Foros, and this bus number 70 falls on his issue, he will see “70 (Sevastopol)” and its full route, which is unlikely to interfere with the user, or rather will help him. (I am exaggerating: a user from Foros can at one point receive an issue from two seventieth routes, and here the display form "70 (Sevastopol)"; "70 (Yalta)" will simply save him.)
6. Next, I will provide a list of meta-data that I found necessary, and which relate to the entire route at once. Immediately and comments to them.
Those fields that would be worth filling out with minimal user freedom will be noted in brackets (auto) or (semi-auto) - the latter provides for a choice from the list.
Metadata that should be attributed to the entire route at once:
- “home city” - see 5.
- type of transport (semi-auto - select from the list) - one drop-down list. Convenience of users for the sake of it can be divided into categories, for example: Rail (subway, tram, city train, funicular, monorail and other exotic), Water (ferry or river tram, by the way, greetings from Sevastopol), Ground (bus, trolleybus, route Taxi).
- route number - in principle, this is a text field. Because: "422d"; bus at the time of the cancellation of the tram "3-tr"; or even Aeroexpress or SkyBus. But it is worth limiting its length, or something like that! Otherwise, the database (as well as the output of routes for a single large city) will be cluttered with the long, unnumbered names of suburban and intercity routes (“Orekhovo-Zuyevo - Spasokokukotsko-Narochnenskoe”) and the “catalog” of routes of a single megapolis cannot be viewed without a moan of horror . And in general, I personally adhere to the belief that such routes should be served by bus stations completely, including drawing their tracks on the map.
- carrier - if possible, the full (with the form of ownership) the name of the legal person-carrier. By the way, here the use of tags is also more than appropriate, even to the point of indicating not only the tag jur. faces of the carrier, but also of the ATP / motorcade / depot serving the route (relevant and very interesting for megalopolises with public utilities).
- fare - the route is often fixed and does not depend on the route - but only on the carrier / route number. But sometimes it does not, for example, when the return route is due to, say, one-way traffic on the highway, it turns out so much longer that the private carrier exposes a different price. This is rare, so the cost is global for the route field. Auto substitution of currency based on the “home city” is, of course, beautiful. But you need to warn users that when creating routes where the cost depends on the distance / zone, they enter a minimum-maximum in the field and / or leave comments about it.
- general features of the route. - for example, “the route is served only by the newest buses MAZ-107”, or “turnstiles are installed on the buses of the route”.
7. Leave the metadata for a while and work on the routes of the routes. One route, as we have already decided, must correspond to one flight% of the bus% one way. In principle, the accuracy with which the Google map gives us the coordinates of the same Google, when we put a dot on it, at the present time is completely commensurate with the GPS positioning accuracy (although it is slightly worse - it is a few meters, and the same approximate error , alas, shows a combination of maps from different sources). It is worth using such blessings of civilization and when drawing a new route - draw it with the most accessible detail, with all the subtleties of passing the interchanges and the side of the street. There is no huge problem if there is no such detail, but it is worth striving for it. In addition to the importance of this for data normalization (there is nothing better than recording a track without jokes, exactly as it actually goes) - sometimes it’s interesting where% bus% turns at this interchange, and, accordingly, how to get to it stop! In addition, great reassures, hitting a foreign city, to navigate at least in something thoroughly.
8. According to the data storage model chosen by us, the routes of the “there” and “back” routes should be stored close to each other, although they should be treated as independent objects (routes). Routes of routes that undergo periodic changes (for example, on weekends), as well as routes of routes that vary from flight to flight (every first bus goes to A, and every second one to B) are distributed to different id. This helps to simplify the search and creation of pairs of "round-trip" routes for routes. Again, can we hook on some kind of parameter on the track like a “circular route? Yes / no or “one way route?” And thus inform the DBMS that we will not process a couple of routes using this id, but only one.
9. Route route - a set of points and lines that connect them. Logical and economical way to store tracks - a sequence of points. On each track there are at least two special points, they are also - two stops - the initial and final. It may be advisable to store their names, and at the same time the remaining stops, along with the track in the list of coordinates. Then they are easy to process: if there is a name in front of a pair of coordinates, it means that it is a stop, and you need to select it when drawing, and when you hover the cursor - show its name. If there is no name, this is the usual point of the path, and you don’t need to select it, but simply draw the trace lines through it. In order not to interfere with numbers and text, you can simply put some label, for example, the sequence number of the stop on the track in the metadata. At the very least, to make the same point as the stop, from which the entire route is built, seems to me very logical.
You can develop the idea of storing data about waypoints and stops: the first stop can be assigned the number 1, and the last one - say, 0, or 9999. For example, route 33 of Kiev, Ukraine:
This gives another advantage - using the button “Draw a reverse route” you can immediately fill in the initial (with the same name as the former final) and final (on the contrary) stops.
A function for the distant future is taking into account multi-route stops, when one stop serves several routes. It seems that I clicked on such a stop - and the pop-up hint "stop here: ...". Implement when drawing, for example, as “magnet” with existing stops of the drawing route.
It is also worth notifying all users that the stops need to be added either only the initial and final, or all at once, otherwise confusions are possible.
10. Now about geolocation and city tags. Suppose a user has successfully drawn a route and clicked the “save route route” button. Now we can feed the drawn stops on the Google maps API and analyze the answers to determine if these bus stop points belong to cities. Moreover, even if the user creating the route has not yet taken a halt, there are already two — the initial and the final — by definition!
Based on the answer, we automatically create cross-links of the route to these cities (and if at least one does not exist yet, we immediately create it in the base of cities), and cities to this route (as it passes in this city!). This will be our tag cloud.
Immediately there are ample opportunities for “protection from a fool” - if none of the cities turned out to be a “home city” from the global for id field of the database record - most likely, an error has crept in here.
Now the “catalog of routes” for a city will show all routes in which the tag with the name of this city is found.
11. Metadata that should be attributed to a specific track:
- a cloud of tags of the cities through which the route follows - see 10 and 3 ++ - why exactly to a specific route / pair of routes.
- list of names of stops - in the minimum case you need to fill at least two. Therefore, this filling should be done immediately when adding a route to the database. The first and last stops can be removed by the DBMS every time when accessing the highway and displayed in the necessary places, separately from the full list or with it - or used when building the reverse route.
- movement interval - a simple text field for comments in the free form. Perhaps, if someday the encyclopedia will count the trip time, and even a plausible one, it will be necessary to bring this field to a standard form for calculating the average waiting time - many% of buses% go at peak hours with a completely different regularity than at a different time.
- the length of the route on the map (autosource).
- weekly schedule - according to the scheme chosen by us, if the route changes the route, for example, for the weekend, a separate id is created under it. Therefore, when linking to an id of a specific route / pair of tracks, the “weekly schedule” field is filled only in the meantime, on which% buses% of the route go along this route / pair of tracks!
- work time is similar to the previous item.
- the range of motion is similar to the previous two.
12. In essence, the database seems to be everything. Now you can devote a little time to things that I would not seem to touch - the interface for adding a record to the database.
ps They say that the use of the “database without schema” architecture solves a lot of problems, since any necessary fields can be screwed up as necessary, using them practically without side effects.
Most of all, when writing this text, I was guided by the remnants of knowledge from the field of programming, at the same time reinterpreting for myself the notion “normal form of writing in the database”.
1. I would like to have a system in which one could look at the bus / trolley bus route in a foreign city, where I am going for the first time. In the most basic case, I just need to find out how it goes, at which intersection it turns off and where the final one is, let us talk to the conductor or driver about the stops. In the
Where to get these details
Information from official sources can only be obtained in text form and incomplete, or in the form of bulky bitmap images for each individual route - carriers and city administrations are too lazy to provide such information to everyone in a digestible form. So, we rely on people - local residents or travelers. Everyone will not be able to listen personally - it means that we are doing such a route Wikipedia.
Since the route wiki, if it has an optimal form of data storage, it claims to be no globality.
')
Users of the encyclopedia, they are local or newcomers in the city where they are or are going to get there, are guided mainly by signs and signs, that is, by signs. Inscriptions everywhere are made in the local language. That is, it is logical that a person who has fallen into, say, Georgia or Latvia, will not be able to really focus on his printouts from such a system made by Cyrillic.
Of course, this does not concern the user interface - the user is free to choose the language of the webmord he wants. But proper-stop names should always be asked to write in the original language. With a great desire, then it will be possible to fasten the "transcription into Russian" field, but this is clearly not a first-priority task. So, the interface is customizable (there are volunteers to translate it), the stops are local: in Russia, Russian, in Finland, Finnish, in Ukraine, Ukrainian, and so on. With the interface in general, then it is possible to solve, since this does not affect the database.
Since the route wiki, if it has an optimal form of data storage, it claims to be no globality.
')
Users of the encyclopedia, they are local or newcomers in the city where they are or are going to get there, are guided mainly by signs and signs, that is, by signs. Inscriptions everywhere are made in the local language. That is, it is logical that a person who has fallen into, say, Georgia or Latvia, will not be able to really focus on his printouts from such a system made by Cyrillic.
Of course, this does not concern the user interface - the user is free to choose the language of the webmord he wants. But proper-stop names should always be asked to write in the original language. With a great desire, then it will be possible to fasten the "transcription into Russian" field, but this is clearly not a first-priority task. So, the interface is customizable (there are volunteers to translate it), the stops are local: in Russia, Russian, in Finland, Finnish, in Ukraine, Ukrainian, and so on. With the interface in general, then it is possible to solve, since this does not affect the database.
2. Route encyclopedia should be, in fact, some kind of DBMS. This means that all routes need to be entered into the database. It is necessary to determine the form of the record in such a database, which can be easily processed, and it is desirable that if something changes in the DBMS, the old database can continue to be used. It is necessary that the record was less unnecessary and more necessary.
3. What do we need in the record? In fact, we need id, route route and metadata.
With id, everything seems to be clear: an identifier for yourself, okay. The route of the route is a set of geographical points, which% of the bus% passes in a sequential order with passengers, and some of these points have stops (landing-disembarking). Each route starts from the initial stop and ends at the final one. Even if the route is a roundabout, you can find some place where the route conditionally begins (from where, say, production of cars for the route usually begins). It turns out one broken line that goes from the starting point to the ending point (with direction). Everything else is not a highway. Also, the highway is not the turn of the transport (if before this, all passengers are expelled at the final one. And if they are not expelled, then, in principle, you can consider the turn as part of the route. But still, the "final" before the U-turn and the "initial" after - should be, in real life, at least formally).
Metadata are those that relate to one route in all its variations, which do not depend on which route any particular bus% will go along with this number. (A clear representative of this type of meta-information is the actual route number). There are metadata that relate only to a specific route (they mainly describe the time-cost-spatial parameters of the trip). It means that they should be shown together with the corresponding route, and not attributed to the metadata of the previous type.
If you look at it like this, the database structure appears like this: id; metadata independent of the road; several sets of tracks with metadata that relate only to them.
3+. A little detail. The statistical majority of routes, whether rail or trackless, will nevertheless require exactly two routes per id - “there” and “back”. This fact should not force us in any way to limit the choice of the form of a record in the database, but it should be taken into account when developing the interface and search. For example, if the user requests a specific, but specific, variation of the route, it is often more convenient for him to immediately see both the direct and reverse tracks, especially since this does not visually clutter up the map.
(If you draw all the routes of the route on the map at once, even if only at one end, it clutters the map with repeatedly induced lines and distracts attention.)
3 ++. There are cases of routes-polymorphs, when, for example, every second% bus leaving% has a different route route (for example, it calls at an array or station). There is such a route 365 in Moscow - I myself was not, but they told me: although it is considered one route, in fact, four routes hide under this number, albeit with the same starting and ending point. And surely a sign on the windshield of the bus always warns about following a specific one. In fact, there are four different routes: “365”, “365 with a stop in A”, “365 through B”, “365 through A and B”. Since the route is, in fact, four different (and when you search from the vicinity of the initial stop to point A, only two of the four results should fall out) - accordingly, I propose and store them under four different id, especially since such routes are overwhelmingly minority. Again, usability does not suffer much, because we can easily and naturally when outputting data to the user, output to him something like a list of “similar routes”.
4. Now that the storage model of the route is chosen, you can think about the meta-data. In addition, some of them depend on a specific route, and some only on a specific route, they can be further divided into two groups - those that require manual filling and those that (in order to store all data in a standardized, same form) users are better not to give. For example, all geographic data (country-subject of federation-region-city) should be stored in a standard form, otherwise it will not be possible to quickly and smoothly fasten tags (for more details in paragraph 10). It is convenient to take the information in the form in which it gives, for example, the google maps API. (There are alternatives like GEOS, they chose Google Maps for example only).
Further, I will always mean by the word "city" at the same time a complete set of meta-categories about it - country-district-region-city.
5. You should not forcibly record a route to any one city from the catalog, otherwise suburban routes raise a lot of questions. Even more questions put routes between the two twin cities, like Dnepropetrovsk and Dneprodzerzhinsk (only closer). But at least we need to tie up routes to something, otherwise we risk getting tram 30 from Moscow time, St. Petersburg, Kiev - and Helsinki, at the request of the “tram thirtieth number”. It happens even worse: within the same Moscow, you can find bus number 1 from Zelenograd, bus number 1 from Korolev and a couple more of the first buses! You need to have a special field for each id, which would tell you where this or that bus comes from - something like “the city where the route is assigned / approved” (semi-auto - select from the list or click on the map with the google API address). Figuratively speaking, the field can be called the “home port”.
To make my thought easier to understand, example:
- there are routes with the same number in Nerezinovaya: 1 (Korolev); 1c; 1 (Zelenograd); 1 (Khimki). For the first route in this tricky field we will write down “Korolev”, for the second - “Moscow”, for the 3rd and 4th - “Zelenograd” and “Khimki”, respectively. When a user searches for a road from point A to point B, instead of answering “bus 1”, we will submit “bus 1 (Korolev)”, even if A and B are entirely in Moscow. Of course, if the “from” and “to” points of the search are both in the “city of registry”, then this field can be not displayed on the screen. Among other uses, this field is the basis for cataloging and a means of combating the same routes in the same city.
Another example: bus 70 for the city of Sevastopol is suburban, it passes a considerable part of Sevastopol, then travels around the suburbs and turns out to be in Foros. If someone searches for a route within Foros, or from Foros, and this bus number 70 falls on his issue, he will see “70 (Sevastopol)” and its full route, which is unlikely to interfere with the user, or rather will help him. (I am exaggerating: a user from Foros can at one point receive an issue from two seventieth routes, and here the display form "70 (Sevastopol)"; "70 (Yalta)" will simply save him.)
6. Next, I will provide a list of meta-data that I found necessary, and which relate to the entire route at once. Immediately and comments to them.
Those fields that would be worth filling out with minimal user freedom will be noted in brackets (auto) or (semi-auto) - the latter provides for a choice from the list.
Metadata that should be attributed to the entire route at once:
- “home city” - see 5.
- type of transport (semi-auto - select from the list) - one drop-down list. Convenience of users for the sake of it can be divided into categories, for example: Rail (subway, tram, city train, funicular, monorail and other exotic), Water (ferry or river tram, by the way, greetings from Sevastopol), Ground (bus, trolleybus, route Taxi).
- route number - in principle, this is a text field. Because: "422d"; bus at the time of the cancellation of the tram "3-tr"; or even Aeroexpress or SkyBus. But it is worth limiting its length, or something like that! Otherwise, the database (as well as the output of routes for a single large city) will be cluttered with the long, unnumbered names of suburban and intercity routes (“Orekhovo-Zuyevo - Spasokokukotsko-Narochnenskoe”) and the “catalog” of routes of a single megapolis cannot be viewed without a moan of horror . And in general, I personally adhere to the belief that such routes should be served by bus stations completely, including drawing their tracks on the map.
- carrier - if possible, the full (with the form of ownership) the name of the legal person-carrier. By the way, here the use of tags is also more than appropriate, even to the point of indicating not only the tag jur. faces of the carrier, but also of the ATP / motorcade / depot serving the route (relevant and very interesting for megalopolises with public utilities).
- fare - the route is often fixed and does not depend on the route - but only on the carrier / route number. But sometimes it does not, for example, when the return route is due to, say, one-way traffic on the highway, it turns out so much longer that the private carrier exposes a different price. This is rare, so the cost is global for the route field. Auto substitution of currency based on the “home city” is, of course, beautiful. But you need to warn users that when creating routes where the cost depends on the distance / zone, they enter a minimum-maximum in the field and / or leave comments about it.
- general features of the route. - for example, “the route is served only by the newest buses MAZ-107”, or “turnstiles are installed on the buses of the route”.
7. Leave the metadata for a while and work on the routes of the routes. One route, as we have already decided, must correspond to one flight% of the bus% one way. In principle, the accuracy with which the Google map gives us the coordinates of the same Google, when we put a dot on it, at the present time is completely commensurate with the GPS positioning accuracy (although it is slightly worse - it is a few meters, and the same approximate error , alas, shows a combination of maps from different sources). It is worth using such blessings of civilization and when drawing a new route - draw it with the most accessible detail, with all the subtleties of passing the interchanges and the side of the street. There is no huge problem if there is no such detail, but it is worth striving for it. In addition to the importance of this for data normalization (there is nothing better than recording a track without jokes, exactly as it actually goes) - sometimes it’s interesting where% bus% turns at this interchange, and, accordingly, how to get to it stop! In addition, great reassures, hitting a foreign city, to navigate at least in something thoroughly.
8. According to the data storage model chosen by us, the routes of the “there” and “back” routes should be stored close to each other, although they should be treated as independent objects (routes). Routes of routes that undergo periodic changes (for example, on weekends), as well as routes of routes that vary from flight to flight (every first bus goes to A, and every second one to B) are distributed to different id. This helps to simplify the search and creation of pairs of "round-trip" routes for routes. Again, can we hook on some kind of parameter on the track like a “circular route? Yes / no or “one way route?” And thus inform the DBMS that we will not process a couple of routes using this id, but only one.
9. Route route - a set of points and lines that connect them. Logical and economical way to store tracks - a sequence of points. On each track there are at least two special points, they are also - two stops - the initial and final. It may be advisable to store their names, and at the same time the remaining stops, along with the track in the list of coordinates. Then they are easy to process: if there is a name in front of a pair of coordinates, it means that it is a stop, and you need to select it when drawing, and when you hover the cursor - show its name. If there is no name, this is the usual point of the path, and you don’t need to select it, but simply draw the trace lines through it. In order not to interfere with numbers and text, you can simply put some label, for example, the sequence number of the stop on the track in the metadata. At the very least, to make the same point as the stop, from which the entire route is built, seems to me very logical.
You can develop the idea of storing data about waypoints and stops: the first stop can be assigned the number 1, and the last one - say, 0, or 9999. For example, route 33 of Kiev, Ukraine:
50.46611, 30.64351, 1 // 50.46414, 30.63630, // 50.46494, 30.63596, 2 // 50.46825, 30.63379, 3 50.47176, 30.63141, {…} // n- 50.48083, 30.62724, 50.48251, 30.63317, 0 // This gives another advantage - using the button “Draw a reverse route” you can immediately fill in the initial (with the same name as the former final) and final (on the contrary) stops.
A function for the distant future is taking into account multi-route stops, when one stop serves several routes. It seems that I clicked on such a stop - and the pop-up hint "stop here: ...". Implement when drawing, for example, as “magnet” with existing stops of the drawing route.
It is also worth notifying all users that the stops need to be added either only the initial and final, or all at once, otherwise confusions are possible.
10. Now about geolocation and city tags. Suppose a user has successfully drawn a route and clicked the “save route route” button. Now we can feed the drawn stops on the Google maps API and analyze the answers to determine if these bus stop points belong to cities. Moreover, even if the user creating the route has not yet taken a halt, there are already two — the initial and the final — by definition!
Based on the answer, we automatically create cross-links of the route to these cities (and if at least one does not exist yet, we immediately create it in the base of cities), and cities to this route (as it passes in this city!). This will be our tag cloud.
Immediately there are ample opportunities for “protection from a fool” - if none of the cities turned out to be a “home city” from the global for id field of the database record - most likely, an error has crept in here.
Now the “catalog of routes” for a city will show all routes in which the tag with the name of this city is found.
11. Metadata that should be attributed to a specific track:
- a cloud of tags of the cities through which the route follows - see 10 and 3 ++ - why exactly to a specific route / pair of routes.
- list of names of stops - in the minimum case you need to fill at least two. Therefore, this filling should be done immediately when adding a route to the database. The first and last stops can be removed by the DBMS every time when accessing the highway and displayed in the necessary places, separately from the full list or with it - or used when building the reverse route.
- movement interval - a simple text field for comments in the free form. Perhaps, if someday the encyclopedia will count the trip time, and even a plausible one, it will be necessary to bring this field to a standard form for calculating the average waiting time - many% of buses% go at peak hours with a completely different regularity than at a different time.
- the length of the route on the map (autosource).
- weekly schedule - according to the scheme chosen by us, if the route changes the route, for example, for the weekend, a separate id is created under it. Therefore, when linking to an id of a specific route / pair of tracks, the “weekly schedule” field is filled only in the meantime, on which% buses% of the route go along this route / pair of tracks!
- work time is similar to the previous item.
- the range of motion is similar to the previous two.
12. In essence, the database seems to be everything. Now you can devote a little time to things that I would not seem to touch - the interface for adding a record to the database.
How should it be
User: I’ll add bus 11 between Zelenograd and Moscow! "New route"
Vicky: - Enter the city to which the route should be tied; recommendations for choosing the city of binding here: (link).
User: read the recommendations. The campaign should be like this: "Zelenograd".
Vicki: - Draw a circular route or round-trip with the end?
User: I put the radio baton in the "round-trip".
Vicki: - Great! Enter the name of the initial stop in this field and click “start drawing”.
User: approx. "Northern". I press the button.
Wiki: - Click on the map where the initial stop is located. After that, follow the route of the route, not very often zhmykaya where the route passes, in sequential order.
User: approx. I squeeze a couple of places on the map.
Vicki: - Here is a button to "finish the drawing." How to cope, click it and be sure to enter the name of the final stop!
User: shook a couple more places, brought the track closer to the final one. Zhmyaknul to the final place. Click "finish drawing." Oh, asks the name of the ending! "Filaretovskaya street".
Vicki: - Great. Give us a couple of minutes to help ... So! Our system found that the route passes through Zelenograd, Matushkino and Moscow. If everything is correct, click "save" or "go to drawing the reverse route."
User: what, in the stump-deck, Matushkino, I do not know any Matushkino, the bus does not stop there! I tick off Matushka, leave the rest and press “draw reverse”
Vicki: - Thank you very much, the route "there" has been preserved. Now we are drawing the route “back”, and to make it easier, I have already given you the route “there”, just changed direction. So now you move the waypoints as necessary, you can not draw everything from scratch. And you can draw.
User: oh! this case! Byrenko moved points. I shake "save."
Vicki: - Done, recorded. Now you can entertain yourself by contemplating the creations of your hands or fill out any additional information or stops before sending it to the moderation with the button “The route is ready!”. By the way, if you wish to escape somewhere from here, your sketch will not disappear, but will remain in the drafts.
User: filled in the information about the carrier and the fare. Until it comes down, someone else will finish it. "The route is ready!"
Vicki: - Well done. From this moment on, you are not the owner of this revision and are discussing its community. Free ...
Vicky: - Enter the city to which the route should be tied; recommendations for choosing the city of binding here: (link).
User: read the recommendations. The campaign should be like this: "Zelenograd".
Vicki: - Draw a circular route or round-trip with the end?
User: I put the radio baton in the "round-trip".
Vicki: - Great! Enter the name of the initial stop in this field and click “start drawing”.
User: approx. "Northern". I press the button.
Wiki: - Click on the map where the initial stop is located. After that, follow the route of the route, not very often zhmykaya where the route passes, in sequential order.
User: approx. I squeeze a couple of places on the map.
Vicki: - Here is a button to "finish the drawing." How to cope, click it and be sure to enter the name of the final stop!
User: shook a couple more places, brought the track closer to the final one. Zhmyaknul to the final place. Click "finish drawing." Oh, asks the name of the ending! "Filaretovskaya street".
Vicki: - Great. Give us a couple of minutes to help ... So! Our system found that the route passes through Zelenograd, Matushkino and Moscow. If everything is correct, click "save" or "go to drawing the reverse route."
User: what, in the stump-deck, Matushkino, I do not know any Matushkino, the bus does not stop there! I tick off Matushka, leave the rest and press “draw reverse”
Vicki: - Thank you very much, the route "there" has been preserved. Now we are drawing the route “back”, and to make it easier, I have already given you the route “there”, just changed direction. So now you move the waypoints as necessary, you can not draw everything from scratch. And you can draw.
User: oh! this case! Byrenko moved points. I shake "save."
Vicki: - Done, recorded. Now you can entertain yourself by contemplating the creations of your hands or fill out any additional information or stops before sending it to the moderation with the button “The route is ready!”. By the way, if you wish to escape somewhere from here, your sketch will not disappear, but will remain in the drafts.
User: filled in the information about the carrier and the fare. Until it comes down, someone else will finish it. "The route is ready!"
Vicki: - Well done. From this moment on, you are not the owner of this revision and are discussing its community. Free ...
ps They say that the use of the “database without schema” architecture solves a lot of problems, since any necessary fields can be screwed up as necessary, using them practically without side effects.
Source: https://habr.com/ru/post/154399/
All Articles