Category: Thread

Today I completed the setup for reverse geocoding. The structure is built around two tables:adm – contains all GADM levels, but without their geometries.adm_geometries – holds all unique GADM geometries. The two tables are linked via a geometry hash. Original GADM properties are stored in the adm.metadata column as JSONB. The adm_geometries table also includes…

I analyzed geometry duplication across GADM levels and found over 9,000 duplicate geometries. This duplication often occurs in small countries, such as the Vatican, or in islands. To verify whether geometries were truly duplicated, I compared their hashes, point counts, and areas — an approach that should eliminate hash collisions. An unexpected benefit of this…

I started working on reverse geocoding endpoint. I decided I will store the geometries in a single table adm_geometries. This should help optimizing the st_within query across GADM levels. The id of a geometry will be calculated as md5 hash of its shape. However I encountered a problem – there are duplicated md5 hashes across…

I polished and refined docker compose files. Additionally I tested if “up” migrations work and intended and I added capability to run “down” migrations.

Horay! I finally deployed first version of my GADM api service. It is very rough at the moment. The deployment process is semi-automated. I can initialize new ubuntu VM, launch dockerized server and database, ingest GADM geopackage and run migrations with just few manual interventions. Some bash script that I wrote will help me to…

I stumbled upon another problem – my linode VM started throwing OOM errors during GADM geopackage ingestion. Running Postgres, ogr2ogr and docker simultaneously turned out to be too much for cheapest 5$/month, 1GB RAM VM. I was’t expecting this problem since I already successfully ingested GADM into a instance that was running directly on my…

I created a dedicated migration service consisting of sql migration files and small GO program that will run these migrations. I am making progress dockerizing the services: I am still jumping over a few hurdles related to deployment, migrations, seeding and overall automation but I feel like I am getting close.