1 PACKAGE sdo_sam AUTHID current_user AS
2
3 ----------------------------------------------------------------------
4 -- Name
5 -- Predicated_Join
6 --
7 -- Purpose
8 -- This routine allows partial predicate-based join of tables.
9 -- Useful in finding co-located items of different categories
10 -- from the same or different tables.
11 --
12 -- Syntax
13 -- Predicated_Join(table1, col1, table2, col2, pred1, pred2, dst_spec)
14 -- table1, col1, pred1: refer to first table, location column and
15 -- associated predicate to filter rows from table1
16 -- table2, col2, pred2: refer to second table, location column and
17 -- associated predicate to filter rows from table2
18 -- dst_spec: is of the form "distance=1 units=mile" (just as in
19 -- regular spatial query windows.
20 --
21 -- Usage
22 -- select a.state_abrv from states a,
23 -- table(sdo_sam.Predicated_Join('STATES', 'GEOM',
24 -- 'STATES', 'GEOM', 'state_abrv=''NH''', null, null)) b
25 -- where a.rowid = b.rid2;
26
27
28 FUNCTION Predicated_Join(table_name1 varchar2, column_name1 varchar2,
29 table_name2 varchar2, column_name2 varchar2,
30 tab1_predicate varchar2, tab2_predicate varchar2,
31 dst_spec in varchar2)
32 RETURN MDSYS.SDO_ROWIDSET
33 PIPELINED;
34
35 ------------------------------------------------------------------------------
36 -- Name
37 -- Simplify_Geometry
38 --
39 -- Purpose
40 -- This procedure simplifies a geometry without actually having to specify
41 -- thresholds. This routine iteratively invokes sdo_util.simplify
42 -- starting with a threshold of tolerance and increasing it by a
43 -- factor of threshold_incrmnts in each iteration. The recursive
44 -- invocation stops when the area of the resulting geometry
45 -- decreases by at most the specified "pct_area_chng_limit", i.e., when
46 -- (area(orig_geom)-area(result_geom))/area(orig_geom) < pct_area_chng_limit/100.
47 --
48 -- Syntax
49 -- Simplify_Geometry (geom, dim, pct_area_chang_limit)
50 --
51 -- Keywords and Paramters
52 -- geom geometry to be simplified. Datatype is MDSYS.sdo_geometry.
53 -- dim diminfo containing tolerance info.
54 -- Datatype is mdsys.sdo_dim_array.
55 -- pct_area_change_limit: Stops simplifying when area of resulting
56 -- geometry changes by at most this percentage.
57 --
58 -- Usage
59 -- select sdo_sam.simplify_geometry(a.geometry, b.diminfo)
60 -- from poly_4pt_temp a, user_sdo_geom_metadata b
61 -- where b.table_name='POLY_4PT_TEMP' ;
62
63
64
65 FUNCTION simplify_geometry(geom mdsys.sdo_geometry,
66 dim mdsys.sdo_dim_array,
67 pct_area_change_limit number default 2)
68 RETURN mdsys.sdo_geometry DETERMINISTIC PARALLEL_ENABLE;
69
70 -- Tolerance as a parameter
71 FUNCTION simplify_geometry(geom mdsys.sdo_geometry,
72 tol number,
73 pct_area_change_limit number default 2)
74 RETURN mdsys.sdo_geometry DETERMINISTIC PARALLEL_ENABLE;
75
76
77 -- Name
78 -- Simplify_Layer
79 --
80 -- Purpose
81 -- The "simplify_geometry" is applied to all geometries in a layer.
82 -- The simplified geometry is re-inserted in the table in column
83 -- "smpl_geom_colname". The commit_interval specifies the interval
84 -- at which each batch of simplified-geom inserts are committed.
85 --
86 -- Syntax
87 -- Simplify_Layer (theme_tablename, theme_colname,
88 -- smpl_geom_colname, commit_interval, pct_area_change_limit)
89 --
90 -- Keywords and Paramters
91 -- theme_tablename Name of table. Datatype varchar2
92 -- theme_colname Geometry column in table. Datatype varchar2
93 -- smpl_geom_colname Name of column to store simplified_geometry.
94 -- Datatype varchar2.
95 --
96 -- Rest of the parameters below are for controlling the # of iterations
97 --
98 -- Usage
99 -- execute sdo_sam.simplify_layer('POLY_4PT_TEMP', 'GEOMETRY', 'SMPL_GEOM');
100
101
102 PROCEDURE simplify_layer(theme_tablename varchar2,
103 theme_colname varchar2,
104 smpl_geom_colname varchar2,
105 commit_interval number default 10,
106 pct_area_change_limit number default 2);
107
108
109
110 ------------------------------------------------------------------------------
111 -- Name
112 -- Intersection_Ratio
113 --
114 -- Purpose
115 -- This routine first computes the intersection of location,thematic_boundary
116 -- geometries. The ratio of the area of this intersection to the
117 -- area of the "thematic_boundary" is computed and returned.
118 --
119 -- If dist and units are specified, then the location geometry
120 -- is "buffered" using the specified distance and the intersection_ratio
121 -- of the buffered_location to thematic_boundary is returned.
122 --
123 -- Syntax
124 -- Intersection_Ratio(location, thematic_boundary, dim,
125 -- dist, units);
126 -- Usage
127 -- select sdo_sam.intersection_ratio(a.geometry, b.geometry,
128 -- c.diminfo) from poly_4pt_temp a, tst b, user_sdo_geom_metadata c
129 -- where c.table_name = 'POLY_4PT_TEMP';
130
131 FUNCTION Intersection_Ratio(location mdsys.sdo_geometry,
132 thematic_boundary mdsys.sdo_geometry,
133 dim mdsys.sdo_dim_array,
134 dist number default null,
135 units varchar2 default null)
136 RETURN number PARALLEL_ENABLE;
137
138
139 ------------------------------------------------------------------------------
140 -- Name
141 -- Tiled_Bins
142 --
143 -- Purpose
144 -- This routine tiles a 2-d space and returns geometries corresponding
145 -- to those tiles. The returned sdo_region object has (the tile_id,
146 -- tile_code (quadtree-code), tile_geometry) corresponding to each tile.
147 -- If srid is non-null, it is set in the tile_geometry.
148 -- If xdivs, ydivs (# of divisions in each dimension) are specified,
149 -- then tiling_level is ignored.
150 -- Syntax
151 -- Tiled_Bins(l1, u1, l2, u2, srid)
152 --
153 -- Usage
154 -- select * from TABLE(sdo_sam.tiled_bins(-180, 180, -90, 90, 1, 0));
155
156
157
158 FUNCTION Tiled_Bins(l1 number, u1 number, l2 number, u2 number,
159 tiling_level number default null,
160 srid number default null,
161 xdivs number default null, ydivs number default null)
162 RETURN MDSYS.SDO_REGIONSET
163 PIPELINED DETERMINISTIC PARALLEL_ENABLE;
164
165
166 ------------------------------------------------------------------------------
167 -- Name
168 -- Bin_Geometry
169 --
170 -- Purpose
171 -- This routine bins the geometry to the most-intersecting bin
172 -- from a bin_tablename.
173 --
174 -- Syntax
175 -- Bin_Geometry(geom, dim, bin_tablename, bin_colname)
176 --
177 -- Usage
178 -- select sdo_sam.bin_geometry(a.geometry, b.diminfo, 'BINTBL', 'GEOMETRY')
179 -- from poly_4pt a, user_sdo_geom_metadata b
180 -- where b.table_name='POLY_4PT' and a.gid=1;
181
182 FUNCTION Bin_Geometry(geom mdsys.sdo_geometry, dim mdsys.sdo_dim_array,
183 bin_tablename varchar2, bin_colname in varchar2)
184 RETURN NUMBER PARALLEL_ENABLE;
185
186
187 -- Signature using tolerance instead of dim
188 FUNCTION Bin_Geometry(geom mdsys.sdo_geometry, tol number,
189 bin_tablename varchar2, bin_colname in varchar2)
190 RETURN NUMBER PARALLEL_ENABLE;
191
192 -- Name
193 -- Bin_Layer
194 --
195 -- Purpose
196 -- This routine computes the most-intersecting bin for each geomtry row
197 -- in a specified layer using the bins in "bin_tablename".
198 -- The bin_id for each geometry is added in the "bin_id_colname".
199 -- Commit_intrvl specifies the batch-size for the inserts.
200 --
201 -- Syntax
202 -- Bin_Layer(tablename, colname, bin_tablename, bin_colname,
203 -- bin_id_colname, commit_intrvl)
204 --
205 -- Usage
206 -- execute sdo_sam.bin_layer('POLY_4PT_TEMP', 'GEOMETRY', 'BINTBL',
207 -- 'GEOMETRY', ' BIN_ID', 1);
208
209
210 PROCEDURE Bin_Layer(tablename varchar2, colname varchar2,
211 bin_tablename varchar2, bin_colname varchar2,
212 bin_id_colname varchar2,
213 commit_interval number default 20);
214
215
216
217 ------------------------------------------------------------------------------
218 -- Name
219 -- Spatial_Clusters
220 --
221 -- Purpose
222 -- This routine computes clusters using existing R-tree index.
223 -- The parameter max_clusters specifies the number of clusters to
224 -- obtain. Returns sdo_region objects where the geometry field
225 -- specifies the cluster boundary of each cluster. (The geometry_key
226 -- is set to null).
227 --
228 -- Syntax
229 -- Spatial_Clusters(tablename, colname, max_clusters,
230 -- allow_outliers, tablepartition)
231 --
232 -- Usage
233 -- select count(*) from
234 -- TABLE(sdo_sam.Spatial_Clusters('POLY_4PT_TEMP', 'GEOMETRY', 3));
235
236 FUNCTION Spatial_Clusters(tablename varchar2, colname varchar2,
237 max_clusters number,
238 allow_outliers varchar2 default null,
239 tablepartition varchar2 default null)
240 RETURN MDSYS.SDO_REGIONSET
241 PIPELINED;
242
243 ------------------------------------------------------------------------------
244 -- Name
245 -- Aggregates For Layer
246 --
247 -- Purpose
248 -- Compute thematic aggregates for a layer of rows.
249 -- For each geometry in tablename, identify the geometries in
250 -- theme_name table, find their intersection_ratio, and multiply
251 -- the specified aggregate using this intersection_ratio and aggregate
252 -- it for each geometry in tablename.
253 --
254 -- Returns a table of geometry(from tablename table), aggregate_value using
255 -- the sdo_regaggr object.
256 --
257 -- Syntax
258 -- Aggregate_For_Layer(theme_name, theme_colname, tablename, colname,
259 -- aggr_type_string, aggr_col_string, dst_spec)
260 --
261 -- Usage
262 -- select * from TABLE(sdo_sam.Aggregate_For_Layer('POLY_4PT_TEMP',
263 -- 'GEOMETRY', 'SUM', '1', 'TST', 'GEOMETRY'));
264 -- select * from TABLE(sdo_sam.Aggregate_For_Layer('POLY_4PT_TEMP',
265 -- 'GEOMETRY', 'SUM', '1','TST', 'GEOMETRY','distance=2'));
266 -- select * from TABLE(sdo_sam.Aggregate_For_Layer('POLY_4PT_TEMP',
267 -- 'GEOMETRY', 'SUM', '1', 'TST', 'GEOMETRY',
268 -- 'distance=2 UNIT=MILE'));
269
270 FUNCTION Aggregates_For_Layer(theme_name varchar2,
271 theme_colname varchar2,
272 aggr_type_string varchar2,
273 aggr_col_string varchar2,
274 tablename varchar2, colname varchar2,
275 dst_spec varchar2 default null,
276 dst_offset number default null, zero_agg_rows number default 0 )
277 RETURN MDSYS.SDO_REGAGGRSET
278 PIPELINED;
279
280 FUNCTION Aggregates_For_Geometry(theme_name varchar2,
281 theme_colname varchar2,
282 aggr_type_string varchar2,
283 aggr_col_string varchar2,
284 geom mdsys.sdo_geometry,
285 dst_spec varchar2 default null,
286 dst_offset number default null)
287 RETURN number ;
288
289
290 ------------------------------------------------------------------------------
291 -- Name
292 -- Tiled Aggregates
293 --
294 -- Purpose
295 -- Similar to thematic aggregates. Instead of explicit tablename
296 -- the table is dynamically generated using tiling information.
297 --
298 -- Given a theme_name table, the tiling domain is determined.
299 -- Based on the tiling_level, a number of tiles are generated.
300 -- For each tile geometry, thematic aggregates are computed
301 -- as described above.
302 -- Parameter zero_agg_tiles, if 0, skips (does not return)
303 -- tiles that have aggregate value of 0. Default is 0.
304 -- Parameter x_divs, y_divs specify the # of divisions if tiling_level
305 -- is not specified. If both are specified, then tiling_level is ignored.
306 --
307 --
308 --
309 -- Returns a table of tile_geometry, aggregate_value using
310 -- the sdo_regaggr object.
311 --
312 -- Syntax
313 -- Tiled_Aggregates(theme_name, theme_colname,
314 -- aggr_type_string, aggr_col_string,
315 -- tiling_level, dst_spec, tiling_domain, zero_agg_tiles)
316 --
317 -- Usage
318 -- select * from TABLE(sdo_sam.Tiled_Aggregates('POLY_4PT_TEMP',
319 -- 'GEOMETRY', 2, 'SUM', '1'));
320
321 FUNCTION Tiled_Aggregates(theme_name varchar2,
322 theme_colname varchar2,
323 aggr_type_string varchar2,
324 aggr_col_string varchar2,
325 tiling_level number default null,
326 tiling_domain mdsys.sdo_dim_array default null,
327 zero_agg_tiles number default 0,
328 xdivs number default null, ydivs number default null)
329 RETURN MDSYS.SDO_REGAGGRSET
330 PIPELINED;
331
332 ------------------------------------------------------------------------------
333 -- Name
334 -- Colocated Reference Features
335 --
336 -- Purpose
337 -- Same as Predicated_Join. Materialize the join results into
338 -- a table "result_tablename". The result_tablename should have
339 -- the columns (tid number, rid1 number, rid2 number).
340 --
341 -- This routine materializes each pair of rowids returned from
342 -- Predicated_Join and stores them in the rid1, rid2 columns of the
343 -- result_tablename. The tid is a unique generated "interaction"
344 -- number corresponding to each "rid1".
345 --
346 PROCEDURE Colocated_Reference_Features(theme_tablename varchar2,
350 colname varchar2,
347 theme_colname varchar2,
348 theme_predicate varchar2,
349 tablename varchar2,
351 ref_predicate varchar2,
352 dst_spec varchar2,
353 result_tablename varchar2,
354 commit_interval number default 100);
355
356
357 END sdo_sam;
[Home] [Help]