2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / Greg's PlanetPostgreSQL3 / PostGIS In Action
2ndQuadrant Press

PostGIS In Action

April 29, 2011/0 Comments/in Greg's PlanetPostgreSQL, PostgreSQL /by 2ndQuadrant Press

I doubt many people can tell you exactly when the first time they read a map was.  Mine was memorable though.  Circa 3rd grade, I went through the usual battery of standardized tests for the first time, which included map reading.  I did pretty bad, which was odd because it was the only section I bombed like that.  Concerned that perhaps I had some sort of learning problem related to spatial data or visualization, a guidance counselor reviewing my scores quizzed me about that section and what I thought of it.  Told her I thought it was pretty neat, and that I was looking forwarding to learning about these “maps” one day.  Turns out, due to a school change and differences in class order between schools, I had never been shown one before the exam.  For someone who had to deduce what the symbols meant during the test, suddenly my scores didn’t look too bad.

It’s easy to feel like a completely disoriented newbie to spatial information when trying to learn how to use PostGIS, the popular PostgreSQL extension adding support for all sorts of map related features.  Geographic information systems (GIS) are filled with their own special terminology and techniques.  To help navigate this maze (literally sometimes!), Regina Obe and Leo Hsu have recently released PostGIS In Action,  a whopping 492 pages of nothing but information on this specialized topic.

The book aims to be a comprehensive resources for three groups:  GIS practitioners, database practitioners, and scientists/researchers/etc.  To the extent that it’s possible to do so, the material in the book tries to write from each of these perspectives.  So you get an introduction go GIS terminology, an introduction to SQL, and an introduction to installing the software and making everything fit together.  Not every section will be useful to every type of reader, but there are enough handy tips sprinkled around every section that you might pick up a useful trick even on material you know well already.  For example, in the performance tuning section that I mainly breezed through, I did pick up some useful windowing function and Common Table Expression ideas, ones that are even useful beyond the GIS context.

I like to kick off working with new technology by picking a real-world project and seeing how far I can get with it.  I’d tried this with PostGIS once before, about a year ago, and failed miserably.  The project involves a long list of addresses that I wanted to transform into spatial data, then analyze using spatial queries.  The process of turning addresses into coordinates, called geocoding, can be done for the US using a public data set named TIGER.  During that earlier attempt, I couldn’t make any sense out of which versions of each component I needed to get that working though, and gave up on the whole thing.  Reading through that section of PostGIS In Action, I felt a little better.  It wasn’t that I was confused about the complexity–it really is that much of a pain to figure out!  Quote from the book:

The TIGER geocoder packaged with PostGIS 1.5 and below doesn’t handle the new U.S. Census data ESRI shapefile format.  For those, therefore, we’re using a newer version currently under development by Stephen Frost…for our exercises we’ve taken [this] newer version and made some minor corrections to support the TIGER Census 2009 data.

This sort of thing is where the book is at its best.  Advice about which versions of which software work together, and helper scripts unique to the book to aid in some of the complicated parts, can skip you past days of frustrating work.  

The book mainly aims at PostgreSQL 8.4 and 9.0, but there’s material going back to 8.2 and some previews of coming features in 9.1.  While the server side of tools covered includes the most common PostgreSQL operating systems (Windows/Linux/Mac OS X), it’s obvious that Windows is the preferred platform for many of the client GIS tools.  Accordingly, it’s not a surprise that the recommendations for PostgreSQL are biased toward using the one-click installers, rather than getting dragged too deeply into the trivia of software building and installation.

But what PostGIS in Action does in many places is refer to web resources for things it skims over, which is commendable.  Even a book of this length can’t cover everything about every possible platform available, and having an author point out the best articles available is a helpful way to extend its reach.  From the sections I know enough about to comment on, the recommended additional reading were often articles I’d already read and found useful.  The main missing one was that the somewhat slim coverage of useful postgresql.conf settings to improve performance could have used a link to the Tuning Your PostgreSQL Server page, which covers some of the same material in more detail.  That wiki is one of the main additional resources suggested at the end though.

With all the specialized terminology and multiple skill sets required to work through this material, finding the right sequence to read this book in is challenging.  Putting things into the best order for learning the material is the area I think could be improved the most in a future edition of this title.  To pick a trivial example, but one that’s characteristic of what I saw in multiple places, the order of things in the “SQL primer” chapter was rather strange.  The first section covers how to use the information_schema to navigate column metadata.  How that section ended up at the very beginning, before even covering what SELECT means, I have no idea.  In a few of these cases I spotted, the information needed is all there, you just need to read it in a different order than it’s presented.  Readers may find it’s worth skimming the whole chapter to get an idea how it flows if things don’t seem to fit together easily.  Don’t be afraid to skip around if the info you need looks like it’s covered better in other sections.

My first pass through PostGIS In Action left me much more comfortable with the big picture of how applications built using these tools fit together.  And I expect to refer back to it for both its introduction to specific programs and its useful sample code.  Trying to be a complete reference for all of the targets this title aims at is very tough though.

GIS practitioners and scientists who don’t already have much SQL and/or database experience will likely need the most additional information beyond what this book covers, in order to become completely functional PostGIS users.  But intros to SQL are easy to find; discussions of GIS aimed at the database practitioner, what I’ve been looking for, are rare.  So far I’ve spent the most time with the terminology introduction in the first two chapters, plus the TIGER use information I mentioned.  And I already feel like my copy of PostGIS In Action was a worthwhile purchase.  It’s great to finally have a full size book on this very important PostgreSQL-based technology.

Tags: postgresql postgis
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
PostgreSQL agent in SQL Standard committee again EuroPython 2011: “The Python and the Elephant”
Scroll to top
×