14 Replies Latest reply on Apr 16, 2014 8:09 AM by Stefan Jager

    Irritation level is getting pretty huge

    FlyingGuy

      Wow, spatial is starting to be a really huge PITA!  Below, "sources" is a cursor...

       

      vnode sdo_geometry ;
      DBMS_OUTPUT.PUT_LINE(' Source Geom = ' || SDO_UTIL.TO_WKTGEOMETRY(sources.geom)) ;
      output =  Source Geom = POINT (-118.18981 34.13884)
      v_node := SDO_UTIL.POINT_AT_BEARING(sources.geom,0,500) ;
      DBMS_OUTPUT.PUT_LINE(' Return from PAB call = '|| SDO_UTIL.TO_WKTGEOMETRY(V_NODE)) ;
      Return from PAB call = POINT (-118.18981 34.1433475527035)
      --
      DBMS_OUTPUT.PUT_LINE('getting x1  '||sources.geom.sdo_point.x) ;
      output = getting x1  -118.18981
      DBMS_OUTPUT.PUT_LINE('getting y1  '||SOURCES.GEOM.SDO_POINT.Y) ;
      output = getting y1  34.13884
      DBMS_OUTPUT.PUT_LINE('getting x2  '||v_node.sdo_point.y) ;
      output = getting x2  
      DBMS_OUTPUT.PUT_LINE('getting y2  '||v_node.sdo_point.y) ;
      output = getting y2 
      

       

      This is really starting to make me a little crazy.  I have tried surrounding v_node in a with n as () statement to give them an alias, I have tried making v_node a simple sdo_point_type and still CAN't extract the vertice so I can take two points and make a line string,  The are 500 meters apart so I can have a nice straight line geometry but I am frustrated at every turn! The only thing I have found that works is to extract them to a wkt and then to all sorts of subtr and instr functions to drag them out and that is a major PITA.  Anyone have a better idea or am I just totally missing the boat on something?

       

      Cheers!

        • 1. Re: Irritation level is getting pretty huge
          FlyingGuy

          THIS is what I resorted to, but there just HAS GOT TO BE a better way!!!!

           

          CREATE OR REPLACE FUNCTION F_LINK_FRM_TWO_POINTS
          (
          G1 SDO_GEOMETRY,
          g2 in sdo_geometry
          )
          RETURN SDO_GEOMETRY
          AS
          P1 CLOB;
          P2 CLOB;
          P3 clob;
          A_WKT clob ;
          b_wkt clob ;
          v_link sdo_geometry ;
          BEGIN
          
            A_WKT := SDO_UTIL.TO_WKTGEOMETRY(G1);
            B_WKT := SDO_UTIL.TO_WKTGEOMETRY(G2);
          
            P1 := SUBSTR(A_WKT,INSTR(A_WKT,'(')+1);       
            P1 := SUBSTR(P1,1,INSTR(P1,' '));       
            P2 := RTRIM(SUBSTR(A_WKT,INSTR(A_WKT,' ',-1)),')');
            P1 := trim(P1)||' '||trim(P2) ;
          
            P2 := SUBSTR(b_WKT,INSTR(b_WKT,'(')+1);       
            P2 := SUBSTR(P2,1,INSTR(P2,' '));       
            P3 := RTRIM(SUBSTR(B_WKT,INSTR(B_WKT,' ',-1)),')');
            P2 := trim(P2)||' '||trim(p3) ; 
          
            V_LINK := SDO_UTIL.FROM_WKTGEOMETRY('LINESTRING('||P1||', '||P2||')');
            V_LINK.SDO_SRID := 8307 ; 
          
          
            RETURN V_LINK ;
          
          END F_LINK_FRM_TWO_POINTS;
          
          • 2. Re: Irritation level is getting pretty huge
            navaneet

            I am not sure whether I understood your question. Are you trying to extract points from the geometry? You could use the following.

            FUNCTION get_point_from_geom (

                  geom SDO_GEOMETRY, point_number NUMBER DEFAULT 1

                ) RETURN SDO_GEOMETRY

                IS

               g SDO_GEOMETRY;   -- Updated Geometry
               d NUMBER;         -- Number of dimensions in geometry
               p NUMBER;         -- Index into ordinates array
               px NUMBER;        -- X of extracted point
               py NUMBER;        -- Y of extracted point

             

                BEGIN

               -- Get the number of dimensions from the gtype
               d := SUBSTR (geom.SDO_GTYPE, 1, 1);

             

               -- Verify that the point exists
               IF point_number < 1
               OR point_number > geom.SDO_ORDINATES.COUNT()/d THEN
               RETURN NULL;
               END IF;

             

               -- Get index in ordinates array
               p := (point_number-1) * d + 1;

             

               -- Extract the X and Y coordinates of the desired point
               px := geom.SDO_ORDINATES(p);
               py := geom.SDO_ORDINATES(p+1);

             

               -- Construct and return the point
               RETURN
               SDO_GEOMETRY (
               2001,
               geom.SDO_SRID,
               SDO_POINT_TYPE (px, py, NULL),
                 NULL, NULL);

                END;

            1 person found this helpful
            • 3. Re: Irritation level is getting pretty huge
              FlyingGuy

              Hey Navaneet,

               

              Yeah that will do the trick, but the more I deal with Oracle Spatial, the more I get the idea that this was really slapped together, and in Java no less.

               

              There are too many times when you get uncaught java exceptions which are just no help at all.  Things like trying to create spatial indexes and if it fails ( FOR ANY REASON ) it leaves behind a broken index then spits errors at you when you try the task again without realizing it left it behind.  Having to deal with permissions on the metadata tables and all that sort of thing.

               

              Having to alias a table name with point geometries in it just to get the x,y stuff.  Having to deal with the constructions at the level of ordinates and arrays is just really WAY more complexity then needed.  I mean, yes if you want to play around with those things fine, but it you are just trying to get work done having to deal with that level of complexity is really a large PITA.  We all know the NUMBER type behind the scenes is a very strange construct to support all sorts platforms and such, but we do not have to know and or mess with it to use it.

               

              And the lava based spatial errors that it spits out are pretty much useless.

               

              The have the right idea, but it needs some SERIOUS work and polish, and don;t even get me started on the documentation,

               

              Cheers.

              • 4. Re: Irritation level is getting pretty huge
                Stefan Jager

                FlyingGuy wrote:

                 

                Yeah that will do the trick, but the more I deal with Oracle Spatial, the more I get the idea that this was really slapped together, and in Java no less

                 

                Slapped together? Not really, it's been around for more than a decade now so it's pretty mature in my book.

                FlyingGuy wrote:

                 

                There are too many times when you get uncaught java exceptions which are just no help at all.  Things like trying to create spatial indexes and if it fails ( FOR ANY REASON ) it leaves behind a broken index then spits errors at you when you try the task again without realizing it left it behind.  Having to deal with permissions on the metadata tables and all that sort of thing.

                I don't get that many Java exceptions, and if I do they almost always tell me what the problem is. And usually it is my own fault. It's true that your data has to meet pretty high standards, but would you want it any other way? I don't want rubbish data: rubbish in is rubbish out.

                 

                FlyingGuy wrote:

                 

                Having to alias a table name with point geometries in it just to get the x,y stuff.  Having to deal with the constructions at the level of ordinates and arrays is just really WAY more complexity then needed.  I mean, yes if you want to play around with those things fine, but it you are just trying to get work done having to deal with that level of complexity is really a large PITA.  We all know the NUMBER type behind the scenes is a very strange construct to support all sorts platforms and such, but we do not have to know and or mess with it to use it.

                It sounds to me like you are trying to use Oacle as a front-end application, which it was never meant to be. It's meant as a backoffice storage solution that offers some extra functionality, mainly in the possibility of having one data store and multiple users. You might be better off using a desktop GIS application as a front-end with Oracle as storage, it might give you less frustration. It also helps if you know more about Oracle, SQL and PL/SQL and when to use what.

                 

                That said: You haven't shown us how you are building your "sources" cursor, and what kind of cursor it is. So I am going to assume a simple table with a geometry column that holds points:

                 

                with firstpoint as

                (

                  select s.geometry from sources s where id=something --or whatever your cursor does;

                ),

                secondpoint as

                (

                  select SDO_UTIL.POINT_AT_BEARING(firstpoint,0,500)  from dual;

                )

                select SDO_GEOMETRY(2001, NULL, SDO_ELEM_INFO(1, 2, 1), SDO_ORDINATE_ARRAY(firstpoint.sdo_point.x,firstpoint.sdo_point.y, secondpoint.sdo_point.x, secondpoint.sdo_point.y)) from dual;

                 

                You could do something like this, I haven't tested this but it should work. replace table and columnnames with your own.

                 

                Cheers,

                Stefan

                • 5. Re: Irritation level is getting pretty huge
                  Paul Dziemiela

                  Hi FlyingGuy,

                   

                  As the other folks have said, I think you are barking up the wrong tree with serializing your SDO_GEOMETRY into WKT in order to "do work on it".  You really need to dive deeper and work directly with the objects and varrays in the geometry for better or worse.  A good place for examples and ideas is the Greener/Ravada book

                  Applying and Extending Oracle Spatial | Packt Publishing

                   

                  I do agree with you that the Java-based WKT routines are not very useful and more about the OGC label than anything else.  Don't get me started on how it handles polygon rings:
                  Re: SDO_UTIL.FROM_WKTGEOMETRY seems ignorant of ring rotation
                  I would say most spatial folks just surrender and write their own PLSQL parser/writer or hook into JTS as Simon shows here

                  http://www.spatialdbadvisor.com/oracle_spatial_tips_tricks/273/3d-4d-and-srid-aware-conversion-functions-for-sdo_geometr…

                  If you want a copy of my WKT stuff, just drop me an email at my first name at my last name dot com.  For example I have the need to accept EWKT sometimes with the long/lat reversed.  On the output side in particular I have the need to trunc the output numbers as (must just be me) sometimes I don't need 32 places of precision on my numbers. 

                   

                  Anyhow, I think avoiding the Java stuff will make your life easier and lower the irritation.  Working with arrays and object attributes in PLSQL is complex but sure beats the black box of generic java errors.

                   

                  Cheers,

                  Paul

                  • 6. Re: Irritation level is getting pretty huge
                    Stefan Jager

                    Something else I noticed, while looking at this again: you declare vnode SDO_GEOMETRY, and then you start using v_node. You are also printing the v_node.sdo_point.y twice instead of printing the x and the y.

                    Can you post your full code (plus a table definition and a couple of sample records), that we might have a look at it and help get it working?

                    And: are you sure that your cursor is returning one single record?

                    Other than that, if I try this at home it works for me (with a faked source of course, so there might be something in your data that's different from mine).

                     

                    Oh, and I agree with Paul: dive a bit deeper into the possibilities of SQL (it's quite amazing what you can do with almost pure SQL), and PL/SQL.

                     

                    Cheers,

                    Stefan

                    • 7. Re: Irritation level is getting pretty huge
                      B Hall

                      Good morning,

                       

                      I do agree with you on the spatial index issue. IF it fails, it should clean up after itself, no excuse there. I don't know how many times I've had to explain why you need to do that to new users.

                       

                      So, even after using it for about 14 years, I feel some of your pain. That said, the reason we use it (for now anyhow) is that it runs rings around SQL Server in terms of nearest neighbor performance, which is something we use extensively.  We had one project they were trying to use SS with for building cubes that ran for 5 weeks. I took the same data and ran it in Oracle in about 2 hours.

                       

                      On the other hand, building those point indexes we use for that project is VERY painful. A B-tree index with long/lat builds in 20 seconds. A R-tree index on the same simple point data takes 23 minutes. Hopefully the in-memory option sorting will soon help with the build, but I'm not sure how well it supports object columns. Making it a more parallel process to take advantage of lots of cores (even with reduced index efficiency due to some overlapping bbox's)  would be a huge win.

                       

                      Keep at it, and definitely get a copy of Simon and Siva's book, and the Pro Spatial book to use as a reference guide.

                       

                      Bryan

                      • 8. Re: Irritation level is getting pretty huge
                        Bkazar-Oracle

                        Hi Paul,-

                        Why should WKT converter worry about the validity of the input geometry?

                        As Siva mentioned:

                         

                        Paul,

                          We don't try to fix the ring orientation as part of the WKT conversion as it is an overhead.

                        Once you do the conversion from wkt to sdo_geometry, run validate_geometry on the layer to see if there

                        are invalid geometries and fix only those that have incorrect orientation.

                          siva

                         

                        It is a definite overhead. You just validate your geometry and then use WKT converter.

                        Isnt that clean and simple?

                        Thanks

                        • 9. Re: Irritation level is getting pretty huge
                          Paul Dziemiela

                          Hi Baris,

                           

                          We are hijacking the original guy's thread. 

                          But the input geometry is valid according to the WKT specification.  That's the crux of my argument.  The result is not clean.  The user must then research the two specifications to figure why their valid input is now their invalid output and craft a solution.  I would not call that simple.

                           

                          I suppose its more about semantics.

                           

                          What exactly do we in the geospatial developer community expect when we encounter a "converter" of some kind?  The existing Oracle Spatial tool takes the data structure of the WKT and faithfully parses it into the equivalent object and varrays structure of an SDO_GEOMETRY.  In a sense that is pretty simple and clean and I don't meant to be over wrought on the matter.  But that equivalent object and its varrays now comprise an invalid SDO_GEOMETRY that needs additional attention to be fixed.  I guess I just have this expectation that to input something valid in one format should convert to something valid in another.  At the very least if that expectation is false, it would be helpful to make a note of it in the documentation.

                           

                          Again, I don't mean to waste time complaining about a function I no longer use.

                           

                          Cheers,

                           

                          Paul

                          • 10. Re: Irritation level is getting pretty huge
                            Bkazar-Oracle

                            Hi Paul,-

                            Quick question:

                            Have you filed a bug on that valid input producing an invalid output for WKT?

                            Thanks

                            • 11. Re: Irritation level is getting pretty huge
                              Paul Dziemiela

                              Hi Baris,

                               

                              Well no as Dr. Ravada clearly says the current process is working as designed.  There are probably not enough native speakers on the list here to make a joke about the horse's mouth.  What I would think is a good idea is to add a blurb in the SDO_UTIL.FROM_WKTGEOMETRY documentation to just mention this issue and how to resolve it.  My DBA folks control the support requests and while I can ask them to open an SR to "add a blurb to the docs page", I fear it might not be the best stewarding of my limited goodwill with them. 

                               

                              I feel guilty for stealing Flying Guy's thread.  Hey Flying Guy, how is it going?  Did you come up with a better approach?  Sorry for the hijacking.

                               

                              Cheers,

                              Paul

                              • 12. Re: Irritation level is getting pretty huge
                                Bkazar-Oracle

                                Hi Paul,

                                i know that idiom.

                                Thanks

                                • 13. Re: Irritation level is getting pretty huge
                                  FlyingGuy

                                  Hey All,

                                   

                                  No worries about thread-jacking, this discussion is quite enlightening.  A few comments about what others have said...

                                   

                                  I have been using oracle for a long time, but spatial is something new to me in the last couple of years and only in the last year really seriously.

                                   

                                  Oracle as simple data store?  Sorry I reject that notion out of hand.  PL/SQL is a godsend.  You want to enforce rules and standards and have security and performance?  Stored Procedures and Packages are gold!   Want to have a trashed database in less time then it takes to get a cup of coffee?  Let some "web programmer" have his way with the database using hibernate or spring.  While one can argue where to put the business logic I prefer it on the DB server, just my preference since there really is no right or wrong in that regard.

                                   

                                  Spatial in general...

                                   

                                  Being able to declare a variable of type of sdo_geometry and then set it, one should then have a set of methods that allow you to get at various parts of it when you want to, directly, but again that is just my opinion.  The NN functions are fast, the other functions work well but things start to break down from a functional POV when instead of simply being able to fetch x and y from a point geometry one has to start playing with ordinates and fetching arrays and what not.  If an sdo_geometry contains an sdo_point_type then by george the object should should have a method to fetch those values in a straight up fashion.  If an sdo_contains a linestring you should be able to ask for the number of vertices then simply be able to grab the nth one(s) you are interested in that are part of a  polygon / circle  / linestring, etc.

                                   

                                  The documentation seems like a lot of documentation that was written by engineers, for engineers and not by technical writers who know how to logically lay out things so that anyone can work through it,  The whole "cola" thing assumes WAY to much spatial knowledge and does not start with the basic building blocks and then work from there.  It mostly looks like it was derived from javadocs which are NOT documentation by any stretch of the imagination.

                                   

                                  Oracle spatial works, but it is a lot of "black art" as it were.  Using NN and then find a distance from an origin to the NN point.  Nowhere does it tell you that if you want to use this you have to convert SDO to LRS and that is just one example where Simon Greener saved my mind as I was trying to figure this stuff out.  I am working with road networks and I need to do a lot of calculations that deal with distances and directions ( most always in degrees but most of spatial is based on radians.  LOL I had to go back and re-learn about radians since it had been 30 years since I had even though about those concepts.

                                   

                                  Anyway.. Like many things in Oracle there is a steep learning curve and the docs don't make it easy for the spatialy uninitiated.  I thnk all of you for your help, suggestions and pointers to where I can get more in depth with this stuff.

                                   

                                  Cheers to all!

                                  • 14. Re: Irritation level is getting pretty huge
                                    Stefan Jager

                                    FlyingGuy wrote:

                                     

                                    While one can argue where to put the business logic I prefer it on the DB server, just my preference since there really is no right or wrong in that regard.

                                    Not wanting to spawn one of "those" discussions again, I do agree with you completely here. What a waste of resources not to use the DB for that....

                                     

                                    FlyingGuy wrote:

                                    The documentation seems like a lot of documentation that was written by engineers, for engineers and not by technical writers who know how to logically lay out things so that anyone can work through it,  The whole "cola" thing assumes WAY to much spatial knowledge and does not start with the basic building blocks and then work from there.  It mostly looks like it was derived from javadocs which are NOT documentation by any stretch of the imagination.

                                    As I've said before, the whole Orale Spatial thing was never meant to be used as a front end, but as a back end with applications talking to it and those applications would be used by the users (with less knowledge). The back end was meant to be maintained and developed by administrators, dba's, developers with more knowledge than the average Joe. That is how it was explained to me by Oracle, and I think it still holds true. The problem is with the mainstreaming of geo-information you start to see people with no geodetic knowledge starting to fiddle with geo-data, and then you have a problem. I still see users zooming in on data until they can see the last 10th of a millimeter on screen, while the data has been gathered by vectorizing aerial photograps. Which implies an accuracy, precision and reliability that runs in 10s of centimeters if you're lucky, so zooming in that far is useless.

                                     

                                    I do agree that some of the documentation could be a bit better, but that is also up to us: If you look at the docs online , you'll notice that all the way at the bottom there's an option to leave comments (@Paul: that might enable you to leave a comment as well). So we should probably use that option a little bit more.

                                     

                                    FlyingGuy wrote:

                                    hibernate or spring

                                    IMHO those two should be placed in the deepest pits of hell to burn forever....

                                     

                                    FlyingGuy wrote:

                                     

                                    Anyway.. Like many things in Oracle there is a steep learning curve and the docs don't make it easy for the spatialy uninitiated.  I thnk all of you for your help, suggestions and pointers to where I can get more in depth with this stuff.

                                     

                                    Cheers to all!

                                    I've learned a lot on these forums myself, and am happy to be able to repay some of that   Besides, it's a nice diversion when you are waiting for another 25-minute process to finish (or crash ).

                                     

                                    Cheers,

                                    Stefan