waiting_postgis_21

The DZone Essential PostGIS reference card got released yesterday. The PDF version is a free download, though does require DZone registration. This reference card is focused on PostGIS 2.1 but does showcase some features present in older versions. Its way too hard to stuff everything on a 9 page reference so we had to leave out some of our favorites that we thought would not be as appreciated by a developer community just starting off with GIS and PostGIS.

We are working on the second edition of Essential PostgreSQL which hopefully will be out in the next 2 months. That one will be advertising our upcoming second edition of PostgreSQL: Up and Running..

On a slightly related topic, we did our Intro to PostGIS talk. We had a good live demo with very few glitches followed by food and spirits. The group of folks was a good cross sectional. Web developers working with geodjango or ruby on rails new to PostgreSQL and PostGIS, some mainstream GIS (urban planing) with little familiarity with relational database spatial (but familiar with ESRI), retired professor, high-end PostgreSQL folks with limited knowledge of PostGIS, IT consultants both GIS and mainstream etc. We promise to have the code posted probably early next week (and possibly a video too if the video turns out okay).

One of the new functions in PostGIS 2.1 is the ST_ColorMap function which converts a 1-band raster into 3 or 4 band 8BUI raster. It's main use is for visualizing otherwise non-visual floating point rasters such as digital elevation models, temperature, weather, and even rasters you create from your own data. For this next example, I'm going to take the fairly uninteresting topic of land area using tiger 2013 state boundaries to demonstrate. This is similar to the ST_SetValues example I demonstrated except using ST_Union and ST_ColorMap. The final output is a PNG image which looks like:

We'll be giving an Introduction to PostGIS talk at the Tuesday, September 10, 2013 PostgreSQL Boston Meetup Group starting 7PM. We'll also be providing free cocktails and food. So if you are in Boston, signup while supplies last. We are right in downtown Boston in the Financial District and easily accessible by all train lines.

In the meeting we'll describe PostGIS uses, demonstrate common queries, tools that work well with PostGIS, and highlight key features of PostGIS 2.1. Time permitting, we'll also talk about PostGIS 2.2: what's already planned (and some even implemented features) of PostGIS 2.2. Note that PostGIS 2.2. has started its development, but still in its infancy.

The PostGIS development team is proud to release a release candidate version of upcoming PostGIS 2.1.0. As befits a minor release, the focus is on speed improvements, more features, and bug fixes. We expect this to be the final release candidate before we officially release 2.1.0. We'd appreciate it if you test it before final release and report back with any issues you run into so we can have a smooth release.

2.1.0rc1 release source files http://download.osgeo.org/postgis/source/postgis-2.1.0rc1.tar.gz

We are shooting for a final release in the next week or two.

Document downloads:

• HTML http://download.osgeo.org/postgis/docs/doc-html-2.1.0rc1.tar.gz
• PDF http://download.osgeo.org/postgis/docs/postgis-2.1.0rc1.pdf
• EPUB http://download.osgeo.org/postgis/docs/postgis-2.1.0rc1.epub

32/64-bit RC1 Binaries for windows users for PostgreSQL 9.2/9.3 are already available.

We had this big raster that we needed to chop up into tiles and only extract a portion of for load into PostGIS. raster2pgsql doesn't currently have an option to pull just a portion of a raster and also we don't have the windows raster2pgsql compiled with MrSID support. Luckily GDAL commandline gdal_translate has this switch that allows you to specify a chunk of a raster to grab based on a projected or unprojected box window. We wanted to grab just that portion that is part of boston and chunk it into bite size pieces. What we needed was a grid generator similar to what we described a while back in Map Dicing and other stuff that would chop our neighborhood into bite sized tiles we could then use to generate the relevant gdal_translate command. Instead of using temp tables and all that stuff, we decided to try with the ST_Tile raster function. Creating an empty raster and then tiling it.

In a nutshell, we're using PostGIS raster technology (ST_Tile function introduced in PostGIS 2.1) that we demonstrated in Waiting for PostGIS 2.1 ST_Tile to create a grid because PostGIS geometry doesn't have cool gridding function like SpatiaLite has :). SpatialLite tesselation. Perhaps in PostGIS 2.2 we'll see some of these SpatiaLite niceties. However ST_Tile does the trick fairly nicely and quickly. For this example took under 600 ms to generate 1524 rows of GDAL commands.

Following PostgreSQL's lead, I thought it would be nice to provide PostGIS manual in ePub format. It turned out not to be a lot of work, but probably a lot more to fine tune it. I've changed Debbie (a PostGIS build-bot) to build this format and publish to PostGIS site whenever a change to PostGIS 2.1. You can download from http://postgis.net/documentation. The link next to EPUB.

