Add geolocation information to the database

[cka-y]

Describe the problem

To enable reverse geolocation of GTFS feeds using the longitude and latitude coordinates from stops.txt, we need to have detailed location data available in our database.

Currently, we lack the necessary geographic information to associate a stop’s coordinates with countries, subdivisions (e.g., states, provinces), and lower administrative levels (e.g., municipalities, regions). This prevents us from identifying GTFS feeds with their respective locations.

Proposed solution

• Collect and store geographic boundaries for countries, subdivisions, and lower administrative levels using OpenStreetMap (OSM) or similar open datasets.
• Ensure the location data contains appropriate geometries (e.g., polygons) that allow for point-in-polygon operations to match stop coordinates with administrative areas.
• Design and implement a dedicated and scalable database structure to hold this geographic data and support reverse geolocation queries.
• Optionally, investigate complementary datasets like GeoNames or GADM to improve global coverage and accuracy when needed.