Another tool to manage toll prices is available

[witoco]

In my country, toll prices are defined by type of vehicle, a time range, day of week, season, hollydays, special “long” weekends, and so on. Even though we were able to use the Waze Toll Prices System to create the JSON file and upload to the repository, that took a huge number of “duplicate” button presses for prices (and adjust parameters). But that’s only the top of the iceberg: prices are updated every year (or every 6 months in some routes)! I can’t imagine how much work it would require to use the Waze tool to update every rule.

A sample of complex prices tables is here…
[hide]

This one has rules for seasons and special weekends:
[/hide]

After the first route I created using the tolls tool, I decided to manage toll prices in a spreadsheet with all the rules that apply, and then I wrote a script that creates the JSON file ready to be uploaded to the Waze tool, tested in the playground and to be sent to production. Some of the JSON tolls files for my country were created using this tool.

With the mass update coming at the end of this year, I transferred the prices of the remaining toll routes for my country to the spreadsheet, so we are waiting for the new prices. In the meanwhile, some of the ones created by the Waze tool were recently updated using the ones from the spreadsheet.

So, if someone thinks that our tool could be useful for another country, or just give it a try, please follow up here for details or contact me directly (PM or email).

[CleitonCasarotto]

I want to try. Could you give me access to this tool?

[witoco]

This tool does not work like the one from the community of Portugal, so there is no website (yet). In this case, you need to run a command in your desktop to obtain the JSON files.

Let me explain how this works…

You have to fill a Google Spreadsheet with your tolls data. Segment data is obtained from WME using a custom plugin, and prices and schedules are obtained from other sources (highway operators websites). When you are ready, you must run a script that extract the info from the spreadsheet and generates a JSON file, which you can upload to the Waze tool and test it in the playground. If it’s OK, just save it as public and fill the form for the staff to add/update prices.

The spreadsheet has many sheets:

• Files: list of managed files. We decided to put all the tolls from a single provider in the same file.
• Tolls: list of toll groups per file. We defined to keep each route or highway in the same group, so usually there is only one toll group per file.
• Sections: list of sections per toll group. Currently it supports one segment per section on pay per section rule.
• Prices: list of prices per section. This is what I call a price plan.
• Hours: Describes all the valid times (multiple ranges) for a price.
• Dates: List of special dates or date ranges.

Prices, Times and Dates sheets are required only if there are multiple prices for a toll segment. Many columns are optional and some support special features.

There is a spreadsheet template available that you can copy. I kept some of my own records in the template as an example. There are 5 different cases to analyze and play with, but you can delete them at any moment.

Entry-Exit matrices are also supported and need to be included in separate sheets.

The relation between records from different sheets must be specified by custom IDs. You can use whatever you want, but I suggest to use some kind of mnemonics, in order to keep them unique. I left some comments with our ID format in column headers as a reminder (currently in Spanish). Also, tolls ID are used as the name of the sheets with an entry-exit prices matrix.

There are some conditional formating rules to simplify the identification of updatable data for the maintainance, for example, shading off formulas for repeating prices.

As noted, this “tool” has two scripts:

• WME plugin: Extracts data for a selected segment at WME, optionally with venue ID (this is no longer required - for the moment), and puts it in the clipboard, and then you have to paste it in the sections sheet.
• JSON generation script: This is a perl script that reads the spreadsheet and outputs the required JSON files.

A perl script runs natively on some systems, but if your workstation does not have a perl interpreter and you don’t want to install one, I can embed it into an EXE file.

Said that, are you ready for more?

[Hugo_Moreira]

Great work, witoco!!

[witoco]

To simplify the process, I decided to build a very simple web frontend for the JSON generation tool and published it in our website: https://www.wazechile.com/tolls/

As the template spreadheet has some examples, it is used by the “test” account, with “123” password. Please do no change any config parameter except for the last one, which is the filename of the JSON file you want to create.

There are 5 files defined in the spreadsheet (links in names will show you their prices):

• CL_CN defines the tolls for Costanera Norte freeway, which has 3 different prices for every free-flow portal at many ranges of time during the day in its two axes, and each of them is assigned to a different “toll” group in the JSON.
• CL_AMB has the tolls of our international airport, with different prices for manual and free-flow (tag).
• CL_VM_G60 is Route G-60 (Variante Melipilla) toll, which depends on the direction of the flow through the toll booth and the destination you would take in the connecting highway, with special toll prices based on days and times of that other highway, including “long” weekends.
• CL_LC Litoral Central is a set of 4 different routes that defines “special weekends” (combination of a normal weekend with a hollyday on Thursday, Friday, Monday and/or Tuesday). Each route is assigned its own “toll” group in the JSON file.
• CL_IP Autopista Interportuaria is an entry-exit configuration like a “Y”. The matrix is defined in a sheet named as CL-IP, which is the Toll ID defined for that highway.

You can generate a JSON file, save it locally and open it to check its contents. They are in pretty-print format to be easily read. If they are loaded in the Waze toll tool, you could see how much work you could save.

If someone wants a personal account, just PM me… then, you can provide your own spreadsheet (a modified copy of the template) and use your own parameters.

Please note that this is a work in progress… It looks ugly, but works. Please report me any issue and I’ll fix it ASAP. Suggestions are welcome!

[witoco]

The target for the previous post was to show how JSON files are created from spreadsheet data, but if you want to try with your own tolls, here is more info to help you…

The most important data is stored in the “Sections” sheet. To populate it with your all your segments info, you don’t need to type all that numbers for lon and lat coordinates, segment and node Ids, url… because I published a WME plugin to pick the fields for every record at once. The plugin is called WME Toll Info Capture.

