Web Mapping Illustrated

Spatial Data Queries

One of the strengths of PostGIS is its ability to store spatial data in a mature, enterprise-level, relational database. Data is made available through standard SQL queries, making it readily available to do analysis with another program without having to import and export the data into proprietary data formats. Another key strength is its ability to use spatially aware PostGIS functions to perform the analysis within the database, still using SQL. The spatial data query examples in this section are the most basic possible. PostGIS boasts of a variety of operators and functions for managing, manipulating, and creating spatial data, including the ability to compute geometric buffers, unions, intersections, reprojecting between coordinate systems, and more. The basic examples shown here focus on loading and accessing the spatial data.

To see what data is in the countyp020 table, you can use any basic SQL query to request a listing. For example, this typical SQL query lists all data in the table, including the very lengthy geometry definitions. Running this query isn't recommended:

# SELECT * FROM countyp020;

Getting a list of over 6,000 counties and their geometries would hardly be useful. A more useful query might be something like Example 13-3 which limits and groups the results.

Example 13-3. A basic SELECT DISTINCT query to list the counties in New Mexico state

# SELECT DISTINCT county FROM countyp020 WHERE state = 'NM'; county --------------------------------- ...