Table Of Contents

Forget Linux, Forget Unix, Forget MacWhatever, forget Windows, and any other OS you may be using. Say hello to PostOS. PostOS is built on PostgreSQL technology and fits you like a glove.

The built-in planner watches you type (or stare in confusion) and creates a composite image of what kind of person you are and what behavior it should emulate. It's integrated monitor display and speakers are used to provide information as well as to watch and hear your every move. The built-in image recognition system (an extension to the PostGIS extension), can distinguish between members of your family and can impersonate them as well and change behavior accordingly.

If gesturing is your thing, not to worry, PostOS is a master at gesturing and can also gesture right back at you. You can signal commands from across the room, and have confirming winks to register your wish has been executed. PostOS is infinitely extensible, and many extensions are already available on PostOS PGXN repository.

Some extensions you might be interested in.

• bug_them_more_than_they_bug_you Will respond to phone messages such as sales calls and call them back every few hours with mindless chatter throwing in a few words they said in their last call.
• rock_a_baby Requires cradle cable. Babies are hard to fool, but Rock-A-baby with some expert training will learn to rock just as you do and make the same googly stupid impressions and noises you do at the baby. The baby won't be able to distinguish you from your PostOS system. WARNING: The baby might start to prefer the company of your PostOS system.
• what_should_I_wear Does deciding what garments to wear each day take up way too much of your time? No problem. This extension will decide for you. It will even check the weather to ensure you are appropriately dressed for the day.
• twit_4_me Like the idea of twittering, but don't have the patience for it. PostOS has you covered. It can even retwit and respond to comments from your friends, so they think you are engaged in their mindless banter.

As with all PostgreSQL extensions, PostOS extensions are simple to install. To install what_should_I_wear, simply do:

You can also have the extension automatically install, by standing naked in front of the display panel.

The 3rd MEAP update of PostGIS In Action, 2nd Edition will be going out very shortly to Early Action purchasers. Keep your eyes peeled. Lots of errata corrections in previous chapters and appendix, and one very VERY new chapter on Raster functions which took a ton of time to write, so hopefully it will be well received. Our progress on the chapters is listed on PostGIS In Action 2nd Edition Chapters and all the ones marked as completed you will find in the MEAP. The ones with paperclips have downloadable code and data which you can click on the paperclip to download.

Regarding Raster, the Raster Function chapter is just merely the tip. You'll see a lot more raster usage in upcoming Relating two or more spatial objects and Raster Processing chapter which we are still fleshing out.

We are immensely grateful to all the early action subscribers who have posted errata or general comments about what can be clarified or examples that don't work. General comments about what specific kinds of examples you'd like to see are also welcome. Your opinions really influence what we write and make for a better book.

We are going a little bit slower than expected for 2 main reasons:

• The difference between PostGIS 1 series (1.3-1.5) and PostGIS 2 series (2.0,upcoming 2.1) is huge and some of the old best practices and ways you do things are obsolete. One of which is installation and upgrade and more coverage of new types raster and topology types. So in short there is a ton more new content in this book than we had planned for.
• We want this book to cover PostGIS 2.1 (possibly 2.2), and also upcoming PostgreSQL 9.3, so we don't want to release before those are readily available. You'll already see examples of 2.1 in there and some mention of PostgresQL 9.3 features already available in 9.3 source.

PostGIS 2.1.0 beta2 is out. Details on what's new in it are in official news release: http://postgis.net/2013/05/11/postgis-2-1-0beta2. This is the first version of PostGIS to work with PostgreSQL 9.3, so if you are planning to experiment with PostgreSQL 9.3 coming out soon, use this one. Also check out the documentation in new ePUB offering format if you have an ereader and let us know how it looks. It seems to vary alot depending on what ePub reader used.

For windows users, we've got binary builds available compiled against PostgreSQL 9.3beta1 (and also available for 9.2 9x32,64) and 9.0,9.1 (x64). Details on windows PostGIS downloads page: http://postgis.net/windows_downloads. It does not yet have the new Advanced 3D offering (provided by SFCGAL https://github.com/Oslandia/SFCGAL), but we hope to have that compiled and packaged with the binaries before release time.

