Error 13367 not resolved with SDO_MIGRATE.TO_CURRENT using 9i R2 - Spatial

After loading several thousand polygons into a 9i database, I have one that still reports a 13367 error.
ie.
select cd_code, sdo_geom.validate_geometry_with_context(geom,diminfo)
from CD_2101009 a,
USER_SDO_GEOM_METADATA m
where m.table_name = CD_2101009
and m.column_name = GEOM
CD_CODE     SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(GEOM,DIMINFO)
------- --------------------------------------------------------------------------------------------------------
2101009     13367 [Element <1>] [Ring <1>]
The Oracle error indicates that the geometry has a wrong orientation of interior/exterior ring. However, the SDO_GTYPE, ie. 2003, and the SDO_ELEM_INFO_ARRAY(1, 1003, 1) indicate that the geometry is composed of only one external ring.
Investigation of the 150 coordinates revealed that the ring is in fact in a anti-clockwise direction.
The 9i function migrate_to_current claims to reverse the direction of polygons that are incorrectly defined.
However, after applying this function, the error remained. For example:
execute sdo_migrate.to_current('CD_2101009','geom',1000)
The same record was loaded into Oracle Spatial 8.1.7 and validation showed that there was nothing wrong with the geometry.
For example:
select cd_code, sdo_geom.validate_geometry (geom,diminfo)
from CD_2101009 a,
USER_SDO_GEOM_METADATA m
where m.table_name = 'CD_2101009'
and m.column_name = 'GEOM'
CD_CODE     SDO_GEOM.VALIDATE_GEOMETRY (GEOM,DIMINFO)
------------     ----------------------------------------------------------------------------
2101009     TRUE
Has anyone experienced similar results?
Is this a bug in 9i?
Geoff

Hi Geoff,
Can you post the geometry?
Thanks,
Dan 

Hi Dan,
I'm not sure if the following is useful to you. If not, please let me know your email address and I will send a dmp file of the offending polygon (8KB).
The SRID used for the data is 8307.
select * from cd_2101009;
HEIGHT ROTATION
------------ ------------
TEXTSTRING
--------------------------------------------------------------------------------
CD_CODE
------------
GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
2101009
SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(144.33114915, -38.31721695, 144.33128816, -38.31723897, 144.33219814, -38.317
22596, 144.33232915, -38.31724696, 144.3338671, -38.31748896, 144.33417011, -38.
31753597, 144.3341831, -38.31753897, 144.33420912, -38.31754296, 144.33423411, -
38.31754796, 144.3342591, -38.31755397, 144.33428413, -38.31755996, 144.33430912
, -38.31756596, 144.33433411, -38.31757197, 144.33435811, -38.31757896, 144.3343
831, -38.31758695, 144.3344071, -38.31759496, 144.3344311, -38.31760296, 144.334
45411, -38.31761097, 144.3345631, -38.31766297, 144.33468013, -38.31771997, 144.
33470112, -38.31773296, 144.33472211, -38.31774596, 144.3347431, -38.31775997, 1
44.3347631, -38.31777397, 144.3347831, -38.31778896, 144.33480211, -38.31780296,
144.33482112, -38.31781796, 144.33484013, -38.31783397, 144.33485811, -38.31784
997, 144.3348751, -38.31786597, 144.33489312, -38.31788196, 144.33490112, -38.31
788997, 144.3353111, -38.31829497, 144.33589107, -38.31886597, 144.33597709, -38
.31895096, 144.33606208, -38.31903497, 144.33663107, -38.31959596, 144.33777606,
-38.31887294, 144.33801306, -38.31743294, 144.33817603, -38.31657493, 144.33852
298, -38.3147529, 144.33467606, -38.31428792, 144.33457405, -38.31480794, 144.33
454406, -38.31496495, 144.33454304, -38.31496695, 144.33454304, -38.31496895, 14
4.33454304, -38.31497094, 144.33454205, -38.31497294, 144.33454205, -38.31497594
, 144.33454106, -38.31497794, 144.33454106, -38.31497995, 144.33454106, -38.3149
8195, 144.33454106, -38.31498395, 144.33454006, -38.31498595, 144.33454006, -38.
31498794, 144.33454006, -38.31498994, 144.33453904, -38.31499194, 144.33453904,
-38.31499394, 144.33453904, -38.31499594, 144.33453805, -38.31499795, 144.334538
05, -38.31500095, 144.33453805, -38.31500295, 144.33453805, -38.31500495, 144.33
453805, -38.31500694, 144.33453706, -38.31500894, 144.33453706, -38.31501094, 14
4.33453706, -38.31501294, 144.33453706, -38.31501494, 144.33453706, -38.31501695
, 144.33453606, -38.31501995, 144.33453606, -38.31502095, 144.33453606, -38.3150
2395, 144.33453606, -38.31502594, 144.33453606, -38.31502794, 144.33453606, -38.
31502994, 144.33453606, -38.31503194, 144.33453606, -38.31503394, 144.33453504,
-38.31503595, 144.33453504, -38.31503795, 144.33453504, -38.31503995, 144.334535
04, -38.31504295, 144.33453504, -38.31504394, 144.33453504, -38.31504694, 144.33
453504, -38.31504894, 144.33453504, -38.31505094, 144.33453504, -38.31505294, 14
4.33453504, -38.31505495, 144.33453504, -38.31505695, 144.33453504, -38.31505895
, 144.33453504, -38.31506095, 144.33453504, -38.31506294, 144.33453504, -38.3150
6594, 144.33453504, -38.31506794, 144.33453504, -38.31506994, 144.33453504, -38.
31507195, 144.33453606, -38.31507395, 144.33453606, -38.31507595, 144.33453606,
-38.31507795, 144.33453606, -38.31507995, 144.33453606, -38.31508194, 144.334536
06, -38.31508394, 144.33458906, -38.31529995, 144.33458704, -38.31546595, 144.33
451408, -38.31577995, 144.33449709, -38.31577995, 144.3344801, -38.31577795, 144
.33446307, -38.31577695, 144.33444608, -38.31577595, 144.33443008, -38.31577495,
144.33441309, -38.31577294, 144.3343961, -38.31577194, 144.3343801, -38.3157699
4, 144.3334681, -38.31566395, 144.33284707, -38.31559195, 144.3328321, -38.31559
095, 144.33281709, -38.31558894, 144.33280211, -38.31558894, 144.3327871, -38.31
558794, 144.33277213, -38.31558894, 144.33275613, -38.31558894, 144.33274112, -3
8.31558995, 144.33272611, -38.31559095, 144.3327111, -38.31559295, 144.33269613,
-38.31559495, 144.33268512, -38.31559695, 144.3326671, -38.31560094, 144.332652
13, -38.31560394, 144.33263811, -38.31560794, 144.33262413, -38.31561295, 144.33
261011, -38.31561696, 144.33259613, -38.31562196, 144.33192912, -38.31587397, 14
4.33175411, -38.31593995, 144.3317391, -38.31594597, 144.33172413, -38.31595296,
144.33171011, -38.31595997, 144.33169712, -38.31596796, 144.33168413, -38.31597
596, 144.3316711, -38.31598497, 144.33166013, -38.31599396, 144.33164813, -38.31
600496, 144.33163811, -38.31601495, 144.33162813, -38.31602496, 144.3316191, -38
.31603597, 144.33161011, -38.31604796, 144.3316031, -38.31605996, 144.33159613,
-38.31607197, 144.33145514, -38.31637097, 144.33125514, -38.31679497, 144.331149
15, -38.31721695))
Cheers...
Geoff 

