Patch to extent substitution of bbox values into SQL

[woodbri]

For complex oracle queries like the following you need to be able to have more control of the placement of the bbox values of XMIN, YMIN, XMAX, YMAX in the

sdo_filter(g.geometry,
   sdo_geometry(2003, 4326, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3),
      SDO_ORDINATE_ARRAY(!XMIN!,!YMIN!, !XMAX!,!YMAX!))) = 'TRUE' -- rectangle coordinate params
)

The attached patch adds support for this. I have been using this we great success.

    DATA "GEOMETRY FROM (
 select --+ no_megre(t) no_merge(g) use_hash(g)
        g.id1,
        g.name,
        nvl(t.value,0) value,
        nvl(t.color,0) color,
        g.geometry
   from (
 select --+ no_megre(t)
        t.*,
        trunc(decode(min_value,max_value,1,
          (1-max_color)/(min_value-max_value)*value+(max_color*min_value-max_value)/(min_value-max_value))+0.000001) color
   from (
 select --+ no_megre(t)
        t.*,
        min(value) over() min_value,
        max(value) over() max_value
   FROM (
 select --+ ordered use_nl(dp)
        msacmsa id1,
        count(1) value, -- aggregate expression param
        10 max_color -- max color number param
   from locator l
  where account = '%account%' -- account param
    and mod_date between to_date('%date_from%','mm/dd/yyyy') -- from date param
                     and to_date('%date_thru%','mm/dd/yyyy') -- through date param
  group by
        msacmsa
        ) t
        ) t
        ) t,
        (
select g.msacmsa id1,
        g.name,
        g.geometry
   from ma g
  where sdo_filter(g.geometry,
          sdo_geometry(2003, 4326, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3),
            SDO_ORDINATE_ARRAY(!XMIN!,!YMIN!, !XMAX!,!YMAX!))) = 'TRUE' -- rectangle coordinate params
  union all
 select g.msacmsa id1,
        g.name,
        g.geometry
   from cm g
  where sdo_filter(g.geometry,
          sdo_geometry(2003, 4326, NULL, SDO_ELEM_INFO_ARRAY(1,1003,3),
            SDO_ORDINATE_ARRAY(!XMIN!,!YMIN!, !XMAX!,!YMAX!))) = 'TRUE' -- rectangle coordinate params
        ) g
  where t.id1 (+) = g.id1
    ) USING SRID 4326 NONE VERSION 10g"

[woodbri]

Patch to maporaclespatial.c

[fsimon]

Hi, Can you provide more info about this issue? The Oracle Spatial driver already use the rectangle positions in query functions. Like, queryby*(in Mapscript) and *query (modes in CGI mode). Best regards.

[woodbri]

To generate efficient queries that are complex joins like the one referenced above it is not enough for mapserver to append the bbox spatial filter to the end of the query. You will notice in the query above that the bbox is actually needed twice because of the union. I also have lots of other queries that out DBA has generated where we can not rely on the mapserver placed bbox to be the most efficient placement. This patch only adds the option to place the values separately while "USING NONE" to get more control over the placement.

We are using this to do thematic mapping so these queries are getting applied during rendering and not during queries.

[dmorissette]

Too late for 5.0. Pushing to 5.2.

[dmorissette]

SteveW: do you still think this is a worthwhile enhancement, and if we include it in 5.6 would you be able to provide a blurb to document it in the Oracle Spatial docs (http://mapserver.org/input/vector/oracle.html)?

[woodbri]

I do not have a specific need for this anymore, but I think it is needed for any sophisticated Oracle user or DBA that know how to optimize Oracle queries. Making this change in the past provide a 10x performance boost over the next best Query we could come up with. But, since no one else has asked for it, it might be questionable.

If You want to add it to the Oracle driver, I'll update the Oracle Spatial docs (http://mapserver.org/input/vector/oracle.html) with some information from this ticket.