BostonGIS

A reminder, PGConfUS 2017 conference is just days away, and we'll be giving a training March 28th 2017, Jersey City, NJ at 1 PM. If you are coming, keep an eye on this page PGConf 2017 US Getting Stuff done with PostGIS materials.

If you haven't signed up already, there are still spots, make sure to buy your tickets at http://pgconf.us/conferences/2017#registration.

GeoHipster interview with me came out today. Covers how I stumbled into database programming and my work on PostGIS, PostgreSQL and pgRouting. Interview with Regina Obe

Leo's pgRouting : a Crash Course video made it thru great. Better than mine. Leo doesn't believe in slides, so this is all live demo stuff. The data he used in the video is part of our code/data download for pgRouting: A Practical Guide.

The videos for FOSS4G NA 2016 have started coming out. Recently Andrea Ross posted PostGIS Spatial Tricks talk video. I'm happy to say it looks pretty good and I didn't suck as badly as I worried I would. Thank you very much Andrea. Some talks unfortunately did not come thru. I'm hoping Leo's pgRouting : a Crash Course video made it thru okay as well, and will post that later if it does.

Only small little nit-picks is the first 2-5 minutes or so didn't make it thru and the blue colors on the slides got a little drowned out, but here are the slides if you need full resolution.

pgRouting 2.2.3 was released last week. Main change is this version now supports PostgreSQL 9.6. Many thanks to Vicky Vergara for working thru the issues with PostgreSQL 9.6 and getting it to work. Vicky has also been doing a good chunk of the coding (a lot of Boost refactoring and integrating more Boost features), testing, and documentation in pgRouting, osm2pgrouting, and QGIS pgRoutingLayer in general for pgRouting 2.1, 2.2, and upcoming 2.3. We are very indebted to her for her hard work.

If you are a windows user testing the waters of PostgreSQL 9.6beta1, we have pgRouting 2.2.3 binaries and PostGIS 2.3.0dev binaries at http://postgis.net/windows_downloads.

