gis - 4

A few people have asked how they can contribute to PostGIS work and sadly PostGIS is very disorganized in this area. Part of the problem is no one knows if setting up the work to collect the funds would be worthwhile, whose job it would be to do it, how that person would be reimbursed for the accounting work needed, and how that money would be distributed.

That said, I have decided to start on my own to ask for funding for the recurring PostGIS maintenance work that I do and to treat it as consulting income. This is partly to test the waters to see if getting a more formal funding setup is worthwhile and also so I can devote more time to PostGIS maintenance without becoming a pauper. If you are interested in funding PostGIS maintenance work, refer to this page Paragon PostGIS funding.

It is my hope that eventually we'll have a specific PostGIS fund managed by the PostGIS PSC that can be used to support the work of package maintainers and subsidize travel expenses of PostGIS developers. The PostGIS developer group is very global and getting people together or to go to conferences is expensive. Sadly many of the developers on the PostGIS team who would love to attend conferences on PostGIS behalf are not able to do so. I almost want to cry every time I hear this. Perhaps one day the PostGIS development group can afford to be in the same room together.

When ogr_fdw came out, I was very excited to try ogr_fdw out on windows. To start with I used the default GDAL that we package with PostGIS, which is built under mingw-w64 (both 32-bit and 64-bit versions). Then I thought imagine how much more I can get by compiling with more drivers. A big one on my list was ODBC. Sadly this did not work out of the box under mingw-w64.

For windows folks who want to try out the ogr_fdw, I have binaries for PostgreSQL 9.4 FDWs which includes ogr_fdw and some of my other favorites. I do have the 9.3s as well, but haven't written up an article about them until I test them on my production instances. The links to those are the same as 9.4, just replace 94 with 93 in the download link. These include ODBC support which should allow you to query a lot of ODBC datasources.

Normally when I try to compile something that depends on ODBC using mingw, the configure scripts or make files are setup to assume you are using UnixODBC (which works but seems to use a different ODBC Manager and drags in all these extra dependencies). The way I normally fix this to use the native ODBC support is to replace all references to -lodbc in the configure script with -lodbc32. Yes even the windows 64-bit ODBC is called odbc32 but put in the system32 folder for 64-bit and SysWow64 for 32-bit. I imagine Microsoft came up with this confusing convention for backward compatibility. So I did that for GDAL, and to my horror that did not work.

What was the problem? GDAL had this extra dependency on odbcinst library. Although the odbcinst header is packaged with mingw-w64, there is no associated library. Searched thru my windows/system32 folder and my mingw-w64 libodbc*.a files and couldn't find such a thing. So I removed this thing from the configure script, and GDAL at least got past configure and happily started compiling, and then failed when linking. So I searched for the function it couldn't find, and discovered this was in a library called odbccp32. So to compile with odbc support, as noted in my ogr_fdw_build gist

1. edit the configure script in gdal source tree and replace all references to -lodbc with -lodbc32, and -lodbcinst with -lodbccp32
2. Then add in your configure --with-odbc=/mingw/${MINGHOST} where MINGHOST is set to x86_64-w64-mingw32 for the 64-bit mingw-w64 chain and i686-w64-mingw32 for the 32-bit mingw-w64 chain.

With this I was able to build libgdal with native windows ODBC support that just depends on the windows packaged odbc system dlls (so no extra dependencies). I tested by querying one of my clients SQL Server databases via ogr_fdw. One issue I ran into is it didn't handle SQL Server datetime right and kept on giving error ERROR: timestamp(62258384) precision must be between 0 and 6. So I had to change the ogr_fdw_info generated definition to bring in datetimes as varchar instead of timestamp and then just cast it to timestamp as part of my PostgreSQL query. This may be an idiosyncracy with how lengths have changed in windows ODBC that I have to patch or something with the ogr_fdw. I haven't tried the MSSpatial driver, but that suddenly showed up as an option after compiling with ODBC support. Also had some issues with UTF encoding which I was able to work thru by stripping high-byte characters in my PostgreSQL query.

One of my plans coming PostGIS 2.2 is to package a GDAL with more drivers - next on my list being SQLite family. I also need to figure out a way to have PostGIS gdal be swappable with the Visual C++ built ones. I can do that with for example curl and libxml, but the VC gdal last I looked seemed to expose weird named symbols so never hooks up. If I can get this going, then people can just swap out the GDAL we package with PostGIS with their own to get proprietary drivers like MrSID and so forth. Why don't I just try to build with it with VC++? Because then I've got to ship a VC++ runtime if I don't build with the same one EDB is using, and they use different flavors for each version of PostgreSQL. I also feel uncomfortable around Visual Studio for anything other than web development.

