SDO_INTERSECTION: Trouble with tolerance - Spatial

Has anyone else out there had this problem?
I am trying to calculate the intersection area between two polygons, but the answer I get depends on the value I set for the tolerance in sdo_intersection.
Basic detail:
Oracle 8.1.6 on NT and 9.2 on Windows 2000
Indexing is Quadtree on 8.1.6 and RTREE on 9.2.
Example:
Two polygons which touch. No intersection area so you would expect SDO_INTERSCTION to return a line.
1 select mdsys.sdo_geom.sdo_intersection(a.geometry1, b.geometry1, 0.000005)
2 from section_polygons a, section_polygons b
3 where a.id = 465
4* and b.id = 466
SDTD> /
MDSYS.SDO_GEOM.SDO_INTERSECTION(A.GEOMETRY1,B.GEOMETRY1,0.000005)(SDO_GTYPE, SDO_SRID, SDO_POINT(X,
----------------------------------------------------------------------------------------------------
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1005, 9, 1, 2, 2, 5, 2, 1, 9, 2, 2, 13, 2, 1,
15, 2, 2, 19, 2, 1, 21, 2, 2, 25, 2, 1, 27, 2, 2), SDO_ORDINATE_ARRAY(211042.71216, 600759.8933, 211
039.33074, 600758.99422, 211037.57524, 600755.96759, 211033.13133, 600722.7358, 211028.68743, 600689
.50401, 211029.58621, 600686.12219, 211032.61305, 600684.36641, 211144.3927, 600669.41873, 211147.77
452, 600670.31751, 211149.5303, 600673.34435, 211158.41811, 600739.80793, 211157.51933, 600743.18975
, 211154.49249, 600744.94552, 211042.71283, 600759.89321, 211042.7125, 600759.89325, 211042.71216, 6
00759.8933))
Not exactly a line! I reduced the tolerance by 10ths to 0.005 and got polygons until a value of 0.005 gave the correct answer:
1 select mdsys.sdo_geom.sdo_intersection(a.geometry1, b.geometry1, 0.005)
2 from section_polygons a, section_polygons b
3 where a.id = 465
4* and b.id = 466
SDTD> /
MDSYS.SDO_GEOM.SDO_INTERSECTION(A.GEOMETRY1,B.GEOMETRY1,0.005)(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y,
----------------------------------------------------------------------------------------------------
SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 4, 2, 1, 2, 1, 5, 2, 2), SDO_ORDINATE_ARRAY(21
1028.68743, 600689.50401, 211033.13133, 600722.7358, 211037.57524, 600755.96759, 211039.33102, 60075
8.99443, 211042.71283, 600759.89321))
Now that's more like it.
Ok I hear you say, just run this query with a tolerance of 0.05 for everything. Well, when I do that I get the dreaded end-of-connection error.
Has anyone out there figured out an algorithm to work out (on the fly) how many decimals the tolerance should have?
I initially set my tolerance using the Oracle "Maximum decimals in sdo_ordinates and add a 5" rule, which sets my tolerance to 0.0000000005.
I reduced my diminfo from this to six zeros a while ago, which solved some problmes I was having with areas of intersection. Not all of them though....
I replicated this problem using the same database on Oracle 9.2 with an RTREE index.
Any suggestions would be much appreciated.
The two geometries in the above examples are:
ID = 465:
GEOMETRY1(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
----------------------------------------------------------------------------------------------------
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 5, 14, 1, 2, 1, 3, 2, 2, 7, 2, 1, 9, 2, 1, 11,
2, 1, 13, 2, 1, 15, 2, 1, 17, 2, 2, 21, 2, 1, 23, 2, 2, 27, 2, 2, 31, 2, 1, 33, 2, 2, 37, 2, 1), SD
O_ORDINATE_ARRAY(211014.87905, 600586.24415, 210949.22282, 600496.37183, 210948.39704, 600492.97145,
210950.21755, 600489.9831, 210850.05577, 600563.15631, 210847.34517, 600559.44596, 210839.47228, 60
0565.1975, 210842.18288, 600568.90785, 210782.59927, 600612.43666, 210780.8942, 600614.89367, 210781
.07276, 600617.87902, 210812.98697, 600694.87395, 210828.57822, 600738.15559, 210840.0217, 600782.71
38, 210841.85401, 600785.51608, 210845.10464, 600786.31828, 211042.71283, 600759.89321, 211039.33102
, 600758.99443, 211037.57524, 600755.96759, 211014.87905, 600586.24415))
ID = 466:
GEOMETRY1(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
----------------------------------------------------------------------------------------------------
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 5, 9, 1, 2, 1, 3, 2, 2, 7, 2, 1, 9, 2, 2, 13,
2, 1, 15, 2, 2, 19, 2, 1, 21, 2, 2, 25, 2, 1), SDO_ORDINATE_ARRAY(211033.13133, 600722.7358, 211028.
68743, 600689.50401, 211029.58621, 600686.12219, 211032.61305, 600684.36641, 211144.3927, 600669.418
73, 211147.77452, 600670.31751, 211149.5303, 600673.34435, 211158.41811, 600739.80793, 211157.51933,
600743.18975, 211154.49249, 600744.94552, 211042.71283, 600759.89321, 211039.33102, 600758.99443, 2
11037.57524, 600755.96759, 211033.13133, 600722.7358))

Some more information on this problem which may be of interest:
Run 'DETERMINE' with the different tolerances.
SDTD> select mdsys.sdo_geom.relate(a.geometry1, 'DETERMINE',
b.geometry1, 0.000005)
2 from section_polygons a, section_polygons b
3 where a.id = 465
4 and b.id = 466;
MDSYS.SDO_GEOM.RELATE(A.GEOMETRY1,'DETERMINE',B.GEOMETRY1,0.000005)
-------------------------------------------------------------------
COVERS
1 select mdsys.sdo_geom.relate(a.geometry1, 'DETERMINE',
b.geometry1, 0.005)
2 from section_polygons a, section_polygons b
3 where a.id = 465
4* and b.id = 466
SDTD> /
MDSYS.SDO_GEOM.RELATE(A.GEOMETRY1,'DETERMINE',B.GEOMETRY1,0.005)
----------------------------------------------------------------
TOUCH
Very strange but consistent with the whole problem.

Related

SDO_RELATE: Process Hangs

