Be able to pass [scale] into DATA strings like SQL

[woodbri]

I would like to be able to make my postgis queries in mapserver be aware of the current draw scale. Is there an easy way to do this in the mapfile? I would like to avoid parameter substitution since mapserver is already aware of the scale, it would be nice if we could just embed a token like [scale] in a mapserver data statement and mapserver would substitute that for the current draw scale.

DATA "way from (
  select way, osm_id, landuse, name
    from osm_landuse1 where landuse is not null
     and is_large_enough(area(way),'landuse1',[scale])
  ) as foo using unique osm_id using srid=900913"

The idea here is that is_large_enough(area(way),'landuse1',[scale]) whould return true or false based on comparing area(way) to some value it looks up for 'landuse1' and the current [scale]. This would allow dynamic filtering of data in the database based on the current scale.

Likewise you could use the [scale] in a SQL case statement to dynamically change the SQL results based on scale.

It would seem that this could greatly simplify mapfiles.

[sdlime]

Wouldn't be a huge addition. Question is where to do it? It's certainly not a CGI-only modification. Since the likely values to be used here are computed and not supplied by a user security implications are minimal I think. Might make sense to include access to the map extent too. Perhaps msLayerOpen() is the spot?

Steve

CC'ing Paul and Jim for additional comment...

[woodbri]

Here is a potential patch. I'm not sure what is the best way to allocate buf or if there are treading issues with making this static. This diff is against version 9378.

woodbri@carto:/u/software/mapserver-SVN/mapserver$ diff -crB mappostgis.c-orig mappostgis.c
*** mappostgis.c-orig   2009-10-05 13:08:55.000000000 -0400
--- mappostgis.c        2009-10-05 14:00:31.000000000 -0400
***************
*** 1258,1263 ****
--- 1258,1264 ----
      char *strWhere = 0;
      char *strSQL = 0;
      static char *strSQLTemplate = "select %s from %s where %s";
+     static char buf[100];

      if (layer->debug) {
          msDebug("msPostGISBuildSQL called.\n");
***************
*** 1291,1296 ****
--- 1292,1300 ----
      if (strFrom) free(strFrom);
      if (strWhere) free(strWhere);

+     sprintf(buf, "%d", layer->map->scaledenom);
+     strSQL = msReplaceSubstring(strSQL, "[scale]", buf);
+
      return strSQL;

  }

[woodbri]

The patch above does not address the more global aspects of doing it for all the other data providers, like oracle, gdal/ogr, shapefiles, etc. but then that might not be an issue for all of them.

[jimk]

I don't want to over complicate this, but the first thing that comes to mind with the given syntax is to support all of the template substitutions that do not depend on the data.

Also, the oraclespatial driver is using prepared statements now, so scale would have to be substituted as a parameter in the prepare as well as in the actual queries which means any substitution would have to be remembered past msLayerOpen.

My second thought is wondering if this functionality is pushing into mapscript territory. My third thought is that it would be cool if you could embed mapscript fragments in the mapfile to override default (cgi) functionality at runtime.

[pramsey]

Wouldn't using the current variable substitution system, but with some "system" variables, work best? So we have %ms_scale and %ms_x_click and %ms_y_click and so on?

[woodbri]

These are all good ideas but I'm not sure the general solution is need yet especially as it seems to be getting way to complicated. I have a real use case for the [scale] example. I would like to dynamically change the where clause in an OSM mapfile like:

... where area(way) > case when [scale]>10000000 then 4500000 
                           when [scale]>3000000 then 1500000 
                           else 0 end

I simplified the number of cases in the example above, but I have 4 layers each with 4-5 cases and I need to create 12-20 layers instead of 4 basic layers where the only thing changing is because of the scale.

To avoid having this look like template substitution, we could use the !BOX! syntax that is already used in postgis and oracle interfaces and change the substitution string to !SCALE! instead of [scale] which might be more appropriate.

Given the simplicity of the current patch and the low amount of overhead it addes to the layer processing, I would hate to have this get to complicated until there is real need for more than this offers.

