This discussion is archived
14 Replies Latest reply: Mar 3, 2011 5:26 AM by Paul Dziemiela RSS

SDO_GEOMETRY size limits here to stay?

Paul Dziemiela Journeyer
Currently Being Moderated
Hi folks,
Recently I seem to be receiving more and more polygons from various ESRI-type folks that are too large to store in an Oracle SDO_ORDINATE_ARRAY. I was just reading the 11g docs at http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28400/sdo_objrelschema.htm#i1004087
and nothing has changed in this regard - I quote "Because the maximum SDO_ORDINATE_ARRAY size is 1,048,576 numbers, the maximum number of vertices in an SDO_GEOMETRY object depends on the number of dimensions per vertex: 524,288 for two dimensions, 349,525 for three dimensions, and 262,144 for four dimensions."
Does anyone have any thoughts on the matter or heard rumors of a fix or an approach to the issue (some kind of way of braking these big guys up?). I'd say generally I am receiving a few exceptional polygons upwards of 500,000 to a million vertices. Not much more than that but enough to muff oracle. Is the official Oracle response to the matter to ask what on earth anyone would want with a 500,000 vertice polygon? I too wish these things were not coming my way but they are. Thanks for any input.
Cheers,
Paul
  • 1. Re: SDO_GEOMETRY size limits here to stay?
    Ivan Bush Journeyer
    Currently Being Moderated
    Paul,

    What do these polygons, with so many vertices, represent in the real world? It must cover a large area.

    Ivan
  • 2. Re: SDO_GEOMETRY size limits here to stay?
    Paul Dziemiela Journeyer
    Currently Being Moderated
    Well, the polygons I am looking at right at the moment represent water surrounding various Alaskan Islands as part of the national hydrography database. The polygon itself is quite simple but the island represents a hole being very craggy with many inlets and counts about 400,000 vertices - yes its a 400,000 vertice hole. Now the NHD is 3D so that overtops the 349,525 limit. The point is that the authors are using ESRI tools to create these large polygons and then I have to stand hat-in-hand and explain that Oracle has these various limitations. It doesn't seem beyond possiblity to have an SDO_GEOMETRY object with more than one SDO_ORDINATE_ARRAY object within it. It seems like more of a structure thing than a technological limitation. Anyhow, anyone have a snappy response for these ESRI folks?
    Cheers,
    Paul
  • 3. Re: SDO_GEOMETRY size limits here to stay?
    Ivan Bush Journeyer
    Currently Being Moderated
    Paul,

    I have received data with what I thought to be excessive number of vertices, but not the number you are dealing with. Hence it fitted into Oracle.

    When I checked the data for redundant points I reduced the number of vertices by a factor of 5. Redundant point, to me, are:
    1. Coincident points when you consider the tolerance
    2. Sequential points which form a straight line

    It would be worth your while doing this. Split your polygons into 2 to achieve this test.

    Doing it for real if it helps is a slightly different issue.

    Let us know how this goes.

    Ivan
  • 4. Re: SDO_GEOMETRY size limits here to stay?
    Simon Greener Journeyer
    Currently Being Moderated
    Keep pestering the Oracle Spatial team for a fix to this problem: I have been one of the only "clashing symbols and banging gongs" on this topic for years.

    I too have hit this problem many times (especially as the use of GPS units means greater "precision" objects) and have had to "hack" database schemas to accommodate the need to split sdo_geometry objects (by including a table at the end of a 1:M relationship - very ugly!)

    It is not good enough to say to anyone: "Why the big polygon?" and "Change the model" or "Thin out the data".

    One example, recently, at GeoScience Australia (Australia's premier data collection agency for national datasets including elevation, bathymetry etc) I worked with bathymetric survey voyage data in which single or multibeam surveys had more than the SDO_ORDINATE limit (the data was 3D which meant that one had only 300,000 vertices to play with).

    But, in the meantime, you have to:

    * Thin out your data;
    * Introduce a second table to hold the split feature;
    * Introduce multiple spatial columns in the base table (eg shape1, shape2, shape3 etc).

    All not pretty but that is all you can do.

    regards
    S
  • 5. Re: SDO_GEOMETRY size limits here to stay?
    Paul Dziemiela Journeyer
    Currently Being Moderated
    Hey folks,

    I am back with another teeth-grinder. I am loading some SSURGO SOILS layers from http://www.ncgc.nrcs.usda.gov/products/datasets/ssurgo/ with polygons representing various soil types. In one dataset (called MUPOLYGON) I have 11 polygons which overtop the Oracle 524,288 vertice limit for two-dimensional geometries. In fact, there is a single polygon representing the bayous of Lousiana which has 3,481,427 vertices! This may be the "mother of all polygons" unless one of you knows of something larger. I can load this dataset via ArcSDE 9.2 using the old ESRI SDELOB and the new shiny ESRI ST_GEOMETRY storage types but it appears that Oracle just is not in the game. Note that even though this polygon is ridiculous in size, I am able to open it in ArcGIS and putter around with it. Has anyone heard anything about redesigning the SDO_GEOMETRY object to hold multiple SDO_ORDINATE arrays? I have quite a few brain cells invested in Oracle Spatial but there is not much I can do in this situation but begin learning the ESRI ST_GEOMETRY functions. It looks - so far at least - like this size limit may be a permanent constraint on Oracle Spatial technology and if my clients want to utilize large goverment datasets as they are, then they need to stay in the ESRI fold. Any advice would be appreciated.

    Thanks,

    Paul
  • 6. Re: SDO_GEOMETRY size limits here to stay?
    Paul Dziemiela Journeyer
    Currently Being Moderated
    Hey folks,

    Thought I should close out this thread with some of the recent events occuring around this topic. At the DC area Oracle Spatial User's Group meeting, my supervisor brought up to James Steiner of Oracle this issue of the VARRAY being too small to hold these large polygons bouncing around out there. At first Jim was a bit skeptical but fortuneately for me some folks from Census chimed in that they had these issues as well. Following up, I submitted to Oracle SR 6696391.993 on the matter and then had our partners at the EPA National Computer Center do the same as SR 6697612.994. I checked with Census and they have not submitted this as an issue. I would encourage anyone else encountering this problem to submit an SR of their own to keep the heat on. You can just copy mine which is what the EPA folks did.

    In response to my activities, Jim Steiner got back to me stating that the limitation will "be addressed in a future release". On my SR responses, I was told by "Keith" (who does not seem to have a last name) that "I cannot give you a time frame for this, but expect to see this in a future release, maybe 11gR2."

    So for now, my approach to the issue is to remove the offending polygons before bringing them into Oracle. If anyone has better ideas, please post them here.

    Cheers,
    Paul
  • 7. Re: SDO_GEOMETRY size limits here to stay?
    Paul Dziemiela Journeyer
    Currently Being Moderated
    Time to trot out this horse for another pointless flogging...

    I heard from some tinny voices over at the Census Bureau about patch 6087331 for 10.2.0.3 which is now part of the 10.2.0.4/11.1.0.6 releases that allows the SDO_TOPO_OBJECT_ARRAY to be redefined beyond its original 1 million size limit.

    It got me wondering just why all these various SDO objects and types have their component VARRAYs limited to around a million in size. I am fairly clueless on the underlying limitations/reasonings. The upper limit for a VARRAY is the upper positive limit for a PLS_INTEGER (the index) which is 2,147,483,647. So in theory the VARRAY that forms the basis of the SDO_ORDINATE_ARRAY object should be able to physically store over a billion 2D points (for example the SDO_LIST_TYPE is maxed out to 2147483647).

    Now I THINK (please fire away) the one million limit has to do with the way the VARRAYs are accessed, whereby the entire VARRAY needs to be loaded to change any part of it?? The quote from the user guide section '5 Using PL/SQL Collections and Records' is "You must update or retrieve all elements of the varray at the same time, which is most appropriate when performing some operation on all the elements at once. But you might find it impractical to store and retrieve large numbers of elements this way."

    So then is the magic number of 1,048,576 elements the best "gulpable" VARRAY value for the average Oracle instance? Or are there internal functions and java procedures and such that require that limit? I have no idea. Any thoughts or insights would be appreciated as I still get questions about when Oracle is going to allow larger geometries and I am never quite sure what is involved in this upgrade.

    Thanks,
    Paul
  • 8. Re: SDO_GEOMETRY size limits here to stay?
    476843 Newbie
    Currently Being Moderated
    My two cents,
    I used to work for a small town (9 square miles). We recieved planimetric data with the street edge of pavement as a polygon coverage. There was one polygon with over 3 million vertices. I attempted to load this into sde using the sdo geometry type many times. Only recently did I read about the limitation. As long as I do not desire to use sdo as my geometry it will load fine you know what it is loaded into oracle in both cases. But it only loads correctly if I don't use sdo. hmm what will my decision be??
    Oracle take note.
    Change or get left behind.
  • 9. Re: SDO_GEOMETRY size limits here to stay?
    mattschell Explorer
    Currently Being Moderated
    Paul and Brad,

    Tinny voice here. I think I roughly follow Paul's attempt to rationalize the varray size limit as a performance issue - that accessing an entire varray of 10million elements is worse than accessing 1million, if thats really how the gulping works. However, I don't really see any evidence of this.

    On 10.2.0.4 machine1 we have the new patch to dim the sdo_topo_object_array to 10million elements. I can repeatedly access a geometry with about 76,000 of the slots filled in 30 seconds.

    SQL> select count(*) from
    2 bas08st09.state a,
    3 TABLE(a.topogeom.GET_TOPO_ELEMENTS()) t
    4 where vintage = 90;

    COUNT(*)
    ----------
    76664

    Elapsed: 00:00:31.62
    SQL> desc mdsys.sdo_topo_object_array;
    mdsys.sdo_topo_object_array VARRAY(10000000) OF MDSYS.SDO_TOPO_OBJECT
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    TOPO_ID NUMBER
    TOPO_TYPE NUMBER

    On machine2 we have an exact copy of the data, but the patch to increase the varray size limit has not been applied. Basically the same performance.

    SQL> select count(*) from
    2 bas08st09.state a,
    3 TABLE(a.topogeom.GET_TOPO_ELEMENTS()) t
    4 where vintage = 90;

    COUNT(*)
    ----------
    76664

    Elapsed: 00:00:30.12
    SQL> desc mdsys.sdo_topo_object_array;
    mdsys.sdo_topo_object_array VARRAY(1000000) OF MDSYS.SDO_TOPO_OBJECT
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    TOPO_ID NUMBER
    TOPO_TYPE NUMBER
  • 10. Re: SDO_GEOMETRY size limits here to stay?
    Paul Dziemiela Journeyer
    Currently Being Moderated
    Hi folks,
    Is there perhaps an award for the longest running unresolved thread? I haven't seen anyone step up here on the forum or in any blogs detailing what went on at the Oracle Spatial Users Conference last week in Tampa. As I was there I thought I should drop a few posts.

    During the conference, I asked my usual question about the SDO_GEOMETRY size limits to Dr. Ravada and he responded - note that all these talks were prefaced with the usual disclaimers that nothing they said might actually happen - that 11gR2 would include a database level option for a some kind of "uber" SDO_GEOMETRY that would be allow larger geometries. That's about all I got from him and was never able to catch him in person to elaborate. It sounds like R2 will allow you choose this option but as they did not announce it as an R2 improvement I suspect it has some downsides (will it be compatible with the normal SDO?). I guess we will have to wait and see.

    Anyhow, that's the scuttlebutt. If anyone else knows more, please respond to the post.

    Cheers,
    Paul
  • 11. Re: SDO_GEOMETRY size limits here to stay?
    758076 Newbie
    Currently Being Moderated
    Hi,

    Are we still in the same boat? I've got an 11g R2 database and still can't load larger geometries (in my case from an ESRI shapefile). I haven't seen anything about an option to increase the maximum ordinates or allow another, bigger SDO geom type as Dr. Ravada apparently suggested.

    Luke


    EDIT:
    Scratch that. It is available in 11g R2 and described under section A.3 Increasing the Size of Ordinate Arrays to Support Very Large Geometries of
    http://download.oracle.com/docs/cd/E11882_01/appdev.112/e11830/sdo_migrat.htm#CHDBCHBG

    Edited by: user9215843 on 04-Mar-2010 19:20
  • 12. Re: SDO_GEOMETRY size limits here to stay?
    Paul Dziemiela Journeyer
    Currently Being Moderated
    Hi Luke,

    My environment has yet to move from 10g but the migration is slated to happen this year once the folks at ESRI certify ArcSDE for 11gR2. My understanding is the geometry upgrade essentially swaps out the existing SDO_GEOMETRY object with a new SDO_GEOMETRY object that has now dimmed the ordinates to the max. That being said I have not yet tried it - I am sure the Oracle folks are rolling their eyes considering I have been the primary complainer on the matter in the forums. But the truth is I am really am held up on 10g at the moment.

    If you are willing to take the plunge I would think folks on the forum would love to hear about how it goes - certainly I would. I've never seen a single post here on the matter - good or bad - since the 11gR2 release. Clearly there were reasons why the designers of Oracle Spatial back in the day used the 1,048,576 maximum for ordinates. So there must be consequences for changing the model. Or not? The only documented issues surround exporting/importing the geometries, nothing about performance or code compatibility issues.

    Great to hear from you.

    Cheers,

    Paul
  • 13. Re: SDO_GEOMETRY size limits here to stay?
    844160 Newbie
    Currently Being Moderated
    I am trying to cycle through some simplified ESRI countries, and put the country polygons into Oracle SDO.

    It turns out, that if the country polygon's coordinate pair count is less than 500, I can insert the polygon into an Oracle SDO table. If the polygon has 500 or more, the insertion fails. (There were a couple of weird countries that made it into the table that had a much larger number of coordinates, but they could not be used for anything).

    I can create a simple polygon (i.e., no holes) of 500 coordinates, and fail to put the geometry in an Oracle SDO table AND be UNABLE to perform a spatial query (i.e., SDO_RELATE) using that SDO geometry.

    If I throw one coordinate away (not the first or last one), I can put the geometry in an Oracle SDO table; also, I can use that geometry to perform a spatial query).

    500 is no where near the size quoted earlier in this thread.

    What is wrong?
  • 14. Re: SDO_GEOMETRY size limits here to stay?
    Paul Dziemiela Journeyer
    Currently Being Moderated
    Hi Dr. G,

    I guess I am responsible for this thread so I'll quick fire off a response. First of all, this topic is basically closed. The original thread was about Oracle Spatial being unable to handle very large geometries in comparison to competitor products. Oracle fixed the situation in 11gR2 with the new "uber" SDO_GEOMETRY type option. Thus the question, "size limits here to stay?" has been answered. As a community we should ideally create a new threads discussing performance and any got-chas surrounding the new uber geometry. However I am still on 10g and have not tried out the new geometry type myself (I know I know).

    All that being said, I don't think your issues have to do with these size limits. A quick review.

    "Classic" SDO_GEOMETRY has a maximum 1,048,576 ordinates. It doesn't matter if these define holes or lines or point clouds or whatever, ordinate is an ordinate. So Classic SDO_GEOMETRY maxes out at

    * 524,288 2D Coordinates
    * 349,525 3D or 2D+M Coordinates
    * 262,144 3D+M Coordinates

    That can be rather limiting as big things like the Russian Federation can easily top the 2D limit.

    Now "Uber" SDO_GEOMETRY has been upped to allow 10,000,000 ordinates creating max limits of

    * 5,000,000 2D Coordinates
    * 3,333,333 3D or 2D+M Coordinates
    * 2,500,000 3D+M Coordinates

    I kind of wonder how the folks at Oracle Spatial came up with the 10 million number. Why 10 and not 12 million (easier to divide by 3!)? No idea. The important take-away is the VARRAY technology that back-ends SDO_GEOMETRY must be entirely "gulped" into memory. You can't just read the first polygon ring or just the third string in a multistring, you have to suck in the whole, complete geometry and then do your thing. So I expect polygons with 5 millions vertices to be rather pokey to work with. If anyone has opinions and experiences, again we should start a new thread.

    So as these limits are orders of magnitude larger than your 500 coordinates, I think you have something else going on needing a new thread. Maybe post in the new thread how you are "putting" the ESRI data into SDO_GEOMETRY. In my opinion, ArcSDE really needs to be led by the nose to know what SRID to put on the Oracle Spatial Geometry.

    Cheers,

    Paul