Oracle Spatial User's Guide and Reference
Release 9.0.1

Part Number A88805-01

Library

Product

Contents

Index

Go to previous page Go to next page

13
Spatial Aggregate Functions

This chapter contains reference and usage information for the spatial aggregate functions, which are listed in Table 13-1.

Table 13-1 Spatial Aggregate Functions  
Method  Description 

SDO_AGGR_CENTROID 

Returns a geometry object that is the centroid ("center of gravity") of the specified geometry objects. 

SDO_AGGR_CONVEXHULL 

Returns a geometry object that is the convex hull of the specified geometry objects. 

SDO_AGGR_LRS_CONCAT 

Returns an LRS geometry object that concatenates specified LRS geometry objects. 

SDO_AGGR_MBR 

Returns the minimum bounding rectangle of the specified geometry objects 

SDO_AGGR_UNION 

Returns a geometry object that is the topological union (OR operation) of the specified geometry objects. 

See the usage information about spatial aggregate functions in Section 1.9.

Most of these aggregate functions accept a parameter of type MDSYS.SDOAGGRTYPE, which is described in Section 1.9.1.


SDO_AGGR_CENTROID

Format

SDO_AGGR_CENTROID(

Description

Returns a geometry object that is the centroid ("center of gravity") of the specified geometry objects.

Parameters

AggregateGeometry

An object of type MDSYS.SDOAGGRTYPE (see Section 1.9.1) that specifies the geometry column and dimensional array.

Usage Notes

The behavior of the function depends on whether the geometry objects are all polygons, all points, or a mixture of polygons and points:

The result is weighted by the area of each polygon in the geometry objects. If the geometry objects are a mixture of polygons and points, the points are not used in the calculation of the centroid. If the geometry objects are all points, the points have equal weight.

See also the information about the SDO_GEOM.SDO_CENTROID function in Chapter 13.

Examples

The following example returns the centroid of the geometry objects in the COLA_MARKETS table. (The example uses the definitions and data from Section 2.1.)

SELECT SDO_AGGR_CENTROID(MDSYS.SDOAGGRTYPE(shape, 0.005))
  FROM cola_markets;

SDO_AGGR_CENTROID(MDSYS.SDOAGGRTYPE(SHAPE,0.005))(SDO_GTYPE, SDO_SRID, SDO_POINT
--------------------------------------------------------------------------------
SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(5.21295938, 5.00744233, NULL), NULL, NUL
L)

SDO_AGGR_CONVEXHULL

Format

SDO_AGGR_CONVEXHULL(

Description

Returns a geometry object that is the convex hull of the specified geometry objects.

Parameters

AggregateGeometry

An object of type MDSYS.SDOAGGRTYPE (see Section 1.9.1) that specifies the geometry column and dimensional array.

Usage Notes

See also the information about the SDO_GEOM.SDO_CONVEXHULL in Chapter 13.

Examples

The following example returns the convex hull of the geometry objects in the COLA_MARKETS table. (The example uses the definitions and data from Section 2.1.)

SELECT SDO_AGGR_CONVEXHULL(MDSYS.SDOAGGRTYPE(shape, 0.005))
  FROM cola_markets;

SDO_AGGR_CONVEXHULL(MDSYS.SDOAGGRTYPE(SHAPE,0.005))(SDO_GTYPE, SDO_SRID, SDO_POI
--------------------------------------------------------------------------------
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(8, 1, 10, 7, 10, 11, 8, 11, 6, 11, 1, 7, 1, 1, 8, 1))

SDO_AGGR_LRS_CONCAT

Format

SDO_AGGR_LRS_CONCAT(

Description

Returns an LRS geometry that concatenates specified LRS geometries.

Parameters

AggregateGeometry

An object of type MDSYS.SDOAGGRTYPE (see Section 1.9.1) that specifies the geometry column and dimensional array.

Usage Notes

This function performs an aggregate concatenation of any number of LRS geometries. If you want to control the order in which the geometries are concatenated, you must use a subquery with the NO_MERGE optimizer hint and the ORDER BY clause. (See the examples.)

The direction of the resulting segment is the same as the direction of the first geometry in the concatenation.

A 3D format of this function (SDO_AGGR_LRS_CONCAT_3D) is available. For information about 3D formats of LRS functions, see Section 6.4.)

For information about the Spatial linear referencing system, see Chapter 6.

Examples

The following example adds an LRS geometry to the LRS_ROUTES table, and then performs two queries that concatenate the LRS geometries in the table. The first query does not control the order of concatenation, and the second query controls the order of concatenation. Notice the difference in direction of the two segments: the segment resulting from the second query has decreasing measure values because the first segment in the concatenation (Route0) has decreasing measure values. (This example uses the definitions from the example in Section 6.6.)

-- Add a segment with route_id less than 1 (here, zero).
INSERT INTO lrs_routes VALUES(
  0,
  'Route0',
  MDSYS.SDO_GEOMETRY(
    3302,  -- line string, 3 dimensions (X,Y,M), 3rd is linear referencing 
dimension
    NULL,
    NULL,
    MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1), -- one line string, straight segments
    MDSYS.SDO_ORDINATE_ARRAY(
      5,14,5,   -- Starting point - 5 is measure from start.
      10,14,0)  -- Ending point - 0 measure (decreasing measure)
  )
);

1 row created.

-- Concatenate all routes (no ordering specified).
SELECT SDO_AGGR_LRS_CONCAT(MDSYS.SDOAGGRTYPE(route_geometry, 0.005))
    FROM lrs_routes;

SDO_AGGR_LRS_CONCAT(MDSYS.SDOAGGRTYPE(ROUTE_GEOMETRY,0.005))(SDO_GTYPE, SDO_SRID
--------------------------------------------------------------------------------
SDO_GEOMETRY(3302, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
2, 2, 0, 2, 4, 2, 8, 4, 8, 12, 4, 12, 12, 10, 18, 8, 10, 22, 5, 14, 27, 10, 14, 
32))

-- Aggregate concatenation using subquery for ordering.
SELECT 
SDO_AGGR_LRS_CONCAT(MDSYS.SDOAGGRTYPE(route_geometry, 0.005)) 
FROM ( 
             SELECT /*+ NO_MERGE */ route_geometry 
             FROM lrs_routes 
             ORDER BY route_id); 

SDO_AGGR_LRS_CONCAT(MDSYS.SDOAGGRTYPE(ROUTE_GEOMETRY,0.005))(SDO_GTYPE, SDO_SRID
--------------------------------------------------------------------------------
SDO_GEOMETRY(3302, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(
2, 2, 32, 2, 4, 30, 8, 4, 24, 12, 4, 20, 12, 10, 14, 8, 10, 10, 5, 14, 5, 10, 14
, 0))

SDO_AGGR_MBR

Format

SDO_AGGR_MBR(

Description

Returns the minimum bounding rectangle (MBR) of the specified geometries, that is, a single rectangle that minimally encloses the geometries.

Parameters

geom

Geometry objects.

Usage Notes

Use this function instead of the deprecated SDO_TUNE.EXTENT_OF function to return the MBR of geometries. The SDO_TUNE.EXTENT_OF function is limited to 2-dimensional geometries, whereas this function is not.

This function is not supported with geodetic data.

Examples

The following example returns the minimum bounding rectangle of the geometry objects in the COLA_MARKETS table. (The example uses the definitions and data from Section 2.1.)

SELECT SDO_AGGR_MBR(shape) FROM cola_markets;

SDO_AGGR_MBR(C.SHAPE)(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SD
--------------------------------------------------------------------------------
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3), SDO_ORDINATE_ARR
AY(1, 1, 10, 11)) 

SDO_AGGR_UNION

Format

SDO_AGGR_UNION(

Description

Returns a geometry object that is the topological union (OR operation) of the specified geometry objects.

Parameters

AggregateGeometry

An object of type MDSYS.SDOAGGRTYPE (see Section 1.9.1) that specifies the geometry column and dimensional array.

Usage Notes

See also the information about the SDO_GEOM.SDO_UNION function in Chapter 13.

Examples

The following example returns the union of the first three geometry objects in the COLA_MARKETS table (that is, all except cola_d). (The example uses the definitions and data from Section 2.1.)

SELECT SDO_AGGR_UNION(
  MDSYS.SDOAGGRTYPE(c.shape, 0.005))
  FROM cola_markets c
  WHERE c.name < 'cola_d';

SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(C.SHAPE,0.005))(SDO_GTYPE, SDO_SRID, SDO_POINT(
--------------------------------------------------------------------------------
SDO_GEOMETRY(2007, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 2, 11, 1003, 1), SDO
_ORDINATE_ARRAY(8, 11, 6, 9, 8, 7, 10, 9, 8, 11, 1, 7, 1, 1, 5, 1, 8, 1, 8, 6, 5
, 7, 1, 7))

Go to previous page Go to next page
Oracle
Copyright © 2001 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index