Geoff & Dan,
I suspect there is a geodetic coordinate system precision problem here. I understand that in 8.1.7 Spatial pretty much treated all data as being cartesian, so there was no geodetic problem there. Dan can you comment on the precision of handling WGS84 data in 9i ?
I played with Geoff's geometry in 9iR1, and found that it is very precise and has points very close together. I thinned it out be eliminating points whose x or y ordinates did not differ from the previous point's ordinates by more than a specified value. If I thin out to 0.000002 (still has 142 points), I still get the 13367 error. If I thin out to 0.000003 (123 points) or more coarse, I don't get the error. 0.000003 corresponds to about one third of a metre.
Regards,
David Penington
SPATIALinfo
Here is my thinned geometry:
SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1),
SDO_ORDINATE_ARRAY(144.331149, -38.317217, 144.331288, -38.317239,
144.332198, -38.317226, 144.332329, -38.317247,
144.333867, -38.317489, 144.33417, -38.317536,
144.334183, -38.317539, 144.334209, -38.317543,
144.334234, -38.317548, 144.334259, -38.317554,
144.334284, -38.31756, 144.334309, -38.317566,
144.334334, -38.317572, 144.334358, -38.317579,
144.334383, -38.317587, 144.334407, -38.317595,
144.334431, -38.317603, 144.334454, -38.317611,
144.334563, -38.317663, 144.33468, -38.31772,
144.334701, -38.317733, 144.334722, -38.317746,
144.334743, -38.31776, 144.334763, -38.317774,
144.334783, -38.317789, 144.334802, -38.317803,
144.334821, -38.317818, 144.33484, -38.317834,
144.334858, -38.31785, 144.334875, -38.317866,
144.334893, -38.317882, 144.334901, -38.31789,
144.335311, -38.318295, 144.335891, -38.318866,
144.335977, -38.318951, 144.336062, -38.319035,
144.336631, -38.319596, 144.337776, -38.318873,
144.338013, -38.317433, 144.338176, -38.316575,
144.338523, -38.314753, 144.334676, -38.314288,
144.334574, -38.314808, 144.334544, -38.314965,
144.334542, -38.314976, 144.334538, -38.315001,
144.334536, -38.31502, 144.334536, -38.315024,
144.334535, -38.315043, 144.334535, -38.315047,
144.334535, -38.315066, 144.334589, -38.3153,
144.334587, -38.315466, 144.334514, -38.31578,
144.334497, -38.31578, 144.33448, -38.315778,
144.334463, -38.315777, 144.334446, -38.315776,
144.33443, -38.315775, 144.334413, -38.315773,
144.334396, -38.315772, 144.33438, -38.31577,
144.333468, -38.315664, 144.332847, -38.315592,
144.332832, -38.315591, 144.332817, -38.315589,
144.332802, -38.315589, 144.332787, -38.315588,
144.332772, -38.315589, 144.332756, -38.315589,
144.332741, -38.31559, 144.332726, -38.315591,
144.332711, -38.315593, 144.332696, -38.315595,
144.332685, -38.315597, 144.332667, -38.315601,
144.332652, -38.315604, 144.332638, -38.315608,
144.332624, -38.315613, 144.33261, -38.315617,
144.332596, -38.315622, 144.331929, -38.315874,
144.331754, -38.31594, 144.331739, -38.315946,
144.331724, -38.315953, 144.33171, -38.31596,
144.331697, -38.315968, 144.331684, -38.315976,
144.331671, -38.315985, 144.33166, -38.315994,
144.331648, -38.316005, 144.331638, -38.316015,
144.331628, -38.316025, 144.331619, -38.316036,
144.33161, -38.316048, 144.331603, -38.31606,
144.331596, -38.316072, 144.331455, -38.316371,
144.331255, -38.316795, 144.331149, -38.317217))

Hi David,
I take your point regarding the precision of the data we are testing. Note that we are using 9i R2.
I have experimented with your new coordinates and with the tolerances in the validate_geometry_with_context on the original data set.
By thinning the data points, the 13367 error can be eventually removed, however, the spatial relationship with adjoining polygons is compromised.
It is interesting to note that adjacent polygons (to the offending one) share the same coordinate values along their common edge, but validate their geometry without error. One would suspect that the adjacent polygons would report the same error where the density of points exceeds the tolerance, but this is not the case.
Cheers...
Geoff

Hi,
This is now bugged - bug number 2618499.
Thanks,
Dan

Related

ORA-13356 same XY but different Z value

