Only One !BOX! Substitution in PostGIS?

[bill@…]

I posted this to the -users list a few days ago.  With no response, I thought a
bug was in order.
...

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?

[bill@…]

Patch to allow multiple !BOX! substitutions

[bill@…]

Patch against CVS HEAD (4.5)

[bill@…]

For some reason, I needed a make clean all after applying this patch and 1305's,
so you might want to consider that if you get troubles.

[bill@…]

Paul, Is this going to make it in before 4.6?

[mark@…]

Applied Bills patches.