bostongis new stuff

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.

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

Its been a while since we've blogged on BostonGIS. We've been very busy as you can imagine.

Couple of quick updates:

• PostGIS 1.5.0 is finally out We are working on packaging the PostGIS Windows binaries and builds, which we hope to have ready sometime late next week. More details at PostgreSQL PostGIS and PL/R news
• PL/R now works with PostgreSQL 8.3/8.4 windows builds. Get the binaries here and instructions at the new and crisp PL/R Wiki .
• We've launched our PostGIS in Action booksite. We are down to the 3-2-1 count down of PostGIS in action chapters. Here you can find details about what is covered in each chapter. Get the code, the data, presentations we've done on PostGIS. We are still working out some annoying display issues on IE 6/7.
• Finally, we'll be presenting at PgCon East 2010 in Philadelphia March 25th-28th 2010. More details on that later as the story unfolds.

We have published the first draft of our SQL Server 2008 Spatial Part 3 tutorial. In this part, we demonstrate how to install the SQL Server Spatial Tools which includes the much needed Union aggregate function. More examples of its use will follow.

We recently published our second tutorial in our SQL Server 2008 spatial series SQL Server 2008 Spatial: Reproject data and More Spatial Queries. This one goes over transforming and loading data, quick look at analyzing plans and more simple spatial queries. We also put in links to other tutorials we found useful on the web.

In looking back at this, I noticed that we violated something. According to OGC spec, ST_PointN, STPointN - is only defined for LINESTRINGS and MULTIPOINTS, but SQL Server 2008 allows you to get away with using this for POLYGONS. If you try the same thing in PostGIS you will get null and I suspect the same holds true with DB2 and Oracle

I guess its somewhat debateable whether it is right to do more than what the spec says or not. Paul Ramsey mentioned (and I think he even changed in PostGIS trunk to have ST_NumPoints (which is only supposed to work for linestrings and multipoint?) to be an alias for ST_NPoints (which is not an OGC spec, but works for everything in PostGIS). STPointN/ST_PointN I guess is more arguable whether it should work for POLYGONS/MULTIPOLYGON because suppose for complex - its unclear what you would consider the order of points where rings are involved. I guess even there there is a natural order.

I suppose from a portability stand point its annoying to have things named the same or sort of the same that don't behave exactly the same across the databases you work with, but then again we don't live in an ideal world and who wants everything to be the same anyway? Where is the choice in that? So needless to say I am torn especially in cases where it seems the spec was short-sighted. ST_PointN/STPointN/ST_NumPoints/STNumPoints to me is one of those cases.

Anyrate stay tuned for the 3rd in our series.

We have started to take a really hard look at what SQL Server 2008 offers. Aside from the usual stuff that makes a database lover's heart go thumpety thump -

• More SQL constructs
• Enhancements to Reporting Services
• New expansion of SQL Server database storage to use filesystem for large document storage AKA (FILESTREAM storage)
• Powershell integration
• Introduction of sparse columns more suitable for datewarehouse type stuff
• And of course introduction of spatial which makes a spatial database analysts heart go thumpety thump thump.

To commemorate our tour of the new SQL Server, we have started a new section in BostonGIS called SQL Server 2008 Tutorials and have put in our very first article. Part 1: Getting Started with SQL Server 2008 Spatial: An almost Idiot's Guide. We are still learning so feel free to let us know where we went wrong.

Those who have read our popular Part 1: Getting Started With PostGIS: An almost Idiot's Guide will recognize some similarities and those are for the most part intentional. Does this mean we are abandoning PostGIS? No. We are technologists and implementors and as such, it is always important to have at least 2 answers to every question. For example PostGIS has the advantage of running on more operating systems and a growing and not too shabby ecosystem and a very nimble architecture, while SQL Server 2008 although it runs only on Windows, it has a massive ecosystem behind it from third-party's and Microsoft itself (e.g. SharePoint, CRM, Virtual Earth), which for many of the clientele we deal with are not too shabby things.

It is equally important to stick with standards and themes within reason because standards provide the glue that allows disparate systems to interoperate. Focusing on standards also means the less you need to learn to get stuff done. The main thing we like about both of these platforms, is that they try to stick with ANSI database standards and OGC standards. This is very important because it means there is synergy with learning both. Learning one helps reinforce learning the other - its sort of like analyzing a coin and flipping it on its side. Where the requirements of one project rules out one for one reason or another , the other may still be a likely fit. In fact we might just have some chocolate and peanut butter examples in the wings.

We've been busy as of late, so has been hard to keep up with blogging. As many of you may notice, we have another satellite site called Postgres OnLine Journal. We try to put only PostgreSQL specific things on that site and PostGIS/GIS things on this site, but often times the lines cross and so we end up cross-linking. This is one of those times.

Cheat Sheets

People who know me tend to think I have a spectacular memory for things. What they don't seem to realize is I have good recollection but bad memory. People seem to have a hard time differentiating between the two concepts. My natural survival instinct to compensate for my terrible memory is to carefully organize my thoughts which often gives the illusion of a spectacular memory in many cases and embarassing absent-mindedness in others.

This also makes me more sensitive to breaks in patterns than most and produces some other interesting and humorous side effects.

One of the tools I use to organize my memory is to create cheat sheets which I gain some joy in making. Cheat sheets provide a reference as well as forces a certain level of knowledge organization. Here are some that PostGIS users may find useful.

Recently Created
PostGIS 1.3.3 pgsql2shp shp2pgsql Command Line Cheatsheet
PostgreSQL Pg_dump Pg_Restore Cheatsheet
PostgreSQL 8.3 Cheat Sheet
Old one which I hope to update when the new release comes out
PostGIS ver. 1.3.1 Quick Guide

Yeh for OpenJUMP

I'm still navigating my way thru the OpenJUMP tool, but so far I like it more than even the commercial tools I have used and even more than QuantumGIS. Although doesn't seem to have a shape importer similar to QuantumGIS's SPIT plug-in or Manifold's link import tools.

So far I have tried to try ESRI ArcGIS, Manifold.net, GvSig, uDig. Manifold seemed to have some interesting features, but required some setting back and reading material which I have had too short of an attention span for. Granted looks like it would be worth it once I can get myself to sit down for longer than 15 minutes.

ESRI ArcGIS layout and terminology, sorry if I am offending anyone here, just seems altogether confusing to me which makes me think what kind of person came up with this chaotic structure?. Perhaps its just a side-effect of consolidating a number of pseudo-disparate products over the years under one umbrella. I'm sure there is beauty in there that I am just not able to appreciate coming from GIS from a non-standard angle.

GvSig, admittedly I haven't explored enough. The layout seemed organized enough, but just not my kind of layout.

uDig I haven't tried in a while, but seemed a bit overwhelming and when I did, I found it annoying that it would arbitrarily pick a geometry field if I had more than one in a table and I couldn't seem to selectively select one. That was a couple of years ago so things probably changed, and I really should give it another go.

OpenJUMP on the other hand was like it just read my mind. I really haven't had to read anything to do the things I have wanted to do in it. Well really most of what I was looking for was an ad-hoc spatial query tool so while Manifold and ArcGIS probably have tons of useful stuff that are real gems, it gets in my way of my short-term objectives. Plus the OpenJUMP mascot was an added benefit since it is similar to the logo of our biggest client and a graphic representation of my old moniker - which was derived from my initials. So OpenJump gets plus 2 for nostalgic effect.

If anyone is interested, I've put together a quick brush through of using it - at our Postgres Online Journal satellite site - in the product showcase section.
OpenJump for PostGIS Spatial Ad-Hoc Queries