Distance to Garbage Containers

This tutorial has the following aims:

1. Turn a business question into smaller data-analysis tasks.
2. Use a sql database to store and handle data from csv files.
3. Represent geographical data on maps.
4. Use heatmaps.
5. Use the OSRM api to compute shortest paths.
6. Use visualizations as diagnostic tools and as final illustrations.
7. Learn to distrust final statistics until intermediate results, edge cases, and outliers have been checked.

Remember: the largest part of data science is not concerned with developing the hottest machine learning techniques and tuning super large neural networks. The largest part is concerned with obtaining an understanding of the data (so that you can clean it, check it, and trust it), communicating visually about data and the information you can derive from it, and struggling with databases and other methods to handle large amounts of data.

In a project for the municipality of Groningen, we (=NvF, plus colleagues) were involved in finding better routes to empty the garbage containers in the city. Better routes are not only shorter, but also respect the drivers’ working hours and breaks. In our discussions with the municipality, one of our contacts said that sometimes people complain about the distance they have to walk with their garbage bag from their house address to the container. In this tutorial we analyze whether such complaints are grounded.

In this tutorial we coach you through the process by means of fairly detailed steps and questions. We expect you to use a GPT tool to help you figure out what to do technically, write code, and visualize data by means of graphs and/or html files. But it is on you to learn, gradually, to ask good questions, chop up the problem in steps, develop ideas on how to test data quality, and report valid findings. You should realize that, while here we help you chop up a large problem into small steps and then combine that into conclusions, soon your boss will pay you to do this in your own job. So, use these tutorials to develop that skill.

You are expected to use a GPT of your liking to (help) write code.¹¹Without a GPT it is too much work. However, there are a few points to watch out for.

1. GPTs make mistakes. Therefore you have to develop tests (and/or ask the GPT to build them) to check the correctness of the output.
2. GPTs might not produce the most efficient or accurate solutions. On small problem instances this does not matter, but on larger problems it does. So, you should ask it to write different programs for the same task, and compare the timings.
3. GPTs might not fully understand the broader context of a project, leading to solutions that don’t align with the specific requirements or nuances of the user’s code base or system. Hence, you should be the architect of the project, which means to chop up a large task into many small tasks (for this you can discuss with the GPT), then ask the GPT to build small (or larger) parts of the code.

In summary: you have to be the boss of the GPT, not the other way round.

The first important step is to get the data and make it accessible for analysis. This we do by writing the data into a database.

1. On brightspace you can find two csv files: Containers_Huisvuil.csv and addresses_cluster_codes.csv. Read a few lines so that you understand what type of information they contain.
2. Make a sqlite database (ask your GPT how to do this) with two tables, one for the addresses, the other for the containers.
3. Find a program to view the data in the SQLite database. I use sqlitebrowser on Linux. Ask your GPT what viewing tool you can use for your OS, and how to install it (or perhaps even ask it to install it for you).
4. Which table contains the locations of the containers?
5. Check the contents of that table. Do you understand the format and type of the data?
6. How many container locations does the table contain? Is this the same as in the related csv file?
7. How many house or building addresses are there in the database? Is the import from csv to sql correct? No houses missing? Do the street names look correct?²²Recall: simple checks are necessary.
8. Which column links the two tables? Check that every code in the address table occurs in the container table, and the other way round. How many codes do not match? All later steps depend on this join, so do not skip this check.

To understand and check the data we need to visualize it. Here are few interesting steps.

1. Find out how to ask the database about the id of the container to which you are supposed to bring your garbage bags (assuming you live in Groningen, if not, take an arbitrary house or a house of one of your colleagues).
2. How many containers does the Damsterdiep have?
3. What streets are served by containers on the Damsterdiep?
4. Are all houses on the Damsterdiep assigned to a container on the Damsterdiep, or perhaps to other streets?
5. Not all houses have a container assigned. Take a few (5 or so) of the houses for which this is the case. Look up the addresses for these 5 houses, and go to Google streetview to check why this might be the case. Is it reasonable that these houses do not have a container? Are there errors in the data?

