OpenLayerer

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

http://openlayerer.appspot.com/

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

Debian Configuration– Tomcat on Boot, revision

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

http://linux-sxs.org/internet_serving/c140.html

#!/bin/sh
# /etc/init.d/tomcat6: start Tomcat6 server.

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

PATH=$PATH:/opt/apache-tomcat-6.0.32/bin/

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/startup.sh | 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/shutdown.sh | logger -t Tomcat6
;;
*) echo "Usage: /etc/init.d/tomcat6 {start|stop}"
exit 2
;;
esac
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… .

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.

FOSS4G 2011 Denver– Ideal schedule

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.

Date	Talk/Presentation	Presenter
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

Really loooong WMS requests

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