PostGIS Training: Creating Overlays

PostGIS training At Boundless, we recognize that software is only as good as the problems it can be used to solve. That's why we don't just develop the best open source spatial software, we also teach you how to use it with supporttraining, and workshops directly from the experts.

One question that comes up often during our PostGIS training is "how do I do an overlay?" The terminology can vary: sometimes they call the operation a "union" sometimes an "intersect". What they mean is, "can you turn a collection of overlapping polygons into a collection of non-overlapping polygons that retain information about the overlapping polygons that formed them?"


So an overlapping set of three circles becomes a non-overlapping set of 7 polygons. screenshot_69

Calculating the overlapping parts of a pair of shapes is easy, using the ST_Intersection() function in PostGIS, but that only works for pairs, and doesn't capture the areas that have no overlaps at all. How can we handle multiple overlaps and get out a polygon set that covers 100% of the area of the input sets? By taking the polygon geometry apart into lines, and then building new polygons back up. Let's construct a synthetic example: first, generate a collection of random points, using a Gaussian distribution, so there's more overlap in the middle. The crazy math in the SQL below just converts the uniform random numbers from the random() function into normally distributed numbers.

WITH rands AS (
  SELECT generate_series as id, random() AS u1, random() AS u2 FROM generate_series(1,100)
    50 * sqrt(-2 * ln(u1)) * cos(2*pi()*u2),
    50 * sqrt(-2 * ln(u1)) * sin(2*pi()*u2)),4326) AS geom
FROM rands;

The result looks like this: screenshot_70 Now, we turn the points into circles, big enough to have overlaps.

SELECT id, ST_Buffer(geom, 10) AS geom FROM pts;

Which looks like this: screenshot_71 Now it's time to take the polygons apart. In this case we'll take the exterior ring of the circles, using ST_ExteriorRing(). If we were dealing with complex polygons with holes, we'd have to use ST_DumpRings(). Once we have the rings, we want to make sure that everywhere rings cross the lines are broken, so that no lines cross, they only touch at their end points. We do that with the ST_Union() function.

CREATE TABLE boundaries AS
SELECT ST_Union(ST_ExteriorRing(geom)) AS geom
FROM circles;

What comes out is just lines, but with end points at every crossing. screenshot_72 Now that we have noded lines, we can collect them into a multi-linestring and feed them to ST_Polygonize() to generate polygons. The polygons come out as one big multi-polygon, so we'll use ST_Dump() to convert it into a table with one row per polygon.

SELECT nextval('polyseq') AS id, (ST_Dump(ST_Polygonize(geom))).geom AS geom
FROM boundaries;

Now we have a set of polygons with no overlaps, only one polygon per area. screenshot_73 So, how do we figure out how many overlaps contributed to each incoming polygon? We can join the centroids of the new small polygons with the set of original circles, and calculate how many circles contain each centroid point. screenshot_74 A spatial join will allow us to calculate the number of overlaps.

UPDATE POLYS set count = p.count
  SELECT count(*) AS count, AS id  
  FROM polys p 
  JOIN circles c 
  ON ST_Contains(c.geom, ST_PointOnSurface(p.geom)) 
) AS p

That's it! Now we have a single coverage of the area, where each polygon knows how much overlap contributed to it. Ironically, when visualized using the coverage count as a variable in the color ramp, it looks a lot like the original image, which was created with a simple transparency effect. However, the point here is that we've created new data, in the count attribute of the new polygon layer. screenshot_75 The same decompose-and-rebuild-and-join-centroids trick can be used to overlay all kinds of features, and to carry over attributes from the original input data, achieving the classic "GIS overlay" workflow. Happy geometry mashing!

Want to learn more? Try our Introduction to PostGIS online training course!

Juan's Thoughts from NSGIC

Last week, a few of us here at Boundless attended the National States Geographic Information Council (NSGIC) Annual Conference in beautiful Charleston, SC. NSGIC's mission is to promote statewide spatial coordination activities in all states and to be an effective advocate for states on national policy and initiatives. It’s an important mission and we were proud to be Gold Sponsors this year.

NSGIC 2014 Annual Conference

What I Presented

I had the privilege to deliver a well-received keynote presentation on Monday evening entitled "Open Source Geospatial Software: Current adoptions and future technologies solving today’s and tomorrow's challenges". While that sounds long and complicated, I basically went over the opportunities unlocked by adopting open source spatial technology and how organizations at the local, state or federal level can take advantage of them. The spatial software industry is one of the few where the open source tsunami hasn't yet fully revolutionized the way we work. However, it is happening and I'm glad to finally see increasing diversity in our ecosystem of available tools. As with most ecosystems, this will bring about a healthier long-term outcome.