1. Plot the locations of the containers to an HTML page. Make markers and add tooltips that show the id of the container. Here is my prompt: “I want to make an HTML page with markers that indicate the locations of the containers and a tooltip that shows the container’s id when I hover over the container. Write your code to the code subdirectory, because I do not want my directory to turn into one big mess. The HTML page should be written to an HTML directory for the same reason.”³³From now on, I expect you to engineer your prompts.
2. Open the HTML page in your browser.
3. Expand the HTML with popups. What is the difference between a popup and a tooltip? If your script overwrites the same HTML file, reload the page in your browser to see the change.
4. Let the GPT adjust the font size so that also old people can read the container ids.⁴⁴That is, not too small.
5. Use the HTML to check the id of the container nearest to your house.⁵⁵And when you are at home, check the id.
6. Make a heatmap of the containers. Is this easier to interpret than the first page we made? Can you think of other data science projects for which heatmaps could be useful?
7. Check one or two plotted locations against OpenStreetMap or Google Street View. Are the markers in plausible places?

8. Change the tile layer. Here are some interesting options:

   • Esri.WorldImagery,
   • Esri.WorldStreetMap,
   • Esri.WorldTopoMap.
   • OpenTopoMap.

   There are of course many other tile layers.

9. Show your results to your family and friends, so they can see what cool maps you can make with a GPT.

1. Go to https://wandelvogel.nl. Click on your home, and on the container nearest to your house. What is the reported distance? (If you like, you can click on some other points on the map to make an interesting detour, for instance via Assen.)
2. As a next visualization test, plot the shortest path between the containers with id 1461-586 and 1911-945. The site https://distances.wandelvogel.nl exposes an OSRM HTTP API endpoint that you can use for path computations. This address does not work by itself. You should send something like: https://distances.wandelvogel.nl/table/v1/foot/6.591711,53.194383;6.5919,53.19442?sources=0&destinations=1&annotations=distance,duration. Ask your GPT how to get this to work and make a plot of the route.
3. You now have a single number: your own walking distance. Is your own experience representative of the city? And if you computed the distances for the whole Damsterdiep, would that one street tell you about Groningen as a whole? Keep your number in mind: in the next section we compute the distances for all houses, so you can check afterwards whether your case is typical or an outlier.

Now we want to compute, for each house, the distance to its container, and from these the average and the maximum over all houses.

1. The file distances.csv on brightspace has one row per address with the walking distance in meters to the container of its cluster. Add these distances to your database, joining on the address. To check that you understand the file, compute the distance for a handful of houses yourself with the OSRM service at distances.wandelvogel.nl, and compare. The file covers fewer addresses than the address table: which addresses are missing, and does that explain the difference?
2. Once the distances are added, make a list with statistics you would want to use to understand whether some (or many) houses are indeed too far from the container. Visualize the statistics. Do the numbers make sense?
3. Make an HTML file with markers for the 20 addresses that have the largest distances. Twenty addresses is few enough to look up their coordinates one by one, for instance with a geocoding service such as Nominatim. Have a look at this file, then check with Google Street View whether you can understand why these houses lie far away. Is it reasonable? Inspect the largest distances carefully. Large outliers often reveal mistakes in the data, the coordinate system, or the assignment rule.
4. Use your imagination and common sense to develop a filter that keeps out the strange addresses (not just these 20, but all ’strange’ houses), so that they don’t pollute your statistics.
5. Recompute the stats, but now from the cleaned list. How many addresses did your filter remove, and what fraction of the total is that? A filter that throws away a large part of the data needs a better justification than one that removes a handful of houses.

Now return to the question we started with. Note carefully: as a consultant, it is not your job to decide whether the complaints are justified or what the municipality should do. Your job is to provide all the information needed for a good discussion and to support their decision making. So summarize your findings in a way that a municipal official, who is not a data scientist, can understand and act on. For instance:

1. What fraction of houses lies more than, say, 150 m from its container?
2. Are the large distances a few unlucky outliers, or a real and widespread problem?
3. Where are the problem areas?

Present the numbers and the maps clearly, state your assumptions, and be explicit about the scope: your analysis describes the assignment of houses to containers as it currently is. In particular, the distances are measured to the container a house is assigned to, which need not be the container nearest to that house; you saw this yourself when you looked up both.

Whether a different assignment of houses to containers, or extra containers, would help is a separate question. We don’t address that in this tutorial.