Sunday, May 10. 2009
As many people may have heard and as Mateusz kindly already commented on; Leo and I already mentioned in our Postgres OnLine Journal site, we are writing a book specifically focused on everything PostGIS. The hard copy version is do out around January of 2010, but you can pre-buy now and view the draft chapters as we write them. We are currently working on chapter 4 and the helper appendices and will try to get out more content before the end of May. Of course the more people pre-buy the more encouraged we will be to write quickly. You can download the first chapter for free from Manning: PostGIS in Action
As Brent Wood kindly commented on our Author Blog -- where is the primer on SQL and writing functions? before he quickly observed we have it planned for the appendix. Originally we stupidly had that as part of the meat of the book, but as our editor, Sebastian, and Marjan Bace rightfully commented, such things would bore people to death who are familiar with these things. So we are stuffing these in the appendix where you can refer to them, as we start doing wacky things you have never seen before done or envisioned relational databases can do, and where they don't interrupt the flow of the book and frustrate people already intimate with databases who want to learn about spatial.
Our book has now reached pre-book sales and nicely we've already got some purchases. You too can buy a copy (whether just MEAP/eBook or MEAP/eBook/hardcopy, start reading, make comments and thus help guide the direction of the final book. We look forward to getting feedback from people and hope this will be a valuable resource for those using PostGIS and future PostGIS users as well as encouraging adoption of PostGIS.
Below is more or less a repeat of what we posted to the PostGIS newsgroups with some minor edits:
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.
Below are the following chapters we have planned (or done)
Appendix A: Additional Resources
- What spatial databases do in general, which ones exist, and OGC
- 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.
- 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.
- 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.
- Deals mostly with relation operations and functions that take more than
one geometry as input (pretty much a companion to chapter 4)
- 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.
- 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.
- 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
- 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.
- PostGIS add-ons and ancillary tools -- planned here are Tiger Geocoder,
PgRouting, PLPython, PLR
- Using PostGIS in web applications -- planned here UMN Mapserver,
GeoServer, SharpMap, OpenLayers, (maybe Web Feature Server)
- Using PostGIS in desktop environment -- here we plan to do some quick
exercises using -- OpenJump, QuantumGIS, uDig, GvSig
- 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.
Appendix B: Compilation, Installation, Upgrade
Appendix C: SQL Primer
- 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
Appendix D: PostgreSQL Specific Features
- 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
- 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.