What I Learned

Instead of talking about what I said, I would like to share a bit of what I heard. NSGIC puts on a great program and one of the best things about this conference is seeing first-hand the challenges that states face. They are usually strikingly similar across states, both in concept and timing, which is a testament to the level of coordination that happens among states, local governments and, in many instances, the Federal Government (which was represented by agencies such as NOAA and USDOT).

“No single source of truth.”

Throughout all the high-quality presentations, I was very interested to see how most states have what I call the "data roll-up problem," where different jurisdictions (i.e. counties) often produce and maintain the data but states collect it as part of the "authoritative" source of information. The biggest challenges to producing statewide data that is complete and comprehensive are usually non-technical, and we heard great stories about how states are breaking down barriers when it comes to accessing data.  Guess what? Charging for data usually ends up being more expensive than implementing an open data policy! When it comes to the technical details, I believe Boundless will have a lot to say in the next year with the work we have been doing on distributed versioning of spatial data to help address some of these challenges.

“Don’t fight the web.”

I was also pleasantly surprised to see how most states have a very modern approach to their services. They realize the importance of the web, and have an IT approach for delivering content and services to their users (which thanks to some very advanced open data policies, in many cases includes just about everybody). We even got to hear novel approaches to geocoding, like using Natural Language Processing (NLP) to extract places from documents so they can be placed on a map (with open source libraries, of course). Imagine having a map of all your legislation!

See you in Annapolis!

At a time when budgets are tight, it's refreshing to witness local, state and federal agencies resort to innovative and imaginative approaches to solving problems. At Boundless, we are big proponents of efficiency through the use of open source spatial software and we are happy to bring our point of view into the conversation while realizing that every organization is at a different stage in adopting open source models and software. We hope to further this discussion at the NSGIC mid-year conference in Annapolis in February of 2015.

Ann's Perspective on FOSS4G 2014

FOSS4G 2014

As Paul Ramsey mentioned, last week almost 900 members of the open source geospatial software community came together in Portland at FOSS4G 2014. We were proud to sponsor, privileged to participate in over nine presentations and nine workshops, excited about our new QGIS offerings, and pleased to see even greater interest in our PostGIS,  GeoServer, and OpenLayers offerings during the conference.

The power of Spatial IT resonated throughout the conference as participants were able to highlight their projects and unique use cases of open source geospatial tools to solve a wide variety of technical and business problems.

Highlights from our sessions

Paul conducted a very useful session on how to convince managers to embrace PostGIS and replace proprietary database offerings. The blend of technical and business elements in Paul's talk spoke about the need to not only use the best available software, but also the continuing need to educate organizations about the value derived from using open source software.

Jody Garnett helped review the new and noteworthy features in GeoServer introduced over the past year. Since GeoServer is part of the core of OpenGeo Suite, it’s always promising to see new support for new standards like WCS 2.0 and new formats like GeoPackage and NetCDF become part of the software.

Andreas Hocevar helped describe what’s new and how to get started with OpenLayers 3. His talk provided an overview from a user's perspective and covered common use cases and new features to help developers get comfortable with integrating spatial information into web applications.

The LocationTech events highlighted the ability of the community to truly embrace cooperation in the interest of advancing common projects and common goals.

FOSS4G is about community

A true sense of community, however, was the best part of the conference. There was a great feeling of camaraderie throughout the weeklong event. All of the presenters and booth participants, regardless of affiliation, were joined together by the common cause of promoting the value of and expanding the use of open source tools to reduce the cost of legacy GIS implementations and escape the monolithic, proprietary software options that dominate the industry.

Nothing drove the open source message home further for me than the train ride I took from the magnificent World Forestry Center back to my hotel after the gala on Thursday evening. Seated on the train, my badge now tucked in my purse, I was relaxing on the quiet train and became captivated by a conversation amongst three gentlemen seated just a few rows away. The three were discussing their week at FOSS4G, which seemed a very positive experience for all. And then, one of the men observed, "Open source is really becoming a standard in GIS. Even Esri was in attendance at FOSS4G."

Well, I suppose that is just the point for the community — if you can't beat 'em, join 'em! Whether Esri is genuine in their open source support or not remains to be seen but what is evident is they can no longer afford to ignore open source. Open source is a part of the geospatial software ecosystem and will continue to grow and provide more affordable opportunities for people to expand the technology into critical business and IT applications throughout their organizations.

I’m already planning for FOSS4G 2015 in Seoul!

Paul's Perspective on FOSS4G 2014

FOSS4G 2014

