PostgreSQL Views within GeoServer, GetFeatureInfo with Freemarker Templates, etc.

GeoServer now has the ability to consume database views from PostGIS, not just raw tables. I say it “now” has that ability– I think that came online with GeoServer 2.x series, but I’m just “now” starting to take advantage of it. You can also create views on the fly within GeoServer, but I prefer to apply the logic at the database level, just in case I use something instead of or supplemental to GeoServer in the future, the application logic is built in at the PostGIS/PostgreSQL level.

To this end, we have an infrastructure database that is maintained in Access, the records of which I’d like a copy of in PostgreSQL. Long term, we will probably move the records over to PostgreSQL and link them, but for now we’ll retain two copies– a master copy in the Access database and a slave copy in PostgreSQL.

The tables in question were exported from Access initially (to get their schema just right) through an ODBC connection, and then re-added as linked tables through an ODBC connection. I decided that synchronization should happen only when I’m the user, so I don’t slow down other users’ experiences, so the code for this is embedded in the main form and looks like this (just a little VBA):

 Private Sub Form_Close() 'Test for user If (Environ("username") = "smathermather") Then 'Disable user confirmation warnings DoCmd.SetWarnings False 'Remove all records in building tables DoCmd.RunSQL "DELETE FROM [public_Building Photos]" DoCmd.RunSQL "DELETE FROM [public_Building Statistics]" DoCmd.RunSQL "DELETE FROM [public_Building Utilities]" DoCmd.RunSQL "DELETE FROM [public_Master Building Inventory]" 'Reload all records in building tables DoCmd.RunSQL "INSERT INTO [public_Building Photos] SELECT [Building Photos].* FROM [Building Photos]" DoCmd.RunSQL "INSERT INTO [public_Building Statistics] SELECT [Building Statistics].* FROM [Building Statistics]" DoCmd.RunSQL "INSERT INTO [public_Building Utilities] SELECT [Building Utilities].* FROM [Building Utilities]" DoCmd.RunSQL "INSERT INTO [public_Master Building Inventory] SELECT [Master Building Inventory].* FROM [Master Building Inventory]" 'Reenable user confirmation warnings DoCmd.SetWarnings True End If End Sub 

Great, now the architecture database dumps into mine. It runs whenever I close the form, although this could be tied to a button or some other mechanism as well. Ideally, I suppose it would be simple enough to create a separate database with linked tables from each, have it run on start, and then schedule it to run periodically.

So, what to do next? Well this is a rich database with all sorts of information– we’ll need curate that information (is this an abuse of that word?). It has all sorts of building stats built in, a photo tied to the database etc.. This can all be curated with a database view.

 CREATE OR REPLACE VIEW public.cm_buildings AS SELECT build.name, build."Year Constructed", build."Square Footage", build."Construction Materials", build."Use Group", build."Architect", build."Structural Engineer", build."General Contractor", build."Site Location", build."Street Address", build."City", build."Phone", build."Capacity", build.link, build.the_geom, res."RES_LINK" AS vs_link FROM public."Building Reservable" res RIGHT JOIN ( SELECT photo."Building Name" AS name, stats."Year Constructed", stats."Square Footage", stats."Construction Materials", stats."Use Group", stats."Architect", stats."Structural Engineer", stats."General Contractor", master."Site Location", master."Street Address", master."City", master."Phone", master."Notations" AS "Capacity", regexp_replace(regexp_replace((string_to_array(photo."txtStockGraph", '\\'))[4], ' ', '_'), '.jpg', '') AS link, foot.the_geom FROM public."Master Building Inventory" master, public."Building Statistics" stats, public."Building Photos" photo, public.facilities_footprints_cm foot WHERE master."Building" = stats."Building" AND master."Building" = photo."Building Name" AND (master."Amenities" = 'BLD' OR master."Amenities" = 'CCF') AND master."Building Code" = foot."Building Code") build ON res.b_name_ar = build.name; ALTER TABLE public.cm_buildings OWNER TO postgres; GRANT ALL ON TABLE public.cm_buildings TO postgres; 

Now here’s the fun part. The paths to the images were several directories deep, etc., but I wanted to dump them all in a single directory. So the interesting part of the query is reformating the relative paths for the image locations and just grabbing the portion with the image name. In this case, they are all at the same depth in the directory structure, so I convert the string to an array using the backslash as my data separator, grab the 4th element, and then trim off the extension “.jpg” so I can manipulate the name further– like take advantage of some thumbnails I created with the same name but a “*.png” extension. That bit of (Postgres enhanced) SQL is as follows (it’s also in the above CREATE VIEW code, excerpted here for clarity):

 regexp_replace(regexp_replace((string_to_array(photo."txtStockGraph", '\\'))[4], ' ', '_'), '.jpg', '') AS link 

So, now we have a table with fields that can inform the user and serve as links to images available on the server. GeoServer allows for some really cool HTML templating with Freemarker Templates that we can take advantage of here. For a simple example, see the tutorial on GeoServer’s site.

For our example, we vamp a little (though not much):

<#--
Body section of the GetFeatureInfo template, it's provided with one feature collection, and
will be called multiple times if there are various feature collections
-->
<table>
  <tr>
<#list type.attributes as attribute>
  <#if !attribute.isGeometry>
    <#if attribute.name!="link">
    <#if attribute.name!="vs_link">
      <th>${attribute.name}</th>
   </#if>
   </#if>
  </#if>
</#list>
  </tr>

<#assign odd = false>
<#list features as feature>
  <#if odd>
    <tr>
  <#else>
    <tr>
  </#if>
  <#assign odd = !odd>
  <#list feature.attributes as attribute>
    <#if !attribute.isGeometry>
     <#if attribute.name!="link">
     <#if attribute.name!="vs_link">
       <td>${attribute.value}</td>
     </#if>
     </#if>
    </#if>
  </#list>
  <#if feature.link.value!="">
      <img src="arch/img/${feature.link.value}.png"><br><br/>
      <a href="www/arch/img/${feature.link.value}.jpg" target="_blank">Full Size Image</a> <br><br/>
  </#if>
  <#if feature.vs_link.value!="">
        <a href="http://external_link.com/${feature.vs_link.value}" target="_blank">Reservable</a> 
  </#if>
  </tr>
</#list>
</table>
<br/>

 

 

(Confession: I haven’t double checked my HTML yet to make sure that it’s perfect… ). Now we have to have a way for the user to get this info back with a GetFeatureInfo query, good old WMS standby (and essentially a subset of WFS features):

 var info = new OpenLayers.Control.WMSGetFeatureInfo({ drillDown: false, queryVisible: true, panMapIfOutOfView: false, url: GeoserverWMS, layerUrls: [GeowebcacheURL], eventListeners: { getfeatureinfo: function(event) { popup = new OpenLayers.Popup.FramedCloud( "popinfo", map.getLonLatFromPixel(event.xy), null, event.text, null, true ); map.addPopup(popup, true); } } }); map.addControl(info); info.activate(); 

And the results? Splendid:

Map of picnic area with GetFeatureInfo enabled on (brown) picnic structure.

Picnic area with GetFeatureInfo triggered

Next trick will be to start building out tabbed interfaces inside the GetFeatureInfo bubble to consolidate and streamline the information as we add the available info.