The Art of PostgreSQL/

...

/

Administrative Zoning

Administrative Zoning

Learn about geographical breakdown and data normalization

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.

Press + to interact
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 ...