The world of open source geospatial gathered itself together again last week, as Boundless joined almost 900 developers, users and managers at FOSS4G 2014 in Portland, Oregon. This is the ninth such gathering I've attended, and they all have a special local flavour: in this case the flavour of locally-sourced ingredients and micro-brewed beer.

Exciting Technology

Each year also has it's own favored technology, the topic that packs rooms and spills attendees into the halls: in the early days, MapServer and then PostGIS; Java technology like GeoServer and GeoTools; the first open source slippy maps like OpenLayers; new server technologies like Node.js. This year the topics that I observed drawing in the big crowds were vector tiles and drones.

Vector tiles are close to home, a technology I understand and have experimented with, and if PostGIS, GeoServer and OpenLayers are not producing and consuming vector tiles within a year I will be surprised. There's lots of demand for the technology, a solid use case in mobile clients, and a clear implementation path forwards.

Drones, on the other hand, represent a whole new opportunity for open source since, like open source software, cheap drones and sensors democratize information about location. Cheap tools and open software are a great match. Aaron Racicot shared his experience building a quadrocopter for image acquisition for under $700, and Stephen Mather described how he processes drone photos from imagery into a 3D point cloud and textured terrain mesh using open source tools. From here it's not hard to imagine a future where a digital model of a city could be automatically and continuously updated from the cameras of hundreds of personal drones swooping around.

New Ideas

In talks there were some great examples of Spatial IT: building tools that integrate spatial thinking with existing IT architectures and data flows. For example, the improved MapFish Printing module, which may find its way into OpenGeo Suite over the next year, is centered around producing reports (which might contain maps) rather than producing maps (that may have some reporting).

Similarly, practical and incremental transformation stories from proprietary to open source were common. Sara Safavi presented basic case studies and patterns for integrating open source into proprietary shops: web first, database first, or desktop first, but never all at once. Karl-Magnus Jonsson shared the story of his city's move from 100% proprietary to 100% open source over several years of gradual transformation: first the web, then the database, and finally then the desktop.

Growing Community

On the show floor was the usual collection of companies like ourselves supporting particular open source projects for enterprises, but a few companies in different but important categories: Amazon and OpenShift, promoting the deployment of open source geospatial systems on their platforms; and PlanetLabs, talking about their new sources of earth imaging. As the open source economy grows, the number of companies that generate value indirectly from and for open source is growing along with it.

Next year FOSS4G will be in Seoul, South Korea, which will give international attendees a great opportunity to learn what is happening in Asia in general and the Korean technosphere in particular. I'm anticipating seeing some truly outstanding work that would otherwise be very hard to discover, it's going to be a must-attend event.

Thanks to the organizers in Portland for a seamless and enjoyable event! And thanks for putting a bird on it!

The Spatial IT Job Board

CareersThose of you who’ve seen Paul Ramsey’s Spatial IT and the Spatial Web presentation or read Michael Terner's blog piece know that we see a great future for software developers and IT professionals with an interest in spatial technology. Below are several job openings we’ve seen in the past month.

Job Listings

There are plenty of opportunities for Spatial IT professionals but if we missed any relevant positions please contact us and we’ll be sure to include them in future job board posts.

Announcing QGIS support offerings!


Over the past two years, our involvement with QGIS has steadily grown. We hired Victor Olaya, participated in community events such as the Inaugural US QGIS  Users Group meeting and the C Tribe Sprint, and developed easy-to-install QGIS packages and OpenGeo Suite integration. Today, we're excited to announce new QGIS support offerings just in time for FOSS4G!

When we introduced our plugin for managing data and publishing layers from QGIS, we knew it would be a powerful interface for desktop GIS analysts wanting to configure OpenGeo Suite from the desktop. The plugin provides greater integration between QGIS and components such as PostGIS and GeoServer, facilitating the management and analysis of spatial data, deployment of web services, and development of web applications using OpenGeo Suite. This meets the needs of experienced GIS analysts and the growing number of developers for whom GIS is one part of a larger Spatial IT challenge.

Now offering QGIS support!

Our new QGIS support options start at $150 per seat and are available to OpenGeo Suite Enterprise customers seeking support for desktop GIS users. Download our hassle-free QGIS packages for Windows and OSX and contact us to get started.

Stay tuned for several QGIS training offerings from Boundless in the future.

QGIS and OpenGeo Explorer plugin

Learn more

Read these previous posts for more about QGIS and our efforts around the project:

Attending FOSS4G? Try our QGIS for Analysts workshop on Tuesday afternoon!

Boundless is heading to Portland for FOSS4G 2014!

