BostonGIS Blog

Chopping rasters with gdal_translate

nospam@example.com (Regina Obe) — Thu, 09 May 2013 04:21:00 -0400

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.

Note the repurposing: Creating a raster with no bands to accomplish a task that has nothing to do with rasters, so that we can then apply it to something to do with rasters. Gridding is a surprisingly common step in a lot of spatial processing workflows.

Here is the SQL to do it and we'll explain in a separate article in more detail.

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.

Continue reading "Chopping rasters with gdal_translate"

PostGIS 2.1 Manual in EPUB format

nospam@example.com (Regina Obe) — Sun, 05 May 2013 14:10:00 -0400

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.

pgRouting Tutorials: Part 1

nospam@example.com (Regina Obe) — Thu, 11 Apr 2013 22:55:00 -0400

As mentioned in prior article we have windows binaries available for upcoming pgRouting. When we get new things, we like to take them for a test drive. So we've put up our first tutorial ever on pgRouting: pgRouting: Loading OpenStreetMap with Osm2Po and route querying. The OSM2PO pgRouting loading content we borrowed from Anita Graser's An osm2po Quickstart, but put a Boston spin to it.

We hope to use this tutorial series as a springboard for testing the radical changes in pgRouting currently going on.

pgRouting 1.07dev windows binaries available for PostgreSQL 9.2 32-bit and 64-bit

nospam@example.com (Regina Obe) — Tue, 09 Apr 2013 00:08:00 -0400

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.

Waiting for PostGIS 2.1: ST_FromGDALRaster free those images

nospam@example.com (Regina Obe) — Mon, 01 Apr 2013 23:01:00 -0400

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:

POSTGIS="2.1.0SVN r11230" GEOS="3.4.0dev-CAPI-1.8.0 r0" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.2, released 2012/10/08" LIBXML="2.7.8" LIBJSON="UNKNOWN" RASTER

PostgreSQL 9.2.2, compiled by Visual C++ build 1600, 64-bit

Using the buildbot builds generated by Winnie PostGIS buildbot.

Continue reading "Waiting for PostGIS 2.1: ST_FromGDALRaster free those images"

Boston OSGeo Code Sprint Synopsis

nospam@example.com (Regina Obe) — Thu, 28 Mar 2013 17:49:00 -0400

The Boston OSGeo Code Sprint ended today. Overall I think it was a great success just in terms of what was accomplished and the fact that it was the very first conference we had ever set up. We were all nerves so couldn't enjoy it as much as we wanted to. It was also the first conference we were able to sorta attend all days for.

Paul already gave a great cap on what was going on in the PostGIS and MapServer camps. I still felt like there was way too much coding going on and would be nice to have a bit more talking and a bit less typing.

I want to thank EnterpriseDb for being a Gold Sponsor and it would have been nice if they gave a talk. Maybe I can drag them in some other day. Rich Grady, of AppGeo gave a wonderful and humorous talk about the metamophisis AppGeo is going thru coming from a predominantly ESRI shop, now focused on the prize of finding the best solutions to solve spatial IT problems; which surprisingly does not always involve ESRI and is more and more requiring Open Source GIS technologies. They did show off their ESRI medals of honor and how well they kept papers from flying around.

For those who contributed to our pgRouting campaign, a BIG THANKS. We'll be circling back later this week or early next to try to finish collecting and charging credit cards. If you already wrote us asking, but how do I pay? and we haven't responded with details, don't worry about it. We'll get to you next week. More concerned about those who haven't contacted us.

Continue reading "Boston OSGeo Code Sprint Synopsis"

pgRouting and Boston OSGeo Code Sprint

nospam@example.com (Regina Obe) — Fri, 22 Mar 2013 23:45:00 -0400

The Boston OSGeo Code Sprint is just days away . Looking forward to seeing a lot of you. Of course, if you'd like to still sponsor some money to offset the cost of sprinters, that would be appreciated.

One of the projects we will be spending a lot of effort on is pgRouting on windows with special focus on pgRouting 2.0. Steve Woodbridge is doing much of the work on both the pgRouting 2.0 (making sure works cleanly with PostGIS 2.0+ series) and also helping us work out the issues with building under windows. That said -- if you promised to give us money for pgRouting windows campaign and haven't contacted us yet, PLEASE DO SO (lr at pcorp dot us). Of course, if you weren't on the campaign, it's not too late to subsidize this effort. Just contact us directly. It will go towards pgRouting on windows, general pgRouting cleanup, integration with PostGIS 2.0+ series, and CREATE EXTENSION support.