Can anyone help with this problem?
Oracle v9.2
I am running a query on the relationship between polygons and it works about 2,000 times in my script and then hangs. No error message. The geometries for the objects verify as TRUE and look ok.
The original query was:
select a.id
from land_use_pol_polys a, blocks b
where a.current_lifecycle_stage = 'GAZETTED'
and b.id = 9450
and (mdsys.sdo_relate(a.geometry1, b.geometry1, 'mask = CONTAINS querytype = WINDOW') = 'TRUE'
or mdsys.sdo_relate(a.geometry1, b.geometry1, 'mask = COVERS querytype = WINDOW') = 'TRUE');
When this hung, I checked the Optimiser and it was stuck at the (blocking operation) "BITMAP OR" then "BITMAP CONVERSION [TO ROWIDS]".
I tried to run the query as separate selects for CONTAINS and COVERS:
select a.id
from land_use_pol_polys a, blocks b
where a.current_lifecycle_stage = 'GAZETTED'
and b.id = 9450
and mdsys.sdo_relate(a.geometry1, b.geometry1, 'mask = CONTAINS querytype = WINDOW') = 'TRUE';
This hung too.
I got some workmates to run the first query on the same data at a different location and it worked for v8.1.7 but hung for v8.1.6.
I set my memory parameters to be the same as the 8.1.7 database, to no avail.
The memory params are:
Shared: 96Mb
Large Pool: 8Mb
Buffer: 8Mb
Java: 32Kb
I changed the Buffer memory to 40Mb with no change.
I rebuilt the spatial indexes for 9.2 and ran an ANALYSE on the tables and their indexes. Still hangs.
Do I need a HINT in the command?
Is there anything else I can try?
Much Appreciated
John M
Hi,
You might try using the ordered hint, and rewrite the query as follows:
select /*+ ordered */ a.id
from blocks b, land_use_pol_polys a
where a.current_lifecycle_stage = 'GAZETTED'
and b.id = 9450
and mdsys.sdo_relate(a.geometry1, b.geometry1, 'mask = CONTAINS+COVERS querytype = WINDOW') = 'TRUE');
If it still runs slowly, if there is an index on a.current_lifecycle_stage you might want to disable it and see if that helps
performance.
If it still seems to hang, can you make sure all of the geometries are valid?
If the geometries are all valid, you might want to then look at resources on the system and see if there are any other
issues (memory consumption, i/o, etc).
Hi,
May be it is choosing the wrong order of execution
and taking a long time. Change the order of the tables in query to:
select a.id
from blocks b, land_use_pol_polys a
where a.current_lifecycle_stage = 'GAZETTED'
and b.id = 9450
and mdsys.sdo_relate(a.geometry1, b.geometry1, 'mask = CONTAINS querytype = WINDOW') = 'TRUE';
The general rule is that the "spatial index" table (table whose spatial index is to
be used in query evaluation) should be the inner table (the second of the two) and the
the column should be the first argument to sdo_relate operator (as in the
example above. You can add the "ORDERED" hint to the above query if explain plan
still does not choose the right order.
- Ravi.
I ran the query with the /*+ ORDERED */ Hint and also in the correct order as suggested, but to no avail. I also tried a /*+ ORDERED INDEX_COMBINE ... */ hint but I get the same result.
I can't drop the index as I don't have the permission, but I'll try it on the laptop and see.
Hi,
Please check the tolerance you set in your user_sdo_geom_metadata.
Please follow the guidelines in the spatial doc
(.000000000...5: the number of 0s should be the number of digits
after decimal in your data if the data is non-geodetic).
In some cases for complicated geometries, incorrect tolerance can
cause problems.
Btw, how many rows do the tables have?
You may want to isolate what geometries in the tables are causing the
hang. Then, we can examine what is wrong with those geometries. One way
to do this isolation is to run sdo_geom.relate instead of sdo_relate
(may take some time) for the <query, data> geometries and
print where you are at each step. That way you will know which
geometries are taking forever. Also, monitor if there is a memory leak.
As far as we know, there are no major memory problems esp. in 9i,
so if you find anything go ahead and open a tar.
- Ravi. 
Yes, I had problems with the tolerance and SDO_INTERSECTION earlier. At that stage the tolerance was set in DIMINFO as 0.0000000005 (9 zeros). When I tried to find the intersection of a geometry with a hole and another geometry, I got the hole returned. I changed the tolerance to 0.000005 (5 zeros) which seemed to fix the problem.
The BLOCKS table has 127,000 rows and the LAND_USE_POL_POLYS table has 4,500.
I am keeping track of the geometries where the hangs occur by writing to a trash table with the block ID and where the script is. EG
50250 587.S.220.2.3.4.Y11711.Y.Y11741.6
50253 588.S.220.2.3.4.Y9445.Y11711.Y.6
50257 589.S.220.2.3.4.Y9445.Y11711.Y.6
50260 590.S.220.2.3
The first number is the ID of the offending geometry, the second is a sequence count, the third is the Start .S of the processing, then the process lot number, then .1, .2, .3 etc until .4 when intersections are tested. A leading Y for testing geom intersection and trailling Y for successful intersection found (greater than 5% of the block area). Point .6 is the end of the processing for the block.
The stopping point (.3) above is always the command:
select /*+ ORDERED INDEX(a land_use_pol_p_idx) */ count(a.id) into v_count
               from land_use_pol_polys a
               where a.geometry1 is not null
and a.current_lifecycle_stage = 'GAZETTED'
               and mdsys.sdo_relate(a.geometry1, r_blocks.geometry1, 'mask = CONTAINS querytype = WINDOW') = 'TRUE';
The geometry of block 50260 is:
GEOMETRY1(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
----------------------------------------------------------------------------------------------------
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 5, 6, 1, 2, 1, 3, 2, 1, 5, 2, 1, 7, 2, 1, 9, 2
, 1, 11, 2, 2), SDO_ORDINATE_ARRAY(211381.73937, 597784.54648, 211375.66551, 597784.37269, 211366.93
013, 597784.07825, 211342.15802, 597782.75591, 211342.98222, 597764.48823, 211382.39373, 597765.4085
, 211382.11838, 597774.97926, 211381.73937, 597784.54648))
or as easier readability:
2003 2D POLYGON SRID=NULL
ELEMENT INFO ARRAY
1,1 1, 5, 6 Segment Compound Polygon
2,4 1, 2, 1 Line
3,7 3, 2, 1 Line
4,10 5, 2, 1 Line
5,13 7, 2, 1 Line
6,16 9, 2, 1 Line
7,19 11, 2, 2 Arc
ORDINATE ARRAY
1 211381.73937388, 597784.5464777
3 211375.66550547, 597784.37269333
5 211366.9301254, 597784.07824718
7 211342.15801578, 597782.75590901
9 211342.98222132, 597764.48823378
11 211382.39372974, 597765.40849684
13 211382.118379384, 597774.979259331
15 211381.73937388, 597784.5464777
Notice that the compound geometry ends in an arc. Many of the geometries which the process hangs on have the arc at the end. Note: the polygon design method using complex geometries consisting of lines and arcs is in place so that Intergraph GeoMedia can read/write it.
Thanks for your help so far,
John
Hi,
Can you run this query from interactive sql, and see if it hangs:
select count(a.id)
from land_use_pol_polys a
where
and mdsys.sdo_relate(a.geometry1,
               mdsys.SDO_GEOMETRY(2003, NULL, NULL,
                    mdsys.SDO_ELEM_INFO_ARRAY(1, 5, 6, 1, 2, 1, 3, 2, 1,
                         5, 2, 1, 7, 2, 1, 9, 2 , 1, 11, 2, 2),
                    mdsys.SDO_ORDINATE_ARRAY(211381.73937388, 597784.5464777,
                         211375.66550547, 597784.37269333,
                         211366.9301254, 597784.07824718,
                         211342.15801578, 597782.75590901,
                         211342.98222132, 597764.48823378,
                         211382.39372974, 597765.40849684,
                         211382.118379384, 597774.979259331,
                         211381.73937388, 597784.5464777)),
               'mask=CONTAINS querytype=WINDOW') = 'TRUE';
