Should I create a separate table for geo-location points?

I am developing the backend for a service that resembles Uber in some aspects (just a simplification to give you the general context). Users will book rides and as such users will define the pickup and drop-off locations. These locations will be addresses in a city (with the possibility of multiple cities in the future). The locations I will receive from the frontend/app will be in the form of latitude, longitude, and google-maps-verified address.

I am wondering whether I should store the locations as a separate table, or whether I should embed the locations in whatever table I need them. For example, the bookings table will have a pickup location and a dropoff location. Should these be just references to a locations table, or should I add more columns to store the location right there? I will need at least 3 columns per location (lat, long, address string), with the possibility to have more columns if we decide to break the street address into more components.

The way I see it the two approaches trade-off memory and time performance:

• A separate table will save some space, as we avoid copying the same

information multiple times. But I am not sure how much space will be
saved as we do not have enough real data yet. My ballpark estimate is that a medium-sized city has tens of thousands unique addresses. So if we take 100 ride bookings there is little chance for overlap. If we have 100,000 ride bookings there is bound to be considerable overlap. However, space is not really a limiting factor in our design, even with millions of bookings. It does not really matter if we spend 100MB more in our database.

• A separate table will make processing slower. First of all, each time we get a booking request we will have to search if the locations in the request already exist in our database. If not, we add them, otherwise we use the existing reference. With potentially tens of thousands addresses in our database this will take some time. Is this extra time significant

General recommendation

I am in favor of this:


whether I should embed the locations in whatever table I need them

Do not create a separate table for storing geo-location points only. Geo location points doesn't really make sense themselves alone.

I would say keep the geo location points to the tables about events. Events such as pickup, drop-off, meet up, dine, and so on...

Albert Einstein once said it's about space-time. It's not about location only (space); also it's not about time only. It should be the binding of space and time. Consider these:

• Where will the user be picked up? and when?

• Where will the user be dropped off? and when?

There is always a question about when every time you talk about location. One good direction is to always think of it as an event.

Besides, would there be a part of your application where you would pin all the locations (pickups-and-drop-offs) on a single map and homogeneously as if they are all just locations? I think most likely, that will not be the case.

Instead, what you may want is to pin all the pickups, pin all the drop-offs, etc.

Again, for me, it would be better to store the locations with a mindset about events.

Specifically for your purpose, this would require you to hook your bookings table to pickups and dropoffs event tables. With this, you can have as many pickup locations and dropoff locations. Thus, extendable as what you've said.

Alternative

In favor of this:


whether I should store the locations as a separate table

Unless though if you use the What 3 Words (W3W) service/application. W3W split the world into 3-meter blocks. Each block then was given a unique ID. The ID is in format word1.word2.word3.

With w3w though, you don't need to store geo-location points on a separate table. They already did that for you. All you have to do is use their IDs. I'm not so sure though if 3-meter resolutions are enough for most applications.

Credentials

By the way, I work on staple crop research. We have lots of GIS applications and we also use PostgreSQL.

StackExchange Database Administrators Q#197056, answer score: 3