I would appreciate if people could try it out on their eReader devices. On my Android tablet it looks pretty good, and had the nice feature of when I clicked on the epub link to download it, it added it to my library. I can't figure out how to click on the links (probably because I just use my desktop). and table of contents seems to crash the reader. On my windows firefox ePub viewer, it looks pretty yucky but much easier to navigate. I guess there are a LOT of differences with ePub readers.

We've made our first huge milestone. With much of Steve Woodbridge's help and patience as well as support from pgRouting Windows campaign sponsors, we have windows PostgreSQL 9.2 binaries for the pgRouting 1.07 development branch for both PostgreSQL 32-bit and 64-bit. You can download these from: Winnie's 9.2 windows downloads. Details on how to install are described on Windows download page at bottom. Please give these a try to see if they work okay for you. These binaries will work for PostGIS 2+ releases as well as the experimental PostGIS 2.1 builds and upcoming PostGIS 2.1 release.

We are still missing payments from some pgRouting sponsors. So if you haven't sent us payment yet, please do so. If you weren't on the initial campaign and would still like to give, you can still contact us at lr at pcorp dot us to make payment arrangements. If you want to contribute, we ask you do $100 USD or more since smaller amounts are too much bookkeeping. Any additional money's will go toward work on pgRouting 2.0 (not just Windows but general support of the rewrite) and future buildbot upgrades.

Steve is now pushing further forward with upcoming pgRouting 2.0, so the pgRouting code base will soon be a bit volatile and unstable. We are still in middle of setting up the buildbot to automate the future builds and then come PostGIS 2.1, you can expect to see pgRouting included in the PostgreSQL 9.2+ PostGIS 2.1 application stack builder packages.

We have a confession to make. We're not GIS analysts; we just play one at parties. Truth is the bread and butter of our business involves pretty boring stuff like e-Commerce, pricing (venture capital, private equity, travel, pension management), project management, work force management and all that other stuff that would bore a real GIS analyst to tears. Somehow we've got a lot of pictures to deal with particularly with project management and e-commerce work. So I was elated when Bborie checked in this new function ST_FromGDALRaster. With this function you can do all resizing and other manipulations right in the database with standard type images like PNGs, bitmaps and anything else users will throw at you.

As mentioned before, we like keeping our work related pictures in the database, but every once in a while, we'd like to manipulate them and it would be nice not to have to keep many sizes of one image in the database. Having to drag them out of the database to do stuff with them is kind of a pain or to keep extra sizes is also a pain. We'd like to keep the original format we were given intact, but all other custom sizes people ask for do on the fly.

For these operations, I'm using:

Using the buildbot builds generated by Winnie PostGIS buildbot.

With all the cool new raster functions coming in postGIS 2.1, it's really hard to pick a favorite. One of the functions I feel like I've been waiting eons for is ability to burn a set of geometry values in a raster with one SQL statement. My dream came true with ST_SetValues. Check this out:

We've done a preliminary generation of function cheat sheets for upcoming PostGIS 2.1. We'll regenerate again before PostGIS 2.1 gets released. We've also updated the PostGIS 2.0 cheatsheets with updated doc links and errata. The html versions of these are generated from the PostGIS documentation xml files using the:

make cheatsheets
build command. Then we just do some massaging to it and print it to PDF.

Pay careful attention to the superscripts on the functions:

Here is list of 2.1 sheets.

In a previous article Waiting for PostGIS 2.1 ST_Resize we demonstrated a new raster function ST_Resize that is available in upcoming 2.1. In this article we'll talk about another function called ST_Tile, which similar to ST_Resize can work with both georeferenced and non-georeferenced rasters. As the name suggests, ST_Tile can be used to cut up rasters right in the database, similar to what the raster2pgsql -t Tilesize commandline switch does when you import. In this article we'll demonstrate the misuse of ST_Tile. ST_Tile can also be used for tiling out of db rasters and what it does when it does that is not to actually tile, but instead mark off the areas where it would cut if it were to actually cut.

Another thing I'd like to mention here which can not be summed up in a single function is that a lot of work has gone into PostGIS 2.1 in improving the performance and robustness of out of database rasters (rasters stored externally but queried as if they were inside the database), and more is planned before 2.1 hits the street. All this work has made me reconsider where out dbs play a role and how their speed profiles compare to in-db rasters. We'll demonstrate the speed profile differences in this article for ST_Tile as well for this small sampling.

If you are not afraid of chewing the fat a little and you are on Windows, there are always fresh windows binaries packaged with all these goodies you can get from: Winnie's fresh baked windows PostGIS binaries corner.

There are three big changes in the Tiger geocoder coming in 2.1, which of course you can enjoy now but I'll mostly focus on the ability to install it as an extension since that's the one I think most people will appreciate.

