BostonGIS Blog

Update to Install instructions for PostGIS 2.0 more coming

nospam@example.com (Regina Obe) — Sun, 13 May 2012 03:33:00 -0400

We finally got around to updating our Part 1: Getting Started With PostGIS: An almost Idiot's Guide for PostGIS 2.0. We kept the 1.5 around so is still accessible under Part 1: Getting Started With PostGIS: An almost Idiot's Guide (PostGIS 1.5).

The Part 2: Introduction to Spatial Queries in the series was even more outdated and was using functions removed in 2.0 and long deprecated since around PostGIS 1.3. We've updated this as well, but still cleaning it up a bit and verifying we didn't make any typos in the code.

We hope to augment these tutorials with similar ones for raster and topology. Some of the misconceptions people have about working with raster in PostGIS 2.0 that we've noticed are:

It is somehow different if you are on windows vs. Unix/Linux. This is not true. In fact the raster2pgsql command-line tool makes the process pretty much the same regardless of what OS you are on.
People use raster2pgsql to generate an SQL file and then try to load it with pgAdmin and pgAdmin crashes. Sorry folks, pgAdmin is not the right tool for the job, it's not designed for loading up a 1 GB sql file in SQL Query browser and running it. Frankly you don't even need to bother with generating an intermediary SQL file - just pipe straight to psql. Works for all OS including Windows.
How to display PostGIS raster with external tools. The story there is still in infancy. Yes you can sorta do it with MapServer, QGIS etc, but its kinda slow at the moment. GeoServer might be better using the GeoTools option, but I haven't had the time to play with all the options. Another option I wanted to experiment with is one suggested by a client of ours publishing PostGIS raster as a tile based store similar to what is done with MBTiles. That has some allure since PostGIS raster can be loaded as chunked tiles with the loader to make analysis faster. So although it's really slick for vector/raster analysis there is no reason I see it can't play kinda dumb as well when its convenient and smart again when the need arises simply by using the output PNG/tiff etc functions as wrappers for tile output. Those functions are fairly fast. :).
Oh yah and it's about analysis not display. I know Paul, but some of us don't live in caves though I admit I do :)

Anyway we'll go thru these lessons in raster tutorials to follow and hopefully I'll have a better story with displaying PostGIS raster with third-party tools after some experimentation and some issues in the PostGIS GDAL driver have been resolved.

New book coming

Yap we just finished the first draft of our new book PostgreSQL: Up and Running and are patiently tweed-ling our thumbs for pre-reviews to come in. It's a short only about 150 pages in length (so far anyway) and we hope to have it on shelves in the next couple of months - both e-book and hard-copy. It will be published by O'Reilly Media and follows their new experimental standard similar in flavor to their HTML 5: Up and Running book.

The difference with this kind of book, is they are generally shorter and more focused because who wants to spend time reading or writing a tome that is almost guaranteed to be out of date by the time it hits the shelf. So the writing cycle is in theory shorter which means less risk for the publisher and the author to write a book that may not have as major of an audience. The other benefit is the e-Book formats of these books can always be kept up to date. So assuming you trust us as authors to continually update the material, if you buy an e-Book version of this title, you'll get any updates we write to it without shelling out extra dough. Of course if you go the standard of buying the hard-copy version you get it only at print cycles and just for that print. It's an interesting business model. We'll see how it goes and if it goes well we'll write other books of a similar flavor.

So Is "PostgreSQL Up and Running" for beginners? Yes and No. Our main audience target are people coming from other relational databases such as SQL Server, Oracle, MySQL, SQLite etc. and why the heck is using PostgreSQL the best decision you ever made :) . It is also for people who have used PostgreSQL but have a hard time keeping up with enhancements in newer versions of PostgreSQL and how to prepare and take advantage of them. The first release will target 9.1 and 9.2 series and if all goes well, we'll keep it up to date with newer versions as they come.

How to upgrade your databse to PostGIS 2.0 let me count the ways

nospam@example.com (Regina Obe) — Fri, 27 Apr 2012 18:43:00 -0400

Windows both 32-bit and 64-bit PostGIS 2.0.0 are up on application stack builder. we'll be updating our almost Idiot's guide in another week or so to show installing PostGIS 2.0. We also have fairly recent builds of upcoming PostGIS 2.0.1SVN and PostGIS 1.5.4SVN in PostGIS windows experimental builds section, which includes an updated GEOS 3.3.4dev to handle GEOS related bugs. Fixed items are itemized in PostGIS 2.0.1 SVN. So if you are bitten by any of these you might want to replace with at least the latest binaries available in the Windows experimental builds