This past Monday we gave an introductory workshop on PostGIS. Our slides are here: PostGIS tutorial. Overall the event was very educational. I did have a butterfly feeling through out since we were the last to speak.

Talk slides

Talks were good, but most of the topics were not new to me so harder to appreciate. The talk I thought was the best was given by Steve Gifford on Geospatial on Mobile. Two things I liked about this talk was it was a topic I didn't know much about (developing native 3D globe map apps for iOS and Android) and Steve was a very funny speaker. That kind of funniness that looks unplanned and natural. I wish it had a bit more Android content in it though. The full list of talks and associated slides below.

• The State of the Geospatial Industry
• Using Application Templates to Jumpstart Mapping Sites
• Running A Scalable OSGeo Stack
• The NoSQL Geo Landscape
• Geospatial on Mobile

Workshops

The workshops I think were the best part of the event. I particularly enjoyed Andrew Hill's CartoDB talk (PostGIS without the pain but with all the fun) mostly because I haven't used CartoDb so first I've seen it in action. Being able to drag and drop a zip file from your local computer or a url of data from some site and have a table ready to query I thought was pretty cool. You could also schedule it to check for updates if it was a url to a shapefile zip or somethng. Of course being able to write raw PostGIS spatial queries and a map app in 5 minutes was pretty slick.

Ours came after, and unfortunately I think it was pretty dry and too technical for most GIS folks. Plus we hadn't told people to download the files before hand so next to impossible to follow along. We should have called it PostGIS with the pain and on a shoestring budget.

• Intro to QGIS
• Making maps for the web: Introduction to CartoDB
• Introduction to PostGIS

For loading OSM data in a format already ready for pgRouting queries, the two common tools I've seen used are osm2po and osm2pgrouting. Unfortunately osm2pgrouting has been for a long time a Unix only tool mostly because no one tried to compile it for windows or test it on windows to see if it works. So this means that windows users who wanted a quick road to pgRouting nirvana had to use osm2po. Turns out the osm2pgrouting code compiles fine on windows (at least under mingw-w64 chain) and seems to work fine, so we should start making it available for windows.

This is still experimental, but if you are a windows user (or have a pgRouting windows user friend) and want to kick the tires a bit, you'll find osm2pgrouting for windows (both 32-bit and 64-bit) binaries on the PostGIS windows download page in the Unreleased section.

I compiled osm2pgrouting (for pgRouting 2.0) against PostgreSQL 9.4 using the same mingw-w64 chain I use to build PostGIS and pgRouting for windows -- clumsily detailed in my gist shell scripts - . Though it depends on libpq, it seems to work fine if you use it against the PostgreSQL 9.3 libpq. So just need to copy the additional files (osm2pgrouting and expat) into your PostgreSQL bin folder and you are good to go loading data into a pgRouting enabled database. It is assumed you already have PostGIS 2.1+ and pgRouting 2.0 already installed which come bundled together as part of the PostGIS 2.1+ windows installers and also available on the PostGIS windows download page.

Which should you use: osm2pgrouting or osm2po?

In many ways osm2po is superior to osm2pgrouting and has had more time invested in it. It can handle larger files on smaller machines, can work with the OSM pbf format, and it supports both Unix like and windows platforms. That said osm2po is not absolutely superior to osm2pgrouting. osm2po is inferior in a couple of ways that are important to me.

• It's not open source, it's freeware -- which means you can't kick the code around and learn from it or inspect it. It also means you can't change it and contribute back your changes.
• The primary motive of osm2po is not as a loading tool for pgRouting. osm2po is a complete routing engine with webserver which doesn't even rely on PostgreSQL. That said, if the maintainer decides one day providing a pgRouting export is no longer beneficial to osm2po development path, or pgRouting devs need to tweak it a bit to work with newer pgRouting, it will be difficult.
• It's written in Java. This is my own personal dislike. Not that anything is wrong with Java, it's just sometimes a bit of a nuisance to get the VM going on a server I happen to get stuck on that I can't install anything I want. osm2pgrouting is much lighter weight not requiring a JVM, though it does require PostgreSQL,PostGIS, and pgRouting -- which you should have already.
• osm2po generates an intermediary sql file which you then have to load via psql. I prefer the direct load approach of osm2pgrouting over having a big-o sql file to contend with after.

