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."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.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 =; 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
<#list type.attributes as attribute>
  <#if !attribute.isGeometry>

<#assign odd = false>
<#list features as feature>
  <#if odd>
  <#assign odd = !odd>
  <#list feature.attributes as attribute>
    <#if !attribute.isGeometry>
      <img src="arch/img/${}.png"><br><br/>
      <a href="www/arch/img/${}.jpg" target="_blank">Full Size Image</a> <br><br/>
  <#if feature.vs_link.value!="">
        <a href="${feature.vs_link.value}" target="_blank">Reservable</a> 



(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.

5 thoughts on “PostgreSQL Views within GeoServer, GetFeatureInfo with Freemarker Templates, etc.

  1. Mmm, I should annotate a bit more for the uninitiated. The first language is VBA, the second SQL with some Postgre enhancements, the third is Freemarker Templates, all as described in the text, but the forth I didn’t describe– it’s javascript using the OpenLayers library.

  2. HI.
    I have a similar webapps which I would like to improve.-ie build in the ability to show photos.

    This shows the windparks for a particular region in germany. I would like to show a photo of the individual Turbine , but haven´t worked out how to do this. I was also going to use freemarker templates to do this. Ideally, the tabbed method would be better. tab1 = info, tab2 = photo. Did you every get it working?Do you have any Examples online?



    1. I haven’t gotten the tabs working, but probably the best way to do it is to modify the css that’s being used in serving the HTML. You can (probably) find this using FireBug. Then the HTML for the elements to go in the tab are abstracted from the representation in the css. It may take a few months before I get to this part of the project.

  3. Hi, i did something like this, but i have a problem. I have diferents images for one code, for example, code X-001 has three images. when i make the getfeatureinfo request, the result is code X-001… image1 / code X-001… image2 / code X-001… image3, how can i do code X-001… image1 image2 image3 / code x-002 image1 image2 /… thanks

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s