Now that PostGIS 2.0.0 is finally out, the number one question people have (or should have) is how to upgrade your PostGIS install to the latest 2.0. We've seen a few casualities on the PostGIS users list of people who did it wrong partly because how you do it has changed a bit. Some ground rules, if you are running 1.x, you MUST do a hard-upgrade and if you go the quick and dirty hard-upgrade path, you MUST run the postgis_upgrade_20_minor.sql and rtpostgis_upgrade_20_minor.sql (if you installed raster support from an early 2.0 build or wktraster). If you are running an alpha of PostGIS 2.0, you can possibly get by with a soft-upgrade with caveats. Now for the long story.

Figure out what you are running by doing a: SELECT postgis_full_version();
If you are running PostGIS 2.0.0 beta2 (technically you could do a micro upgrade if you have alpha1 or above but there were some operator changes with geography that require a dump/restore to take advantage of) or above follow the micro upgrade route otherwise Please follow one of the hard upgrade routes. There are a few upgrade routes in fact, so choose which ever seems less scary.

Continue reading "How to upgrade your databse to PostGIS 2.0 let me count the ways"

PostGIS 2.0.0 Shapefile GUI Loader and Exporter

nospam@example.com (Regina Obe) — Thu, 05 Apr 2012 18:16:00 -0400

One of the new features I'm pretty excited about is the new postgis gui batch features that allows not only for multiple import of shape files, but also multiple export of shapefiles. It will work fine with your postgis 1.5 databases as well. We have windows standalone gui binaries at http://www.postgis.org/download/windows.

Is this cool or what? Here are some screen shots with it loaded as a pgAdmin plugin.

Import Tab

Export Tab

Add Table

Hold the ctrl key to select multiple tables/views

Supports multiple geometry columns

If your table has multiple geometry columns have option of changing to different one

Export

Export the shapefiles to a folder w option of creating new subfolder

PostGIS 2.0.0 windows binaries including 64-bit

nospam@example.com (Regina Obe) — Thu, 05 Apr 2012 17:09:00 -0400

PostGIS 2.0.0 windows binaries are up http://www.postgis.org/download/windows/#windbinaries. This is the first ever version of PostGIS to support PostgreSQL 64-bit windows installs. We want to extend a big thank you again to our generous sponsors for helping make this a reality.

We are in the middle of packaging the installers for both windows 32-bit and 64-bit installs and hope to have those up in the coming weeks for those who require that comforting Setup button. They will then be available via StackBuilder.

PostGIS 2.0.0 hits RC1

nospam@example.com (Regina Obe) — Thu, 29 Mar 2012 16:45:00 -0400

PostGIS 2.0.0 is coming closer to gold and we are on final count down. One small step for you and one giant leap for PostGIS. We are at RC1 right now. We have experimental windows builds for both 32-bit and 64-bit PostgreSQL windows (8.4,9.0,9.1) for those who want to give it a whirl before official release.

Give them a try :Windows PostGIS 2.0.0 rc1 binaries. Join the party.

PostGIS 2.0.0 64-bit for Windows beta out

nospam@example.com (Regina Obe) — Thu, 15 Mar 2012 19:01:00 -0400

We recently posted 64-bit versions of PostGIS for windows that work with the 64-bit PostgreSQL 9.0 and 9.1 editions. This is for 2.0.0 beta 3 release. You can download them from PostGIS windows experimental builds. We are hoping to get some people testing before the PostGIS 2.0.0 final release. PostGIS 2.0.0 will be released once we get ticket list down to 0. Given we've got only 5 or 6, it's pretty close. So get your motors revving.

If there are no major issues, we hope to include the 64-bit for 2.0 on Stack Builder. We greatly thank those who funded our campaign to do this.

What does the 64-bit come with. The same stuff as the 32-bit but all compiled under Mingw64-bit tool chain.

Command line loader / dumper for ESRI shape files
2-in-1 GUI Loader/dumper for ESRI shape files. Note this version is greatly improved over the 1.5 version. key areas were: stability enhancements, ability to import multiple files at once, and ability to export files to ESRI shapefile.
Raster support
Topology support
Tiger Geocoder
Enhanced 3D support. 3D will get a bit more loving in 2.1 series.

Back from DDOS Attack

nospam@example.com (Regina Obe) — Sun, 05 Feb 2012 10:27:57 -0500

As many may have noticed, BostonGIS.com has been down for the past week or so and probably is still not reachable from many parts of the world since our DNS server was also taken down as a result of a Denial of Service (DDOS) attack. We were attacked by an Activision Call of Duty Game botnet.

