SDO_RELATE: Process Hangs - Spatial

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

Related

SDO_INTERSECTION: Trouble with tolerance

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.

sdo_relate bug6011024

Hi.
I have a table "rmqltest" with Index:
INSERT INTO user_sdo_geom_metadata (TABLE_NAME, COLUMN_NAME, DIMINFO,SRID)VALUES
('rmqltest','shape',SDO_DIM_ARRAY( SDO_DIM_ELEMENT('X', 0, 1000, 0.01),SDO_DIM_ELEMENT('Y', 0, 1000, 0.01)),NULL); CREATE INDEX rmqltest_idx     ON rmqltest(shape) INDEXTYPE IS MDSYS.SPATIAL_INDEX;
and Content select shape from rmqltest where id=8009;
--------------------------------------------------------------------------------
SDO_GEOMETRY(2004, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 1, 3, 1, 1, 5, 1, 1, 7, 1, 1, 9, 2, 1, 13, 1, 1, 15, 1, 1, 17, 1, 1, 19, 1, 1, 21, 1, 1, 23, 1, 1, 25,1, 1, 27, 1, 1, 29, 1, 1, 31, 1, 1, 33, 1, 1, 35, 1, 1), SDO_ORDINATE_ARRAY(131, 0, 131, 158, 131, 132, 131, 165, 158, 0, 158, -2, 158, 158, 158, 131, 146, 0, 146, 166, 166, 0, 166, 132, 166, 146, 165, 0, 132, 0, 132, 165, 132, 131, 132, 166));
I tryed :
select c.id from rmqltest c where SDO_relate(c.shape, SDO_GEOMETRY(2004, NULL, NULL,sDO_ELEM_INFO_ARRAY(1,1,1,3,1,1),SDO_ORDINATE_ARRAY(131,0, 131,158)) ,'mask=contains')='TRUE'
the Relation with Points 131,0 131,158 should be on or covers but it Does NOT return the id 8009
I also tryed ALL relations
...mask=CONTAINS+EQUAL+INSIDE+COVEREDBY+ COVERS + TOUCH + OVERLAPBDYINTERSECT + DISJOIN+TOUCH+OVERLAPBDYDISJOINT
which returns "no rows selected"
AND
select c.id,sdo_geom.relate (c.shape,'determine',SDO_GEOMETRY(2004, NULL, NULL,
SDO_ELEM_INFO_ARRAY(1,1,1,3,1,1),SDO_ORDINATE_ARRAY(131,0, 131,158)), 0.5) from rmqltest c where c.id>8000 and c.id<8015;
That returns 8009 Contains !!!
1) Why is that?
I am using Version 10.2.0.3.0
2)is the reason the bug (6011024) or is my querry or my Indexing not correct ????
3) If I only have points Is it recommended to change my geometrys SDO_GTYPE 2004(COLLECTION) to MULTIPOINT 2005
4) Does the change make the sdo_relate querrys faster?
thx 4 help
timon

problems with basic spatial query

