Using PostgreSQL Views in zigGIS 2.0.2

When we mapped out the way ahead for zigGIS, we knew we wanted to lay the groundwork to add support for SQL Server 2008 and possibly other database platforms. The way zigGIS was originally constructed, it used the PostGIS geometry_columns table to discover available spatial tables. This has worked well but we came to realize that there are numerous workflows that can be employed to create spatial tables in PostGIS and many of these don’t necessarily register in the geometry_columns table. For example, you can use a standard CREATE TABLE to create a table that includes a geometry column. In this case, the table will not be registered but will still be a valid spatial table. The same can be said for views.

SQL Server 2008 doesn’t have any equivalent to the geometry_columns table and most likely won’t. When considering extending zigGIS to support SQL Server, we were basically left with two paths:

1. Create tables analagous to the PostGIS geometry_columns and spatial_ref_sys tables (and possibly other supporting structures) inside a zigGIS schema or:
2. Eliminate the need to have your data listed in geometry_columns and have zigGIS “discover” your spatial data by examining the schema information.

Since our preference is always to keep zigGIS as light as possible, we went with the latter. This approach was implemented in version 2.0.2. While we haven’t extended to SQL Server yet, zigGIS does provide more flexibility for user to implement their own data workflow. For me, one of the biggest benefits is the ability to create a view in PostgreSQL and add it into ArcMap as a layer.

For example, I have a spatial table called “states” which was imported from a shapefile of the US states. Additionally, I have a table called “us_states_pop” that contains population estimates from the US Census Bureau. Using the following SQL, I created a view that joined the two tables based upon the state abbreviation and displayed the 2007 and 2008 poplation estimates.

[sourcecode language=”sql”]
SELECT s.gid, s.shape, s.state_name, s.state_abbr, p.e20080701, p.e20070701
FROM states s
JOIN us_states_pop p ON s.state_abbr::text = p.abbr::text;

I saved this view with the name “vwStatePop”. Your view must return a geometry column and an integer ID column (shape and gid in this case). Next, I started up ArcMap and selected my view like so:

zigGIS 2.0.2 layer selector (click to enlarge)
zigGIS 2.0.2 layer selector (click to enlarge)

I simply selected the view in the dialog, then used the dropdowns to set the correct values for the ID column, geometry column and geometry type and then clicked “OK”. The view data then displayed in ArcMap as any other layer like so:

PostgreSQL/PostGIS spatial view displayed in ArcMap. (click to enlarge)
PostgreSQL/PostGIS spatial view displayed in ArcMap. (click to enlarge)

This was a fairly simple example but you can get pretty sophisticated with your views. I like the fact that I just need to create a standard PostgreSQL view in the same way that I always do and I can use it in ArcMap right away. The ability to use views in zigGIS is the enhancement I am most excited about in version 2.0.2.

  • markb

    Do plans exist to make Views editable, at least with Instead Of Triggers anyway?

    Since things are somewhat more database agnostic now, how is potential support of SpatiaLite shaping up?

    By the way, great work guys.

  • I will need to discuss editable views with Abe. I know the topic has come up but I don’t think it’ll make it into 2.0.2. Our approach with zigGIS has been to try to change the database as little as possible.

    SpatiaLite? Who said anything about SpatiaLite? 😉

    Actually, as I’ve gotten more comfortable with it, the thought has crossed my mind. I would expect us to abstract our code base a little more and implement SQL first before turning our attention to other platforms.

    Thank you!