Multiple bounding boxes in PostGIS data source

[Bill Binko]

Hello everyone,

After my mortifying mistake(s) yesterday, I hope this is a less dumb
question :)

I am using PostGIS as a data source for some of my maps and I wanted to be
able to use the current bounding box as a parameter within my queries.
For example, I would like to be able to calculate an average over the
visible area, and color items on the map based on their variation from
that average.

So, I went looking and found no way to get at that bounding box in the
documentation (that I found).  However, I went into the code, and found
that, in fact, you can use the string "!BOX!" within your query to insert
the bounding box!  However, there was a limitation that you could only use
the box once (as well a comment saying that was intentional).

Does anyone know why this was?  Here is an example of where I would like
to use the !BOX! more than once in a query.  Please keep in mind that it
is a proof of concept and I'm sure there are far more efficient ways of
doing the SQL, but it seems that given the availability of GIST indexes
and the need to be explicit in using them, that this would be a fairly
common need.

DATA "parcel_shape from
 (select  parcel_shape, parcels.oid, total_acreage/ave_acreage as pct,
          total_acreage, ave_acreage
          from parcels join parcel_info on sdf_id = feat_num,
            (select avg(total_acreage) as ave_acreage from
                 parcels join parcel_info on sdf_id = feat_num
                 where parcel_shape &&
                       setSRID(!BOX!, 26759) and
                       Within(parcel_shape, setSRID(!BOX!, 26759))) as bar)
        as foo using unique oid using SRID=26759"

That query finds the average acreage for the current view, and returns all
visible parcels colored by the 'pct' field which is their deviation
from that average.

When I replaced the line

     if (strstr(geom_table,"!BOX!"))

with
     while (strstr(geom_table,"!BOX!"))


in mappostgis.c, it allowed me to run the above query, and it performed as
expected: slowly, but accurately. :-)

I realized later that I had introduced a small memory leak, so I have
included a real solution below as a patch.

My question is, was there any reason for the limitation?  Or did it solve
the problem it was meant to, and just not need more tinkering?

Thanks, and feel free to redirect me if this isn't the right forum.

Bill








PATCH TO mappostgis.c FOLLOWS

460,462c460
<     // substitute token '!BOX!' in geom_table with the box3d - do at most 1 substitution
<
<     if (strstr(geom_table,"!BOX!"))
---
>     // substitute token '!BOX!' in geom_table with the box3d - do an unlimited # of subs
464,478c462,480
<         // need to do a substition
<         char    *start, *end;
<         char    *result;
<
<         result = malloc(7000);
<
<         start = strstr(geom_table,"!BOX!");
<         end = start+5;
<
<         start[0] =0;
<         result[0]=0;
<         strcat(result,geom_table);
<         strcat(result,box3d);
<         strcat(result,end);
<         geom_table= result;
---
>       char* result = NULL;
>       while (strstr(geom_table,"!BOX!"))
>       {
>         // need to do a substition
>         char    *start, *end;
>         char *oldresult = result;
>         result = malloc(7000);
>         start = strstr(geom_table,"!BOX!");
>         end = start+5;
>
>         start[0] =0;
>         result[0]=0;
>         strcat(result,geom_table);
>         strcat(result,box3d);
>         strcat(result,end);
>         geom_table= result;
>         if (oldresult != NULL)
>           free(oldresult);
>       }
481d482
<