I have a small confession to make. One of the businesses we co-own is an e-Commerce site that sells condoms. You never know how people will react when you say that in mixed company. Some people are glad we are in a business protecting against venereal diseases or unwanted pregnancies and some feel we are violating a mother nature creed of conduct. Anyrate that was the site that was under attack on a UDP port and we are not sure if it was a malicious intent or not since the root instigator has not been found yet. The attack was higher up from our servers that it knocked our ISP out and we never saw the traffick.

The tragic thing is that it can happen to any site and does all the time. It really hit home when it happened to us.

Details of our fight are described here: WowCondoms plugs hole in Activision's Call of Duty Game Servers

GeoInformatics PostGIS 2.0 and writing

nospam@example.com (Regina Obe) — Wed, 14 Dec 2011 23:46:00 -0500

Our article about upcoming PostGIS 2.0 just came out recently in GeoInformatics December Issue (issue 8). If you want to know what all the buzz is about with PostGIS 2.0, read the article which starts on page 30.

Raster is not just for raster lovers anymore

What most people don't realize about the PostGIS 2.0 raster support is that it enhances the PostGIS geometry support as well. It has functions that allow you to output geometries as raster and overlay them with rasters. This allows you to for example overlay a parcel footprint on top of an aerial image without anything but PostGIS, SQL and your favorite language of choice. We also have ST_Union aggregation functionality for raster which means you can union raster tiles with SQL. You can even clip a raster by a geometry. I'm really very excited about the limitless potential for both standard raster users and geometry-only users that the new raster functionality provides.

Stay tuned for another book from us

We just got another book contract accepted. More on that once things become more final. It is not about PostGIS this time, but PostgreSQL. Yes we are going back to our database roots a bit. We've always thought of ourselves as web application / financials applications / database programmers who accidentally stumbled into the forest of GIS. We hope this new book will be most useful to programmers with not so much experience with databases and PostgreSQL specifically.

Happy Post GIS day - November 17th 2011

nospam@example.com (Regina Obe) — Wed, 16 Nov 2011 22:34:00 -0500

GIS Day November 16th, 2011 has just about ended. Why end the celebration there when you can continue it the day after with Post GIS day. November 17th, 2011, the day after GIS has already arrived in many parts of the world and commemorates the PostGIS spatial database.

To ring in this new day, we have prepared some Post GIS Day 2011 Commemorative playing cards. These cards will provide many hours of joy, education, and laughter for the whole family or random crowd gathering.

Get your Post GIS day 2011 Playing cards here

PostGIS 2.0 Cheat sheets in the works

nospam@example.com (Regina Obe) — Thu, 03 Nov 2011 19:29:00 -0400

Every PostGIS version I try to make up a little cheatsheet of functions and uses. People seemed fond of these and I enjoyed doing them. One person suggested we need the function arguments listed. There was no way I was going to do that by hand. PostGIS 2.0.0 is much bigger unfortunately so if I were to do it by hand I would not do any parts justice, so this time I decided to add to the PostGIS code base a cheatsheet generator. which is called with

make cheatsheets

uses xsltproc and xsl style sheets to navigate the PostGIS documentation. One side effect is that its a bit easier to spot glaring errors in the documentations. I've been playing with css styling and xsl a bit more. A bit too much playing probably.

Here is what it generates so far. The postgis core one I cheated a bit because I wanted the columns sort of balanced and really wanted 2 columns. I will work on having this autogenerate probably by doing something crazy like having my xsl script count how many functions have been output and then start a new column in the middle. The only one I configured to output examples from the docs was the Tiger geocoder one, because the other added a significant number of pages. Will probably have to add flags to examples to have it selectively output some exampels and not others.

FOSS4G 2011 PostGIS videos galore

nospam@example.com (Regina Obe) — Fri, 28 Oct 2011 17:48:00 -0400

In the last article FOSS 4G 2011 Synopsis and Slides we provided the slides to our talk. The video is out now PostGIS 2.0 the new stuff -- The Trailer. Don't miss it.

We have some other PostGIS videos listed on PostGIS FOSS4G 2011 videos have landed. We'll be adding some more to that list.

FOSS4G 2011 Synopsis and Slides coming in PostGIS 2.0

nospam@example.com (Regina Obe) — Sun, 18 Sep 2011 22:25:00 -0400

We were able to attend some of FOSS4G this year and for the very first time too. Hopefully next time we can stay for the full conference. Overall it was an enjoyable time. We got to see many of the people in person we've only corresponded with via email and even bumped into one of our college fraternity brothers we haven't seen in twenty years. It is indeed a small world. We gave our talk on Friday on what's up and coming in PostGIS 2.0. We were a bit shocked at the vast number of people attending our session and all the PostGIS sessions in fact. Next time we'll try to make our presentation less dense. The density of the presentation does make for a good after the conference hand-out. WARNING: The following slides feature SQL doing unconventional things suitable only for mature audiences. Viewer discretion is adviced.. You can check out our slides here PostGIS 2.0 the new stuff.