I'm trying to learn Oracle Spatial working with 11g R2 and with 3D georeferenced data (specifically data describing buildings in a city).
But I'm having trouble getting a basic query to work on my dataset (it works for the book example), and I'm trying to do it exactly the way it's done in the Spatial Developer's Guide for 11g.
To learn how spatial queries work, I set up the cola_markets tables used in the documentation, made the appropriate manual entry in the user_sdo_geom_metadata view and created the index. Having done that, I can run the following simple query (as well as the others in the manual) on the book tables:
SELECT SDO_GEOM.SDO_DISTANCE(c_b.shape, c_d.shape, 0.005)
FROM cola_markets c_b, cola_markets c_d
WHERE c_b.name = 'cola_b' AND c_d.name = 'cola_d';
but when I try to do the same thing on my own tables (created from citygml data), I get an error. There is the difference that the data is 3D, and the index was created without any PARAMETERS ( ... ), hence is just 2D. But still I don't get why the following query doesn't work:
SELECT SDO_GEOM.SDO_DISTANCE(c_w.envelope, c_b.envelope, 0.0005)
FROM cityobject c_w,
cityobject c_b
WHERE c_w.id = 50025
AND c_b.id = 50018;
The id's for the buildings are valid, and I used the same tolerance used by the software that set up the database.
Here's the error I get in SQL developer:
ORA-29532: Java call terminated by uncaught Java exception: java.lang.Exception: 54535
ORA-06512: at "MDSYS.SDO_3GL", line 637
ORA-06512: at "MDSYS.SDO_GEOM", line 1973
ORA-06512: at "MDSYS.SDO_GEOM", line 1990
29532. 00000 - "Java call terminated by uncaught Java exception: %s"
*Cause: A Java exception or error was signaled and could not be
resolved by the Java code.
*Action: Modify Java code, if this behavior is not intended.
So, thinking it might have something to do with the fact that it's a Java interface, I also tried running it from SQL/PL command line and get essentially the same thing:
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.lang.Exception: 54535
ORA-06512: at "MDSYS.SDO_3GL", line 637
ORA-06512: at "MDSYS.SDO_GEOM", line 1973
ORA-06512: at "MDSYS.SDO_GEOM", line 1990
Any ideas why this isn't working? 
The error 54535 means the geometry data is not valid.
So you should first run validate_geometry_with_context on the data to check the errors and fix the data.
siva 
i'll give that a try, thanks! 
Hi,-
As Siva said, your 3d geometry is not valid and the reason is that
the axis aligned box surface was not on the yz, xz, or xy plane due to ORA-54535.
Please make sure of aligned box surface is completely on one of those planes.
Thanks 
i must not be understanding something here, as the database can't be such that the walls, etc. of all my buildings have to be orthogonal to one of the axes?? that would be a horribly restrictive condition for 3D data ... 
If you are defining your geometry as a 3D box (i.e. just with minx,miny,minz and maxx, maxy,maxz) then there are restrictions.
If you define your geometry with explicit polygons for each face, then there are no restrictions.
How are you defining your buildings ?
siva 
i get for pretty much every row the value 54668 when i run the following:
SELECT SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(envelope, 0.05)
FROM cityobject;
(same result when i use a tolerance of 0.005, which is the default for that column).
btw, is there a single reference manual that gives the meaning of the various error codes, or is it best just to do a web search?
from a web search on this error, i'm seeing here that i have a 2D SRID for a 3D column. hmmmm... that actually makes sense given the way the data was generated, and i think i should be able to correct the problem.
as to siva's point about the definitions:
each cityobject (includes buildings, etc. and is the table with the relevant SDO_GEOMETRY field) really should be explicit polygons. the SDO_GTYPE field consistently has the value 3003, which, if i'm understanding the developer's guide correctly (2.2.1) should be a single 3D polygon or surface. that doesn't make sense to me for a building, though (??). maybe it's just mapping the building itself to the corresponding polygon on the earth's surface and then using relationships to other cityobjects (walls, etc.) to construct the rest of the building. i'm guessing that's probably the way it's supposed to work.
anyhow, i think the first thing i need to try is to try to get the SRID to be 3D rather than 2D. 
Hi,-
There are many ways to model a building with our open 3D data model:
Please note that each polygon can be any planar surface as long as you dont use
shortcut definitions like axis-aligned box as Siva mentioned. We dont allow curved surfaces in 3D.
Gtype 3003 can be composite surface which means each polygon should be connected to another polygon with an edge. Etype must be then 1006. You can have as many polygons as possible as long as they are connected.
Gtype 3003 can also be a single polygon. But, this will not be able to model a building.
As you pointed out, maybe it is the footprint of the building.
You can also model building with gtype 3008 which is simple or composite solid.
Etype 1007 means it is simple solid and etype 1008 means it is composite solid.
Composite solid has one or more solids which has at least one full or partial common surface in between.
In your case with single polygon, you can use sdo_util.extrude as follows to make
3D buildings. The surfaces created will be on the out-side surface of the building.
You will not have walls inside the building when you use this following function:
SELECT SDO_UTIL.EXTRUDE(
  SDO_GEOMETRY(
    2003,
    null,
    null,
    SDO_ELEM_INFO_ARRAY(1,1003,1),
    SDO_ORDINATE_ARRAY(5, 1,8,1,8,6,5,7,5,1)),
  SDO_NUMBER_ARRAY(0,0,0,0,0),
  SDO_NUMBER_ARRAY(5,10,10,5,5),
  0.005) from dual;
