18.15 SDO_POINTINPOLYGON

Format

SDO_POINTINPOLYGON(cur, geom_obj, tol, params) RETURN ANYDATASET;

Description

Takes a set of rows whose first column is a point's x-coordinate value and the second column is a point's y-coordinate value, and returns those rows that are within a specified polygon geometry.

Keywords and Parameters

Returns

SDO_POINTINPOLYGON returns an object of ANYDATASET TYPE, which is described in Oracle Database PL/SQL Packages and Types Reference. The ANYDATASET output columns are those specified by the cur parameter.

Usage Notes

SDO_POINTINPOLYGON is technically not an operator, but a table function. (For an explanation of table functions, see Oracle Database PL/SQL Language Reference.) However, it is presented in the chapter with spatial operators because its usage is similar to that of the operators, and because it is not part of a package with other functions and procedures.

The SQL statement used in the cur parameter can have any number of predicates in the WHERE clause. This feature can be used to filter the data on other attributes before passing the resulting rows into the SDO_POINTINPOLYGON operator.

The output columns are identical to the input columns, but the only rows returned are those matching the selection criteria.

Table 18-5 shows the keywords for the params parameter.

To use parallel query servers, you must do either of the following:

• Specify the /*+ PARALLEL(<table alias>, <n>) */ optimizer hint, where <table_alias> is the specified table alias and <n> is the degree-of-parallelism.

• Enable parallel query execution by entering the following command from a suitably privileged account:

  ALTER SESSION FORCE PARALLEL QUERY;

Examples

The following example creates a new table named COLA_MARKET_POINTS based on the data from the COLA_MARKETS table, which is described in Simple Example: Inserting_ Indexing_ and Querying Spatial Data and illustrated in Figure 2-1. The example then selects a point within each geometry where the MKT_ID column value is greater than 1. (It uses the SDO_UTIL.INTERIOR_POINT function to get a point that is guaranteed to be inside each geometry that matches the query criteria.)

-- Create a new table with a different name based on the data from the 
-- COLA_MARKETS table. This table has four columns: X, Y, MKT_ID, and NAME.
 
CREATE TABLE cola_market_points AS
SELECT a.point.sdo_point.x X, a.point.sdo_point.y Y, MKT_ID, NAME
 FROM (
SELECT mkt_id, name, sdo_util.interior_point(shape) point FROM cola_markets) a;

-- Limit to MKT_ID > 1. Also, use the PARALLEL hint.
SELECT /*+ PARALLEL(a, 4) */ *
FROM TABLE(sdo_PointInPolygon(
  CURSOR(select * from cola_market_points where mkt_id > 1),
  SDO_GEOMETRY(
    2003,
    NULL,
    NULL,
    MDSYS.SDO_ELEM_INFO_ARRAY(1, 1003, 1),
    MDSYS.SDO_ORDINATE_ARRAY(1, 1, 8, 1, 8, 6, 5, 7, 1, 1)),
  0.05)) a;
 
         X          Y     MKT_ID NAME                                           
---------- ---------- ---------- --------------------------------               
    6.3125      2.875          2 cola_b                                         
    4.6875      3.875          3 cola_c

The following example uses a bind variable in the WHERE clause, and it specifies a params string. It assumes the existence of a table named PIP_DATA.

DECLARE
 my_cursor SYS_REFCURSOR;
 my_pip_cursor SYS_REFCURSOR;
 stmt varchar2(2000);
 cnt number;
BEGIN
  stmt := 'SELECT count(*) FROM ' ||
          ' TABLE (Sdo_PointInPolygon(' ||
          'CURSOR(select * from pip_data where x < :x1),' ||
          ' :g1, :tol, ''mask=DISJOINT sdo_batch_size=6000'')) ';
 open my_cursor for stmt
 using 100, -- :x1
       SDO_GEOMETRY( 2003, NULL, NULL,
              SDO_ELEM_INFO_ARRAY(1, 1003, 1),
              SDO_ORDINATE_ARRAY(10, 10, 70,10, 70, 70, 50,70,
                                 40,50, 20,70, 10,70, 10,10)), -- :g1
       0.05; -- :tol
 FETCH my_cursor into cnt;
 dbms_output.put_line(to_char(cnt));
END;
/

Related Topics

• SDO_UTIL.INTERIOR_POINT