Brilliant devs of devrant, I come to you for wisdom. How are locations typically stored in a database, is it just latitude and longitude along with a text field denoting the name of the location?

That depends 🤷‍♂️
What do you want to store? Points? Go for lat long. Another way is geohashing
Polygons, I.e. areas? GeoJSON is what I used in a side project

Oh and it can become hella confusing when you want to do it across a large area accurately, since you’ll leave the Euclidean domain and have to start taking the round shape of the earth into account

If you just need to store the data and grab everything at the same time you could just store it as a numeric type. If you want any form of querying - and I mean any form - use something like what @lungdart mentioned. Querying geospatial is practically impossible using traditional SQL due to the curvature of earth and the coordinate system we use for lat/lon as @Chewbanacas mentioned.

Thinking of it there may be some alternative coordinate system that suits itself better for a specific type of query. So if you know your use case (which you hopefully do if you're building) you might be able to get away with not using a DB extension.

Stuff you don't calculate with just as string

If you have the hardware, spin up Nominatim and Overpass. Nominatim for (reverse) geocoding, Overpass for areas. Solves the location problem pretty quickly. You store the addresses in the database and get the latest longs from Nominatim