Request for Oracle Spatial 3D functionality

[Trevor.Tracey-Patte@…]

We have all our geometries (shapes) stored in Oracle Spatial (9i) in 3D format 
and so have to use OGR to act as a middleman between MapServer and Oracle. If 
MapServer natively handled Oracle Spatial 3D geometries, the additional 
overhead of using OGR to connect to the database and process the 3D geometries 
into 2D for MapServer would be removed and consequently speed up the process.

[fsimon@…]

Ok,
    I believe that with native support is more quickly. This a feature will be
in my check list, for 4.6 or 4.8. Any problem?
    Thanks.

[Trevor.Tracey-Patte@…]

Fernando
Either release would be good (obviously the sooner the better). Could you 
confirm that the FILTER parameter will be able to be used dynamically to pass 
a 'where' clause to the layer for an Oracle database connection using 3D. 
Thanks.

[fsimon@…]

Hi Trevor,
    About the FILTER for 3D data I can't do this now because I use the rectObj
to create the box for the query, this internal mapserver object don't contains
the z value. So when this object store z I can apply this for the box without
problem.
    Any problem?
    Thanks.

[Trevor.Tracey-Patte@…]

Fernando

If the FILTER parameter cannot be used, I believe it will not be faster. My 
understanding is that all the records from the layer are passed to MapServer 
and then it uses regular expression matching to filter (via 
map_layername_filter) all of the records to display only the record(s) 
requested on the map. For our point layer, we have approximately 140,000 
records which would have to be filtered to display perhaps only one point on 
the map. This is extremely inefficient. If the required records are passed 
using the FILTER parameter to the database, only the record(s) required to be 
displayed would be passed back to MapServer from the database. The same applies 
for our lines and polygon layers. Our polygon layer has only about 7,500 
records but some of these are quite complex and contain up to 100,000 vertices 
(approx 2Mb of data). The network overhead alone in passing this much data 
around adds a huge overhead to our web pages and is slowing the display quite 
considerably.
If we cannot use the FILTER to pass a 'where' clause to the database, using 
MapServer to process 3D records would not have a substantial increase in 
performance and is probably not worth doing (for our purposes). I have 
submitted a bugzilla for OGR and they have advised they will get the FILTER 
parameter enabled to be passed from MapServer to OGR, thus speeding up 
processing.

Trevor

[fsimon@…]

Hi,
   I believe that we are not talking about the same problem. The layer "filter"
parameter can be used without problem, you can specify without problems any
filter. The maporaclespatial will use the layer filter parameter directly in
"WHERE" for internal SQL. 
   The problem that appear with 3D data is that the maporaclespatial can't use
correctly the SDO_FILTER oracle operator with 3D data because rectObj inside of
mapserver don't contain the z value, so the problem is not with layer filter but
between the FILTER function and rectOBj. This FILTER is used to increase the
performance, this use the actualy user extent to retrieve from database only the
data inside of the user extent. So if you want to use the FILTER the internal
SQL will use the SDO_FILTER Oracle operator.
    As example of layer:
...
   data "geom from (select oid, age, geom from build) using unique oid FILTER"
   status "on"
   type "point"
   filter "oid > 10 AND age > 20"
...

   So the internal sql will be:
SELECT oid, geom FROM (select oid, age, geom from build) WHERE oid > 10 AND age
> 20 AND SDO_FILTER( geom, MDSYS.SDO_GEOMETRY(2003, NULL, NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3), MDSYS.SDO_ORDINATE_ARRAY(-180,-90,-140,-50)
),'querytype=window') = 'TRUE'

   In this SQL you can see where the problem appear with 3D data and the
SDO_FILTER, the mapserver use only x and y yet, the z don't appear. when the
mapserver will use the z I can use this value in ORDINATE_ARRAY. You can sse
this in the source code (rect.minx, rect.miny, rect.maxx and rect.maxy):
...
sprintf( query_str + strlen(query_str),

   "SDO_FILTER( %s, MDSYS.SDO_GEOMETRY("

   "2003, %s, NULL,"

   "MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),"

   "MDSYS.SDO_ORDINATE_ARRAY(%.9g,%.9g,%.9g,%.9g) ),"

   "'querytype=window') = 'TRUE'",

   geom_column_name, srid, rect.minx, rect.miny, rect.maxx, rect.maxy );
...

    Sorry about my last confused comment.
    Thanks.

[Trevor.Tracey-Patte@…]

Fernando

There are three different items to be considered here. 

Firstly, if the records returned are in 3D will MapServer handle it? From my 
(very) brief overview of the code, it appears that you only handle 2D records. 
If it cannot display 3D, it doesn't matter about the FILTER because it won't 
display the records (or not correctly) anyway.

Secondly, if the data is stored in geodetic form (ie lat/long) you cannot use 
an optimized rectangle in the query geometry (pre Oracle 10g) - it must be the 
coordinates of a box given in an anti-clockwise direction and returning to the 
starting point (ie 5 coordinate pairs). Given this, the query you are using 
will work in most circumstances on 3D data. The default indexing for Oracle 
Spatial is 2D and I believe this is used most by most organisations (we do). So 
even if the data is in 3D, the query geometry can be in 2D and this will work 
correctly. 

Lastly using SDO_FILTER is faster but is not accurate. SDO_FILTER is the 
primary filter used in a two part process. It uses approximations (ie minimum 
bounding rectangles) around the spatial data and if these MBRs intersect with 
the MBR of your search geometry it returns the record. This works well for 
points but not for lines or polygons. eg. if you search for a point just off 
the coast of South America (say near Rio) and use SDO_FILTER you will get the 
record of Brazil returned as Oracle has drawn a box around Brazil and the point 
off the coast is inside the box. You should use the Oracle function SDO_RELATE 
to return exact results from Oracle. It is slower but it is accurate. If you 
use SDO_RELATE, there is also a required 'mask' parameter and I would suggest 
using 'mask=ANYINTERACT querytype=WINDOW' as this will return any geometry that 
is inside, intersects or touches the query geometry.

Hope this is helpful.

[fsimon@…]

Hi,
   Thanks for your reply. For the 4.4 version I implemented the options:
"FILTER", "RELATE", "GEOMRELATE" and "NONE" token for the layer data. You can
define any of this tokens, if you define FILTER the code will use the SDO_FILTER
operator, if you defined RELATE the code will use the SDO_RELATE
(mask=anyinteract) operator, if you defined GEOMRELATE the code will use the
RELATE geom function and if you defined the NONE the code don't use spatial
operator or spatial functions.
   About the geodetic function Mike from US Army told me about the problem and
we already fixed this problem for 9i version (10g version can work correctly
with geodetic data). I will commit the new version with this and other
improvements tomorrow.
   Yes, exist one problem about the 3D data in maporaclespatial, the code don't
draw the 3D data, I will add this support for the next version of code. But some
data in Mapserver don't use the z value, store but don't use. Of course that the
z values is very important and I will add this support for Oracle.
   Thanks for your hints.
   

[fsimon@…]

Hi,
    I added the support for 3D data in the new version of maporaclespatial.c
(1.21), I commited the file in cvs.
    The version of mapserver in CVS is 4.5 but you can tyr the new version
maporaclespatial.c with 4.4.x version without problem.
    Can you test this new version before I close the bug?
    Thanks.

[fsimon@…]

Some problems appears with 1.21 version when the kind of data is 3D but the z
value is defined as NULL.
Fixed with 1.22 version.
Thanks Trevor for the point about this problem with NULL value for z.