Entries by Regina Obe

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)

pgRouting Beta 2.1.0 released details: https://lists.osgeo.org/pipermail/pgrouting-dev/2015-August/001569.html

The pgRouting team would like to announce:
 
      pgRouting 2.1.0 Beta Release
 
is ready for review and testing.

NEWS:
https://github.com/pgRouting/pgrouting/blob/develop/NEWS
 
Documentation:
http://docs.pgrouting.org/
 
Download:
https://github.com/pgRouting/pgrouting/releases/tag/pgrouting-2.1.0-beta
 
Source:
https://github.com/pgRouting/pgrouting/tree/develop
 
Closed Issues
https://github.com/pgRouting/pgrouting/issues?q=is%3Aissue+milestone%3A%22Release+2.1.0%22+is%3Aclosed

 
We are very excited about this release and all the new features that are
being made available. 
 
We hope the community will join in and support all the effort to get the
release this far with additional testing and feedback.
 
Best regards,
   The pgRouting Team

Windows binaries

Windows experimental binaries for both PostGIS 2.2.0dev and pgRouting Beta 1 available at http://postgis.net/windows_downloads

If you are in Boston or wanna visit Boston for a FOSS4G conference, please join our effort in bringing the FOSS4G 2017 international conference to Boston.

For details check out Help Bring FOSS4G to Boston. Since we are Bostonians we'll be submitting talks and workshops for PostGIS and pgRouting if the event is hosted in Boston.

Just got our shipment of PostGIS In Action 2nd Edition. Here is one here.

It's a wee bit fatter than the First Edition (just by about 100 pages). I'm really surprised the page count didn't go over 600 pages given the large additional ground of coverage this edition has. This edition covers a lot more raster than the first edition and has chapters dedicated to PostGIS topology and PostGIS tiger geocoder.

We got back from PostgreSQL Conf 2015 which was in New York City. It's probably the most educational conference we've been to in a while and mostly because we stayed thru the whole conference. One of the key take aways from the conference was Magnus Hagander's 9.5 showcase of features slides at http://www.hagander.net/talks/postgresql95.pdf. There are 2 things I am looking for which are along the lines of parallelization and the 3rd I'm not sure about is of much benefit but could be.

Inheritance support for Foreign Tables (aka sharding with FDWs)

The thing I was most excited about was Foreign Table inheritance (and BTW you can add check constraints to Foreign tables) which means you can use the constraint exclusion feature of PostgreSQL to query across federated PostgreSQL servers. The thing that excited me about this, is I had a perfect use case I was itching to try -- which I have on my list. The PostGIS Tiger geocoder utilizes an inheritance hierarchy to partition tables. When I took on maintainance of the geocoder and changed it to use a table inheritance instead of single table structure, I was not so much concerned about speed as much as being able to easily spit out a backup of a set of states to create a smaller database I could hand-off to a user or specific client. Since it is already naturally sharded and has that built-in its query logic, it would seem a perfect fit to test if parallelization could work in this fashion and improve speed for similar setups. Michael Paquier has a simple example of this feature in PostgreSQL 9.5 feature highlight: Scale-out with Foreign Tables now part of Inheritance Trees

Parallel sequential scans

This one isn't quite in yet, but is in the queue of likely candidates. So we don't have parallalization for all query methods, but this is the first step to getting there and one that doesn't require any changes on your part to take advantage of.

GiST index only scan

Big gotcha here is that it is only currently supported for built in PostgreSQL box and point classes. The bigger question is, is it really worthwhile to implement in PostGIS 2.2 for PostGIS types. I have to say this was a bit of a yawn for me, but I point it out because it was the only time Magnus mentioned PostGIS - "PostGIS folks in crowd, can PostGIS use this?". I doubt it will provide much benefit since the GisT we have just contains the box and requires a RECHECK anyway. Conceivably it could be useful for counting points or as a very fuzzy estimate of count of objects in a window, but that's all I can think it could be useful for. How much effort to build in the logic in PostGIS 2.2 I don't know. I leave that as an excercise for someone smarter than me.

There will be two PostGIS workshops at FOSS4G NA 2015 on March 9th. Signup if you haven't already.

• PostGIS Up and Running 9AM - 12 PM. This is an introductory workshop where we'll cover the basics of configuring PostGIS and using the PostGIS geometry and geography types. We also plan to demonstrate some new features coming in PostGIS 2.2, particularly of the 3D kind. If time permitting, we'll do a quick coverage of pgRouting as well.

  Someone asked on IRC if we will be handing out certificates of completion to folks who complete the workshop. Some people need this because they are allowed to attend workshops on company time, but not conferences. The thought hadn't crossed our mind, but we like the idea a lot. So yes you can have a certificate if you stay thru the whole session complete with Regina and Leo's seal of approval. We might even have some door prizes.

• Advanced spatial analysis with PostGIS. Pierre Racine will be leading this workshop. Expect to be blown away by images of rasters dancing on legs of geometries. He'll also have some other cool advanced spatial analysis stuff beyond raster. Expect a lot of geometry processing tricks in this one.

Sadly I think our PostGIS In Action 2ed is going to be released a little after conference time and probably won't be ready until mid March, so probably just a wee bit too late for FOSS4G NA 2015, but just in time for PGCon.US New York 2015 March 25th-26th . Final book proofing is like getting our teeth pulled. I really hope it's worth the wait. We'll have coupons but no book. We will have some copies of our PostgreSQL: Up and Running 2nd Edition available though. If you've already bought one of our books and want it autographed, bring it along on your trip.