mapserver/mapserver — pull request #4659 by msmitherdc (2013-05-18 12:22:00+00:00)
Title: Add support for specifying spatial index when needed

In support of using Oracle Spatial Linear Referencing functions, we
need to be able to work with views and subselects, but also be able to efficiently use indexes, so we needed to add a keyword to the "DATA" component of the layer file. The keyword, which is optional, allows the user to select a different field for the FILTER and RELATE operations so that an index can be utilized. The use case arises from LRS event themes and the fact that we want to render the derived geometry from the LRS CLIP_GEOM_SEGMENT but use the index from the original route shape. A sample view is:

```sql
create view vw_pal_status as
select t1.id as fema_event_id,
fmeas,tmeas,t1.align_id,
t2.id as pal_id,
pal_status_id,
pal_status_d,
pal_scenario_d,
pal_agree_date,
lastupdate,
in_pal_process_d,
t2.comments,
SDO_LRS.CLIP_GEOM_SEGMENT(shape,fmeas,tmeas) as lrsshape,
t3.shape
from fema_event t1,pal_status t2,levee_system_align_line t3
where t1.id=t2.fema_event_id and t3.align_id=t1.align_id;
```

The keyword added is INDEXFIELD and it is followed by the field name that has the index you would like to use. For the view above, an example layer file looks like:

```
LAYER
NAME "pal"
CONNECTIONTYPE oraclespatial
CONNECTION "user/passwd@host:port/service_name"
DATA "LRSSHAPE from vw_pal_status using SRID 8265 INDEXFIELD shape"
PROCESSING "CLOSE_CONNECTION=DEFER"
....
```

The query getting sent to oracle then becomes:

```sql
SELECT IN_PAL_PROCESS_D, rownum, LRSSHAPE FROM vw_pal_status WHERE SDO_FILTER( shape, MDSYS.SDO_GEOMETRY(2003, :srid, NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),:ordinates ),'querytype=window') = 'TRUE';
```