MapServer/OGR vs table views

[Jeremy Cothran]

On a development note, Dan Ramage was working with HF Radar datasets recently and was pushing the data to a sqlite database table and mapping via MapServer/OGR and OpenLayers.

Dan was collecting a days worth of data in the same table, but only wanted to map the most recent data. The performance issue we ran into for this case is that as MapServer/OGR does not support general database indexes for query (like an index on the time column) only supporting spatial indexes or attribute indexes for shapefiles. This results in a slow performing query/map render as MapServer/OGR was performing a full table scan(reading all table rows).

The solution to getting around this issue is to utilize a database view which represents an indexed subselect on the table data which is of interest - in this case the table rows which had been flagged 'recent' by an indexed column field/attribute. MapServer/OGR references the view resulting in greatly improved performance. In the past we have also generated separate tables(map tables) or hourly shapefiles, but the view approach allows us to achieve that subset query performance without having to physically split data out from the initial tables.

In addition to indexes on time columns, crude spatial indexes on longitude and latitude, or observation/measurement type columns could also be specified and utilized in a database view to support faster MapServer/OGR subset query/render.

Jeremy

[Rob Cermak]

Jeremy,

Sounds like a good breakthrough.  Will more details show up on the wiki somewhere?  Thanks -Rob

[Jeremy Cothran]

Hey Rob,

The place I'm posting announcements like this is at http://code.google.com/p/xenia/wiki/VMwareUpdates
for now(last item at wiki page bottom) - but that or the google group should contain the same info.  Also cross-posting some things to your ioos-tech group also.

One side note though, the WMS GetFeatureInfo for query individual points doesn't seem to work with the view approach, not sure why yet, seems like it should work if a table and view are both interfaced the same by Mapserver/OGR.

Thanks

Jeremy