Driving Log Version 3.1: Rental Cars and Rental Locations

I’ve been storing my driving log tracks in a GeoPackage file since version 3.0 in June 2023, and it’s generally worked well.

Driving tracks aren’t the only driving data I track, though. I’ve also kept an Excel spreadsheet of all of my rental cars, tracking who I rented them from, where I rented them and returned them, what kind of car it was, mileages, and other such data. In version 3.1, I’ve migrated my car rental data into the Driving Log itself.

Why Migrate Car Rental Data?

Using Excel worked for tracking my car rentals, but it wasn’t perfect:

• I needed to update two files with driving data: the driving log GeoPackage and the Excel file for rentals.

• It was difficult to distinguish unique rental locations, and keep track of where they were geographically located. While it was easy to record an airport code and agency name, sometimes airport locations moved (e.g. from a standalone location to a consolidated rental car facility) and I wanted to track those as separate locations. Non-airport locations changed names or permanently closed often enough that they were difficult to keep track of unless I stored their address alongside their name in Excel. Since I track both pickup and return, I often had to store the same address twice.

• Excel dates and times don’t allow including time zones, but my rentals occurred in many different time zones. I had been recording the pickup and return times in the local time for the pickup and return locations, but that made it difficult to compare times between rentals.¹ Additionally, I didn’t have the pickup and return times for some of my oldest rentals,² so these rentals only had dates without times mixed in the same column as date-times. Excel allowed it, but it was a nightmare to do anything with these dates and times programmatically.

Since the GeoPackage format is a SQLite database with a specific schema (i.e. a relational database), it supports multiple tables. Consolidating the rental car data into the driving log GeoPackage would allow me to have proper relational data without trying to keep primary keys synchronized between a database table and a spreadsheet. If I created separate tables for car rentals and rental locations, I could store a unique record for each rental location and link each car to them for both pickup and return. And the GeoPackage data types include DATETIME (TEXT in ISO-8601 format), so I could properly store times with time zone awareness.

GeoPackage Structure

Each layer in a GeoPackage is equivalent to a table in a SQLite database. I updated the GeoPackage to have the following structure:

driving_tracks (LineString)

As of version 3.0, my driving log already had a driving_tracks table. I added a rental_fid INTEGER column to allow joins to the new rentals table. Tracks that are not from rental cars may have a null rental_fid.

rental_locations (Point)

Each location that a rental car is picked up from and/or returned to will have an entry in the rental_locations table, stored as a Point. If a rental location keeps the same name but moves to a new address (e.g. moving from an off-airport location to an on-airport location), a new record should be created, and the discriminator text field should be used to distinguish between the two locations in labels (e.g. Off Airport, On Airport).

Each rental location should also have its IANA time zone specified in the time_zone field, to allow conversions of UTC datetimes in the rentals table into local times.

rentals (No Geometry)

The rentals table contains most of the data that had been in the Excel spreadsheet, with some updates.

Instead of having text columns for pickup and return location, the rentals table instead contains pickup_location_rental_id and dropoff_location_rental_id columns to allow joining with the rental_locations table. (Note that for most rentals, these two location IDs will be the same, since most cars are returned to the same location they were picked up from.)

Instead of using a single field to represent pickup dates (with or without times, and without timezones), I created a pickup_date_local DATE field (which contains a non-timezone-aware pickup date in the local time of the pickup agency) and a pickup_time_utc DATETIME field (which contains the UTC time the car was picked up). All entries should have a pickup_date_local, but entries with unknown pickup times may leave the pickup_time_utc field null. The same changes were made for return dates and times.

Note that both the rental_locations and rentals tables have agency fields. Generally these would match for any given rental, as a rental from a particular agency would generally be picked up from a location of that agency and returned to a location of that agency. However, I wanted to allow for the possibility of unusual circumstances where that might not be true.