SDO_POINTINPOLYGON

Format

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

Description

Takes a set of rows, and returns those rows that are within a specified polygon geometry.

Keywords and Parameters

Value	Description
cur	One of the following: A REF cursor: the first two columns in the SELECT of the SQL for ref_cursor must be an X and Y point coordinates pair from a user table. The two columns must be of type NUMBER. (Any other columns can be number, character, and date types). Data type is SYS_REFCURSOR. A REF cursor: the first column in the SELECT of the SQL for the ref cursor must be a spatial geometry object from a user table. The column must be of type SDO_GEOMETRY. (Any other columns can be number, character, and date types). Data type is SYS_REFCURSOR.
geom_obj	Spatial geometry object: either a geometry from a table or a transient instance of a geometry, against which all of the selected points from `cur` will be checked. Data type is SDO_GEOMETRY.
tol	Tolerance value (see Tolerance). Must be greater than 0.0. Data type is NUMBER.
params	Optional parameter string of keywords and values. Determines the behavior of the operator. See Table 20-5 in the Usage Notes for information about the available keywords. Data type is VARCHAR2. Default is NULL.

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 20-5 shows the keywords for the params parameter.

Table 20-5 params Keywords for the SDO_POINTINPOLYGON Operator

Keyword Description

Keyword	Description
mask	The topological relationship of interest. Valid values are 'mask=<value>' where <value> is one or more of the following: `TOUCH`, `OVERLAPBDYDISJOINT`, `OVERLAPBDYINTERSECT`, `EQUAL`, `INSIDE`, `COVEREDBY`, `CONTAINS`, `COVERS`, `ANYINTERACT`, `ON`. Multiple masks are combined with the logical Boolean operator OR, for example, '`mask=inside+touch'`. See Spatial Relationships and Filtering for an explanation of the nine-intersection relationship pattern. If `cur` is a pair of X and Y point coordinates, `TOUCH` and `ON` are synonymous. If this parameter is null or contains an empty string, `mask=ANYINTERACT` is assumed.
sdo_batch_size	Specifies the maximum number of rows that are processed in a batch. The default value is 4000 and the maximum value is 32768. Data type is NUMBER. For example: 'sdo_batch_size=5000'

mask

The topological relationship of interest. Valid values are 'mask=<value>' where <value> is one or more of the following: TOUCH, OVERLAPBDYDISJOINT, OVERLAPBDYINTERSECT, EQUAL, INSIDE, COVEREDBY, CONTAINS, COVERS, ANYINTERACT, ON. Multiple masks are combined with the logical Boolean operator OR, for example, 'mask=inside+touch'. See Spatial Relationships and Filtering for an explanation of the nine-intersection relationship pattern.

If cur is a pair of X and Y point coordinates, TOUCH and ON are synonymous.

If this parameter is null or contains an empty string, mask=ANYINTERACT is assumed.

sdo_batch_size

Specifies the maximum number of rows that are processed in a batch. The default value is 4000 and the maximum value is 32768. Data type is NUMBER.

For example: 'sdo_batch_size=5000'

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 and illustrated in Simple Example: Inserting_ Indexing_ and Querying Spatial Data. 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 does the same SDO_POINTINPOLYGON query as the previous one, but without the need to create a COLA_MARKET_POINTS table, and where for each row with a MKT_ID column value greater than 1, a point geometry is returned instead the just the X and Y coordinate pair. (The output has been reformatted for readability.)

-- Limit to MKT_ID > 1. Also, use the PARALLEL hint. 
SELECT /*+ PARALLEL(a, 4) */ name, mkt_id, point
FROM TABLE(sdo_PointInPolygon(
  CURSOR(select sdo_util.interior_point(shape) point, mkt_id, name 
         from cola_markets 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;

NAME   MKT_ID  POINT(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
------ ------- ----------------------------------------------------------------------------
cola_b   2     SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(6.3125, 2.875, NULL), NULL, NULL)
cola_c   3     SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(4.6875, 3.875, NULL), NULL, NULL)

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;
/