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.
Table "gadm.adm_geometries"
Column | Type | Collation | Nullable | Default
--------+-----------------------------+-----------+----------+---------
id | text | | not null |
geom | geometry(MultiPolygon,4326) | | not null |
Indexes:
"adm_geometries_pkey" PRIMARY KEY, btree (id)
"idx_adm_geometries_geom" gist (geom)However I encountered a problem – there are duplicated md5 hashes across GADM levels. This is unexpected! Does it mean that certain states fully overlap with their countries?
I will figure it out tomorrow … hopefully