10 Replies Latest reply: Jul 4, 2013 1:59 AM by Stefan Jager RSS

    Oracle 11g calculate  items in a polygon

    592815

      Friends,

       

      I'm new in geo. We have a table contains item information with a position (latitude and longtitude).

      The user requests to get item's name in polygon of ABCD areas with below  4 points value (latitude and longtitude)

       

      A: 35N;104W

      B: 34N; 112W

      C: 33N,97 W

      D: 32N, 108W

      How do I find these items in requested polygon ABCD area?

       

      select item_Name, time FROM TEST_POS p

      WHERE  (GET_TEXT (p.lat_lon.sdo_point.y) between yya and yyb')

      and (GET_TEXT (p.lat_lon.sdo_point.x) between xxa and xxb')

      and  (p.date < (sysdate -1)

       

      where put c and d condition of latitude and longtitude?

       

      Thanks for help!!

      newdba

        • 1. Re: Oracle 11g calculate  items in a polygon
          Stefan Jager

          Hi newdba,

          I will assume you have a table called "polygons", with an SDO_GEOMETRY column called "geom" that holds the actual geometries. Then you can do something like this:

           

          select t.item_Name,
                    t.time
          from test_pos t,
          polygons p
          where   sdo_contains(p.geom, t.lat_lon)='TRUE'
          and  p.date < (sysdate -1);
          

           

          By the way, it's not a good idea to name a column "time" or "date" or some other reserved word. It will probably not become a problem, but I would rename that column to something like item_time and item_date, to prevent problems in the future.

           

          Secondly, if you need to learn more about Spatial, check the excellent documentation. There's a lot that can be done with fairly simple SQL statements.

           

          HTH,

          Stefan

          • 2. Re: Oracle 11g calculate  items in a polygon
            592815

            Hi Stefan,

             

            the test_position table  with  geom column as SDO_GEOMETRY type.

            I use SDO function to get lat and log value from this colum. But i do not know how to put 4 point polygons as condition in SQL.

            I modified your SQL as below.

             

              select t.item_Name, 

                          t.time 

                from test_pos t

                where   sdo_contains(p.geom, xa,ya,xb,yb,xc,yc,xd,yd)='TRUE' 

                and  p.date < (sysdate -1); 

             

            Thanks for your help!

            • 3. Re: Oracle 11g calculate  items in a polygon
              Stefan Jager

              592815 wrote:

               

              Hi Stefan,

               

              the test_position table  with  geom column as SDO_GEOMETRY type.

              I use SDO function to get lat and log value from this colum. But i do not know how to put 4 point polygons as condition in SQL.

              OK, in that case I think I misunderstood. So you only have the four points? You don't have a table with polygons?

               

              592815 wrote:

                select t.item_Name,

                            t.time

                  from test_pos t

                  where   sdo_contains(p.geom, xa,ya,xb,yb,xc,yc,xd,yd)='TRUE'

                  and  p.date < (sysdate -1);

              This is not going to work. Oracle will give you an error message on that.

              If you only have the four points, you can easily create a temporary geometry from that and use that as a query window:

                select t.item_Name,
                            t.time
                  from test_pos t
                  where   sdo_contains(p.geom,
                  SDO_GEOMETRY(2003,  -- Element GTYPE
                                              NULL, -- SRID: coordinatesystem definition
                                              NULL, -- SDO_POINT
                                              SDO_elem_info_array(1,1003,1),  -- Polygon with straight sides
                                              SDO_ordinate_array(35,104,  -- Point A
                                                                            34, 112, -- Point B
                                                                            33, 97,  --Point C
                                                                            32, 108  -- Point D
                                                                            35,104,  -- Point A again, because a polygon MUST be closed so first point must be equal to last point
                                                                           )                              
                                              )
                  )='TRUE'
                  and  p.date < (sysdate -1);
              

               

              Be carefule: your SRID should be the same as used in your table. I would strongly suggest you read the Spatial Developer's Guide (11.2), because it is full of simple examples like this.

              • 4. Re: Oracle 11g calculate  items in a polygon
                592815

                Thanks so much.

                I executed your SQL and  received an error message as belo

                *

                ERROR at line 1:

                ORA-13226: interface not supported without a spatial index

                ORA-06512: at "MDSYS.MD", line 1723

                ORA-06512: at "MDSYS.MDERR", line 8

                ORA-06512: at "MDSYS.SDO_3GL", line 88

                ORA-06512: at "MDSYS.SDO_3GL", line 339

                 

                w

                 

                does SDO_ordinate_array(longitude, latitude,longitude, latitude,longitude, latitude,longitude, latitude,) is correction syntax?

                Thanks

                newdba

                • 5. Re: Oracle 11g calculate  items in a polygon
                  Stefan Jager

                  592815 wrote:

                  ORA-13208: internal error  while evaluating [window SRID does not match layer SRID] operator

                  Hi newdba,

                  this is your error, and it's an easy fix. Check the SRID on your existing data, and use the same SRID on your query. In order to do that, execute this query:

                    select t.geom.sdo_srid
                      from test_pos t
                      where rownum=1;
                  

                  The result of that should be a number. This number should be the same number as the result of this query:

                  select srid
                  from user_sdo_geom_metadata
                  where table_name='TEST_POS'
                  and column_name='GEOM';
                  

                   

                  Then change the query as follows:

                  select t.item_Name,  
                              t.time  
                    from test_pos t  
                    where   sdo_contains(p.geom,  
                    SDO_GEOMETRY(2003,   
                                                ######, -- Replace the ## with the number from the earlier queries here 
                                                NULL,   
                                                SDO_elem_info_array(1,1003,1),  
                                                SDO_ordinate_array(35,104,   
                                                                              34, 112,  
                                                                              33, 97,    
                                                                              32, 108    
                                                                              35,104, t  
                                                                             )                                
                                                )  
                    )='TRUE'  
                    and  p.date < (sysdate -1);
                  

                  Replace in this query the ### with the results you got from the query on the SDO_SRID, and your query should work.

                  NOTE: This is assuming that your data in your table is in the same coordinate system as the four points that you want to use as query window!!!

                  • 6. Re: Oracle 11g calculate  items in a polygon
                    592815

                    Per checking, the first SQL return  SRID but nothing is for second SRID query.

                    are there some metadata issue?

                    • 7. Re: Oracle 11g calculate  items in a polygon
                      navaneet

                      --> ORA-13226: interface not supported without a spatial index

                      The above error indicates that spatial index is missing. Try inserting the meta data and create spatial index.

                       

                       

                      INSERT INTO USER_SDO_GEOM_METADATA VALUES ('TEST_POS', 'GEOM',

                        MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT(''Longitude', -180, 180, 0.005),

                                            MDSYS.SDO_DIM_ELEMENT('Latitude', -90, 90, 0.005)),

                        NULL  -- replace it with srid like 8307

                      );

                       

                       

                      create index QENET1_link_sidx on TEST_POS(geom)

                        indextype is mdsys.spatial_index;

                      • 8. Re: Oracle 11g calculate  items in a polygon
                        Stefan Jager

                        592815 wrote:

                         

                        Per checking, the first SQL return  SRID but nothing is for second SRID query.

                        are there some metadata issue?

                        What do you think? Have you read the documentation, as I suggested a couple of times? Oracle's error messages are usually quite clear about what the problem is, and the documentation explains quite well what needs to be done in order to use the spatial stuff.

                        • 9. Re: Oracle 11g calculate  items in a polygon
                          592815

                          Hi Stefan,

                          Thanks for your help. I am read documentation .

                          As your said, a table called "polygons", with an SDO_GEOMETRY column called "geom" that holds the actual geometries.

                          Manager like to create a new table  to store SDO_GEOMETRY actual geometries that request. how to do that?

                           

                          newdba

                          • 10. Re: Oracle 11g calculate  items in a polygon
                            Stefan Jager

                            Hi newdba,

                             

                            1. Create an appropriate datamodel for your business requirement. Think of geometry as just another attribute of an object, especially in Oracle geometry is nothing special (ok, I'm going to get some comments on this one but in principle it is true )

                            2. Create the functionality that fullfills your business requirement.

                             

                            From reading the earlier posts, and the documentation, you should be able to figure out how to do this, it's not that difficult: 2.1 Simple Example: Inserting, Indexing, and Querying Spatial Data. Read this, follow the examples and try them on your own database, so you can see how it works.

                             

                            Regards,

                            Stefan