SDO_UTIL.EXTRUDE(SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_O
--------------------------------------------------------------------------------
SDO_GEOMETRY(3008, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1007, 1, 1, 1006, 6, 1, 10
03, 1, 16, 1003, 1, 31, 1003, 1, 46, 1003, 1, 61, 1003, 1, 76, 1003, 1), SDO_ORD
INATE_ARRAY(5, 1, 0, 5, 7, 0, 8, 6, 0, 8, 1, 0, 5, 1, 0, 5, 1, 5, 8, 1, 10, 8, 6
, 10, 5, 7, 5, 5, 1, 5, 5, 1, 0, 8, 1, 0, 8, 1, 10, 5, 1, 5, 5, 1, 0, 8, 1, 0, 8
, 6, 0, 8, 6, 10, 8, 1, 10, 8, 1, 0, 8, 6, 0, 5, 7, 0, 5, 7, 5, 8, 6, 10, 8, 6,
0, 5, 7, 0, 5, 1, 0, 5, 1, 5, 5, 7, 5, 5, 7, 0))
The following example returns the three-dimensional composite solid geometry representing an extrusion from a two-dimensional polygon geometry with inner rings.
SELECT SDO_UTIL.EXTRUDE(
  SDO_GEOMETRY(
    2003,
    null,
    null,
    SDO_ELEM_INFO_ARRAY(1, 1003, 1, 11, 2003, 1,
      21, 2003,1, 31,2003,1, 41, 2003, 1),
    SDO_ORDINATE_ARRAY(0,0, 8,0, 8,8, 0,8, 0,0,
      1,3, 1,4, 2,4, 2,3, 1,3, 1,1, 1,2, 2,2, 2,1, 1,1,
      1,6, 1,7, 2,7, 2,6, 1,6, 3,2, 3,4, 4,4, 4,2, 3,2)),
  SDO_NUMBER_ARRAY(-1.0),
  SDO_NUMBER_ARRAY(1.0),
  0.0001) from dual;