If this hangs, can you change the mask to anyinteract, and see if it hangs.
If mask=anyinteract doesn't hang, can you create a table with just the
geometries that returned true from the anyinteract mask, then run sdo_geom.relate
with the determine mask to find out which geometry comparison is hanging. Hopefully you
can then post the two geometries.
Thanks,
Dan 
Dan,
I ran the sdo_relate and it hung with the 'CONTAINS' mask. The ANYINTERACT mask produced two geometries: 9445 and 11711:
ID
------------
GEOMETRY1(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
----------------------------------------------------------------------------------------------------
9445
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 5, 29, 1, 2, 2, 5, 2, 1, 7, 2, 2, 11, 2, 2, 15
, 2, 2, 19, 2, 2, 23, 2, 1, 25, 2, 1, 27, 2, 1, 29, 2, 2, 33, 2, 1, 35, 2, 2, 39, 2, 2, 43, 2, 2, 47
, 2, 2, 51, 2, 1, 53, 2, 1, 55, 2, 2, 59, 2, 2, 63, 2, 2, 67, 2, 2, 71, 2, 2, 75, 2, 1, 77, 2, 1, 79
, 2, 1, 81, 2, 1, 83, 2, 2, 87, 2, 2, 91, 2, 1), SDO_ORDINATE_ARRAY(210656.54995, 598648.01519, 2106
61.08731, 598632.98923, 210665.73968, 598617.99847, 210679.60718, 598624.70568, 210819.39373, 598730
.93666, 210949.30647, 598849.03823, 210953.79335, 598850.18701, 210956.98129, 598846.82712, 211061.0
625, 598536.8366, 211222.34801, 598252.38274, 211346.96062, 597993.35571, 211381.87097, 597708.04085
, 211381.24017, 597692.81409, 211396.46694, 597692.18366, 211397.09774, 597707.41042, 211385.82194,
597891.50129, 211337.22129, 598069.4186, 211351.43299, 598074.92129, 211305.27932, 598175.56214, 211
247.3002, 598269.88693, 211083.28542, 598561.96788, 210980.61787, 598880.82757, 210984.21377, 598884
.57063, 210987.79993, 598888.32303, 211103.00352, 599051.2583, 211166.42435, 599240.46075, 211217.78
597, 599526.90154, 211224.29133, 599556.80165, 211226.69214, 599566.96711, 211229.167, 599577.11479,
211233.25731, 599582.70767, 211240.0954, 599583.82635, 211217.59903, 599590.46066, 211196.06502, 59
9599.75512, 211200.25961, 599595.39161, 211200.80966, 599589.36398, 211194.3871, 599562.77108, 21118
8.46291, 599536.06274, 211184.45686, 599517.83405, 211174.77376, 599463.83197, 211172.19671, 599445.
34705, 211136.42319, 599245.84025, 211075.82107, 599065.0468, 210965.73763, 598909.3531, 210824.3125
, 598775.01155, 210670.27051, 598655.34624, 210656.54995, 598648.01519))
11711
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 5, 40, 1, 2, 1, 3, 2, 1, 5, 2, 2, 9, 2, 1, 11,
2, 1, 13, 2, 1, 15, 2, 1, 17, 2, 2, 21, 2, 1, 23, 2, 1, 25, 2, 1, 27, 2, 1, 29, 2, 1, 31, 2, 1, 33,
2, 1, 35, 2, 1, 37, 2, 1, 39, 2, 1, 41, 2, 1, 43, 2, 2, 47, 2, 1, 49, 2, 1, 51, 2, 1, 53, 2, 1, 55,
2, 1, 57, 2, 2, 61, 2, 2, 65, 2, 1, 67, 2, 1, 69, 2, 1, 71, 2, 1, 73, 2, 1, 75, 2, 1, 77, 2, 1, 79,
2, 1, 81, 2, 1, 83, 2, 1, 85, 2, 1, 87, 2, 2, 91, 2, 1), SDO_ORDINATE_ARRAY(210801.96889, 598321.86
04, 210788.47862, 598314.76018, 210964.67218, 597958.43359, 210977.14154, 597920.02393, 210978.082,
597879.65188, 211102.60552, 597769.68172, 211260.58898, 597697.80935, 211381.24017, 597692.81409, 21
1381.87097, 597708.04085, 211382.11898, 597774.96155, 211377.30333, 597841.70921, 211339.68541, 5978
37.55879, 211321.08825, 597842.53512, 211246.6151, 597883.78323, 211224.46065, 597843.78354, 211219.
12803, 597846.7371, 211241.28248, 597886.73678, 211189.33396, 597915.50927, 211172.35475, 597930.380
72, 211150.78748, 597973.98684, 211203.18899, 598005.99553, 211167.87882, 598037.10418, 211170.03809
, 598039.26028, 211172.10356, 598041.50639, 211208.63476, 598009.322, 211273.09741, 598048.69805, 21
1307.3926, 597976.58988, 211327.00758, 597891.84446, 211369.02191, 597899.30452, 211315.0039, 598083
.869, 211222.34801, 598252.38274, 211088.87341, 598477.61398, 210991.41478, 598720.60863, 210955.826
42, 598708.43215, 210963.17073, 598688.399, 210890.99622, 598663.54341, 210845.19446, 598614.65087,
210826.28536, 598550.38023, 210839.32938, 598415.11907, 210890.90549, 598420.09286, 210895.71333, 59
8370.23757, 210848.40993, 598346.85307, 210889.52166, 598247.83148, 210852.68297, 598229.61355, 2108
49.1914, 598229.38083, 210846.55799, 598231.68526, 210801.96889, 598321.8604))
Running sdo_geom.relate on these gives:
1 select mdsys.sdo_geom.relate(a.geometry1,'DETERMINE',
2 mdsys.SDO_GEOMETRY(2003, NULL, NULL,
3 mdsys.SDO_ELEM_INFO_ARRAY(1, 5, 6, 1, 2, 1, 3, 2, 1,
4 5, 2, 1, 7, 2, 1, 9, 2 , 1, 11, 2, 2),
5 mdsys.SDO_ORDINATE_ARRAY(211381.73937388, 597784.5464777,
6 211375.66550547, 597784.37269333,
7 211366.9301254, 597784.07824718,
8 211342.15801578, 597782.75590901,
9 211342.98222132, 597764.48823378,
10 211382.39372974, 597765.40849684,
11 211382.118379384, 597774.979259331,
12 211381.73937388, 597784.5464777)), 0.000005)
13 from bung_geom_temp a
14* where a.id = 11711
15 /
MDSYS.SDO_GEOM.RELATE(A.GEOMETRY1,'DETERMINE',MDSYS.SDO_GEOMETR
---------------------------------------------------------------
COVERS
SDTD> select mdsys.sdo_geom.relate(a.geometry1,'DETERMINE',
2 mdsys.SDO_GEOMETRY(2003, NULL, NULL,
3 mdsys.SDO_ELEM_INFO_ARRAY(1, 5, 6, 1, 2, 1, 3, 2, 1,
4 5, 2, 1, 7, 2, 1, 9, 2 , 1, 11, 2, 2),
5 mdsys.SDO_ORDINATE_ARRAY(211381.73937388, 597784.5464777,
6 211375.66550547, 597784.37269333,
7 211366.9301254, 597784.07824718,
8 211342.15801578, 597782.75590901,
9 211342.98222132, 597764.48823378,
10 211382.39372974, 597765.40849684,
11 211382.118379384, 597774.979259331,
12 211381.73937388, 597784.5464777)), 0.000005)
13 from bung_geom_temp a
14 where a.id = 9445;
MDSYS.SDO_GEOM.RELATE(A.GEOMETRY1,'DETERMINE',MDSYS.SDO_GEOMETRY(2003
---------------------------------------------------------------------
TOUCH
It seems that a geometry which begins or ends with an arc hangs when an sdo_relate is done on it.
I'm just getting ready to alter a geometry which starts with an arc so that it begins somewhere else and see if the problem is fixed.
Thanks for the help.
John 
More info, but baffling results:
I selected a geometry, ID = 9445 and rewrote it so that it didn't start or end with an arc. The resulting geometry was given and ID = 1.
The geometries are:
9445
2003, 2D, SRID =
SDO_ELEM_INFO_ARRAY(
1, 5, 29
1, 2, 2
5, 2, 1
7, 2, 2
11, 2, 2
15, 2, 2
19, 2, 2
23, 2, 1
25, 2, 1
27, 2, 1
29, 2, 2
33, 2, 1
35, 2, 2
39, 2, 2
43, 2, 2
47, 2, 2
51, 2, 1
53, 2, 1
55, 2, 2
59, 2, 2
63, 2, 2
67, 2, 2
71, 2, 2
75, 2, 1
77, 2, 1
79, 2, 1
81, 2, 1
83, 2, 2
87, 2, 2
91, 2, 1
SDO_ORDINATE_ARRAY
210656.54995013, 598648.01519464
210661.087313019, 598632.989227937
210665.73968465, 598617.9984716
210679.60717774, 598624.70568273
210819.393731008, 598730.936655338
210949.3064669, 598849.03822632
210953.793350857, 598850.187008695
210956.9812904, 598846.82711732
211061.062495793, 598536.836602775
211222.34801134, 598252.38274202
211346.960620821, 597993.355712383
211381.87096601, 597708.04084715
211381.2401705, 597692.8140882
211396.46694457, 597692.18366199
211397.0977399, 597707.41042098
211385.821944004, 597891.501291739
211337.22128765, 598069.4185997
211351.43299165, 598074.92128815
211305.279318533, 598175.562141029
211247.30020356, 598269.88692588
211083.285420231, 598561.967877413
210980.61786866, 598880.82757266
210984.213768782, 598884.570634513
210987.79992519, 598888.32303267
211103.003519799, 599051.258299343
211166.42434994, 599240.46075081
211217.78597134, 599526.90153942
211224.29133429, 599556.80165453
211226.692139711, 599566.967110544
211229.16700029, 599577.11479145
211233.257309155, 599582.707670606
211240.09540024, 599583.8263458
211217.599031812, 599590.460664176
211196.06501592, 599599.75512125
211200.259605731, 599595.391605968
211200.80965986, 599589.36397876
211194.387100479, 599562.771078306
211188.46291047, 599536.06273511
211184.45685554, 599517.83405077
211174.77375689, 599463.83197024
211172.19670546, 599445.34705165
211136.42319409, 599245.84025007
211075.82106751, 599065.046796446
210965.73763266, 598909.35309718
210824.31249632, 598775.011549773
210670.27050708, 598655.3462411
210656.54995013, 598648.01519464
SDTD> select mdsys.sdo_geom.validate_geometry(geometry1, 0.000005)
2 from bung_geom_temp where id = 9445;
MDSYS.SDO_GEOM.VALIDATE_GEOMETRY(GEOMETRY1,0.000005)
---------------------------------------------------------------------
TRUE
Geometry 1
2003, 2D, SRID =
SDO_ELEM_INFO_ARRAY(
1, 5, 29
1, 2, 1
3, 2, 1
5, 2, 2
9, 2, 1
11, 2, 2
15, 2, 2
19, 2, 2
23, 2, 2
27, 2, 1
29, 2, 1
31, 2, 2
35, 2, 2
39, 2, 2
43, 2, 2
47, 2, 2
51, 2, 1
53, 2, 1
55, 2, 1
57, 2, 1
59, 2, 2
63, 2, 2
67, 2, 1
69, 2, 2
73, 2, 1
75, 2, 2
79, 2, 2
83, 2, 2
87, 2, 2
91, 2, 1
SDO_ORDINATE_ARRAY
211381.24017, 597692.81409
211396.46694, 597692.18366
211397.09774, 597707.41042
211385.82194, 597891.50129
211337.22129, 598069.4186
211351.43299, 598074.92129
211305.27932, 598175.56214
211247.3002, 598269.88693
211083.28542, 598561.96788
210980.61787, 598880.82757
210984.21377, 598884.57063
210987.79993, 598888.32303
211103.00352, 599051.2583
211166.42435, 599240.46075
211217.78597, 599526.90154
211224.29133, 599556.80165
211226.69214, 599566.96711
211229.167, 599577.11479
211233.25731, 599582.70767
211240.0954, 599583.82635
211217.59903, 599590.46066
211196.06502, 599599.75512
211200.25961, 599595.39161
211200.80966, 599589.36398
211194.3871, 599562.77108
211188.46291, 599536.06274
211184.45686, 599517.83405
211174.77376, 599463.83197
211172.19671, 599445.34705
211136.42319, 599245.84025
211075.82107, 599065.0468
210965.73763, 598909.3531
210824.3125, 598775.01155
210670.27051, 598655.34624
210656.54995, 598648.01519
210661.08731, 598632.98923
210665.73968, 598617.99847
210679.60718, 598624.70568
210819.39373, 598730.93666
210949.30647, 598849.03823
210953.79335, 598850.18701
210956.98129, 598846.82712
211061.0625, 598536.8366
211222.34801, 598252.38274
211346.96062, 597993.35571
211381.87097, 597708.04085
211381.24017, 597692.81409
SDTD> select mdsys.sdo_geom.validate_geometry(geometry1, 0.000005)
2 from bung_geom_temp where id = 1;
MDSYS.SDO_GEOM.VALIDATE_GEOMETRY(GEOMETRY1,0.000005)
-------------------------------------------------------------------
TRUE
Now a simple rectangle geometry was created that intersects with the above geometry:
MDSYS.SDO_GEOMETRY(2003, NULL, NULL,
mdsys.SDO_ELEM_INFO_ARRAY(1,5,4,1,2,1,3,2,1,5,2,1,7,2,1),
mdsys.SDO_ORDINATE_ARRAY(211141.254, 598419.597,
211206.260, 598309.448,
211409.403, 598462.483,
211322.729, 598566.311,
211141.254, 598419.597))
The tests on the two are as follows:
1. Find the "official" interaction between the two:
1 select mdsys.sdo_geom.relate(a.geometry1, 'DETERMINE', MDSYS.SDO_GEOMETRY(2003, NULL, NUL
2 mdsys.SDO_ELEM_INFO_ARRAY(1,5,4,1,2,1,3,2,1,5,2,1,7,2,1),
3 mdsys.SDO_ORDINATE_ARRAY(211141.254, 598419.597,
4 211206.260, 598309.448,
5 211409.403, 598462.483,
6 211322.729, 598566.311,
7 211141.254, 598419.597)), 0.000005)
8 from bung_geom_temp a
9* where id = 9445
SDTD> /
MDSYS.SDO_GEOM.RELATE(A.GEO
----------------------------------------------------------------------------------------------
OVERLAPBDYINTERSECT
Now return this relationship using sdo_relate:
1 select id from bung_geom_temp a
2 where a.id = 9445
3 and mdsys.sdo_relate(a.geometry1,
4 MDSYS.SDO_GEOMETRY(2003, NULL, NULL,
5 mdsys.SDO_ELEM_INFO_ARRAY(1,5,4,1,2,1,3,2,1,5,2,1,7,2,1),
6 mdsys.SDO_ORDINATE_ARRAY(211141.254, 598419.597,
7 211206.260, 598309.448,
8 211409.403, 598462.483,
9 211322.729, 598566.311,
10* 211141.254, 598419.597)), 'mask=OVERLAPBDYINTERSECT querytype=WINDOW') = 'TRUE'
SDTD> /
no rows selected
Quite odd?
1 select id from bung_geom_temp a
2 where a.id = 1
3 and mdsys.sdo_relate(a.geometry1,
4 MDSYS.SDO_GEOMETRY(2003, NULL, NULL,
5 mdsys.SDO_ELEM_INFO_ARRAY(1,5,4,1,2,1,3,2,1,5,2,1,7,2,1),
6 mdsys.SDO_ORDINATE_ARRAY(211141.254, 598419.597,
7 211206.260, 598309.448,
8 211409.403, 598462.483,
9 211322.729, 598566.311,
10* 211141.254, 598419.597)), 'mask=OVERLAPBDYINTERSECT querytype=WINDOW') = 'TRUE'
SDTD> /
ID
------------
1
Now that's what I would expect to get. The only difference between geometries 9445 and 1 is their order of construction. However, the processing of these geometries did not hang, which was what I was hoping for.
Blast!
John
Hi John,
What kind of indexes are you using?
Also, any chance we could work with your data (i.e. just the geometries, no need for attributes).
I tried to reproduce problems with the geoms as posted but couldn't.
Thanks,
Dan (daniel.abugov#oracle.com) 
Hi Dan,
The Oracle version which produced the above problem is 8.1.6 and so the indexes are quadtree. I also have 9.2 with rtree indexing.
On my 9.2 database, I wrote a function which re-orders the geometry so that it doesn't begin or end with an arc. Remarkably, all the places where my program previously hung have stopped.
However, there are some geometries which cannot be re-ordered because they don't contain a sequence of two lines to start and end the re-ordered polygon; E.g. A polygon consisting of line-arc-line-arc-line-arc. When my program encountered these geometries, it produced the errors:
ERROR: with LUP 9698
ORA-29902: error in executing ODCIIndexStart() routine
ORA-13034: Invalid data in the SDO_ORDINATE_A
Clapped out at c_blocks << This is my error message!
ORA-01001: invalid cursor
ORA-29902: error in executing ODCIIndexStart() routine
ORA-13034: Invalid
The LUP (Land Use Polygon) in question, ID = 9698 had been accessed 232 times previous to this error and even though it does end in an arc the de-arcing process has rendered it workable.
The other geometry being compared is:
2003, 2,NULL,NULL,
SDO_ELEM_INFO_ARRAY(
1, 5, 5
1, 2, 1
3, 2, 2
7, 2, 1
9, 2, 2
13, 2, 2
SDO_ORDINATE_ARRAY
210738.401702, 606609.43294014
210752.68642642, 606590.73239092
210773.423143229, 606621.447422646
210774.66087507, 606658.48649145
210767.17507649, 606646.50670952
210754.600926561, 606629.914585996
210739.2596295, 606615.84147639
210737.503414434, 606612.814890868
210738.401702, 606609.43294014
which as you can see is a line-arc-line-arc-arc geometery which I cannot currently make workable. I am working on a process to break a line in two to produce the consecutive lines needed for the de-arcing.
I also have some geometries which are all arcs. I have yet to decide what to do with these!
If you like I can send you a set of ok and crook geometries.
My email is jmcglynn#navigate.com.au
Thanks,
John

SPATIAL_RELATE DOES NOT WORK ON VIEW ?

Hi
The following statement does not work correct - it claims that there are no spatial indexes. The view is very complex (including union all). The view is put into user_sdo_geom_metadata.
Whats wrong ?
SELECT GEOM FROM MISE_A_JOURPFP3 WHERE FID=70491;
This returns
GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1005, 11, 1, 2, 1, 3, 2, 2, 7, 2, 1, 13, 2, 2, 17, 2, 1, 27, 2, 2, 31, 2, 1, 103, 2, 2, 107, 2, 1, 111, 2, 2, 115, 2, 1, 119, 2005, 10, 119, 2, 1, 125, 2, 2, 129, 2, 1, 131, 2, 2, 135, 2, 1, 163, 2, 2, 167, 2, 1, 169, 2, 2, 173, 2, 1, 177, 2, 2, 183, 2005, 3, 183, 2, 1, 191, 2, 2, 195, 2, 1), SDO_ORDINATE_ARRAY(575526,016, 184396,522, 575522,171, 184370,728, 575522,191, 184370,674, 575522,247, 184370,662, 575678,571, 184417,747, 575774,588, 184446,858, 575869,409, 184475,451, 575869,442, 184475,474, 575869,452, 184475,513, 575866,134, 184514,875, 575863,237, 184550,71, 575863,237, 184550,712, 575863,236, 184550,715, 575855,625, 184597,93, 575855,603, 184597,968, 575855,56, 184597,98, 575817,694, 184594,079, 575817,693, 184594,079, 575779,647, 184589,561, 575779,644, 184589,56, 575779,642, 184589,56, 575760,713, 184585,669, 575760,711, 184585,668, 575760,709, 184585,668, 575742,776, 184580,811, 575742,774, 184580,81, 575711,463, 184571,042, 575681,849, 1845
61,823, 575681,848, 184561,823, 575653,299, 184552,366, 575653,299, 184552,366, 575624,364, 184542,482, 575624,362, 184542,481, 575608,52, 184536,628, 575608,518, 184536,627, 575608,516, 184536,626, 575594,546, 184530,149, 575594,544, 184530,149, 575594,542, 184530,148, 575580,796, 184522,588, 575580,794, 184522,586, 575580,792, 184522,585, 575568,694, 184514,569, 575568,692, 184514,568, 575568,69, 184514,566, 575557,75, 184506,036, 575557,748, 184506,035, 575557,747, 184506,033, 575547,93, 184497,069, 575547,928, 184497,068, 575547,927, 184497,067, 575540,772, 184489,753, 575540,755, 184489,711, 575540,773, 184489,668, 575541,753, 184488,693, 575539,485, 184486,399, 575539,474, 184486,384, 575539,469, 184486,366, 575526,016, 184396,522, 575456,142, 184567,991, 575581,098, 184603,851, 575663,938, 184627,624, 575753,329, 184653,277, 575753,375, 184653,271, 575753,404, 184653,236, 575770,26, 184594,501, 575770,254, 184594,454, 575770,216, 184594,426, 575759,18, 184591,871, 575741,055, 184586,909, 575709,707, 18
4577,166, 575680,93, 184568,419, 575679,878, 184568,099, 575651,254, 184558,428, 575622,3, 184548,627, 575606,091, 184542,531, 575599,557, 184539,564, 575591,605, 184535,955, 575577,419, 184528,038, 575564,828, 184519,711, 575553,658, 184510,986, 575543,325, 184501,439, 575543,313, 184501,43, 575543,299, 184501,425, 575479,972, 184484,946, 575479,927, 184484,952, 575479,899, 184484,987, 575465,302, 184535,853, 575456,101, 184567,916, 575456,107, 184567,962, 575456,142, 184567,991, 575490,379, 184553,269, 575487,99, 184561,46, 575516,025, 184569,447, 575519,283, 184558,023, 575495,012, 184551,068, 575494,977, 184551,039, 575494,971, 184550,993, 575495,03, 184550,79, 575494,928, 184550,761, 575490,379, 184553,269))
This select fails:
Select /*+ ordered */ A.fid from MISE_A_JOURPFP3 A, MISE_A_JOURPFP3 B where B.FID= 70491 AND mdsys.sdo_relate(B.geom, A.GEOM ,'mask = anyinteract querytype=window') = 'TRUE' ;
output:
Select /*+ ordered */ A.fid from MISE_A_JOURPFP3 A, MISE_A_JOURPFP3 B where B.FID= 70491 AND mdsys.sdo_relate(B.geom, A.GEOM ,'mask = anyinteract querytype=window') = 'TRUE'
*
FEHLER in Zeile 1:
ORA-13226: Oberfläche ohne räumlicher Index nicht unterstützt
ORA-06512: in "MDSYS.MD", Zeile 1723
ORA-06512: in "MDSYS.MDERR", Zeile 8
ORA-06512: in "MDSYS.SDO_3GL", Zeile 58
DB is 9.2.0.5 on Windows XP 
is there a spatial index on the underlying spatial layer in the view? 
Hi
Yes - there are spatial indexes on all geom-columns in the view. The select works fine when i put the geom object directly into the select - instead of the sub-select. But we do not want to do this because depending on perimeter the SQL can get very long and exceeds parameter-binding-limit.
Thank you for help
Toni 
Hi,
Can you post the view definition?
Thanks!
This might be related to bug 3561140 - we are working with the appropriate folks in Oracle to try to resolve it. If you can post your view definition then we can try to ensure this is fixed at the same time (a small test case would be appreciated). 
See Tar 3859345.999 for View definition
Thank you for help
Toni 
Hi
We got a workaround from ORA-Support
The following works:
Select A.FID
From MISE_A_JOURPFP3 A
Where Exists (
Select NULL From MISE_A_JOURPFP3 B
Where B.FID= 70491
And MdSys.Sdo_Relate(B.GEOM,a.GEOM ,'mask = anyinteract querytype=window') ='TRUE')
Anyway, i hope this will be fixed in future releases
Thank you for help
Toni

Problem about the "EOF"

I am trying to load spatial data into a database with SQL*Loader and it work, after that i valid each geometry in the spatial column by calling SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT() then oracle show me that the connection gateway is "EOF" and the connection break off, even calling the SDO_GEOM.SDO_AREA() it show me the same message too. I wonder that if the original data is wrong, maybe the MDSYS.SDO_ELEM_INFO_ARRAY does not match the MDSYS.SDO_ORDINATE_ARRAY, could this problem happen, or it should show me the information if the geometry is invalid rather than break the connection. 
Hi Lee,
Can you do anything with spatial? Do you get the eof message on the first geometry you try? Can you do a:
select geom_col_name from your_table where rownum <2;
and post the results? What version of spatial are you using?
Thanks,
Dan 
Hi,
here is the definition of the spatial table
CREATE TABLE P1(
ID NUMBER(10),
SHAPE MDSYS.SDO_GEOMETRY);
and with the statement "SELECT shape FROM p1" i can see all the content of the geometry column without any error message, but when i try to do this
DECLARE
CURSOR c1 IS
SELECT * FROM P1;
info VARCHAR2(200);
BEGIN
FOR c1_rec IN c1 LOOP
info := SDO_GEOM.VALIDATE_GEOMETRY(c1_rec.shape, 0.00001);
INSERT INTO test VALUES(c1_rec.id, info);
commit;
END LOOP;
END;
oracle shows "ORA-03113: communication gateway is EOF", with the table "test" i am sure that it is wrong with specific geometry object but don't know how this problem is happen, can you help me? Thanks! 
here is the record with problem, can somebody try this and tell me why it wrong, thanks!
ID
----------
SHAPE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
66894
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1005, 41, 1, 2, 1, 3, 2, 1
, 5, 2, 2, 9, 2, 1, 11, 2, 1, 13, 2, 1, 15, 2, 1, 17, 2, 1, 19, 2, 1, 21, 2, 1,
23, 2, 1, 25, 2, 2, 29, 2, 1, 31, 2, 1, 33, 2, 1, 35, 2, 1, 37, 2, 1, 39, 2, 1,
41, 2, 1, 43, 2, 2, 47, 2, 2, 51, 2, 2, 55, 2, 2, 59, 2, 2, 63, 2, 1, 65, 2, 1,
67, 2, 1, 69, 2, 1, 71, 2, 1, 73, 2, 1, 75, 2, 1, 77, 2, 1, 79, 2, 1, 81, 2, 1,
83, 2, 1, 85, 2, 1, 87, 2, 1, 89, 2, 2, 93, 2, 2, 97, 2, 1, 99, 2, 1, 101, 2005,
36, 101, 2, 1, 103, 2, 1, 105, 2, 1, 107, 2, 1, 109, 2, 1, 111, 2, 1, 113, 2, 1
, 115, 2, 1, 117, 2, 1, 119, 2, 2, 123, 2, 1, 125, 2, 1, 127, 2, 1, 129, 2, 1, 1
ID
----------
SHAPE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
31, 2, 1, 133, 2, 1, 135, 2, 1, 137, 2, 1, 139, 2, 1, 141, 2, 1, 143, 2, 2, 147,
2, 1, 149, 2, 1, 151, 2, 1, 153, 2, 1, 155, 2, 1, 157, 2, 1, 159, 2, 1, 161, 2,
1, 163, 2, 2, 167, 2, 1, 169, 2, 1, 171, 2, 1, 173, 2, 1, 175, 2, 1, 177, 2, 2)
, SDO_ORDINATE_ARRAY(271414.83, 2750721.49, 271423.33, 2750724.49, 271427.9, 275
0724.17, 271430.81, 2750720.62, 271431.75, 2750717.32, 271433.41, 2750711.45, 27
1435.08, 2750705.6, 271436.73, 2750699.76, 271438.4, 2750693.9, 271440.07, 27506
88.01, 271441.67, 2750682.38, 271444.34, 2750672.98, 271443.83, 2750668.38, 2714
40.26, 2750665.43, 271439.22, 2750665.1, 271434.58, 2750663.65, 271429.78, 27506
62.14, 271431.92, 2750654.42, 271442.58, 2750657.77, 271457.91, 2750662.59, 2715
ID
----------
SHAPE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
01.57, 2750676.29, 271503.802, 2750677.21, 271505.84, 2750678.51, 271508.65, 275
0681.37, 271510.53, 2750684.91, 271510.857, 2750685.95, 271511.1, 2750687.01, 27
1511.328, 2750690.41, 271510.72, 2750693.77, 271510.69, 2750693.89, 271510.65, 2
750694, 271508.68, 2750699.96, 271506.64, 2750706.14, 271504.61, 2750712.3, 2715
02.56, 2750718.51, 271500.52, 2750724.69, 271498.47, 2750730.9, 271496.42, 27507
37.09, 271494.4, 2750743.23, 271492.4, 2750749.28, 271508.7, 2750756.58, 271508.
26, 2750758.07, 271497.38, 2750755.89, 271490.7, 2750752.9, 271488.86, 2750754.3
, 271486.62, 2750754.88, 271485.43, 2750754.84, 271484.27, 2750754.55, 271414.36
, 2750729.8, 271414.83, 2750721.49, 271486.87, 2750740.54, 271488.04, 2750737, 2
ID
----------
SHAPE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
71489.99, 2750731.07, 271491.97, 2750725.08, 271493.91, 2750719.2, 271495.87, 27
50713.26, 271497.81, 2750707.37, 271499.77, 2750701.43, 271503.05, 2750691.49, 2
71502.7, 2750686.9, 271499.17, 2750683.92, 271496.95, 2750683.22, 271491.92, 275
0681.64, 271486.95, 2750680.08, 271485.42, 2750679.6, 271470.98, 2750675.07, 271
469.56, 2750674.62, 271464.92, 2750673.17, 271460.31, 2750671.72, 271455.65, 275
0670.26, 271455.6, 2750670.24, 271451.01, 2750670.68, 271448.13, 2750674.28, 271
445.73, 2750682.74, 271444.01, 2750688.77, 271442.3, 2750694.8, 271440.59, 27507
00.83, 271438.87, 2750706.85, 271437.16, 2750712.88, 271435.45, 2750718.9, 27143
4.52, 2750722.17, 271434.99, 2750726.8, 271438.49, 2750729.86, 271443.7, 2750731
ID
----------
SHAPE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
.7, 271453.11, 2750735.03, 271465.52, 2750739.42, 271472.76, 2750741.99, 271479.
24, 2750744.28, 271483.84, 2750744.02, 271486.87, 2750740.54))
Hi Lee,
You've found a problem with validate - it has been entered in Oracle's bug database as number 2669515.
I was fortunate enough to see the problem with the geometry. The last item in the elem info array is
defined as 177,2,2, which means at offset 177 there is a line composed of circular arcs. however, there
are only 180 ordinates in the sdo_ordinates field, so the last element can't be a circular arc as it would
require there were 182 coordinates in the ordinate array.
I changed the 177,2,2 to 177,2,1 and it validated correctly.

BUFFER around a CIRCLE problem

Hello All;
When I create a buffer around a circle and when there are negative and positive coordinates, the result is wrong. It's correct when all coordinates are positives or negatives.
Here is a wrong result :
SQL> SELECT * FROM buffer WHERE GID = '4';
GID
4
GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 3, 4), SDO_ORDINATE_ARRAY(
-10, 15, 0, 30, 10, 15))
BUFFER(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1005, 8, 1, 2, 1, 3, 2, 2,
7, 2, 1, 9, 2, 2, 13, 2, 1, 15, 2, 2, 19, 2, 1, 21, 2, 2), SDO_ORDINATE_ARRAY(0
, 32, -3,538E-09, 32, -9,074537, 28,2412037, 9,5833E-09, 19,1666667, 9,5833E-09,
19,1666667, -9,074537, 10,0921297, -3,538E-09, 6,33333334, 0, 6,33333334, 9,074
53702, 10,0921296, 9,5833E-09, 19,1666667, 9,5833E-09, 19,1666667, 9,07453702, 2
8,2412037, 0, 32))
is there anybody who has noticed this problem?
Thanks
Fx
Note: I'm using Spatial 8.1.7 on NT4 
Hi,
I tried this on 8.1.7 - this is the result I got:
SQL> select * from buff_tab;
GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z),
SDO_ELEM_INFO, SDO_ORDINATES)
---------------------------------------------
SDO_GEOMETRY(2003, NULL, NULL,
SDO_ELEM_INFO_ARRAY(1, 3, 4),
SDO_ORDINATE_ARRAY(-10,15,0,30,10,15))
SQL> select sdo_geom.sdo_buffer(geom,2,0.5) from buff_tab;
SDO_GEOM.SDO_BUFFER(GEOM,2,0.5)(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELE
--------------------------------------------------------------------------------
SDO_GEOMETRY(2003, NULL, NULL,
SDO_ELEM_INFO_ARRAY(1, 1005, 4, 1, 2, 1, 3, 2, 2,7, 2, 1, 9, 2, 2),
SDO_ORDINATE_ARRAY(0, 32.1666667,
-.19004525, 32.1576169, -12, 19.1666667,
-.19004525, 6.17571644, 0, 6.16666667, 13,
19.1666667, 0, 32.1666667))
I did check the geometry returned for your query - it is definately wrong. Also, in 9i I got a slightly different (although still correct) answer.
Can you post the query you ran?
Thanks.
dan 
My query is :
INSERT INTO BUFFER VALUES ('14',
MDSYS.SDO_GEOMETRY(2003, NULL, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,3,4),
MDSYS.SDO_ORDINATE_ARRAY(-10,50, 0,70, 10,50)), NULL);
UPDATE BUFFER SET (BUFFER) = (select mdsys.SDO_GEOM.SDO_BUFFER(b.GEOMETRY, 2, 0.00000000005) from BUFFER b where b.GID = BUFFER.GID) where GID = '14';
SELECT * FROM buffer WHERE GID = '14';
Result is :
GID
----------
GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
BUFFER(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
14
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 3, 4), SDO_ORDINATE_ARRAY(
-10, 50, 0, 70, 10, 50))
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1005, 8, 1, 2, 1, 5, 2, 2,
9, 2, 1, 11, 2, 2, 15, 2, 1, 19, 2, 2, 23, 2, 1, 25, 2, 2), SDO_ORDINATE_ARRAY(
-2,742E-16, 72, -9,200E-09, 72, -1,489E-07, 72, -10,253048, 67,7530485, 2,8750E-
08, 57,5, 2,8750E-08, 57,5, -10,253049, 47,246952, -9,200E-09, 43, -2,742E-16, 4
3, 1,0041E-07, 43, 10,253048, 47,2469514, 2,8750E-08, 57,5, 2,8750E-08, 57,5, 10
,2530486, 67,753048, -2,742E-16, 72))
I tried with other circles and sometimes it works sometimes it doesn't... It's strange.
Thanks for your help
Francois 
Hi,
Thanks for following up. It looks like there is some numerical instability associated with the tolerance value.
Would it be possible to use a less precise tolerance? When I set the tolerance to .0000005 it worked correctly in every case.
Also, buffer code has been reworked significantly in 9i. It works correctly regardless of the tolerance value specified.
hope this helps,
dan

