Re: oracle spatial: change the spatial tolorance and round the values in the fields (hope to solve ORA-04031 messages with this)

From: Pedro Lopes <pedro.lopes_at_netvisao.pt>
Date: Thu, 18 Oct 2007 20:38:00 +0100
Message-ID: <newscache$f7h4qj$wsi$1@newsfront4.netvisao.pt>

eywitteveen_at_gmail.com wrote:
> Hello,
>
> I want to change the tolorance from: 0.00001 into: 0.005, Is there a
> way to update this column so the value is rounded and simplified, so
> the value is filled with values which do not contain too much decimals
> behind the "."?
>
> I want to change the tolorance of a table that i have, so oracle
> doesnt have to do unused work(and give error ORA-04031: ..."large
> pool".. on index usage).
>
> query i want to run:
> pand table: 13124 records with polygons
> number table: 14070 records with points
>
> CREATE TABLE NUMMER_IN_PAND AS
> SELECT
> PAND.GID AS PAND_ID,
> NUMMER.GID AS NUMMER_ID
> FROM
> PAND,
> NUMMER
> WHERE
> SDO_RELATE(PAND.GEOM, NUMMERAANDUIDING.GEOM, 'mask=CONTAINS
> querytype=JOIN') = 'TRUE'
> (i also tried adding a mbr column to the table "pand" and do 2 contain
> statements, used querytype=window, so i'm more / less clueless what to
> do next)
>
> Currently my geometry column is filled with the following information
> like the following:
> INSERT INTO USER_SDO_GEOM_METADATA
> (
> TABLE_NAME,
> COLUMN_NAME,
> DIMINFO,
> SRID
> )
> VALUES
> (
> 'pand',
> 'geom',
> MDSYS.SDO_DIM_ARRAY(
> MDSYS.SDO_DIM_ELEMENT('X', -7000, 300000, 0.00001),
> MDSYS.SDO_DIM_ELEMENT('Y', 289000, 629000, 0.00001 )
> ),
> 90112
> )
>
>
> Furthermore, if this is not the path to follow, what else should i do?
>

Hi,
As far as I remember you can update the USER_SDO_GEOM_METADATA view to change the tolerance value or pass the tolerance argument to the SDO_RELATE operator.

 From the docs :

A tolerance value is specified in two cases:

       In the geometry metadata definition for a layer
       As an input parameter to certain functions

The parameter passed to the function overrides the metadata definition.

You can try this :

DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME='PAND'; INSERT INTO USER_SDO_GEOM_METADATA(TABLE_NAME,COLUMN_NAME,DIMINFO,SRID) VALUES(
'PAND','GEOM',

MDSYS.SDO_DIM_ARRAY(
MDSYS.SDO_DIM_ELEMENT('X', -7000,300000, .0005),
MDSYS.SDO_DIM_ELEMENT('Y',289000, 629000, .0005)

),
90112
);

COMMIT; Recreate your index.

Hope it helps,
pedro Received on Thu Oct 18 2007 - 14:38:00 CDT