Smathermather's Weblog

Remote Sensing, GIS, Ecology, and Oddball Techniques

Archive for August, 2011


Posted by smathermather on August 25, 2011

Today, I’ll just link to a cool online tool I found for slimming down the OpenLayers library:

Getting dangerously close to releasing our in-house application. Time to slim down… .

Posted in OpenLayers | Tagged: , | Leave a Comment »

Debian Configuration– Tomcat on Boot, revision

Posted by smathermather on August 23, 2011

I revised my startup script for Tomcat to use a non-privileged user for security reasons. I used the following page as my resource:

# /etc/init.d/tomcat6: start Tomcat6 server.

test -f /opt/apache-tomcat-6.0.32/bin/ || exit 0


case "$1" in
start) export JAVA_HOME=/opt/jre1.6.0_26/
logger -t Tomcat6 "Starting Tomcat 6..."
exec su - tomcat -c /opt/apache-tomcat-6.0.32/bin/ | logger -t Tomcat6
stop) export JAVA_HOME=/opt/jre1.6.0_26/
logger -t Tomcat6 "Shutting down Tomcat 6..."
exec su - tomcat -c /opt/apache-tomcat-6.0.32/bin/ | logger -t Tomcat6
*) echo "Usage: /etc/init.d/tomcat6 {start|stop}"
exit 2
exit 0

Now, perhaps what I should be doing is modifying the skeleton file that’s in my /etc/init.d directory for a truly professional look/setup… .

Posted in Other | Tagged: , , , , | Leave a Comment »

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

Posted by smathermather on August 20, 2011

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.

Posted in Database, GeoServer, OpenLayers, PostGIS, SQL | Tagged: , , , , , , , , , , | 5 Comments »

FOSS4G 2011 Denver– Ideal schedule

Posted by smathermather on August 19, 2011

I spent a few hours winnowing down my list of desired talks at FOSS4G and came up with the following list. Path dependence (i.e. technologies related to the ones I’m already deploying) were at the top of this list, but I do hope to expand my knowledge of all the options as well. For almost every slot, there were anywhere from 2 to 4 presentations that really excite me. And finally, one of the most exciting (to me) presentations is the last in this list (Lifemapper Moves Forward: Bringing Together Geospatial and Biodiversity Informatics Tools to Save the World)– which I’ll miss because I’ll be flying out at that time. Alas, I’ll have to download the presentation when I get back to Ohio.




Sept 13th

Developing OGC Compliant Web Applications With GeoExt

Andreas Hocevar

Sept 14th

State of PostGIS

Paul Ramsey

PostGIS 2.0, the new stuff

Regina Obe, Leo Hsu

Functional Coverages

Gennadii Donchyts, Deltares Fedor Baart

Tutorial: GeoServer Scripting with Python and RESTConfig

David Winslow

Introducing GXP: Webmapping made easy

Bart van den Eijnden

GeoScript – Spatial Capabilities for Scripting Languages

Justin Deoliveira, Jared Erickson

Scripting GeoServer with GeoScript

Justin Deoliveira, Tim Schaub

Sept 15th

Open Source tools for digital field mapping

ing Andrea Antonello, S.r.l., ing Silvia Franceschi

OpenLayers Mobile

Tim Schaub, Eric Lemoine

LiDAR Point Cloud Processing with libPC and libLAS

Howard Butler, Michael Gerlek

A web-based PostGIS educational tool

Javier Morales

The status of GeoServer WPS

Andrea Aime

Tutorial: Working with GeoScript

Tim Schaub, Justin Deoliveira

Sept 16th

HTML5 for Rich Geospatial applications on the web

Javier de la Torre

PostGIS Replication

Steven Singer

Store, manipulate and analyze raster data within the PostgreSQL/PostGIS spatial database

Pierre Racine

Once upon a time: JGrass

ing Andrea Antonello, HydroloGIS s.r.l., ing Silvia Franceschi, HydroloGIS

Lifemapper Moves Forward: Bringing Together Geospatial and Biodiversity Informatics Tools to Save the World

Aimee Stewart

Posted in Conferences, FOSS4G 2011, Other | Tagged: , | Leave a Comment »

Really loooong WMS requests

Posted by smathermather on August 3, 2011

We have a GeoExt/Mapfish/GeoServer/PostGIS stack in house that allows us to print nice maps through a web interface. There has been a ceiling, however, as far as size– 140dpi and 22×34 has been the largest we’ve been able to render maps, and even then, sometimes the layers are too complicated.

For a long time I was under the naïve impression that it was a memory issue with JAITools. The GeoServer users group set me straight on that– it was a timeout issue. A couple more posts to GeoServer Users and Mapfish, and I really got set straight– in my case it was a client side timeout issue (I had already allowed for long WMS requests in GeoServer and in Mapfish’s config.yaml). Since the stack includes GeoExt, and therefore leverages ExtJS, the solution is simple. I now have the following line at the start of my javascript document:

Ext.Ajax.timeout = 120000; //2 minutes

Now we can do 300dpi images/maps at 36 inches x 48 inches (about A0 for the folks on the International system).

Posted in GeoExt, GeoServer, MapFish | Tagged: , , , , | Leave a Comment »