OracleSpatial attribute queries

Mon Sep 18 08:57:28 EDT 2006

Hi Johan,
    The idea with NONE parameter is tell to maporaclespatial driver to
don't use the spatial operator, like SDO_FILTER, SDO_RELATE. As example:
"shape FROM fastighetsytor USING UNIQUE OBJECTID VERSION 10g NONE". I
suggest to you to change the data using the Mapscript and not in the
Mapfile. You can just change the "layerObj->data" before the query
function (querybyAttribute, querybuShape....) and go back with the
original "layerObj->data" after the end of the scope of the query.
    Mapserver broke the query in two parts, in the first it's try to
find the rows that are inside of the actual extent. This is the reason
of the bbox with the extents limits. In the second part, Mapserver try
to get, from the database, the text data for the records that found in
the first query. The big problem that I see it's that Mapserver do a
login/logoff for every record that need to retrieve, and for database
side it's a high cost. Mapserver use this way with all the database
connections, Oracle Spatial and PostGIS.
    I suggest you to use NONE to try to improve the performance of your
request. But this way can generate another problem. As example: If you
have 10.0000 of rows in your table that are spread in a big area (big
extent) and you just want to see a small area, with a small extent (that
cover just 100 rows of your table), Mapserver use the spatial operator
to improve the performance and just return the 100 records. This is, in
my view, why Mapserver used the bbox with databases (let me
know if I'm wrong). With NONE the maproaclespatial don't use the spatial
operator, will be more fast, but will return all 10.0000 records from
the table.
    You can to try to user "filter" and "filteritem" in the Mapfile, or
define it with Mapscript, to add a criteria directly in the query that
maporaclespatial will use, and draw a map with just these records.
     Of course that the way that Mapserver uses is not the ideal for
databases. Mapserver was originally designed for shapefiles not for
databases, so the way to retrieve data is not the best, but for Oracle
Spatial side you can use the SDO_FILTER, SDO_RELATE operators and other
functions to work with geodetic data, functions specific for every
database version 8i/9i/10G, you can store and retrieve 3D data (x,y,z).
     Best regards.
   
------------------------------------------------------------------------
Fernando Simon
Mapserver and Oracle Spatial developer
G10 - Laboratorio de Computacao Aplicada - Brazil
http://www.univali.br/g10 - UNIVALI/CTTMAR
------------------------------------------------------------------------
 

Hallgren Johan E wrote:
> Do you mean that I should switch the data string from 
> DATA "shape FROM fastighetsytor USING UNIQUE OBJECTID VERSION 10g"
> to something like
> DATA "shape FROM fastighetsytor USING NONE OBJECTID VERSION 10g"
> and then turn it back to what it was before?
>
> Doesn't that mean that it don't return spatial data at all? I think I
> tried it and it ended up with an empty map. What I want is to return
> only those records that meets my criteria and render the map with those
> only (just to clarify things).
>
> Related to MapServes way of handeling queries like this I can't see the
> reason why there isn't a virtual function for each related function in
> MapScript. In that case it whould be up to actual driver to use the same
> call to the database or to have different calls dependent on what
> function the database could supply. Is it looks now, MapServer can't use
> all facileties in the database becaus of how MapServer works (let me
> know if I'm wrong). 
>
> /Johan
>
> ___________________________________
>
>
> Johan Hallgren
>
>  
> WM-data
> Pelle Bergs backe 3
> Box 1938, 791 19 Falun
> Tel (och mobil): 023-547 46 (int: +46-2354746)
>
> johan.e.hallgren at wmdata.com
> http://www.wmdata.se
> -----Original Message-----
> From: Fernando Simon [mailto:fsimon at univali.br] 
> Sent: den 14 september 2006 15:12
> To: Hallgren Johan E
> Cc: MAPSERVER-DEV at LISTS.UMN.EDU
> Subject: Re: [UMN_MAPSERVER-DEV] OracleSpatial attribute queries
>
> Hi Johan,
> It's relate with the way how Mapserver do the query. It's need to work
> with many kind of sources, and sometimes cannot be the better way for
> databases.
> For Oracle you can define a token in you data definition to force the
> Mapserver Oracle driver to don't the extents (bbox) in the query. You
> just need to define the NONE in your data, as example:
> data "geom from table USING NONE"
> You can see more about this in this page:
> http://mapserver.gis.umn.edu/docs/howto/oracle_spatial_howto
> So, you can change the data parameter before apply the query and test
> the results.
> Best regards.
>
> ------------------------------------------------------------------------
> Fernando Simon
> Mapserver and Oracle Spatial developer
> G10 - Laboratorio de Computacao Aplicada - Brazil
> http://www.univali.br/g10 - UNIVALI/CTTMAR
> ------------------------------------------------------------------------
>
>
> Hallgren Johan E wrote:
>   
>> This is an old question but still the problem remains.
>>
>> I use MapServer/MapScript/csharp.
>>
>> I use the OracleSpatial built in support in MapServer.
>>
>> I want to show a selected bunch of features on the map. The selection
>> is made only by an attribute query (postcode = 123).
>>
>> By turning logging on I can se that even if I do an attribute query
>> the query sent to Oracle include both the envelope and the actual
>>     
> query.
>   
>> I can catch that query and run it in SQLPlus and I will see it takes
>> looonnngg time to perform. If I remove the envelope it's much faster.
>> I have created index on both the geometry and the attribute column,
>> but as I can understand it's not possible to create index on shape and
>> the attribute column in combination.
>>
>> So my question is: why must the geometry be part of the query when I
>> don't need it?
>>
>> /Johan
>>
>>
>>       *___________________________________*
>>
>>
>>       *Johan Hallgren*
>>
>> WM-data
>>
>> Pelle Bergs backe 3
>>
>> Box 1938, 791 19 Falun
>>
>> Tel (och mobil): 023-547 46 (int: +46-2354746)
>>
>> _johan.e.hallgren_ at wmdata.com
>> <BLOCKED::mailto:johan.e.hallgren at wmdata.com>
>>
>> http://www.wmdata.se
>> <BLOCKED::/exchweb/bin/redir.asp?URL=http://www.wmdata.se/>
>>
>>     
>
>