One of the things that really frustrated me about the KNN GIST distance box box centroid operators that came in PostgreSQL 9.1 and PostGIS 2.0 was the fact that one of the elements needed to be constant to take advantage of the index. In PostGIS speak, this meant you couldn't put it in the FROM clause and could only enjoy it in one of two ways.

The very constant

SELECT s.pid 
FROM ch10.land AS l
WHERE l.land_type = 'apartment'
ORDER BY ST_GeomFromText('POINT(234054 902182)',26986) <-> l.geom LIMIT 10;

The exploited correlated query constant

This approach forces a query for each row and each subquery in row references a constant geometry which is the current row thus is constant enough

SELECT l.pid AS reference_parcel 
  , (SELECT pid 
  FROM ch10.land As s 
    ORDER BY l.geom <#> s.geom LIMIT 1) As closest_parcel   
FROM ch10.land AS l
WHERE l.land_type = 'education';

And if you needed to get more than one row back, like what is the N closest (N > 1), it was some really ugly code you had to resort to if you wanted to use KNN gist. So ugly you'd be better off just sticking with ST_DWithin and guess an expansion to check.

Coming in PostgreSQL 9.3: LATERAL correlated query

But in 9.3 we have LATERAL which allows us to put our subquery back in the FROM clause and still utilize a KNN GIST index and adorn with as many additional computed columns as we want. It looks like this:

SELECT l.pid, s.pid As closest_apartment, s.dist_m
FROM ch10.land As l CROSS JOIN LATERAL 
(SELECT pid, ST_Distance(b.geom,l.geom) as dist_m 
  FROM ch10.land As b WHERE b.land_type = 'apartment'
   ORDER BY b.geom <-> l.geom LIMIT 10  ) As s 
   WHERE l.land_type = 'education'
ORDER BY l.pid, dist_m;

The explain plan demonstrates the spatial index is being used

Sort  (cost=15074.71..15081.26 rows=2620 width=24) (actual time=129.435..129.495 rows=2620 loops=1)
  Output: l.pid, b.pid, (st_distance(b.geom, l.geom))
  Sort Key: l.pid, (st_distance(b.geom, l.geom))
  Sort Method: quicksort  Memory: 301kB
  ->  Nested Loop  (cost=0.56..14925.95 rows=2620 width=24) (actual time=0.411..119.107 rows=2620 loops=1)
        Output: l.pid, b.pid, (st_distance(b.geom, l.geom))
        ->  Index Scan using idx_land_land_type on ch10.land l  (cost=0.29..178.63 rows=262 width=277) 
         (actual time=0.040..0.163 rows=262 loops=1)
              Output: l.pid, l.bldg_val, l.land_val, l.other_val, l.total_val, l.addr_num, l.full_str, l.land_type, l.units, l.geom
              Index Cond: ((l.land_type)::text = 'education'::text)
        ->  Limit  (cost=0.28..56.09 rows=10 width=277) (actual time=0.176..0.452 rows=10 loops=262)
              Output: b.pid, (st_distance(b.geom, l.geom)), ((b.geom <-> l.geom))
              ->  Index Scan using idx_land_geom_gist on ch10.land b  (cost=0.28..5882.65 rows=1054 width=277) 
              (actual time=0.174..0.449 rows=10 loops=262)
                    Output: b.pid, st_distance(b.geom, l.geom), (b.geom <-> l.geom)
                    Order By: (b.geom <-> l.geom)
                    Filter: ((b.land_type)::text = 'apartment'::text)
                    Rows Removed by Filter: 224
Total runtime: 129.687 ms

PostgreSQL 9.3 will be coming out in beta soon and with that, some who want to experiment with both PostGIS and PostgreSQL 9.3 have asked if they can use PostGIS 2.0. The answer is NO. A lot of major changes happened in PostgreSQL 9.3 that required us to patch up upcoming PostGIS 2.1. These changes were not backported to 2.0 and I personally do not plan to back-port them unless lightning strikes me and I escape unscathed, a big wad of cash falls from the sky, or for some reason we can't make the 2.1 cut before 9.3 comes out. So if you are planning to experiment with PostgreSQL 9.3, PLEASE use PostGIS 2.1 development branch. I will try to make sure we release 2.1 before PostgreSQL 9.3 comes out even if I have to resort to hitting some people over the head with a rubber bat :).

If ever in doubt what versions of PostGIS works with what versions of PostgreSQL /GEOS / GDAL, please refer to the matrix that we try to keep up to date. http://trac.osgeo.org/postgis/wiki/UsersWikiPostgreSQLPostGIS.

Now some people might say "Isn't it cruel not to support PostGIS 2.0 for 9.3", and my answer is "it's crueler to". The reason is simple. We have limited bandwidth for testing permutations of things and the more permutations of things we support, the dirtier our code base becomes making it harder to maintain and also the less time we can devote to properly testing each permutation. I'd rather say we don't support something than to do a half-hearted job of supporting all. On a slightly different, but also pragmatic note, package maintainers (except for windows maintainers :)) generally only carry one version of PostGIS per version of PostgreSQL, and I'd rather users getting from packages see our best foot than a two year old aging foot.