I think the last time we were in the Denver area (not counting airport stops) was when we were driving from Cambridge, Massachusetts to Stanford, California and we got stuck in a hail storm. Since we were in town, we thought we'd take in one of the nearby sites we've always fantasized about visiting; Casa Bonita Restaurant as featured in South Park. I wasn't expecting it to look or be quite as portrayed in South Park, especially the cave divers and waterfall. Indeed there were cave divers plunging every 30 minutes off the water fall and a Black Bart's Cave. We explored the cave and various other cavities of the restaurant and picked up some gifts at the gift shop to take back. The sopapillas were super delicious too as Cartman described. Then we went for dinner to eat some game food -- like Rocky Mountain Oysters. I exclaimed to Leo -- "these do not taste anything like oysters, not particularly interesting taste". To which he laughed and said "You've never tasted these oysters before." Had I known they were Rocky mountain oysters before-hand I might have appreciated the flavor a little more.

On Saturday we had the code sprint and the whole PostGIS clan (including remote folks) got together to strategize on the details of PostGIS 2.0 which includes the feature freeze, final expected release, what we would try to get done before release and what we would set aside for the future. A very strong showing of people about 8 local and several more on PostGIS IRC. The details are sketched on the PostGIS 2011 Sprint etherplans page. We will have Feature Freeze sometime in November and release Early 2012.

Minimalist Web-based PHP PostGIS 2.0 Spatial Geometry/Raster Viewer

nospam@example.com (Regina Obe) — Fri, 26 Aug 2011 18:08:58 -0400

In the last article Minimalist Web based ASP.NET PostGIS 2.0 Spatial Geometry/Raster Viewer, we mentioned that we would provide a PHP equivalent. Well before we were able to lift our fingers, Bborie created a PHP version o the postgis handler and gave us a copy. For those running PHP, you can download it from http://www.postgis.us/downloads/postgis_webviewer_php.zip

Minimalist Web-based ASP.NET PostGIS 2.0 Spatial Geometry/Raster Viewer

nospam@example.com (Regina Obe) — Fri, 26 Aug 2011 14:27:00 -0400

One of the things I'm most excited about in upcoming PostGIS 2.0 are the raster output functions, largely created by Bborie Park. The latest one he added ST_AsRaster should be a welcome addition to even those who don't care about raster because it allows you to convert regular PostGIS geometries to PostGIS raster and then use all the available export functions to output to your favorite image format without ever leaving the database. If you want to try out PostGIS 2.0 and are on windows, you can download compiled versions for PostgreSQL 8.4-9.1 at: http://www.postgis.org/download/windows/experimental.php#PostGIS_2_0_0

This means that PostGIS is now both a spatial as well as a graphical database engine. To test out how I would take advantage of these new features, I decided to create a mini ASP.NET 2.0+ app that is a PostGIS ad-hoc query viewer for both PostGIS geometry and raster. We have it written in both VB.NET and C# and are planning to follow up very soon with a PHP version. We'll also be putting up an online version and tutorial for people to play with who don't have PostGIS 2.0 installed so they can see what they are missing out on :). You can also download the source code from: http://www.postgis.us/downloads/postgis_webviewer_aspnet.zip

We'll be attending FOSS4G2011 in Denver this year, giving a tour of all the new treats in PostGIS 2.0 which will include this as well as PostGIS geometry analysis and maintenance functions/changes, 3D support types and functions, Topology, and Tiger Geocoder. If you are attending, please attend our talk on Friday PostGIS 2.0, the new stuff.

The Application Makeup

The application is very simplistic and is composed of 3 parts:

JQuery for the html page UI which handles all the Ajax calls and DOM changes. JQuery is pretty slick and the more I use it, the more I love it. If you do web development, you should really give it a try.
A stored function just so if I need to do more complex things like, intrusion detection,or benchmarking etc its compartmentalized.
A server-side web handler that passes off the query to the stored function which returns back the query as a PNG file.

Check out these snapshots

I'm hoping in the very near term future to combine this with my favorite .NET reporting engine -- ActiveReports.NET which I currently use for my PostGIS based web apps and which can take database fields consisting of blobs of images. Being able to auto generate an image from spatial data to go in a report will be slick beyond imagination.

PostGIS in Action now in ePub and Kindle format

nospam@example.com (Regina Obe) — Wed, 13 Jul 2011 15:17:12 -0400

Just got news that Manning has released the ePub and Kindle (Mobi) format of PostGIS in Action. Those who already purchased a hard or e-Book copy, should get their download link by email.