That said osm2po is great in many ways, but alternatives are always nice to have. Only annoying thing with having alternatives is deciding which alternative is best for what you are doing.

This year's PostGIS day, I decided to celebrate with a little Conway's Game of Life fun inspired by Anita Graser's recent blog series Experiments with Game of Life. The path I chose to simulate the Game of life is a little different from Anita's. This variant exercises PostGIS 2.1+ raster mapalgebra and PostgreSQL 9.1+ recursive queries. Although you can do this with PostGIS 2.0, the map algebra syntax I am using is only supported in PostGIS 2.1+. My main disappointment is that because PostGIS does not yet support direct generation of animated gifs I had to settle for a comic strip I built unioning frames of rasters instead of motion picture. Hopefully some day my PostGIS animated gif dream will come true.

We'll be giving a PostGIS Introduction tutorial at AvidGeo Conference: LocationTech Tour Boston which will be held 8 December 2014 from 8:30 AM to 4:30 PM at:

Hack / Reduce
275 Third St
Cambridge, MA, 02142

Tickets are $40 for admission.

In addition to our PostGIS Intro tutorial, there is a great line-up of talks from visionaries such as Adena Schutzberg, talking: State of the Geospatial Industry and Raj Singh on the topic of The NoSQL Geo Landscape. Also check out other tutorials on CartoDb and QGIS.

FOSS4G NA 2015 (Free and Open Source Geospatial North America conference) will be running March 9th-12th 2015 In Burlingame, California (Hyatt, San Francisco Airport). Call for talks and workshops is now open and we've already got some submissions to review. Deadline for submission is November 17th 2014. Presenters including (workshop and those giving 35-minute standard talk) get to attend the 3-days general sessions for free. The conference consists of one day of workshops and three days of general sessions, with lightning talks on some or all of of the general session days.

Details about presentations here: CFP details. The FOSS4G NA 2015 is a collaborative event co-sponsored by OSGeo and LocationTech

FOSS4G NA 2015 will be co-hosted with EclipseCon North America 2015. Admission to FOSS4G NA 2015 gives you admission to EclipseCon NA 2015. So get 2 conferences for the price of one and go conference hopping.

For PostgreSQL and PostGIS folks there is a likely chance that there will be a PostgreSQL day at the same venue during the same period. So we might have a very special PostGIS / PostgreSQL day. I'll update as more details of that surface.

One of the new features coming in PostGIS 2.2 is ST_ClipByBox2D (thanks to Sandro Santilli's recent commits funded by CartoDB ). However to take advantage of it, you are going to need your PostGIS compiled with GEOS 3.5+ (very recent build) which has not been released yet. Windows folks, the PostGIS 2.2 9.3 and 9.4 experimental binaries are built with the latest GEOS 3.5 development branch, so you should be able to test this out with Winnie's experimental builds.

Since the dawn of PostGIS, PostGIS users have needed to mutilate their geometries in often painful and horrific ways. Why is ST_ClipByBox2D function useful, because its a much faster way of mutilating your geometries by a rectangular mold than using ST_Intersection. Why would you want to mutilate your geometries? There are many reasons, but I'll give you one: As your geometry approaches the area of your bounding box and as your bounding box size decreases, the more efficient your spatial index becomes. You can consider this article, Map dicing redux of the article I wrote (eons ago) - Map Dicing and other stuff which describes the same approach with much older technology. Though I will be using more or less the same dataset Massachusetts TOWNSSURVEY_POLYM (its newer so you can't really compare) and I tried to simplify my exercise a bit (not resorting to temp tables and such), my focus in this article will be to compare the speed between the new ST_ClipByBox2D approach and the old ST_Intersection approach. The spoiler for those who don't have the patience for this exercise is that using ST_ClipByBox2D at least on my sample data set on my puny Windows 7 64-bit desktop using PostgreSQL 9.4beta2 64-bit was about 4-5 times faster than using ST_Intersection. There was a downside to this speedup. With the ST_Intersection approach, I had no invalid polygons. In the case of ST_ClipByBox2D, I had one invalid polygon. So as noted in the docs, use with caution. We'd be very interested in hearing other people's experiences with it.

One other benefit that ST_ClipByBox2D has over ST_Intersection which I didn't test out is that although ST_Intersection doesn't work with invalid geometries, ST_ClipByBox2D can.