Note: that going from PostGIS 2.0 to 2.1 is a soft upgrade so you can install 2.1 on your existing PostgreSQL 9.2 without dump restore and then you should be able to pg_upgrade over to 9.3 if your database is too big to dump restore.

One of the features of PostGIS (pain to some however you look at it), is that PostGIS library file is versioned by minor version. The library will have for example a postgis-2.0 or postgis-1.5.dll / .so to denote the version. Each version of PostGIS can be compiled to run on usually about 3 or 4 versions of PostgreSQL.

Since PostGIS is not part of PostgreSQL proper and has to be installed separately, it is possible to run a different version of PostGIS in each database of a cluster. While this is a great feature for PostGIS developers and also great for users who want to keep their old legacy PostGIS apps, while testing or creating new apps with the PostGIS 2.0 or experiment with 2.1 development series, it does pose some obvious challenges.

For example you can't simply just upgrade your cluster to a new version of PostgreSQL. You need to make sure the new cluster has the various versions of PostGIS compiled and available. One step to that end is figuring out exactly what version of PostGIS each database in your cluster is running. Here is a quick psql script I wrote up to help with that.

The script

This psql script will generate another script which will generate a pipe delimeted database list of postgis databases and what version of postgis they are running.

The contents of check_postgis_version.sql look as follows

\t
\a
\o postgis_report_query.psql
\qecho '\\o postgis_report_query.psv'
SELECT '\connect ' || quote_ident(datname) || E'\n' || 'SELECT ' || quote_literal(datname) || ' As db, PostGIS_Lib_Version();'
 FROM pg_database
WHERE datname NOT IN ('postgres', 'template0', 'template1');
\qecho '\\o'
\o
\i postgis_report_query.psql

to run the script you'd do something like this and you'll need to run under a super user account

The output will be a file called postgis_report_query.psv which is a | delimeted text file will look something like this:

testpostgis210|2.1.0SVN
testpostgis2|2.0.3

Note, if you want more detail as to the versions of other dependency libraries, you can replace postgis_lib_version() with postgis_full_version(). Check the PostGIS Management Function reference for additonal details

Now that PostgreSQL 9.3 beta1 has been released we've started to jump start our experimentation by compiling our favorite extensions. First on the list is PL/V8 js.

This was compiled against 9.3beta1 for 64-bit and 32-bit and plv8 version 1.4.0. We briefly tried with the EDB windows builds which we downloaded from: http://www.enterprisedb.com/products-services-training/pgbindownload and seems to work fine.

• PostgreSQL 9.3 plv8 32-bit download
• PostgreSQL 9.3 plv8 64-bit download

We hope windows users find these useful.

We've got experimental pgRouting windows binaries available for windows PostgreSQL 9.2 32-bit and 64-bit for pgRouting 1.0.7 development branch. More details on our Boston GIS blog page.

