This discussion is archived
10 Replies Latest reply: Jul 3, 2013 11:59 PM by Stefan Jager RSS

Oracle 11g calculate  items in a polygon

592815 Newbie
Currently Being Moderated

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 Journeyer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    --> 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 Journeyer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points