The first three columns must be typed in the following way:

• TollID (A): Assign a unique toll Id for a group of sections of a highway. Use real route number or highway codes to keep them unique.
• SectionID (B): Each section requires its own Id. Assign something that represents the section, in order to identify the right price from the highway tolls publication. Currently, only one segment per section is allowed for the “pay_per_segment” rule. Keep route number or code to avoid duplicates if you add many files in the same spreadsheet.
• PriceID (C): Here you may assign a single price value for that segment, or write an Id of a group of prices, which must be registered in the “Prices” sheet. This column is not needed for the “entry_exit_price” rule.
  The remaining columns are for segment information. To capture each record with the WME plugin, just select a segment and press the tool button at the top of the left panel, then go to the spreadsheet and paste the record(s) in the “Name” (D) column, then edit the name to match with the prices listing from the highway. If you want to include the venue of the corresponding toll booth (not required), just select it first and use the tool to capture it ID, which it will be appended to every following segment processed. More instructions about its use in the plugin’s page.

For each toll Id defined, you must add a record to describe that Id in the “Tolls” sheet, but you might group tolls in a single file by assigning a common File Id for them.

The file name for every toll group is assigned in the “Files” sheet. This sheet may be used to keep files update information.

I’ll explain how price tables work in another post… It will include “Prices”, “Hours” and/or “Dates” sheets. The magic for complex prices plans happens in them. There are some helpfull features available.

In the meanwhile, if you want to provide your own copy of the spreadsheet, you must provide the Google Doc spreadsheet’s URL, which is NOT the one from the address bar. You must “publish” the spreadsheet: Go to File -> Publish to the web -> Entire document / OpenDocument spreadsheet (.ods) and then copy the link and paste in the first field of the tool.

[witoco]

Here I’ll explain how to set up a prices plan for a toll.

As I said, the most simple plan is a fixed price, which is given as a single value in the “Sections” sheet. But you can also supply it through the “Prices” sheet, by adding a record with the price and an Id, which must be copied to the section in the “Sections” sheet. The advantage of using this method is that it can be specified different prices for the same segment if the same Id is used in many records, and assign some conditions to each price for a segment. So you can have one price for a car and another for a motorcicle, or one for weekdays and another for weekends, or by time ranges, or by a mix of them!!!

Most conditional parameters can be omitted and, therefore, the default values apply (some of them are defined in the tools interface).

The columns in “Prices” sheet are:

• PriceID (A) is a label for a price that can be repeated to represent each feasible price for a segment (or group of them).

• Permit (B) requires a number of a permit (pass) for which is valid the price, or empty for none. Only one permit per record. The real permit id is taken from the list in the configuration.

• Type (C) represents the type of vehicles. It is required a comma separated list of numbers that represents a type or group of them, which are also defined in the config. Currently, the default is 1 for “PRIVATE/TAXI/EV” for car and 2 for “MOTORCYCLE”.

• Price (D) is the toll price for that rule. In order to simplify the price maintenance, you can assign a price in row 10 and use a cell reference (=D10) in the following rows. Rows with references change the font color to gray, so you can remember which are the only cells that must be updated on a price change.

• Days (E) is a list of valid days of the week for a price. 1 to 6 for Monday to Saturday, and Sunday is both 0 and 7. You can use comma or dashes in a way that “1,2,3,4,5” is the same than “1-5”. If this field is left blank, it means that the price is valid for any day.

• Times (F) contains a range of valid times in 24 hours format. An empty cell means “00:00-23:59” (the whole day). Instead of a time range you can specify a TimeID, and assign many ranges in the “Hours” sheet.

• DateFrom (G) in the “YYYY-MM-DD” format defines an initial date for a range of dates. Instead of a date, you might specify a DateID, and assign many date ranges for that price in the “Dates” sheet.

• DateTo (H) is the last date of a range. If it is omitted, defaults to the same date from the previous column.
  If a DateID is selected, it is also possible to apply a range offset in the following way. Imagine you have a DateID called “HOLLYDAY” that specifies some 3 days ranges through the year, then:

• HOLLYDAY(0) is the day before of every range.

• HOLLYDAY(1) is the first day of every range.

• HOLLYDAY(-1) is the last day of every range.

• HOLLYDAY(1,-1) is every whole range (same as just HOLLYDAY).

• HOLLYDAY(2,-2) is every range without the corresponding first and last days.
  Using this, it is possible to select a different price for each portion of a range without having to define each date separately.

“Hours” sheet can be used to define multiple time ranges for a price. Use a TimeID code that could be easily associated to a plan for a toll. Times in TimeRange column are accepted in 24 hours format like in “12:00-14:59” or in minutes like in “720,899”.

Finally, in “Dates” sheet you can add many special dates or dates ranges that could be used to filter prices. They can be the hollydays for the current and following years, a season or whatever. Use only one date column for a single day.

[witoco]

I added two more files to the spreadsheet as an experiment and for debugging purposes.

They are two different approaches for the somehow complex underground highway called Duplex A86 in France, as it was explained in another post: the original one using an Entry-Exit rule and another for a sections hack, both using the same set of prices.

[witoco]

All those that already have an account in this tool might notice a small improvement in the frontend I did today.
Please tell me if something was broken. :mrgreen:

[BellVillense]

Hello everyone!

So I want to publicly thank witoco for all his help.
The ones who are tech-savvy (aka Carlos) tell me the toll is simply amazing!
At the Mexican community we are feeling the pressure of an upcoming deadline to launch the feature and it seems that with this tool it will be possible to be ready on time.

I want to publicly THANK witoco for his effort, humbleness and for always being ready to help!
This is the true spirit of the Waze Community!