ORA-13349 with different tolerances

Hi
I have the following geometry:
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 5, 3, 1, 2, 2, 9, 2, 1, 25, 2, 2), SDO_ORDINATE_ARRAY(577581.917, 184411.939, 577578.113, 184409.454, 577574.969, 184406.174, 577572.632, 184402.257, 577571.243, 184397.912, 577576.479, 184372.519, 577595.843, 184375.038, 577605.277, 184384.152, 577601.714, 184399.53, 577602.738, 184399.438, 577599.34, 184414.163, 577598.332, 184414.207, 577590.675, 184414.136, 577586.17, 184413.538, 577581.917, 184411.939))
I got from validate_geometry the following output:
SQL> select sdo_geom.validate_geometry(geom, 0.05) from bienfonds_tser where fid = 52375;
SDO_GEOM.VALIDATE_GEOMETRY(GEOM,0.05)
---------------------------------------------------------------------------------------------
TRUE
SQL> select sdo_geom.validate_geometry(geom, 0.03) from bienfonds_tser where fid = 52375;
SDO_GEOM.VALIDATE_GEOMETRY(GEOM,0.03)
---------------------------------------------------------------------------------------------
13349
SQL> select sdo_geom.validate_geometry(geom, 0.01) from bienfonds_tser where fid = 52375;
SDO_GEOM.VALIDATE_GEOMETRY(GEOM,0.01)
---------------------------------------------------------------------------------------------
TRUE
Wath I don't understand: I get a valid geometry with 0.01 and 0.05, but a invalid with 0.03.
I expected that a geometry is invalid to a certain tolerance, say 0.05, and for tolerances less than 0.05 the geometry is valid.
Did I understand something wrong? Can someone explain me?
Philipp 
Hi Philipp,
This looks like a bug. Whe a geometry is valid at a given tolerance it should not change invalid when the tolerance is tightened (made smaller). I tested this in 9.2 and it seems to be fixed (I got "TRUE" at all three tolerances).
What version are you using?
Hi Daniel
I use 9.2.0.2 (latest patchset) on Windows XP.
Philipp 
I wonder if this is an XP platform specific bug. Can you post the results of the following three statements?
select sdo_geom.validate_geometry(
MDSYS.SDO_GEOMETRY(2003, NULL, NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1, 5, 3, 1, 2, 2, 9, 2, 1, 25, 2, 2),
MDSYS.SDO_ORDINATE_ARRAY(577581.917, 184411.939, 577578.113, 184409.454,
577574.969, 184406.174,
577572.632, 184402.257, 577571.243, 184397.912, 577576.479, 184372.519,
577595.843, 184375.038, 577605.277, 184384.152, 577601.714, 184399.53,
577602.738, 184399.438, 577599.34, 184414.163, 577598.332, 184414.207,
577590.675, 184414.136, 577586.17, 184413.538, 577581.917, 184411.939)),0.05) from dual;
select sdo_geom.validate_geometry(
MDSYS.SDO_GEOMETRY(2003, NULL, NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1, 5, 3, 1, 2, 2, 9, 2, 1, 25, 2, 2),
MDSYS.SDO_ORDINATE_ARRAY(577581.917, 184411.939, 577578.113, 184409.454,
577574.969, 184406.174,
577572.632, 184402.257, 577571.243, 184397.912, 577576.479, 184372.519,
577595.843, 184375.038, 577605.277, 184384.152, 577601.714, 184399.53,
577602.738, 184399.438, 577599.34, 184414.163, 577598.332, 184414.207,
577590.675, 184414.136, 577586.17, 184413.538, 577581.917, 184411.939)),0.03) from dual;
select sdo_geom.validate_geometry(
MDSYS.SDO_GEOMETRY(2003, NULL, NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1, 5, 3, 1, 2, 2, 9, 2, 1, 25, 2, 2),
MDSYS.SDO_ORDINATE_ARRAY(577581.917, 184411.939, 577578.113, 184409.454,
577574.969, 184406.174,
577572.632, 184402.257, 577571.243, 184397.912, 577576.479, 184372.519,
577595.843, 184375.038, 577605.277, 184384.152, 577601.714, 184399.53,
577602.738, 184399.438, 577599.34, 184414.163, 577598.332, 184414.207,
577590.675, 184414.136, 577586.17, 184413.538, 577581.917, 184411.939)),0.01) from dual;
Thanks. 
All three statements returns TRUE!
I was feeling like a fool...
But my geometry still returns 13349.
Then I understand, that SQL*Plus don't give me the exact coordinates.
Try this statement:
select sdo_geom.validate_geometry(
MDSYS.SDO_GEOMETRY(2003, NULL, NULL,
MDSYS.SDO_ELEM_INFO_ARRAY
(1, 5, 3, 1, 2, 2, 9, 2, 1, 25, 2, 2),
mdsys.SDO_ORDINATE_ARRAY(
577581.917000, 184411.939000,
577578.113488, 184409.453614,
577574.969000, 184406.174000,
577572.631742, 184402.256894,
577571.243000, 184397.912000,
577576.479000, 184372.519000,
577595.843000, 184375.038000,
577605.277000, 184384.152000,
577601.714000, 184399.530000,
577602.738000, 184399.438000,
577599.340000, 184414.163000,
577598.332000, 184414.207000,
577590.675000, 184414.136000,
577586.170391, 184413.538240,
577581.917000, 184411.939000))
,0.03) from dual;
Hi Philipp,
Now this is the same as the other one - try wrapping it in arc_densify and it should work fine:
select sdo_geom.validate_geometry(sdo_geom.sdo_arc_densify(
MDSYS.SDO_GEOMETRY(2003, NULL, NULL,
MDSYS.SDO_ELEM_INFO_ARRAY
(1, 5, 3, 1, 2, 2, 9, 2, 1, 25, 2, 2),
mdsys.SDO_ORDINATE_ARRAY(
577581.917000, 184411.939000,
577578.113488, 184409.453614,
577574.969000, 184406.174000,
577572.631742, 184402.256894,
577571.243000, 184397.912000,
577576.479000, 184372.519000,
577595.843000, 184375.038000,
577605.277000, 184384.152000,
577601.714000, 184399.530000,
577602.738000, 184399.438000,
577599.340000, 184414.163000,
577598.332000, 184414.207000,
577590.675000, 184414.136000,
577586.170391, 184413.538240,
577581.917000, 184411.939000)),0.01,'arc_tolerance=0.03')
,0.03) from dual;
regards,
dan

Categories

Resources