FOSS4G 2014Next week, several members of the Boundless team are heading to Portland for the annual FOSS4G conference. We look forward to this conference every year as it's the best place to exchange ideas about open source spatial software with our customers, partners, and friends. We're proud to be sponsors and be participating in at least nine presentations and nine workshops — even more than last year!

Come by booth #16 to meet our experts and learn about the newest version of OpenGeo Suite and other tools we’re working on. Don't forget to register for field trips and check out the social calendar as well!

Here’s a list of many of the events we’re involved with:

DateEventStart TimeSpeaker/Attending
9/8/2014OpenLayers 3: First Contact8:00Andreas Hocevar with Marc Jansen, Éric Lemoine, and Tim Schaub
Building Apps with OpenLayers13:00Andreas Hocevar
Introduction to PostGIS13:00Paul Ramsey
9/9/2014GeoTools DataStore Workshop8:00Jody Garnett and Eva Shon with Tom Kunicki
Introduction to GeoServer8:00Mike Pumphrey
Versioning Spatial Data with GeoGig (formerly GeoGit)8:00Benjamin Trigona-Harany
GeoServer Cartography and Styling13:00Jody Garnett, Eva Shon
GeoWebCache in Production13:00Mike Pumphrey
QGIS for Analysts13:00Benjamin Trigona-Harany
9/10/2014GeoServer Feature Frenzy10:00Jody Garnett and Justin Deoliveira with Andrea Aime and others
The Manager’s Guide to PostGIS11:00Paul Ramsey
GeoScript: A Geospatial Swiss Army Knife13:00Justin Deoliveira
OSGeo Incubation13:30Jody Garnett
Getting Started with OpenLayers 315:00Andreas Hocevar with Tim Schaub
9/11/2014LocationTech Projects13:30Jody Garnett and Justin Deoliveira with Andrew Ross, Rob Emanuele, and others
MapStory: The next plateau16:00Ian Schneider
9/12/2014State of GeoServer and GeoTools10:00Justin Deoliveira and Jody Garnett with Andrea Aime, Martin Davis, and others
PostGIS Feature Frenzy13:00Paul Ramsey

OpenGeo Suite 4.1.1 Released!

We've been hard at work fixing bugs, and polishing OpenGeo Suite, so we're proud to announce the release of OpenGeo Suite 4.1.1!  This release focuses exclusively on performance and stability updates.

For those who don’t regularly follow our blog, the OpenGeo Suite 4.1 release included many new features and improvements:

Try it!

Download 4.1.1 and try our census map tutorial or heat map tutorial to learn more. Details about this release are included in the release notes and, as always, we strongly advise you to read the upgrade instructions and backup your data before installing.

OpenLayers 3.0 Released!

OpenLayers 3 has been a long time in the making but we're proud to announce that the project is ready for release! This release includes a ton of new features such as an easier to use API as well as improved vector rendering and image manipulation.

While we've confidently supported OpenLayers 3 in production since the OpenGeo Suite 4.0 release and have long offered professional services for developers looking to build web applications, we hope this announcement marks a significant step forward in adoption of the library.

Check out some of our previous blog posts to learn more about using OpenLayers 3:

And don't forget to check out the project website for some great documentation and examples.

OL3 vector rendering

Announcing the OpenGeo Suite App Contest!

OpenGeo Suite

Boundless wants to know how you’ve been using OpenGeo Suite! We already know what customers such as NYC DoITT, FCCSan Jose Water Company, and Howard County are building with our software but we are launching an app contest where developers, like you, can show off applications that use components of OpenGeo Suite.

Winners will be promoted on our website and will receive free access to our online training offerings. We know you are making some awesome real-world applications, and we want everyone to know because, in the end, OpenGeo Suite is only as great as the apps it helps power.

FCC National Broadband Map

The process is simple. All you have to do is provide the following information through our submission form:

  1. Describe what you’ve built. What’s its purpose?

  2. What technology does it use, including specific OpenGeo Suite components?

  3. Why did you choose OpenGeo Suite?

  4. What are your future plans for this app?

  5. How did you architect and build the app?

  6. Is this available to the public, if so, what is the URL?

  7. Is this your first time using OpenGeo Suite or any of its components?

You can enter the contest starting today and we will be taking submissions through September 13th, the last day of FOSS4G. Throughout the contest, we will promote the best submissions on our blog and through our Twitter account. We’ll announce semi-finalists the week after FOSS4G and then welcome the community to cast a vote for the finalists, which we will announce at the GeoNYC Meetup in October.

We can’t wait to see, share, and celebrate what you’ve been creating with OpenGeo Suite so submit your app!