SDO_UTIL.EXTRUDE(SDO_GEOMETRY(2003,NULL,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1,11,200
--------------------------------------------------------------------------------
SDO_GEOMETRY(3008, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1008, 4, 1, 1007, 1, 1, 10
06, 16, 1, 1003, 1, 46, 1003, 1, 91, 1003, 1, 106, 1003, 1, 121, 1003, 1, 136, 1
003, 1, 151, 1003, 1, 166, 1003, 1, 181, 1003, 1, 196, 1003, 1, 211, 1003, 1, 22
6, 1003, 1, 241, 1003, 1, 256, 1003, 1, 271, 1003, 1, 286, 1003, 1, 301, 1007, 1
, 301, 1006, 10, 301, 1003, 1, 328, 1003, 1, 355, 1003, 1, 370, 1003, 1, 385, 10
03, 1, 400, 1003, 1, 415, 1003, 1, 430, 1003, 1, 445, 1003, 1, 460, 1003, 1, 475
, 1007, 1, 475, 1006, 6, 475, 1003, 1, 490, 1003, 1, 505, 1003, 1, 520, 1003, 1,
535, 1003, 1, 550, 1003, 1, 565, 1007, 1, 565, 1006, 10, 565, 1003, 1, 592, 100
3, 1, 619, 1003, 1, 634, 1003, 1, 649, 1003, 1, 664, 1003, 1, 679, 1003, 1, 694,
1003, 1, 709, 1003, 1, 724, 1003, 1), SDO_ORDINATE_ARRAY(4, 0, -1, 4, 2, -1, 4,
4, -1, 3, 4, -1, 2, 4, -1, 2, 7, -1, 1, 7, -1, 1, 6, -1, 1, 4, -1, 1, 3, -1, 0,
3, -1, 0, 8, -1, 8, 8, -1, 8, 0, -1, 4, 0, -1, 4, 0, 1, 8, 0, 1, 8, 8, 1, 0, 8,
1, 0, 3, 1, 1, 3, 1, 1, 4, 1, 1, 6, 1, 1, 7, 1, 2, 7, 1, 2, 4, 1, 3, 4, 1, 4, 4
, 1, 4, 2, 1, 4, 0, 1, 4, 0, -1, 8, 0, -1, 8, 0, 1, 4, 0, 1, 4, 0, -1, 8, 0, -1,
8, 8, -1, 8, 8, 1, 8, 0, 1, 8, 0, -1, 8, 8, -1, 0, 8, -1, 0, 8, 1, 8, 8, 1, 8,
8, -1, 0, 8, -1, 0, 3, -1, 0, 3, 1, 0, 8, 1, 0, 8, -1, 0, 3, -1, 1, 3, -1, 1, 3,
1, 0, 3, 1, 0, 3, -1, 1, 3, -1, 1, 4, -1, 1, 4, 1, 1, 3, 1, 1, 3, -1, 1, 4, -1,
1, 6, -1, 1, 6, 1, 1, 4, 1, 1, 4, -1, 1, 6, -1, 1, 7, -1, 1, 7, 1, 1, 6, 1, 1,
6, -1, 1, 7, -1, 2, 7, -1, 2, 7, 1, 1, 7, 1, 1, 7, -1, 2, 7, -1, 2, 4, -1, 2, 4,
1, 2, 7, 1, 2, 7, -1, 2, 4, -1, 3, 4, -1, 3, 4, 1, 2, 4, 1, 2, 4, -1, 3, 4, -1,
4, 4, -1, 4, 4, 1, 3, 4, 1, 3, 4, -1, 4, 4, -1, 4, 2, -1, 4, 2, 1, 4, 4, 1, 4,
4, -1, 4, 2, -1, 4, 0, -1, 4, 0, 1, 4, 2, 1, 4, 2, -1, 0, 3, -1, 1, 3, -1, 1, 1,
-1, 2, 1, -1, 3, 2, -1, 4, 2, -1, 4, 0, -1, 0, 0, -1, 0, 3, -1, 0, 3, 1, 0, 0,
1, 4, 0, 1, 4, 2, 1, 3, 2, 1, 2, 1, 1, 1, 1, 1, 1, 3, 1, 0, 3, 1, 0, 3, -1, 0, 0
, -1, 0, 0, 1, 0, 3, 1, 0, 3, -1, 0, 0, -1, 4, 0, -1, 4, 0, 1, 0, 0, 1, 0, 0, -1
, 4, 0, -1, 4, 2, -1, 4, 2, 1, 4, 0, 1, 4, 0, -1, 4, 2, -1, 3, 2, -1, 3, 2, 1, 4
, 2, 1, 4, 2, -1, 3, 2, -1, 2, 1, -1, 2, 1, 1, 3, 2, 1, 3, 2, -1, 2, 1, -1, 1, 1
, -1, 1, 1, 1, 2, 1, 1, 2, 1, -1, 1, 1, -1, 1, 3, -1, 1, 3, 1, 1, 1, 1, 1, 1, -1
, 1, 3, -1, 0, 3, -1, 0, 3, 1, 1, 3, 1, 1, 3, -1, 1, 6, -1, 2, 6, -1, 2, 4, -1,
1, 4, -1, 1, 6, -1, 1, 6, 1, 1, 4, 1, 2, 4, 1, 2, 6, 1, 1, 6, 1, 1, 6, -1, 1, 4,
-1, 1, 4, 1, 1, 6, 1, 1, 6, -1, 1, 4, -1, 2, 4, -1, 2, 4, 1, 1, 4, 1, 1, 4, -1,
2, 4, -1, 2, 6, -1, 2, 6, 1, 2, 4, 1, 2, 4, -1, 2, 6, -1, 1, 6, -1, 1, 6, 1, 2,
6, 1, 2, 6, -1, 1, 3, -1, 2, 3, -1, 2, 4, -1, 3, 4, -1, 3, 2, -1, 2, 1, -1, 2,
2, -1, 1, 2, -1, 1, 3, -1, 1, 3, 1, 1, 2, 1, 2, 2, 1, 2, 1, 1, 3, 2, 1, 3, 4, 1,
2, 4, 1, 2, 3, 1, 1, 3, 1, 1, 3, -1, 1, 2, -1, 1, 2, 1, 1, 3, 1, 1, 3, -1, 1, 2
, -1, 2, 2, -1, 2, 2, 1, 1, 2, 1, 1, 2, -1, 2, 2, -1, 2, 1, -1, 2, 1, 1, 2, 2, 1
, 2, 2, -1, 2, 1, -1, 3, 2, -1, 3, 2, 1, 2, 1, 1, 2, 1, -1, 3, 2, -1, 3, 4, -1,
3, 4, 1, 3, 2, 1, 3, 2, -1, 3, 4, -1, 2, 4, -1, 2, 4, 1, 3, 4, 1, 3, 4, -1, 2, 4
, -1, 2, 3, -1, 2, 3, 1, 2, 4, 1, 2, 4, -1, 2, 3, -1, 1, 3, -1, 1, 3, 1, 2, 3, 1
, 2, 3, -1))These are examples from Spatial User's Guide.
ORA-54668 means that you need to update your srid to a 3D srid.
Please check out Spatial User's Guide, Pro Oracle Spatial for 11g book and
the following paper (which we can send you a copy offline)
B. M. Kazar, R. Kothuri, P. v. Oosterom and S. Ravada, "On Valid and Invalid Three-
Dimensional Geometries", 2nd International Workshop on 3D Geo-Information: Requirements, Acquisition,
Modelling, Analysis, Visualisation, 12-14 December 2007, Delft, the Netherlands (Published as Chapter 2,
pp. 19-46 in Advances in 3D Geoinformation Systems Series: Lecture Notes in Geoinformation and
Cartography Oosterom, P.v.; Zlatanova, S.; Penninga, F.; Fendel, E. (Eds.) 2008, XX, 441 p. 235 illus.,
Hardcover ISBN: 978-3-540-72134-5
Maybe if you can post here an example geometry from your data,
we can help you more.
Hope these help.
Thanks 
thanks very much for all the helpful replies! here's an example of an sdo_geometry from the cityobject table:
MDSYS.SDO_GEOMETRY(3003,
26951,
'null',
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
MDSYS.SDO_ORDINATE_ARRAY(-135.70527230834,32.2928723345802,-39.8833466258274,-118.457789287001,63.9933626249088,-32.8271085454738))I'm seeing now, too, that the sdo_etype and sdo_interpretation fields in sdo_elem_info_array are 1003 and 3, so i'm assuming that this table contains building footprints only and as optimized rectangles. then the geometry of walls, etc., will be found in related tables.
As to the context here: These tables were built by code designed to generate a database for storing CityGML (an XML dialect for storing geographic data). I mistakenly entered the SRID of 26951 when prompted during database creation--not realizing that I really needed a 3D SRID (I think 4957 is probably the best 3D srid for our data and am experimenting further using that one).
The database I'm using needs to work with software designed to import CityGML files into Oracle and, as desired also to create new CityGML files showing some particular selection of buildings or landscape structures. So, there are definite limitations on what I can change within the database without breaking the import/export software. 
Hi,-
Once you bring all pieces of your building geometry together in an SDO_GEOMETRY
or in separate SDO_GEOMETRY objects in the database, you can get the GML 2 and GML 3.1.1
representations of these geometries as GML documents starting with gml:<geometry_type>
like gml:Point, gml:Curve, gml:Polygon, gml:Circle (circle in 2D only), gml:CompositeSurface, gml:Solid, gml:CompositeSolid, gml:MultiGeometry,gml:MultiPoint,..., gml:MultiSolid etc.
After this, you can plug these GML snippets (documents) into your CityGML (open standard
for 3D city models or 3D urban objects) document.
Hope this helps.
Thanks 
Hello Aisthesis,
Can you help me on creating a LOD0 linear network in CityGML.
I need to perform network analiysis on that linear network also. but i dont know how to import network data from CityGML into Oracle Spatial.
I need some tooltips about how to overcome these cases.
Thanks in advance...

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.

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

Categories

Resources