Thursday, September 12. 2013
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).
Friday, August 30. 2013
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:
Continue reading "Using ST_ColorMap for Thematic Maps"
Thursday, July 18. 2013
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.
Thursday, July 11. 2013
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.
32/64-bit RC1 Binaries for windows users for PostgreSQL 9.2/9.3 are already available.
Continue reading "PostGIS 2.1.0 rc1 released"
Thursday, May 09. 2013
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"
Sunday, May 05. 2013
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.
Tuesday, April 09. 2013
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.
Monday, April 01. 2013
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"
Saturday, March 09. 2013
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:
Continue reading "Waiting for PostGIS 2.1: ST_SetValues - The road to thematic maps"
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.
Saturday, February 09. 2013
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:
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"
Tuesday, February 05. 2013
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.
Continue reading "Waiting for PostGIS 2.1 - ST_Tile"
Wednesday, January 16. 2013
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"
Sunday, December 02. 2012
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"
Friday, November 16. 2012
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.
Friday, November 02. 2012
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
Continue reading "Waiting for PostGIS 2.1 - Faster Dump Points"