PostgreSQL 9.6beta1 came out yesterday. It is the first version of PostgreSQL that will have parallel query support and PostGIS 2.3 will be the first PostGIS to support parallelism in queries. Although work is not yet committed in PostGIS repo to support this, you can expect to see this soon (currently here - https://github.com/pramsey/postgis/tree/parallel , waiting for someone to you know who you are do something about it.)

Because of the newness of the parallelization feature, there are some caveats. As with all big things, we expect there to be a lot of bugs, and the more eyeballs on those and real workloads we've got hammering on them, the sweeter the PostGIS 2.3.0 and PostgreSQL 9.6 release will be.

Binaries for Windows users

For windows users, winnie the PostGIS windows buildbot is now building PostGIS for 9.6. Get PostgreSQL 9.6 binaries and installers from PostgreSQL 9.6beta1 for windows.

Once you have that, just copy the contents of the respective PostGIS 2.3 9.6 binaries listed here - http://postgis.net/windows_downloads/ into your install folder.

In the extras folder, you'll also find ogr_fdw foreign data wrapper latest development version which we covered extensively in FOSS4GNA2016 PostGIS Spatial Tricks. Talk also covered some new PostGIS 2.3.0 stuff.

We don't have pgRouting binaries available yet. pgRouting team is working out some compatibility issues with PostgreSQL 9.6. Once those are resolved, we will publish pgRouting binaries as well.

The FOSS4G NA 2016 in North Carolina early bird registration running May 2nd-5th (May 2nd is workshop day) will be ending this month. This means after this month, ticket prices will go up by as much as $200 depending on what kind of pass you need. If you are a speaker you should register now so we have a good head-count. Hotel prices will go up after this coming week too since conference room block will expire so all the more reason to register now.

On a related note, both Leo and I will be giving talks.

• PostGIS Spatial Tricks, will also cover some neat-highlights coming in PostGIS 2.3
• pgRouting: A crash course focused on pgRouting 2.1-2.2

There will be other PostGIS talks and PostgreSQL day (May 3rd) talks for PostGIS and PostgreSQL users.

We'd like to give a big thanks to Olivier Courtin and Oslandia for organizing this year's OSGeo Code Sprint in Paris. It was quite memorable and I walked away feeling energized.

Leo and I attended the Paris OSGeo Code Sprint for 1.5 days. I'm really glad we were able to attend at least part of it. It would have been nicer to attend all of it. Though we didn't get too much done while there, we did have some interesting conversations and learned about what others were doing. I walked out with a TO DO list, of which I'm happy to say I've accomplished some of now.

While we were there Leo spent time cleaning up my Mingw64 compile scripts and starting to test PostGIS against PostgreSQL 9.6 in preparation for parallelization testing of PostGIS 2.3 features specifically targetted for PostgreSQL 9.6.

Our upcoming book pgRouting: A PRACTICAL GUIDE (publisher LocatePress) just went on sale a couple of days ago. We currently have a little over 100 pages of content. We expect the final published book to weight in at a little over 250 pages.

pgRouting is a PostgreSQL extension that also utilizes PostGIS for solving routing problems. Routing problems involve application of costs to network paths as well as considerations of the parties involved such as kinds vehicles, people, packages etc. that will be carried across these networks. While routing is often applied to physical networks like roads and bike paths, the fundamentals of routing go beyond the physical. Spatial concepts such as edges and nodes are a convenient way of visualizing costs across even a logical network.

If you purchase now, you'll get the following benefits:

• Significant discount off the eBook final price. Currently 50% off
• Our current preview draft and updates to the draft as we update it
• Input via our discussion page of what you'd like to see covered. That could be specific functions or kinds of examples or just some clarity.

The focus of this book is pgRouting 2.1+. It's really hard to talk about pgRouting without giving some air time to PostGIS and PostgreSQL, which are some of the key foundations of pgRouting. As such expect to see a lot of talk of PostGIS and PostgreSQL in this book. Since pgRouting 2.0 is so different from pgRouting 2.1, we had to make a difficult decision to focus on the future rather than the past. The appendix does cover some content of how things were done in pgRouting 2.0 and how they changed in pgRouting 2.1+. You can expect the appendix discussion to be expanded. For the core of the book we've largely abandonned any mention of pgRouting 2.0 though pgRouting 2.1 is for the most part backward compatible with pgRouting 2.0 if you choose to be limited by the pgRouting 2.0 function signatures.

We've been pushing package maintainers to focus on releasing pgRouting 2.1 rather than sticking with pgRouting 2.0 and also encouraging maintainers who haven't carried pgRouting before, to start doing so. The PostGIS 2.2 windows bundle comes packaged with pgRouting 2.1 for example. Yes these are selfish reasons, but FOSS is inherently a selfish scratch your own itch kind of pursuit, and that's a good thing since all our common self-interests come together to make something very special that will change the world.

We are planning for the book to be released in another 3-4 months. I know we had planned to release around now, but as happens with every book we've written so far, as we get deeper into it, we realize, keeping it baking just a little longer is so much better than rushing thru the process and outputting a half-baked pie. Although pgRouting 2.2 is still being heavily worked on, the development version is almost feature complete and slated for release in next three to four months. As such we plan to cover some new features coming in pgRouting 2.2 and hope pgRouting 2.2 is out just before or around the same time as this book.

PostGIS 2.3 is still in its incubation, but I suspect it will be even more impressive than PostGIS 2.2. PostGIS 2.2 was long in the oven, and out came out a lot, but I'm hoping PostGIS 2.3 will have a shorter gestation period, but be just as, if not more impressive than PostGIS 2.2.

Before starting my "what's already there and what I hope to be there" list for PostGIS 2.3, I'll point out things that PostGIS 2.2 brought

PostGIS 2.2 offerings

Many itemized in PostGIS 2.3 new features

• KNN for geography and 3D geometries
• Geometry clustering functions
• True distance KNN for PostgreSQL 9.5+. If you are using 9.5, your KNN is not just bounding box distance, but true real distance.
• Faster topology, now a lot has been moved into C for better performance.
• Lots more 3D advanced functions via SFCGAL, SFCGAL now installable as extension.
• Lots of new geometry functions like geometry clustering, geometry clipping, subdivide, trajectory functions
• More raster stats and create overview table function without having to rely on raster2pgsql
• Tiger geocoder updated for Tiger 2015 data. Address standardizer now part of PostGIS.
• Last but not least, we added Dan Baston to our Core Contributor list. He gave us all the clustering functions in PostGIS 2.2, fixed lots of bugs and is already got more commits and proposed plans than anyone else for PostGIS 2.3. Go Dan go, and welcome.

The PostgreSQL 9.5 FDW api changed a little bit between 9.5beta2 release and 9.5rc1. This means that many FDWs may be broken at the moment. I patched up the OGR_FDW to be compatible with 9.5rc1 and Paul merged this into the ogr_fdw repo.

That said I have not repackaged the Windows PostGIS 2.2.0 9.5 bundle to have this updated library, so as such, if you upgraded to PostgreSQL 9.5rc1, your ogr_fdw will crash your backend if you are using the ones on Stackbuilder. However there are newer versions compiled against 9.5rc1 with the patch on winnie's 9.5 extras folder - http://winnie.postgis.net/download/windows/pg95/buildbot/extras.

We plan to do another release with this patched version (or newer patched version), for PostGIS 2.2.1, which should be out within the next week or so.

I also submitted a patch for ogr_fdw for the new IMPORT FOREIGN SCHEMA support which is available for 9.5+. This patch has not been accepted yet, but I'm hoping it will be in some shape or form before 9.5/PostGIS 2.2.1 release time. How to take advantage of this new feature is briefly described in Import Foreign Schema for ogr fdw for PostgreSQL 9.5. If any one on windows is in a rush to try this new feature, let me know and I'll spin up a binary from my ogr_fdw fork. The IMPORT FOREIGN SCHEMA feature is much more useful than I dreamed it would be for ogr_fdw so I'm very excited.

For those who are unaware. Free and Open Source 4 GIS North America (FOSS4G NA 2016) will be happening in Raleigh, NC May 2-5th 2016. We are accepting proprosals now. Early Bird proposal deadline is January 22nd, 2016. Which means if you submit early, you'll get a notification early before the February 8th, 2016 deadline.

Unlike last year where decisions were made entirely by the program committee, we will have users voting for presentations similar to what has been done for many FOSS4G international.

We are also looking for Sponsors, so if you'd like to sponsor the event in some way, please refer to our Sponsor Prospectus page.

The PostGIS 2.2.0 bundles for windows for PostgreSQL 9.3-9.4 (both 32/64-bit) and PostgreSQL 9.5beta1 64-bit are now on stackbuilder. I'm working out some compile issues for the PostgreSQL 9.5beta1 32-bit so don't have those ready yet. This includes pgRouting 2.1.0 and inaugural release of PostGIS SFCGAL extension, OGR FDW spatial foreign data wrapper, and pgPointCloud on windows.

I have also updated our popular BostonGIS - Part 1: Getting Started With PostGIS: An almost Idiot's Guide to utilize PostgreSQL 9.5beta1 and PostGIS 2.2. Still on our todo is to update the loader/query example and also the pgRouting tutorial for pgRouting 2.1.0. A ton has changed in pgRouting 2.1.0, so be sure to buy our book pgRouting: A Practical Guide which will have a preview release sale coming soon with first 4-5 draft chapters available and all on final publication all chapters (approximately 10-12).

Check out the PostGIS Bundle 2.2 extensions higlighted in yellow in pgAdmin extensions drop down

To take full advantage of what PostGIS 2.2 has to offer (notable true KNN distance for geometry, geography, and 3D geometries -- not just bounding box like is available for PostgreSQL 9.4 and below), you need to use PostgreSQL 9.5. So start testing out your apps on PostgreSQL 9.5 now so you will be ready when it hits release.

I missed Seoul, but I spotted these familiar looking family faces in Jody Garnett's picture deck.

Ko Nagase and Vicky Vergara: pgRouting from KYUHO SHIM's photos

Hiroaki Sengoku and Vicky Vergara: pgRouting

Vicky Vergara and Vincent Picavet: pgRouting and SFCGAL

Olivier Courtin: PostGIS and SFCGAL

Daniel Kastl: pgRouting

And last but not least - the Lone Ranger Paul Ramsey: PostGIS

pgRouting 2.1.0 got released today along with pgRouting 2.0.1 and osm2pgrouting 2.1.0-alpha1. Full details on Daniel's note: https://lists.osgeo.org/pipermail/pgrouting-users/2015-September/002148.html.

I want to especially thank Vicky Vergara for putting in all the long hours to make this happen. I am most excited about the changes in osm2pgrouting -- now support of bigints, speed improvement, and mutli-file loading much of which is Vicky and GSoc work. As a small contribution, I added schema support for osm2pgrouting 2.1

I also want to extend a BIG thank you to Ko Nagase for fixing a TRSP bug that only exhibited on 64-bit windows (fixed in pgRouting 2.0.1 and pgRotuing 2.1.0) as well as his fixing of other windows and boost related compile issues.

For windows users excited to try all these new features, you can get them - on PostGIS windows download page (for PostgreSQL 9.2-9.4) : http://postgis.net/windows_downloads

We plan to package osm2pgrouting 2.1.0 (latest) and pgRouting 2.1.0 as part of the upcoming PostGIS 2.2 Windows Application builder bundle install for PostgreSQL 9.3-9.5. We would very much appreciate people testing these packages out now so no surprises come official package time.

If you already have pgRouting 2.0.0 installed in your database, you can upgrade with :

ALTER EXTENSION pgrouting UPDATE TO "2.1.0";

If you were experimenting with a beta or RC pgrouting, you'll need to drop the extension and readd

DROP EXTENSION pgrouting;
CREATE EXTENSION pgrouting VERSION "2.1.0";

To confirm you are running the latest and greatest, run this query:

SELECT * 
FROM pgr_version();

Which should output:

 version |       tag       | build |  hash   | branch | boost
---------+-----------------+-------+---------+--------+--------
 2.1.0   | pgrouting-2.1.0 | 1     | b38118a | master | 1.59.0
(1 row)