For these exercises, I'm going to also abuse the PostGIS raster function ST_Tile for geometry use, cause for some strange reason, we have no ST_Tile function for PostGIS geometry. For those who missed the improper use of ST_Tile for raster, refer to Waiting for PostGIS 2.1 - ST_Tile.

If you haven't signed up for our Postgres Open 2014 PostGIS tutorials, there are still a few tickets available, but time and space is running out.

For those of you who've already signed up for our tutorials in September 17th, 2014, keep an eye on this page: http://www.postgis.us/pgopen2014. Not much to see there yet, but we'll be posting content there before the tutorials as well as the examples so you can flaunt your mastery of cutting and pasting.

I've been thinking for a long time about creating an HTML based X3D viewer to visualize PostGIS 3D geometries. Now that I've started to document the new SFCGAL features, the need became more pressing. So I've created a very rudimentary one. You can check out the code on my github page https://github.com/robe2/postgis_x3d_viewer. I'm hoping to expand it in the future to allow people to pick materials for their geometries and also allow people to save the generated scene as a full X3D document.

It utilizes the X3DOM Javascript library and JQuery for injecting X3D objects into the scene. I'm really excited about X3DOM JS. It was fairly trivial to work with and integrate the ST_AsX3D function. Utilizing it will help me stress test the ST_AsX3D function and patch up the holes in it. When I developed the function I was only interested in 3D geometries and didn't think too much about collections either. So as a result the function is kinda broken when working with 2D or Geometry Collections.

Features and Limitations

• It has one scene and every query you do writes into the scene. This allows you to dump a bunch of objects on the same white board using different queries.
• Similar to Raster Viewer I created a while ago, it's got a query log and a color picker. The color picker allows you to color each query output differently. I hope to augment this to allow picking different materials too.
• Requires either .NET or PHP and of course a PostGIS 2+ enabled database
• Similar to the Web raster viewer I created, it can only handle a query that outputs a single geometry (geometry mode) or an X3D scene snippet (raw mode)

We'll be giving two training sessions on PostGIS at Postgres Open in Chicago September 17th 2014. First will be fairly introductory PostGIS Introduction: Geometry, Geography, and Geocoding and the second will be on more advanced concepts and tools PostGIS on caffeine: Raster, Topology, and pgRouting. Check out the other sessions at Postgres Open Conference Sssions Postgres Open 2014.

You might have noticed I haven't been blogging much lately. Hopefully I can get back to that and updating the BostonGIS tutorials soon. They are embarrassingly dated at this point and I want to add more like using the very cool TileMill, comparisons between Leaflet and OpenLayers, updated comparisons between SQL Server, PostGIS, and Oracle among other content updates. Most of the reason for sluggish pace is that writing has been taking up a good chunk of our time.

We are in final stages of two books, both 2nd editions:
PostGIS In Action, 2nd
, which covers predominantly PostGIS 2.1 and

PostgreSQL: Up and Running, 2nd edition
which covers mostly PostgreSQL 9.2 and 9.3.

Both are feature complete, but still undergoing polishing . Hopefully when all is said and done we'll have two books that make great companions for GIS folks working with PostGIS. Both are now available in draft form direct from the publishers and you'll get the final polished version when it comes out.

Interesting times ahead. The upcoming MySQL 5.7 promises to provide great enhancements in the spatial space. They've scrapped much of the existing GIS plumbing they have in place seen in prior 5.6 and are replacing muc of it with Boost Geometry. In addition R-Tree indexes will be available for InnoDb. Details: Why Boost.Geometry in MySQL? and Making use of Boost Geometry in MySQL. Along the roadmap (not for 5.7) they plan to have geography support as well and projections, 3D and the various output formats e.g GeoJSON currently available in PostGIS.

Just when I thought database spatial domain was getting boring. It will be interesting to see how the new MySQL Boost Geometry stacks against PostGIS GEOS /3D CGAL support. Might be time for a PostGIS/MySQL shoot-out soon.

Amazon recently added to its Relational database Services (RDS) family -- PostgreSQL 9.3. Not only that, to sweeten the pot, the PostgreSQL RDS comes pre-configured with PostGIS 2.1 among other great PostgreSQL extensions. Check out the details here PostgreSQL RDS.

The full news release is on Amazon's blog Amazon RDS for PostgreSQL - Now Available.

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).