Hello,
I'm loading geometries with XYZ ordinates, and in some, special cases there are two points in a linestring with the same XYordinate but a different Z value.
These geometries are in Oracle 10.2.0.3.0 invalid (validate_geometry says ora-13356: adjacent points in a geometry are redundant. Cause: There are repeated points in the sequence of coordinates.)
But in earlier versions of Oracle 10.1.0.2.0 is the same geometry valid.
What's the reason for this change?
Bart van der Lely 
Hi Bart,-
Can you give an example?
Best regards,
baris 
Sorry Baris for the delay,
an example is very easy just a polygon with coordinates like this:
X1,Y1,Z1
X1,Y1,Z2
Thanks in advance for you cooperation
Bart 
Hi Bart,-
Can you give us your example which fails?
I just wanna make sure there are no other issues.
How big is your failing geometry? How many vertices are there in your geometry that fails?
Do you have multiple geometries failing like this? Or, is it just one specific geometry that fails? What is SRID for that geometry.
I think if you let us know your failing geometry or geometries, you will help us more in pinpointing the problem.
Best regards,
baris 
This is fixed in 10.2 and you can file a TAR to request a fix in 10.1
hope this helps 
Hi,
are you sure that this is fixed in 10.2? In my point of view is just a problem in 10.2.
Here the data:
SQL> select geometrie from dgdtw_topografie where id=227389;
GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
SDO_GEOMETRY(3002, 90112, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY
(167597.115, 386241.783, 1, 167599.531, 386236.456, 1, 167602.974, 386237.23, 1,
167602.974, 386237.23, 2, 167604.596, 386233.57, 2, 167609.646, 386235.632, 2,
167607.399, 386239.501, 2))
SQL> select t.id,t.x,t.y,t.z from dgdtw_topografie g, table(sdo_util.getvertices(g.geometrie)) t where g.id=227389;
ID X Y Z
---------- ---------- ---------- ----------
1 167597.115 386241.783 1
2 167599.531 386236.456 1
3 167602.974 386237.23 1
4 167602.974 386237.23 2
5 167604.596 386233.57 2
6 167609.646 386235.632 2
7 167607.399 386239.501 2
7 rows selected.
++++++++++++++++++++++++++++++++++++++++++++
I think this is a correct geometry, its is valid in 10.1 but invalid in 10.2.
Regards,
Bart 
bkazar,
Sorry for my ignorance, (it is not clear to me what you mean by "this is fixed") but can you explain what the expected behaviour is for validating the geometry of Bart?
With other words, should this be a valid or invalid geometry according to the implementation of the validate geometry function?
tx
Luc 
We had a problem related to validation in 10.1, which is fixed in 10.2.
I thought you said it did not work in 10.1. Sorry my mistake.
what tolerance value are you using?
thanks 
Hi Bart,-
The following geometry is a 3D geometry.
SDO_GEOMETRY(3002, 90112, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY
(167597.115, 386241.783, 1, 167599.531, 386236.456, 1, 167602.974, 386237.23, 1,167602.974, 386237.23, 2, 167604.596, 386233.57, 2, 167609.646, 386235.632, 2,167607.399, 386239.501, 2))
we donot support 3D geometries (with gtypes starting with 3000) in 10.x releases.
I should have noticed this earlier. Sorry about that.
I tried this geometry in 11g where we support 3D geometries and it has the following result:
SQL> select sdo_geom.validate_geometry_with_context(
SDO_GEOMETRY(3002, 90112, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY
(167597.115, 386241.783, 1, 167599.531, 386236.456, 1, 167602.974, 386237.23, 1,
167602.974, 386237.23, 2, 167604.596, 386233.57, 2, 167609.646, 386235.632, 2,
167607.399, 386239.501, 2)), 0.000000005) from dual;
2 3 4 5
SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(SDO_GEOMETRY(3002,90112,NULL,SDO_ELEM_IN
--------------------------------------------------------------------------------
TRUE
SQL> select sdo_geom.validate_geometry_with_context(
SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY
(167597.115, 386241.783, 167599.531, 386236.456, 167602.974, 386237.23,
167602.974, 386237.23, 167604.596, 386233.57, 167609.646, 386235.632,
167607.399, 386239.501)), 0.000000005) from dual;
2 3 4 5
SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INF
--------------------------------------------------------------------------------
13356 [Element <1>] [Coordinate <3>]
Luc: it is a valid 3D geometry but invalid 2D geometry.
thanks
baris 
Hi,
I'm getting more and more confused.
The geometry is valid in 10.1 and not valid in 10.2. And you mentioned that gtype range 3000 is not supported in 10? Is it a undocumented feature?
And gtype range 3000 is valid in 9 en 11?
Regards,
Bart 
Are your geometries LRS geometries?
Then, we have 33xx GTYPEs.
Do you have access to spatial documentation and/or Pro Oracle Spatial book?
Hope this clears your confusion.
Best regards
baris 
Hello,
no, I'm not using LRS, just G3xxx gtype's. I have the Pro Oracle Spatial book and I'm not aware of the fact that the differences in validation in Oracle 9 and 10 are explained.
So I'm still looking for the strategy of the validation routines in Oracle spatial.
Best regards,
Bart 
Bart,
Prior to 11g, all the validation is done in the 2D space.
So if the geometry is 3D, but has two vertices that are the same ignoring the z value,
that geometry will be reported as invalid.
You mention that this geometry is valid in 10.1: that must have been due to
some bug in validate function. It should have been invalid even in 10.1.
siva

Circle Polygon Looks Like an Ellipse.

I created a cirlce polygon using the sdo.buffer command, when I displaying the polygon on the screen, the circle looks like an Ellipse (with the major axis greater than the minor axis) instead of a CIRCLE (with the same radius), I submit question to oracle support (in metalink), the answer I got is "that is expected result" since I used the geodetic system. I am really frustrated here, can some expert help to clear this out. Thanks a lot.
=============================
SDO_GEOM.SDO_BUFFER(mdsys.sdo_geometry(2001,
8307,
mdsys.sdo_point_type(-77.09538871320356, 38.85403950014284, null), null,
null),
10, 0.5, 'arc_tolerance=0.05 unit=mile')
============================= 
If you use mapviewer you will see a circle if you change the mapViewerConfig.xml file so that you allow local adjustment (or make the output projected). 
Since I don't use MapViewer to display the data, what i am trying to do is to generate coordinates value from buffer function and use ArcIMS to display the circle according the coordinates value from Oracle Spatial
========================
"<LAYER type=\"acetate\" name=\"bufferLayer\" id=\"circleBuf\">\r\n <OBJECT units=\"database\">\r\n <COORDSYS id=\"26785\" />\r\n <POLYGON coords=\"-77.03625712482858 38.89077360412174684;-77.0357062024252388 38.89080735067698448;-77.0351688440214345 38.89090775948532984;-77.0346582797765868 38.89107235842386631;-77.0341870803753993 38.8912970949490676;-77.0337668476036926 38.89157643584192104;-77.0334079287431658 38.89190350340128347;-77.0331191618108608 38.89227024473710953;-77.0329076579140366 38.89266763000072603;-77.0327786260819258 38.89308587467680071;-77.0327352448943637 38.89351468046861492;-77.0327785840787634 38.89394348884935416;-77.0329075780192738 38.89436174103878192;-77.0331190518451537 38.8947591380047202;-77.0334077994707239 38.89512589408633196;-77.0337667116786032 38.89545297799152378;-77.0341869511029572 38.89573233523020664;-77.0346581698108793 38.89595708650119367;-77.0351687641266714 38.89612169714205198;-77.0357061604220761 38.89622211346375047;-77.03625712482858 38.89625586260791316;-77.0368080892350839 38.89622211346375047;-77.0373454855304886 38.89612169714205198;-77.0378560798462807 38.89595708650119367;-77.0383272985542028 38.89573233523020664;-77.0387475379785568 38.89545297799152378;-77.0391064501864361 38.89512589408633196;-77.0393951978120063 38.8947591380047202;-77.0396066716378862 38.89436174103878192;-77.0397356655783966 38.89394348884935416;-77.0397790047627963 38.89351468046861492;-77.0397356235752342 38.89308587467680071;-77.0396065917431234 38.89266763000072603;-77.0393950878462992 38.89227024473710953;-77.0391063209139942 38.89190350340128347;-77.0387474020534674 38.89157643584192104;-77.0383271692817607 38.8912970949490676;-77.0378559698805732 38.89107235842386631;-77.0373454056357255 38.89090775948532984;-77.0368080472319212 38.89080735067698448;-77.03625712482858 38.8907736041217468\">\r\n <SIMPLEPOLYGONSYMBOL fillcolor=\"255,0,0\" filltype=\"solid\" transparency=\"0.2\">\r\n </SIMPLEPOLYGONSYMBOL>\r\n </POLYGON>\r\n </OBJECT>\r\n</LAYER>
==========================
The result is an Ellipse in ArcIMS generated image,
do I need to project the data (from 8307) to 41049 (state plane for Maryland) to GET THE CIRCLE SHAPE???,
I used TRANSFORM command, but it doesn't give the value in Lat and Long
==============
select SDO_CS.TRANSFORM(
SDO_GEOM.SDO_BUFFER(mdsys.sdo_geometry(2001,8307,
mdsys.sdo_point_type(-77.09538871320356, 38.85403950014284, null), null, null),
10, 0.5, 'unit=km'),
41049) from dual
========
result
(2003, 41049, , (1, 1003, 1, , , , , , , , , , , , ), (391730.73338416, 141807.125805351, 390859.179770485, 141769.985322922, 389994.179705935, 141657.025224082, 389142.316381759, 141469.105212682, 388310.073007199, 141207.655488164, 387503.783466853, 140874.665859665, 386729.58411502, 140472.670600847, 385993.367073693, 140004.729160732, 385300.735389878, 139474.402877573, 384656.960393506, 138885.72787307, 384066.941580302, 138243.184333348, 383535.169325079, 137551.66241061, 383065.690709009, 136816.425005219, 382662.078720781, 136043.067711274, 382327.405066079, 135237.476230865, 382064.216792017, 134405.781580864, 381874.516904307, 133554.313433317, 381759.749124711, 132689.551944335, 381720.786904366, 131818.078438129, 381757.92677685, 130946.525321258, 381870.886101295, 130081.525608215, 382058.805212793, 129229.662442375, 382320.253963774, 128397.418996191, 382653.24260664, 127591.129131943, 383055.236934898, 126816.929198346, 383523.177567729, 126080.711329835, 384053.503231329, 125388.078603819, 384642.177860011, 124744.302397312, 385284.721311022, 124154.282267335, 385976.243459315, 123622.508660654, 386711.481413086, 123153.02873669, 387484.839566804, 122749.415563777, 388290.432186992, 122414.740923479, 389122.128206734, 122151.551930038, 389973.597887889, 121961.85164293, 390838.360995806, 121847.083820513, 391709.836119934, 121808.121930651, 392581.390764688, 121845.262502118, 393446.391829261, 121958.222867564, 394298.256092081, 122146.143315051, 395130.500315443, 122407.593631949, 395936.790588973, 122740.583991223, 396710.990536117, 123142.580097071, 397447.20801675, 123610.522474802, 398139.839970465, 124140.849757748, 398783.615059082, 124729.525793861, 399373.633783967, 125372.070365703, 399905.405772919, 126063.59328968, 400374.883952795, 126798.831634971, 400778.495348095, 127572.18977886))
Did I choose the wrong SRID?? or other suggestions?
Thanks a lot.
PS, I also tried to use SRID 41049 in buffer function directly, but get a rectangle instead of a circle
====================
select SDO_GEOM.SDO_BUFFER(mdsys.sdo_geometry(2001,41049,
mdsys.sdo_point_type(-77.09538871320356, 38.85403950014284, null), null, null),
1, 0.5,'unit=km')
from dual;
=====================
(2003, 41049, , (1, 1003, 2, , ), (-77.0953887132036, 1038.85403950014, -1077.0953887132, 38.8540395001429, -77.0953887132036, -961.145960499857, 922.904611286796, 38.8540395001426, -77.0953887132036, 1038.85403950014, ,))
Message was edited by:
user532797

Rogue Polygon Hoses various SDO functions

Hello,
I have what I think is a fairly reasonable polygon made up of a single ring that has an 13349 error where the polygon closes. I would think I could correct this by unioning the polygon against itself. However, this results in -13050 unable to construct spatial object error.
Tested on 10.2.0.3 Linux and AIX.
Furthermore, when I run the polygon through any of the stock "fixers" like REMOVE_DUPLICATE_VERTICES or RECTIFY_GEOMETRY, I get completely whacked out results with the new polygon vertices in a completely different spatial system. If anyone has 11g handy, could you try the code down below? Do you receive back a reasonable polygon?
SELECT bad_deal FROM dual;
The polygon I get back is completely different, here are the first three vertices of the strange thing I get back:
-75.000000000016,44.9999999999196,
123.640136719051,68.0676269499346,
-148.066406250019,31.9313049297389
I would be very hesitant to suggest that anyone run these corrective functions in mass without carefully checking the results.
Thanks,
Paul
CREATE OR REPLACE FUNCTION bad_deal
RETURN SDO_GEOMETRY
AS
baddie SDO_GEOMETRY := SDO_GEOMETRY
(
2003,
8265,
NULL,
SDO_ELEM_INFO_ARRAY
(
1,
1003,
1
),
SDO_ORDINATE_ARRAY
(
452805, 2454525,
452823.640136719, 2454591.93237305,
452911.93359375, 2454628.06869507,
453030.065917969, 2454715.65673828,
453045, 2454735,
453059.157714844, 2454780.84365845,
453090.842285156, 2454839.15634155,
453119.157714844, 2454930.84365845,
453151.303710938, 2455020.43395996,
453103.315429688, 2455063.31268311,
453036.342773438, 2455138.26416016,
453000.842285156, 2455290.84365845,
452967.385253906, 2455415.77262878,
452985, 2455665,
452760.571289063, 2455860.57197571,
452617.294921875, 2455953.09402466,
452580.571289063, 2456076.03858948,
452668.286132813, 2456241.79824829,
452549.428710938, 2456279.42802429,
452460.571289063, 2456340.57197571,
452009.428710938, 2456369.42802429,
451950.571289063, 2456400.57197571,
451799.143066406, 2456436.64215088,
451620.124511719, 2456573.91998291,
451545, 2456655,
451410.571289063, 2456760.57197571,
451202.219238281, 2456883.06060791,
451023.662109375, 2456875.98335266,
450870.571289063, 2457000.57197571,
450659.428710938, 2457029.42802429,
450442.368164063, 2457118.5861969,
450312.143554688, 2457049.67697144,
450180.571289063, 2457150.57197571,
449999.428710938, 2457179.42802429,
449835, 2457255,
449805, 2457255,
449805, 2457285,
449775, 2457285,
449775, 2457315,
449745, 2457315,
449745, 2457345,
449488.337402344, 2457331.65939331,
449207.709960938, 2457292.1534729,
448981.662597656, 2457148.34060669,
448900.297851563, 2457114.05754089,
448831.662597656, 2457028.34060669,
448649.816894531, 2456996.69517517,
448635, 2456745,
448684.921875, 2456755.078125,
448801.2890625, 2456812.92114258,
448815, 2456745,
449422.075195313, 2456731.48269653,
449548.06640625, 2456638.06869507,
449745, 2456625,
449789.157714844, 2456609.15634155,
450198.801269531, 2456577.08999634,
450253.315429688, 2456458.31245422,
450398.247070313, 2456366.13693237,
450435, 2456325,
450570.842285156, 2456339.15634155,
450689.157714844, 2456370.84365845,
450877.565917969, 2456400.20347595,
451058.61328125, 2456498.58581543,
451194.887695313, 2456436.04042053,
451544.868164063, 2456130.78987122,
451746.262207031, 2456038.35777283,
451799.157714844, 2455979.15634155,
451846.684570313, 2455936.68754578,
451962.905273438, 2455806.6142273,
452061.123046875, 2455761.53457642,
452100.842285156, 2455650.84365845,
452133.771972656, 2455369.75364685,
452249.289550781, 2455289.06364441,
452336.667480469, 2455294.27139282,
452489.157714844, 2455169.15634155,
452662.060546875, 2455128.92715454,
452705.529785156, 2455007.78274536,
452697.36328125, 2454870.7585144,
452751.247558594, 2454822.6159668,
452789.157714844, 2454539.15634155,
452805, 2454525
)
);
BEGIN
RETURN SDO_UTIL.REMOVE_DUPLICATE_VERTICES(baddie,0.05);
END bad_deal; 
11.1.0.6 on win32 (XP). --DD
SQL> SELECT bad_deal FROM dual;
BAD_DEAL(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
SDO_GEOMETRY(2003, 8265, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(-75, 45, 123.640137, 68.0676269, -148.06641, 31.9313049, -29.934082, -55.6567
38, -15, -75, 179.157715, -59.156342, -149.15771, -.84365845, 59.1577148, 89.156
3415, 91.3037109, -.43395996, 43.3154297, -43.312683, 156.342773, -61.73584, -59
.157715, 89.1563416, -92.614746, -35.772629, -75, 75, -119.42871, -59.428024, 97
.2949219, 33.0940247, -119.42871, 23.9614105, 148.286133, -38.201752, 29.4287109
, -.57197571, -59.428711, 60.5719757, -150.57129, 89.4280243, -29.428711, 59.428
0243, 179.143066, 23.3578491, -179.87549, -66.080017, 105, 15, 150.571289, 59.42
80243, -57.780762, -63.060608, 123.662109, -55.983353, 150.571289, .57197571, -6
0.571289, 29.4280243, -97.631836, 61.4138031, -47.856445, 49.6769714, .571289063
, 29.4280243, 179.428711, .57197571, 15, -75, -15, -75, 165, -75, 135, -75, 135,
BAD_DEAL(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
-45, 105, -45, 105, -15, -151.6626, -28.340607, -72.290039, -67.846527, -118.33
74, 31.6593933, 160.297852, 65.9424591, -88.337402, 28.3406067, 89.8168945, -3.3
048248, -105, 75, -55.078125, 64.921875, 61.2890625, 7.07885742, 75, 75, -37.924
805, 88.5173035, -91.933594, -1.9313049, 105, -15, 149.157715, -30.843658, -161.
19873, -62.910004, 73.3154297, 1.68754578, 38.2470703, 86.1369324, 75, 45, -149.
15771, 59.1563416, 149.157715, 89.1563416, -22.434082, 59.7965241, 158.613281, -
38.585815, -65.112305, 23.9595795, -75.131836, -30.789871, 126.262207, 61.642227
2, -.84228516, 59.1563415, 46.6845703, 16.6875458, -17.094727, -66.614227, 81.12
30469, -21.534576, 120.842285, 89.1563415, 153.771973, 10.2463532, 89.2895508, 8
9.0636444, -3.3325195, 85.7286072, -30.842285, -30.843658, 142.060547, -71.07284
5, 5.52978516, 12.2172546, 177.363281, 30.7585144, -128.75244, -17.384033, -90.8
BAD_DEAL(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
42285, 59.1563416, -75, 45)) 
Thanks DD,
Well, so its an issue on 11g as well. Anyone disagree that this is bug?
Just be darn carefull about running REMOVE_DUPLICATE_VERTICES on anything that does not pass VALIDATE_GEOMETRY_WITH_CONTEXT.
I can't quite understand how the function can so radically change the coordinate system. Its still the same shape but scaled and thrown into the Atlantic.
:)
Paul 
Hi Paul,
The coordinate system id (SDO_SRID=8265) you specified in the input geometry is a geodetic (long/lat) coordinate system. I would hazard a guess that the data is not long/lat, but is in some projected coordinate system.
When you asked Oracle to work with it, it probably your values to normal longitude (-180 to 180) and normal latitude (-90 to 90) values.
You may want to fix the coordinate system ID and see what Oracle does with it.
I would be very hesitant to suggest that anyone run these corrective functions in mass without carefully checking the results.I always suggest to carefully check the results of all automated fixup routines. If the input data is invalid, you can't be sure what the output will look like. 
OUCH!
You are absolutely correct Dan. I should have noticed that right away but I have been in a funk since the Packers lost last Sunday. Thanks for the help!
Paul

Why does sdo_intersection return a compound geometry if regions are combined ?

Hi,
I am using sdo_geom.sdo_intersection to get the intersection of two polygons. Indepedently from the footprint of sdo_intersection and the tolerances I use I always get a compound element back (2004) that contains not only regions but also points or lines. This is not what I would expect.
I tested that the input polygons are pure polygons.
Is there an alternative function that returns only polygons as the result of the intersection ? I need to have a pure polygon as the result, otherwise I will not be able to display the result in my GIS (MapXtreme Java 4).
Using 9.0.1.2.0
Thanks 
Hi,
Can you make sure the geometries are valid?
If they are valid, can you post them here?
Thanks,
Dan 
Dan,
thank you for answering. I validated the geometries and they are valid. Here is the first geometry:
SDO_GEOMETRY(2003, 8265, SDO_POINT_TYPE(-88.233118, 41.801953, NULL), SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(-87.643855, 41.969432, -87.646039, 41.972612, -87.646925, 41.977863, -87.649421, 41.978167, -87.649821, 41.977768, -87.650717, 41.978868, -87.650316, 41.979966, -87.650813, 41.980965, -87.650608, 41.98416, -87.650303, 41.985276, -87.651318, 41.985683, -87.654767, 41.989136, -87.655172, 41.990354, -87.65454, 41.990466, -87.655175, 41.998288, -87.656548, 42.000811, -87.657401, 42.006356, -87.656122, 42.006549, -87.659146, 42.009767, -87.658765, 42.009868, -87.661203, 42.012014, -87.662453, 42.015186, -87.663462, 42.018347, -87.664313, 42.019281, -87.663883, 42.0194, -87.664278, 42.021473, -87.665266, 42.023052, -87.666959, 42.026432, -87.66875, 42.028022, -87.669858, 42.035419, -87.669056, 42.035519, -87.670159, 42.037222, -87.669357, 42.039728, -87.669658, 42.041231, -87.672158, 42.043731, -87.673358, 42.04793, -87.672558, 42.04823, -87.673458, 42.04913, -87.673558, 42.05073, -87.673758, 42.05203, -87.672058, 42.05203, -87.671658, 42.05243, -87.672258, 42.05253, -87.672258, 42.05353,-87.673658, 42.05363, -87.674358, 42.05413, -87.674158, 42.05503, -87.672558, 42.05613, -87.671358, 42.05623, -87.671358, 42.05533, -87.672458, 42.05553, -87.672258, 42.05423, -87.670758, 42.05313, -87.670858, 42.05243, -87.671458, 42.05233, -87.669966, 42.05173, -87.66947, 42.052028, -87.670163, 42.054511, -87.669861, 42.059476, -87.671747, 42.060471, -87.672244, 42.059776, -87.672939, 42.059777, -87.674329, 42.060771, -87.674031, 42.061565, -87.673926, 42.062269, -87.674731, 42.062274, -87.676531, 42.067024, -87.677238, 42.067026, -87.679809, 42.071673, -87.682076, 42.075141, -87.682459, 42.077229, -87.681359, 42.077129, -87.681459, 42.078129, -87.680059, 42.078829, -87.681859, 42.078629, -87.69356, 42.083829, -87.703056, 42.089855, -87.71102, 42.095422, -87.713102, 42.097094, -87.723505, 42.106762, -87.730886, 42.115266, -87.729662, 42.116528, -87.73746, 42.122927, -87.741677, 42.128193, -87.748631, 42.137382, -87.748033, 42.137781, -87.750564, 42.140268, -87.759024, 42.152619, -87.767667, 42.165544, -87.769993, 42.168388, -87.772943, 42.170976, -87.778853, 42.178059, -87.784312, 42.185444, -87.787993, 42.189659, -87.787744, 42.1905, -87.791212, 42.19456, -87.796385, 42.203433,-87.800132, 42.20806, -87.802321, 42.209947, -87.80306, 42.212993, -87.807426, 42.223079, -87.808692, 42.224735, -87.814739, 42.239586, -87.817784, 42.248208, -87.827593, 42.268285, -87.828298, 42.269735, -87.830077, 42.28095, -87.831575, 42.282243, -87.832265, 42.287016, -87.834051, 42.294326, -87.833427, 42.295557, -87.834951, 42.301202, -87.833676, 42.305082, -87.832068, 42.308004, -87.832928, 42.308243, -87.832955, 42.308979, -87.832157, 42.308729, -87.829157, 42.311662,-87.830705, 42.321606, -87.832254, 42.326346, -87.831716, 42.327125, -87.831217, 42.33045, -87.826747, 42.341159, -87.825585, 42.360395, -87.824035, 42.360846,-87.82316, 42.362974, -87.823859, 42.365886, -87.820542, 42.366183, -87.820304,42.367919, -87.817492, 42.366765, -87.818801, 42.366007, -87.820482, 42.363743,-87.820646, 42.36282, -87.819427, 42.362044, -87.814092, 42.362048, -87.815371,42.364972, -87.811446, 42.376875, -87.807727, 42.383221, -87.811196, 42.381459,-87.814472, 42.381842, -87.812979, 42.38315, -87.805347, 42.384689, -87.803823,42.413851, -87.803347, 42.420638, -87.803971, 42.427921, -87.802646, 42.441778,-87.799453, 42.449685, -87.799816, 42.452674, -87.798784, 42.459084, -87.79885,42.462377, -87.799665, 42.463396, -87.799232, 42.464183, -87.798035, 42.471721,-87.800086, 42.478757, -87.79909, 42.481069, -87.798623, 42.48373, -87.800565, 42.491921, -87.821373, 42.492021, -87.852174, 42.492421, -87.883144, 42.49278, -87.949778, 42.49372, -87.951179, 42.49372, -88.003106, 42.494584, -88.026182, 42.49502, -88.070083, 42.495619, -88.095384, 42.495919, -88.108698, 42.496065, -88.182788, 42.496019, -88.188288, 42.496019, -88.199888, 42.496019, -88.200172, 42.496017, -88.237821, 42.495875, -88.25009, 42.495824, -88.265791, 42.494947, -88.285102, 42.494772, -88.298604, 42.494658, -88.304692, 42.494619, -88.339093, 42.494618, -88.354994, 42.494618, -88.378495, 42.494518, -88.415196, 42.494618, -88.422596, 42.494618, -88.470598, 42.494618, -88.515782, 42.494933, -88.525795, 42.495028, -88.540483, 42.495044, -88.589597, 42.495155, -88.658369, 42.49478, -88.70738, 42.493588, -88.706594, 42.451324, -88.705984, 42.414974, -88.705862, 42.354975, -88.705695, 42.342358, -88.705721, 42.327881, -88.70548, 42.240987, -88.705633, 42.153561, -88.739863, 42.153596, -88.768852, 42.153472, -88.822449, 42.153336, -88.831617, 42.153304, -88.861425, 42.152872, -88.887714, 42.15292, -88.939715, 42.152344, -88.939491, 42.137752, -88.939133, 42.086922, -88.938701, 42.065105, -88.941985, 42.065036, -88.941859, 42.051739, -88.941934, 42.037103, -88.942038, 41.986108, -88.94183, 41.978832, -88.942193, 41.978826, -88.941447, 41.891782, -88.941658, 41.833679, -88.941532, 41.826337, -88.941379, 41.80445, -88.941497, 41.760766, -88.941683, 41.746211, -88.9418, 41.717165, -88.940701, 41.717097, -88.940518, 41.717028, -88.938634, 41.6282, -88.910004, 41.628903, -88.871812, 41.630081, -88.837851, 41.630731, -88.818371, 41.631053, -88.712802, 41.630317, -88.705794, 41.630307, -88.687447, 41.630552, -88.682545, 41.630589, -88.654253, 41.630795, -88.650686, 41.63083, -88.602343, 41.63138, -88.602341, 41.631247, -88.601864, 41.615839, -88.601624, 41.609157, -88.600469, 41.57615, -88.600058, 41.566375, -88.599401, 41.544183, -88.596644, 41.486219, -88.595831, 41.457037, -88.595303, 41.427616, -88.594901, 41.41316, -88.594005, 41.383926, -88.593485, 41.369274, -88.589895, 41.303523, -88.589228, 41.289466, -88.589622, 41.289443, -88.588897, 41.282353, -88.586923, 41.209668, -88.586795, 41.195073, -88.586668, 41.18057, -88.586187, 41.107865, -88.584765, 41.107842, -88.547837, 41.10805, -88.480284, 41.108527, -88.471467, 41.108686, -88.470529, 41.108663, -88.363224, 41.111243, -88.356237, 41.111429, -88.336969, 41.112007, -88.298644, 41.112925, -88.290415, 41.113089, -88.251638, 41.113944, -88.241038, 41.114217, -88.242271, 41.143244, -88.242829, 41.15784, -88.244042, 41.194169, -88.244352, 41.20133, -88.186709, 41.202351, -88.128973, 41.203595, -88.109082, 41.203897, -88.086317, 41.204549, -88.011778, 41.205599, -88.011866, 41.208174, -88.013898, 41.292269, -87.900387, 41.294309, -87.897297, 41.294352, -87.890513, 41.294447, -87.820431, 41.295436, -87.800924, 41.295619, -87.784574, 41.295868, -87.78151, 41.29589, -87.76176, 41.295975, -87.669277, 41.296855, -87.664818, 41.296877, -87.526769, 41.298178, -87.526762, 41.312295, -87.526469, 41.348374, -87.525339, 41.384633, -87.525302, 41.398819, -87.525735, 41.435395, -87.525672, 41.470115, -87.524941, 41.522735, -87.525031, 41.542815, -87.525041, 41.557713, -87.524642, 41.578335, -87.524642, 41.581235, -87.524742, 41.600535, -87.524643, 41.625735, -87.524843, 41.630635, -87.524668, 41.644637, -87.524743, 41.660435, -87.524844, 41.676235, -87.525044, 41.705835, -87.524044, 41.706735, -87.524044, 41.708335, -87.526831, 41.709773, -87.527632, 41.710773, -87.526232, 41.712273, -87.528232, 41.715173, -87.524232, 41.717273, -87.525043, 41.720424, -87.524377, 41.722259, -87.524142, 41.72399, -87.529549, 41.732248, -87.52875, 41.733628, -87.529923, 41.740635, -87.539446, 41.740635, -87.539545, 41.741037, -87.530095, 41.741524, -87.53071, 41.748243, -87.537134, 41.750839, -87.542842, 41.752134, -87.543938, 41.755125, -87.540413, 41.757551, -87.543217, 41.760052, -87.547728, 41.758552, -87.551795, 41.759351, -87.556474, 41.762457, -87.560647, 41.766035, -87.560002, 41.768801, -87.562648, 41.769504, -87.565648, 41.774136, -87.569648, 41.776936, -87.571748, 41.779037, -87.573715, 41.778996, -87.571301, 41.780719, -87.570614, 41.782359, -87.572195, 41.783589, -87.575014, 41.784666, -87.576379, 41.786084, -87.576565, 41.788134, -87.577248, 41.788234, -87.576541, 41.788338, -87.576183, 41.789073, -87.578591, 41.794194, -87.576977, 41.795112, -87.575865, 41.795218, -87.575229, 41.795893, -87.575631, 41.796769, -87.577065, 41.796984, -87.579447, 41.798444, -87.580148, 41.799622, -87.580044, 41.804011, -87.586644, 41.81023, -87.586644, 41.811529, -87.590341, 41.813627, -87.592239, 41.816924, -87.59855, 41.824434, -87.59855, 41.825633, -87.600551, 41.826834, -87.601651, 41.829735, -87.602944, 41.83132, -87.603346, 41.832927, -87.604852, 41.833529, -87.604551, 41.834835, -87.609439, 41.845194, -87.609339, 41.846197, -87.611446, 41.850206, -87.610845, 41.852813, -87.611851, 41.855033, -87.612351, 41.854933, -87.612951, 41.855633, -87.612351, 41.859933, -87.612851, 41.865233, -87.612451, 41.865233, -87.612551, 41.865733, -87.610551, 41.865733, -87.609851, 41.862333, -87.610451, 41.859233, -87.609151, 41.856633, -87.609751, 41.853533, -87.607759, 41.853203, -87.606353, 41.853261, -87.606848, 41.859935, -87.605951, 41.860333, -87.606351, 41.861933, -87.607351, 41.863733, -87.606651, 41.865133, -87.606151, 41.864833, -87.606052, 41.867135, -87.613272, 41.866935, -87.613171, 41.868138, -87.615349,41.868215, -87.616239, 41.868906, -87.616251, 41.874401, -87.616459, 41.880606,-87.615758, 41.880707, -87.61586, 41.881507, -87.61646, 41.881406, -87.616461, 41.882307, -87.613451, 41.884436, -87.61415, 41.88843, -87.61265, 41.889135, -87.61275, 41.890229, -87.620452, 41.890133, -87.620352, 41.890434, -87.611647, 41.890636, -87.611347, 41.889339, -87.609552, 41.88934, -87.609552, 41.890936, -87.598678, 41.891439, -87.598778, 41.892237, -87.609852, 41.892133, -87.609855, 41.893134, -87.602456, 41.893349, -87.60256, 41.896346, -87.610353, 41.896237, -87.612347, 41.893437, -87.613746, 41.893236, -87.619711, 41.901515, -87.622846, 41.902021, -87.624038, 41.904192, -87.625328, 41.909979, -87.624634, 41.911455, -87.620399, 41.914166, -87.628246, 41.918613, -87.62964, 41.926473, -87.631054, 41.928732, -87.631054, 41.932332, -87.631452, 41.932791, -87.630539, 41.933623, -87.634054, 41.940332, -87.634254, 41.941732, -87.635039, 41.94173, -87.635447, 41.940324, -87.636857, 41.939827, -87.638558, 41.941736, -87.638858, 41.943135, -87.640456, 41.944933, -87.640156, 41.946131, -87.641554, 41.94723, -87.642255, 41.948032, -87.640103, 41.946457, -87.637783, 41.946047, -87.636378, 41.943116, -87.634864, 41.943314, -87.63426, 41.942404, -87.633185, 41.942755, -87.638187, 41.948255, -87.64109, 41.954719, -87.642088, 41.958631, -87.641885, 41.960736, -87.642386, 41.961138, -87.642385, 41.962141, -87.64078, 41.96264, -87.635967, 41.962935, -87.635867, 41.962233, -87.638376, 41.960732, -87.640581, 41.961336, -87.641283, 41.961137, -87.641284, 41.960435, -87.638076, 41.959629, -87.636471, 41.960128, -87.631855, 41.963031, -87.632355, 41.965532, -87.635955, 41.965732, -87.640755, 41.967032, -87.643855, 41.969432))
And here comes the second (it's a little big and it does not fit here completely. I'll try it in another post):
SDO_GEOMETRY(2007, 8265, SDO_POINT_TYPE(-88.060485, 41.758951, NULL), SDO_ELEM_INFO_ARRAY(1, 1003, 1, 13393, 2003, 1, 14161, 2003, 1, 14541, 2003, 1, 14873, 2003, 1, 14881, 1003, 1, 15833, 1003, 1, 17989, 1003, 1, 19641, 1003, 1), SDO_ORDINATE_ARRAY(-88.432674, 41.458925, -88.44269, 41.458708, -88.443053, 41.458701, -88.454906, 41.458457, -88.456504, 41.458418, -88.459119, 41.45837, -88.470345, 41.458138, -88.481624, 41.457917, -88.490256, 41.457865, -88.500071, 41.457793, -88.510832, 41.457714, -88.514905, 41.457686, -88.516915, 41.457671, -88.535188, 41.457535, -88.539899, 41.457501, -88.541081, 41.457493, -88.558972 
Dan,
It has to many coordinates to be posted here. But I would be happy to send it to you directly!
Thanks!

Spatial query problem

I am doing a query as follows :
Select rtrim(Cigna_Zone), rtrim(FIPS), Panel, State, Community, Cigna_Conf, Firm_Panel, PComm
From Flood_Zones fz
Where mdsys.sdo_relate (fz.geoloc,
mdsys.sdo_geometry (1, 8260, mdsys.SDO_POINT_TYPE(-86.970888, 33.08214, 0), null, null),
'mask=ANYINTERACT layer_gtype=POINT') = 'TRUE'
and rownum < 2 ;
The problem with the above is that the actual polygon where the point lies does not have any data. However, the above query returns data which seems to have been extracted from a neighbouring polygon. Can someone suggest what it is that I am doing wrong ?
Thanks in advance. 
Hi,
One thing you might want to look at is the tolerance. If it is set too coarse, then extra
geometries may be included in the results. If that doesn't do it, try posting the geometry that
is returned. Also post the Oracle version number you are using, the contents of
user_sdo_geom_metadata for the Flood_Zones table, and your index create statement.
Thanks. 
Hi,
The tolerance value for both X and Y coordinates is set to 0.0000005. I think that the mentioned tolerance should be good enough. Anyway, the polygon from which the results are being reported from is about 6200 ft. away from the location of the point. At the exact location of the point there is noe data available. Here are the details that you requested :
1. Oracle version - Oracle9i ver 9.0.1
2. Contents of the "user_sdo_geom_metadata" table :
TABLE_NAME
--------------------------------
COLUMN_NAME
DIMINFO(SDO_DIMNAME, SDO_LB, SDO_UB, SDO_TOLERANCE)
SRID
----------
FLOOD_ZONES
GEOLOC
SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', -180, 180, .0000005), SDO_DIM_ELEMENT('Y', -90, 90, .0000005))
8260
3. The index I am using is an RTREE index and I created it with a command similar to the following :
create index flood_zones_sx on flood_zones(geoloc)
indextype is mdsys.spatial_index
parameters ('sdo_commit_interval=100000')
parallel ;
4. The following is the geometry which I got in SQL Worksheet when I ran the above query :
GEOLOC(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
, -86.9288, 33.06736, -86.92828, 33.06754, -86.92772, 33.06828, -86.92728, 33.06853, -86.92727, 33.06895, -86.9273, 33.06918, -86.92728, 33.06924, -86.92724, 33.07009, -86.92727, 33.07093, -86.92708, 33.07134, -86.92704, 33.07179, -86.92697, 33.07222, -86.9267, 33.07258, -86.92692, 33.07297, -86.92702, 33.07338, -86.92687, 33.07377, -86.92641, 33.07389, -86.9259, 33.07398, -86.92562, 33.07431, -86.92535, 33.0747, -86.92503, 33.075, -86.92456, 33.07515, -86.9241, 33.07533, -86.92364, 33.07549, -86.92355, 33.07589, -86.92397, 33.07611, -86.92397, 33.07611, -86.92445, 33.0761, -86.92492, 33.07607, -86.92541, 33.0761, -86.9259, 33.07611, -86.92638, 33.07619, -86.92686, 33.07632, -86.9268, 33.07676, -86.92633, 33.07692, -86.92633, 33.07692, -86.92586, 33.0768, -86.92543, 33.07658, -86.92495, 33.07654, -86.92448, 33.07671, -86.92414, 33.077, -86.92392, 33.07738, -86.92379, 33.07777, -86.9238, 33.07818, -86.92393, 33.07858, -86.9237, 33.07896, -86.9237, 33.07896, -86.92321, 33.07886, -86.92303, 33.07843, -86.92286,
33.078, -86.92207, 33.07746, -86.92188, 33.07705, -86.92178, 33.07663, -86.92179, 33.0762, -86.9218, 33.07578, -86.92141, 33.07552, -86.92332, 33.1382, -86.9695, 33.13811))
I hope the above inputs help.
Thanks,
Ramas. 
Hi,
The geometry as posted is not a legal geometry in Oracle Spatial - can you repost so all information is visible, and also
correct it - for instance, in what was posted some coordinates are duplicated so the geometry is invalid:
-86.9237, 33.07896, -86.9237, 33.07896,
Since you are using 9i, for geodetic data the tolerance should actually be specified in meters, so the tolerance value
.0000005 will effect performance in a negative way - you are telling spatial to carry enough precision to evaluate
geometries to .5 micrometers (or something like that).
One other minor thing: sdo_commit_interval has no effect when building an R-tree index.
Thanks,
Dan 
Hi,
Here is the complete geoloc column.
GEOLOC(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SDO_GEOMETRY(2003, 8260, SDO_POINT_TYPE(-86.945455, 33.102675, NULL), SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(-86.9695, 33.13811, -86.96949, 33.1363, -86.96902, 33.11198, -86.96873, 33.09941, -86.95127, 33.09954, -86.95074, 33.0848, -86.95041,
33.08477, -86.95004, 33.08448, -86.94958, 33.08429, -86.9492, 33.08399, -86.94875, 33.08379, -86.9483, 33.08393, -86.94797, 33.08422, -86.94751, 33.08435, -86.94704, 33.08441, -86.94653, 33.08445, -86.94601, 33.08446, -86.94547, 33.08454, -86.94503, 33.0
8473, -86.94485, 33.08516, -86.94476, 33.08556, -86.94479, 33.08601, -86.94489, 33.08645, -86.94493, 33.08689, -86.94495, 33.08732, -86.94503, 33.08774, -86.94521, 33.08856, -86.9453, 33.08897, -86.94537, 33.0894, -86.9454, 33.08982, -86.9454, 33.09024, -
86.94562, 33.09064, -86.94599, 33.09092, -86.94635, 33.09125, -86.94655, 33.09166, -86.94651, 33.0921, -86.94636, 33.09253, -86.94629, 33.09296, -86.94625, 33.09338, -86.94606, 33.09377, -86.94621, 33.09416, -86.94667, 33.09433, -86.94695, 33.09468, -86.9
4713, 33.09508, -86.94759, 33.09521, -86.94799, 33.09546, -86.94802, 33.09587, -86.94756, 33.09599, -86.94706, 33.09592, -86.94653, 33.09592, -86.94607, 33.09581, -86.94559, 33.09565, -86.94524, 33.09536, -86.9449, 33.095, -86.94447, 33.09472, -86.94431,
33.09432, -86.94435, 33.09391, -86.94459, 33.09356, -86.94521, 33.09288, -86.94525, 33.09248, -86.94508, 33.09207, -86.94485, 33.09168, -86.94477, 33.09125, -86.9443, 33.09132, -86.94401, 33.09166, -86.94358, 33.0919, -86.94308, 33.09193, -86.9427, 33.091
65, -86.94249, 33.09127, -86.94279, 33.09095, -86.94312, 33.09063, -86.94348, 33.09031, -86.9438, 33.08998, -86.94367, 33.08954, -86.94341, 33.0892, -86.9435, 33.08878, -86.94347, 33.08836, -86.94319, 33.088, -86.94288, 33.08766, -86.94284, 33.08724, -86.
94289, 33.08681, -86.94292, 33.08638, -86.94252, 33.08603, -86.94214, 33.0857, -86.94192, 33.08533, -86.94205, 33.08492, -86.94249, 33.08469, -86.94289, 33.08446, -86.94328, 33.08419, -86.94361, 33.08384, -86.94357, 33.08342, -86.94322, 33.08306, -86.9427
6, 33.08285, -86.94225, 33.08279, -86.94177, 33.08272, -86.94135, 33.08251, -86.94102, 33.08217, -86.94067, 33.08184, -86.94028, 33.08159, -86.93976, 33.08157, -86.93971, 33.08114, -86.9395, 33.08074, -86.93943, 33.08031, -86.93938, 33.07988, -86.93906, 3
3.0795, -86.93902, 33.07942, -86.93369, 33.07942, -86.93336, 33.0717, -86.93317, 33.0719, -86.93284, 33.0722, -86.93237, 33.07224, -86.93217, 33.07185, -86.9317, 33.07198, -86.93127, 33.07215, -86.93109, 33.07177, -86.93127, 33.07135, -86.93156, 33.07102,
-86.93153, 33.07061, -86.93107, 33.07049, -86.93056, 33.07057, -86.93008, 33.07058, -86.93006, 33.07016, -86.93035, 33.06982, -86.93043, 33.0694, -86.93063, 33.06917, -86.93074, 33.06877, -86.93118, 33.06848, -86.93136, 33.06808, -86.93165, 33.06772, -86
GEOLOC(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
.93214, 33.0677, -86.93173, 33.06744, -86.93076, 33.06715, -86.93023, 33.06722, -86.92982, 33.06745, -86.92927, 33.06751, -86.9288, 33.06736, -86.92828, 33.06754, -86.92772, 33.06828, -86.92728, 33.06853, -86.92727, 33.06895, -86.9273, 33.06918, -86.92728
, 33.06924, -86.92724, 33.07009, -86.92727, 33.07093, -86.92708, 33.07134, -86.92704, 33.07179, -86.92697, 33.07222, -86.9267, 33.07258, -86.92692, 33.07297, -86.92702, 33.07338, -86.92687, 33.07377, -86.92641, 33.07389, -86.9259, 33.07398, -86.92562, 33.
07431, -86.92535, 33.0747, -86.92503, 33.075, -86.92456, 33.07515, -86.9241, 33.07533, -86.92364, 33.07549, -86.92355, 33.07589, -86.92397, 33.07611, -86.92445, 33.0761, -86.92492, 33.07607, -86.92541, 33.0761, -86.9259, 33.07611, -86.92638, 33.07619, -86
.92686, 33.07632, -86.9268, 33.07676, -86.92633, 33.07692, -86.92586, 33.0768, -86.92543, 33.07658, -86.92495, 33.07654, -86.92448, 33.07671, -86.92414, 33.077, -86.92392, 33.07738, -86.92379, 33.07777, -86.9238, 33.07818, -86.92393, 33.07858, -86.9237, 3
3.07896, -86.92321, 33.07886, -86.92303, 33.07843, -86.92286, 33.078, -86.92207, 33.07746, -86.92188, 33.07705, -86.92178, 33.07663, -86.92179, 33.0762, -86.9218, 33.07578, -86.92141, 33.07552, -86.92332, 33.1382, -86.9695, 33.13811))
Thanks,
Ramas.

Categories

Resources