Waiting for PostGIS 2.1: ST_SetValues - The road to thematic maps

nospam@example.com (Regina Obe) — Sat, 09 Mar 2013 13:14:00 -0500

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:

A side note, people have asked how we visualize these things. We used the ad hoc web viewer we had created that we described in Minimalist Web based ASP.NET PostGIS 2+ and brother version PHP viewer version.

We do a lot of ASP.NET development and for using the ASP.NET viewer, we just set the credentials, in web.config, install the helper function in our test database running PostGIS, and then right-click View in Browser the postgis_viewer.htm with Visual Studio or Visual Web Express. The PHP version is setup much the same and in fact the postgis_viewer.htm are identical except the php version calls a PHP handler file and the ASP.NET one calls an ASP.NET http handler. The client side has some jquery magic that feeds that posts the query to the handler. The handler does the query and outputs the results which are then injected via jquery call.

Continue reading "Waiting for PostGIS 2.1: ST_SetValues - The road to thematic maps"

Windows PostgreSQL pgRouting campaign close coming soon

nospam@example.com (Regina Obe) — Sat, 16 Feb 2013 11:16:00 -0500

Just a heads up to folks who are interested in funding our Windows pgRouting campaign. We need 5 more sponsors before March 1st to make our target funding of 20 sponsors (each giving a $100 USD). With the funding we'll build pgRouting for both 32 and 64/bit PostgreSQL 9.2 and package with upcoming PostGIS 2.1.0. We also plan that barring no issues, all future versions of PostGIS and PostgreSQL we will package with pgRouting. So in a nutshell, this means that when you download from StackBuilder, PostGIS, you'll be getting a pgRouting extension as well that you can install. Yap that's right you'll be able to do: CREATE EXTENSION pgRouting;

We also plan to before PostGIS 2.1.0 release have pgRouting available via the winnie PostGIS build bot builds for those who want to experiment with it before release.

Waiting for PostGIS 2.1: Cheatsheets

nospam@example.com (Regina Obe) — Sat, 09 Feb 2013 02:13:00 -0500

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.

Continue reading "Waiting for PostGIS 2.1: Cheatsheets"

Waiting for PostGIS 2.1 - ST_Tile

nospam@example.com (Regina Obe) — Tue, 05 Feb 2013 06:37:00 -0500

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.

WARNING: This article depicts PostGIS being used in a less than productive manner and shows graphic pictures which some audiences may consider violent and insensistive to the feelings of paintings. Please remember that PostGIS is a tool for doing SERIOUS work and should only be toyed with for demonstration or testing purposes. The fact that PostGIS can be used for amusement, and can become addictive is merely an accident of design. Studies have shown that although PostGIS is a powerful mind-altering drug, continued use and exposure WILL augment intellect and possibly spawn creativity.

Continue reading "Waiting for PostGIS 2.1 - ST_Tile"

Waiting for PostGIS 2.1 - Install PostGIS Tiger Geocoder as an Extension

nospam@example.com (Regina Obe) — Wed, 16 Jan 2013 20:41:00 -0500

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.

Continue reading "Waiting for PostGIS 2.1 - Install PostGIS Tiger Geocoder as an Extension"

Waiting for PostGIS 2.1: ST_Resize not just for GIS

nospam@example.com (Regina Obe) — Sun, 02 Dec 2012 14:23:00 -0500

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:

POSTGIS="2.1.0SVN r10777" GEOS="3.4.0dev-CAPI-1.8.0 r0" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.1, released 2012/05/15" LIBXML="2.7.8" LIBJSON="UNKNOWN" RASTER

PostgreSQL 9.2.0, compiled by Visual C++ build 1600, 64-bit

Using the buildbot builds generated by Winnie PostGIS buildbot.

Continue reading "Waiting for PostGIS 2.1: ST_Resize not just for GIS"

PostGIS day flash cards 2.1 early celebration

nospam@example.com (Regina Obe) — Fri, 16 Nov 2012 17:36:00 -0500

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.

pgRouting packages for Redhat PostgreSQL 9.1, 9.2

nospam@example.com (Regina Obe) — Tue, 13 Nov 2012 18:09:53 -0500

Devrim has pgRouting packages built for PostgreSQL 9.1 and 9.2 and will make those available on yum.postgresql.org. This thanks to generous sponsorship support from Norsk institutt for skog og landska.