TABLE OF CONTENTS
- 1. PACKAGE/TOOLS
- 1.1. TOOLS/Generate_Series
- 1.2. TOOLS/ST_DB_Version
- 1.3. TOOLS/ST_GetSridType
- 1.4. TOOLS/ST_isLocator
- 1.5. TOOLS/TokenAggregator
- 1.6. TOOLS/Tokenizer
PACKAGE/TOOLS [ Modules ]
NAME
TOOLS - A collection of common functions used by other packages and object types.
DESCRIPTION
A package that publishes a collection of common functions used by other packages and object types.
AUTHOR
Simon Greener
HISTORY
Simon Greener - Jan 2017 - Original coding.
COPYRIGHT
(c) 2005-2018 by TheSpatialDBAdvisor/Simon Greener
TOOLS/Generate_Series [ Functions ]
[ Top ] [ TOOLS ] [ Functions ]
NAME
Generate_Series -- Function that generates a series of numbers mimicking PostGIS's function with the same name
SYNOPSIS
Function generate_series(p_start pls_integer,
p_end pls_integer,
p_step pls_integer
)
Return &&INSTALL_SCHEMA..T_IntValues Pipelined;
EXAMPLE
with data as (
select sdo_geometry('LINESTRING(0 0,10 0,10 5,10 10,5 10,5 5)',null) as line
from dual
)
select t.IntValue as point_id,
v.x,
v.y
from data a,
table(tools.generate_series(1,sdo_util.GetNumVertices(a.line),1)) t,
table(sdo_util.getvertices(a.line)) v
where v.id =t.intValue;
POINT_ID X Y
---------- ---------- ----------
1 0 0
2 10 0
3 10 5
4 10 10
5 5 10
6 5 5
6 rows selected
INPUTS
p_start (Integer) - Starting value p_end (Integer) - Ending value. p_step (Integer) - The step value of the increment between start and end RETURN Array of Integers (T_IntValues)
AUTHOR
Simon Greener
HISTORY
Simon Greener - Jan 2008 - Original coding.
COPYRIGHT
(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
TOOLS/ST_DB_Version [ Functions ]
[ Top ] [ TOOLS ] [ Functions ]
NAME
ST_DB_Version -- Returns Database Version and sub version.
SYNOPSIS
Static Function ST_DB_Version
Return Number Determinisitc
DESCRIPTION
This function return database version which is useful when handling Oracle functions that only appear in certain versions
RESULT
Database Version (NUMBER) - eg 11.2 Database(12.1)
EXAMPLE
select T_GEOMETRY.ST_DB_Version() as database_version from DUAL; DATABASE_VERSION ---------------- 11.2
AUTHOR
Simon Greener
HISTORY
Simon Greener - May 2016 - Original coding.
COPYRIGHT
(c) 2012-2018 by TheSpatialDBAdvisor/Simon Greener
TOOLS/ST_GetSridType [ Functions ]
[ Top ] [ TOOLS ] [ Functions ]
NAME
ST_GetSridType - Determines ref sys kind of provided p_srid
SYNOPSIS
Function ST_GetSridType(p_srid IN Integer)
Return VarChar2 Deterministic;
DESCRIPTION
Given a null p_srid this function returns PLANAR.
The the p_srid is not null the function checks the Mdsys.Sdo_Coord_Ref_System table's coord_ref_sys_kind column, mapping its values as follows:
COMPOUND ==> PLANAR,
ENGINEERING ==> PLANAR,
GEOGENTRIC ==> GEOGRAPHIC,
GEOGRAPHIC2D ==> GEOGRAPHIC,
GEOGRAPHIC3D ==> GEOGRAPHIC,
PROJECTED ==> PLANAR,
VERTICAL ==> GEOGRAPHIC,
INPUTS
p_srid (integer) - Returns PLANAR or GEOGRAPHIC
NOTE
Used mainly in determinining type of arithmetic to be used when calculating distances etc
EXAMPLE
select distinct f.sridType
from (select Tools.ST_GetSridType(a.srid) as SridType
from cs_srs a
order by DBMS_RANDOM.VALUE
) f
where rownum < 100;
SRIDTYPE
----------
PLANAR
GEOGRAPHIC
AUTHOR
Simon Greener
HISTORY
Simon Greener - Jan 2011 - Original coding.
COPYRIGHT
(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
TOOLS/ST_isLocator [ Methods ]
NAME
ST_isLocator -- Makes best effort to osee if database is a locator databases.
SYNOPSIS
Function ST_isLocator
Return INTEGER Deterministic,
DESCRIPTION
A method that attempts to determine if the host database is a Locator database or has Spatial objects.
RESULT
BOOLEAN (INTEGER) -- 1 Is Locator else 0 (Spatial)
EXAMPLE
select T_GEOMETRY.ST_isLocator() as isLocator from dual; ISLOCATOR --------- 0
AUTHOR
Simon Greener
HISTORY
Simon Greener - Jan 2016 - Original coding.
COPYRIGHT
(c) 2012-2017 by TheSpatialDBAdvisor/Simon Greener
TOOLS/TokenAggregator [ Functions ]
[ Top ] [ TOOLS ] [ Functions ]
NAME
TokenAggregator - A string aggregator.
SYNOPSIS
Function TokenAggregator(p_tokenSet IN &&INSTALL_SCHEMA..T_Tokens,
p_delimiter IN VarChar2 DEFAULT ',')
Return VarChar2 Deterministic;
DESCRIPTION
Takes a set of strings an aggregates/appends them using supplied separator
INPUTS
p_tokenSet (T_Tokens) - The strings to be aggregated.
p_separator (varchar2) - The character that is placed between each token string.
NOTE
Requires t_Tokens Object Type to exist.
EXAMPLE
with data as (
select t.ID, T.TOKEN, T.sEPARATOR
from table(TOOLS.Tokenizer('LINESTRING(0 0,10 0,10 5,10 10,5 10,5 5)',',()')) t
)
select TOOLS.TokenAggregator(tokens,',') AS result
from (select CAST(COLLECT(codesys.T_Token(l.id,l.token,l.separator)) AS T_Tokens) as tokens
from data l
) f;
RESULT
----------------------------------------
LINESTRING(0 0,10 0,10 5,10 10,5 10,5 5)
AUTHOR
Simon Greener
HISTORY
Simon Greener - Jan 2011 - Original coding.
COPYRIGHT
(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener
TOOLS/Tokenizer [ Functions ]
[ Top ] [ TOOLS ] [ Functions ]
NAME
Tokenizer - Splits any string into tokens and separators.
SYNOPSIS
Function Tokenizer (
p_string varchar2,
OR p_string varchar2,
p_separators varchar2 default ' '
)
Return T_Tokens Pipelined
Where T_Tokens is array of T_Token:
id integer,
token varchar2(30000),
separator varchar2(30000)
)
DESCRIPTION
Supplied a string and a list of separators this function returns resultant tokens as a table collection.
Function returns both the token and the separator.
Returned table collection contains a unique identifier to ensure tokens and separators are always correctly ordered.
INPUTS
p_string (varchar 30000) - Any non-null string.
p_separators (varchar 30000) - List of separators eg '(),'
RESULT
Table (Array) of T_TOKEN: id (integer) - Unique identifier for each row starting with first token/separator found. token (varchar 30000) - Token between separators separator (varchar 30000) - Separator between tokens.
EXAMPLE
select t.id, t.token, t.separator
from table(TOOLS.Tokenizer('LINESTRING(0 0,10 0,10 5,10 10,5 10,5 5)',',()')) t
ID TOKEN SEPARATOR
-- ---------- ---------
1 LINESTRING (
2 0 0 ,
3 10 0 ,
4 10 5 ,
5 10 10 ,
6 5 10 ,
7 5 5 )
7 rows selected
AUTHOR
Simon Greener
HISTORY
Simon Greener - Jan 2008 - Original coding.
COPYRIGHT
(c) 2008-2018 by TheSpatialDBAdvisor/Simon Greener