The final versions we plan to release with upcoming PostGIS 2.1 PostgreSQL 9.2 on stackbuilder as part of the PostGIS install. Barring no difficulties we'll also have experimental binaries for PostgreSQL 9.3 releases once 9.3 reaches beta.

This version and upcoming pgRouting versions support the PostgreSQL extension model, so if you have postgis already installed, its just an additonal simple step:

In Happy Valentine PostGIS we demonstrated how to use PostGIS raster to decipher letters from a raster, vectorize them and then reuse this vectorized letters to form new words. Admittedly the letters were a bit grainy since they were vectorizations of low res rasters and I didn't bother smoothing them. Bruce Rindahl offered a script to SVG to PostGIS geometry and using Batik to convert a font file to SVG format and gave me a hi-res converted kankin fontset. I still haven't figured out how his script works.

Bborie Park thought that was all too complicated and thought (as I have always) that we need an ST_GeomFromSVG function for PostGIS of which he is on a mission to create when he's less busy. He also suggested I wrap my letter writer function as an extension. Taking all these ideas, I formulated an extension you install with

CREATE EXTENSION postgis_letters;

postgis_letters (http://www.bostongis.com/postgisstuff/postgis_letters_extension.zip) is an sql / data extension containing mostly data, but as the name suggests relying on PostGIS. The data are geometry vectors of the kankin font. I plan to add in more free fonts later once I figure out how to use Bruce's script or Bborie comes up with a better way and also more positioning logic and handling of spaces. So its a little rough at the moment. The purpose of the extension is so I can write words on my images in reports e.g. state names or overlay labels on geometry features like roads and land. Using the power of both geometry/raster you can have a fully functioning report image writer that would return a fully formed image for use in LibreOffice (or for my ASP.NET web apps Active Reports.NET). This wouldn't rely on any mapping server to draw images (just pure PostGIS/PostgreSQL). Hopefully with new and improved binary features coming in PSQL for (looks like 9.4), outputting these raster images from psql will also be trivial. While on my mission to do something useful, I got distracted by something more entertaining: describing spatial processes with words. Here it goes.

It gets more interesting when you use raster since you can colorize them, but wanted to make this short. For viewing the queries I just used OpenJump and it randomly picked a different color for each query. The examples below you'll notice I often use ST_Boundary to return just the linework so it doesn't cover up the letters. These examples use the default behavior of the ST_LettersAsGeometry function which is position at 0,0, height 100 units and SRID unknown. If you add additonal arguments you could for example position letters on a road that are 10 meters high.

Spatial SQL	Word Image
WITH word AS (SELECT ST_LettersAsGeometry('Triangle', 'kankin') As geom ) SELECT ST_Collect(geom , ST_DelaunayTriangles(geom,10,1) ) FROM word;	ST_DelaunayTriangles is new in 2.1 and requires GEOS 3.4
WITH word AS (SELECT ST_LettersAsGeometry('Concave', 'kankin') As geom ) SELECT ST_Collect(geom , ST_Boundary(ST_ConcaveHull(geom,0.99)) ) FROM word;
WITH word AS (SELECT ST_LettersAsGeometry('Convex', 'kankin') As geom ) SELECT ST_Collect(geom , ST_Boundary(ST_ConvexHull(geom)) ) FROM word;
WITH word AS (SELECT ST_LettersAsGeometry('Flip', 'kankin') As geom ) SELECT geom FROM word UNION ALL SELECT ST_FlipCoordinates(geom) FROM word;
WITH word AS (SELECT ST_LettersAsGeometry('Longest', 'kankin') As geom ) SELECT geom FROM word UNION ALL SELECT ST_LongestLine(geom,geom) FROM word;
WITH word AS (SELECT ST_LettersAsGeometry('Rotate', 'kankin') As geom ) SELECT geom FROM word UNION ALL SELECT ST_Rotate(geom,pi()/4, ST_Centroid(geom)) FROM word;
WITH word AS (SELECT ST_LettersAsGeometry('MBC', 'kankin') As geom ) SELECT ST_Collect(geom, ST_Boundary(ST_MinimumBoundingCircle(geom))) FROM word;