New Book PostGIS in Action

The book will cover both basic concepts as well as advanced.

Things that will be covered (we have chapters 1,2,3 already written but nothing is set in stone until it hits hard-copy), so feel free to voice any ideas you have on our author blog.

1. What spatial databases do in general, which ones exist, and OGC terminology
2. What geometry types PostGIS supports and how well it supports each with some simple exercises of creating each type. Here we cover 2d, 3d(z), 3d(m), 4d(zm) basic geometry types and curved geometries and what the hell is M good for.
3. Data modeling -- This chapter starts by going over some basic storage models -- using schemas, do you use table inheritance, have untyped or specific typed geometry column, separate tables, separate columns, use srids or not and so forth as well as pros and cons of each. It then concludes with simple exercises demonstrating these different storage approaches.
4. Working with geometries -- this covers key geometry properties and concepts -- such as what a spatial reference system is, other key geometry properties and functions that take one geometry. It will conclude with business case uses for these.
5. Deals mostly with relation operations and functions that take more than one geometry as input (pretty much a companion to chapter 4)
6. This chapter covers combining what was learned in 4 and 5 to create more sophisticated queries. It will show things such as clipping geometries, splitting geometries with lines, combining spatial aggregates with sql aggregates, generating a farm of test data.
7. working with real data -- we go in depth into how to load data with shp2pgsql and OGR2OGR. Tricks for determining the spatial reference system of a data source when there is or is no meta data. Also exporting to various different data sources. What are the different classes of spatial reference systems exists and how to decide on the best spatial reference system(s) to store your data in.
8. Speeding up queries -- demonstrates how to write optimal SQL statements as well as somewhat hackish statements to force the PostgreSQL planner to follow a more optimal plan. How to use explain and graphical explain to determine where the bottlenecks in your queries are. Choosing the right indexes (spatial and btree, compound vs. non-compound). Key PostgreSQL settings
Part 2:
9. This covers specific use cases -- how to fix different kinds of invalid geometries, various proximity analysis/nearest neighbor exercises, using buffers, linear referencing. I think we have about 25 exercises planned out.
10. PostGIS add-ons and ancillary tools -- planned here are Tiger Geocoder, PgRouting, PLPython, PLR
11. Using PostGIS in web applications -- planned here UMN Mapserver, GeoServer, SharpMap, OpenLayers, (maybe Web Feature Server)
12. Using PostGIS in desktop environment -- here we plan to do some quick exercises using -- OpenJump, QuantumGIS, uDig, GvSig
13. WKT Raster -- new work going on here -- we'll cover difference between raster and vector data, installing WKT Raster, loading raster data, raster /vector overlays, analysis of color bands

We'll have listings of useful blogs and sites for more tips and tricks, places to get data, concepts etc.

• We'll provide resources where you can get binaries for various OSes for PostGIS or learn how to compile it if you are adventurous
• How to PostGIS enable a new db
• How to upgrade from one version to another (both soft and hard and the lazy hard)

• Basic SQL stuff that is pretty standard in most ANSI SQL relational databases Different join types (INNER, LEFT, RIGHT, FULL, EXCEPT, UNION [ALL], INTERSECT with cute diagrams of each), schemas
• Using Aggregates
• recursive queries, common table expressions (CTE), Windowing functions which are (new in PostgreSQL 8.4 but supported in the latest versions of other high-end dbs -- SQL Server, IBM, Oracle)
• Doing inserts, updates, cross table updates

• Things that are somewhat unique to PostgreSQL -- such as how to create custom aggregates, arrays as data types, rules, triggers, sql and plpgsql function primer
• User and security management -- this we were going to put in Appendix C, but since each database vendor does it vastly differently and PostgreSQL has one neat feature of supporting various kinds of authentication -- we though it best maybe to move to this section. We are still thinking about this.