[jimk]

That is a good use case. I like having a simple implementation for now. My worry is should we want to extend this later that we leave ourselves a decent path forward without introducing the need to create incompatibility.

One thing to be weary of is while this may look to the user like a normal mapfile substitution, is isn't because it needs to occur after the mapfile and parameters are parsed. With mapscript it could change on every query/request w/o reopening the layer.

I was unaware of the !BOX! syntax, that appears to be in postgis,mygis, & mssql2008 drivers. oraclespatial and sde make no reference to it. This (!SCALE!) is probably a good way to go. I'd suggest an additional ticket to add these substitutions to the rest of the applicable drivers or factor them out into the mapserver core.

Also, scaledenom is a double, not an int (in the sprintf).

[sdlime]

These are runtime subs so I'd favor the %ms_var% syntax for consitency. I've had other folks want access to the map extent in a similar fashion in conjunction with MS SQL Server. I would just do exactly what the patch does but in a general way for all layers to use. E.g.

if(layer->data) {
  // do runtime subs on the data string
}

My 2 cents...

Steve

[pramsey]

Then may I suggest

• %ms_bbox% replaces with the WKT POLYGON version of the current geographic bounding box
• %ms_scale% replaces with the current scale

[pramsey]

Oh, detail: the presence of !BOX! not only triggers a replacement, it also suppresses the usual inclusion of the bounding box term in the SQL. So there's a special side effect there, it's not just a replacement.

[woodbri]

Regarding Oracle this also relates to #2129.

[woodbri]

The patch above is not complete, because before that is called, msPostGISLayerGetItems() is called and stuffs the SQL into another SQL statement so the substitution needs to get done earlier in the process.

Below is a patch that works for [scale] in the postgis sql in the mapfile like:

DATA "way from (select way, osm_id, landuse, name from osm_landuse1
                 where area > case when [scale]>3100000 then 4000000
                                   when [scale]>510000 then 1000000
                                   when [scale]>51000 then 300000
                                   else 0 end) as foo
      using unique osm_id using srid=900913"

There is probably a better way to do this with only a single substitution to the sql in the DATA statement earlier in the process and this might in fact work for all layer types. The hack below works for my immediate needs and hopefully someone can pick this up implement the other ideas discussed above.

woodbri@mappy:/u/software/mapserver-svn/mapserver$ diff -crB mappostgis.c-orig mappostgis.c
*** mappostgis.c-orig   2009-10-05 18:07:10.000000000 -0400
--- mappostgis.c        2009-10-06 00:19:07.000000000 -0400
***************
*** 1258,1263 ****
--- 1258,1264 ----
      char *strWhere = 0;
      char *strSQL = 0;
      static char *strSQLTemplate = "select %s from %s where %s";
+     static char buf[100];

      if (layer->debug) {
          msDebug("msPostGISBuildSQL called.\n");
***************
*** 1291,1296 ****
--- 1292,1300 ----
      if (strFrom) free(strFrom);
      if (strWhere) free(strWhere);

+     sprintf(buf, "%d", (int) layer->map->scaledenom);
+     strSQL = msReplaceSubstring(strSQL, "[scale]", buf);
+
      return strSQL;

  }
***************
*** 1975,1980 ****
--- 1979,1985 ----
      char *col;
      char found_geom = 0;
      int item_num;
+     static char buf[100];

      assert(layer != NULL);
      assert(layer->layerinfo != NULL);
***************
*** 2001,2006 ****
--- 2006,2014 ----
      sql = (char*) malloc(strlen(strSQLTemplate) + strlen(layerinfo->fromsource));
      sprintf(sql, strSQLTemplate, layerinfo->fromsource);

+     sprintf(buf, "%d", (int) layer->map->scaledenom);
+     sql = msReplaceSubstring(sql, "[scale]", buf);
+
      if (layer->debug) {
          msDebug("msPostGISLayerGetItems executing SQL: %s\n", sql);
      }