• Upgraded to download and load Tiger 2012 data.
• Less greedy loading. In 2.0 it would just download all the tiger data for a state even for tables it did not use. In the 2.1 version we rewrote to just download the relevant tables defined in the table tiger.loader_tables. So it is in essence a fairly table driven loader that uses SQL to build the commandline script.
• Last but not least, ability to install it like a PostgreSQL extension if you are using PostgreSQL 9.1+. This is what we'll focus on for the rest of this article.

Brief whirlwind history of PostGIS Tiger Geocoder

PostGIS Tiger geocoder is a packaged PostGIS extra that utilizes US Census Tiger data for geocoding. As such, it probably only has utility for people dealing with US data. Aside from being US-centric, its other key differentiator from most other PostGIS geocoders is that it's a pure PostgreSQL play. Everything runs in the database, callable in queries, and only dependencies are PostGIS and fuzzystrmatch (PostgreSQL fuzzy string match extension that comes with most PostgreSQL packages). This makes it easy to wrap anything around it and a drop in tool for most apps. We for example created a .NET web service with 5 lines of VB.NET code that does nothing but connect to the database, passes the address to the geocode function, and gets back the result. For most other uses we just use it as an in-db batch geocoder.

It has a long history, first created by Refractions, then stagnant for a while when US Census made major structural changes switching to ESRI shape format and restructuring things. Then it got upgraded by Stephen Frost et. al to utilize Tiger 2008 new structure and ESRI shapefile. Then we picked it up from there and have been doing much of enhancements and maintenance since mostly funded through client work.

• First facelift we did was changing the structure to use inheritance so each state's data could be loaded as needed and attached/detached from the hierarchy for easier maintenance (like a tire replacement).
• Then redoing much of the query logic, adding indexes to improve speed and improve address normalizer.
• Adding how to use and descriptions and usage of functions in the official PostGIS docs.
• Adding reverse geocoder function
• Lastly including as part of it a loader generation and maintenance SQL functions that generates a platform specific loading commandline script to download the data from US Census, stage it, and load it into final tables. For most Linux/Unix installs the unzip, wget, and shp2pgsql dependencies are already present and for Windows, equivalent tools are an easy 10 minute download/install away. Maintenance functions scan the table heirarchy and add indexes where needed.

There has been a surprising amount of interest in it, perhaps because Google and others have started charging a non-trivial amount of money for bulk geocoding services. When you've got to geocode thousands of US addresses to geocode, it's an appealing option. One plus means more people stress testing it reporting bugs, which means (we get free testing for our consulting work). Getting good QA testers that catch issues before your clients do is not cheap. This encourages us to refine and on the bad side means more people reporting bugs and expecting us to do something about them. It's the classic curse of open source, you build something people want to use; give it away; and people expect you to make it better.

I have a confession to make. I'm one of those folk who likes keeping my pictures in the database. File paths are just too annoying and when some network guy/gal decides to do some spring cleaning, all the pictures referenced in your database, associated with important projects are misplaced. On top of that paths aren't as easily accessible across firewall connections as database connections, vary depending on OS/network and are just ancient. I can go on and on about why I like my pictures in the database along side the data they pictorially describe, and some wise ass will tell me how wrong I am, so I'll stop here.

One thing I have always wanted to do is do all my cropping and resizing with the terseness and beauty that is SQL. With latest changes in PostGIS I can enjoy the same features with non-georeferenced pictures that I can with georeferenced ones. Thanks Bborie. We now have an ST_Resize function that doesn't require a known spatial reference system and many of the other gdal functions e.g. ST_ReSample have been changed in a similar fashion.

For these operations, I'm using:

Using the buildbot builds generated by Winnie PostGIS buildbot.

My mental clock is on a 36-48 hour cycle, so it's still Post GIS day for me. Well it's always Post GIS day technically except when it's GIS day. To celebrate the new year, I've updated the Flash cards.

You can find them PostGIS Day 2012 Flash / Playing Cards. HINT: The functions with superscript 1 or 2 are new or changed in upcoming 2.1 release and there seem to be a lot of 1s and 2s around the raster functions.

One of the latest exciting additions to PostGIS 2.1 is a complete C implementation of ST_DumpPoints which is a patch contributed by Nathan Wagner. Past versions of ST_DumpPoints piggy backed on ST_Dump and plpgsql for recursion so this has been a long-awaited todo. This function is dear to my heart because it is a sub-step of many geoprocessing workflows. What's dearer to my heart though is getting rid of the memory copy bottlenecks that make plpgsql processes inherently slower than C-native ones. That's probably too much to ask. In theory this new ST_DumpPoints version should be faster, but how much. There was also some intimation that the new ST_DumpPoints implementation has improvements over the C-based ST_Dump. I decided to put both of these theories to the test.

If you are on windows PostgreSQL 64-bit or using PostgreSQL 9.2+ - you can download experimental binaries for windows