Search⌘ K
AI Features
Home
Courses
The Art of PostgreSQL

Administrative Zoning

Explore how to normalize and query GeoNames data to handle administrative zoning in PostgreSQL. Understand the use of country, region, and district codes, and practice writing SQL queries with normalized data to produce meaningful geographic insights.

We'll cover the following...

Geographical breakdown

The raw data from the GeoNames website then offers an interesting geographical breakdown in the country_code, admin1_code, and admin2_code.

PostgreSQL
select geonameid, name, admin1_code, admin2_code
  from raw.geonames
 where country_code = 'FR'
 limit 5
offset 50;

To get an interesting result set, we select randomly from the data for France, where the code has to be expanded to be meaningful. With a USA-based dataset, we get state codes as admin1_code (e.g., IL for Illinois), and the necessity for normalized data might then be less visible.

Of course, never use offset in your application queries. Here, we’re making an interactive discovery of the data, so it’s found ...