SDO_UTIL.GET_VECTORTILE

Format

SDO_UTIL.GET_VECTORTILE(
    TABLE_NAME       IN VARCHAR2,
    GEOM_COL_NAME    IN VARCHAR2,
    TILE_X           IN NUMBER,
    TILE_Y           IN NUMBER,
    TILE_ZOOM        IN NUMBER,
    ATT_COL_NAMES    IN MDSYS.SDO_STRING_ARRAY DEFAULT NULL,
    SIMPLE_PREDICATE IN MDSYS.SDO_STRING_ARRAY DEFAULT NULL,
    LAYER_NAME       IN VARCHAR2 DEFAULT 'LAYER',
    TILE_EXTENT      IN NUMBER DEFAULT 4096,
    GOOGLE_TS        IN BOOLEAN DEFAULT TRUE,
    MAX_FEATURES     IN NUMBER DEFAULT 20000,
    ROWID_FLG        IN BOOLEAN DEFAULT FALSE
    ) RETURN BLOB;

or

SDO_UTIL.GET_VECTORTILE(
    TABLE_NAME       IN VARCHAR2,
    GEOM_COL_NAME    IN VARCHAR2,
    TILE_X           IN NUMBER,
    TILE_Y_PBF       IN VARCHAR2,
    TILE_ZOOM        IN NUMBER,
    ATT_COL_NAMES    IN MDSYS.SDO_STRING_ARRAY DEFAULT NULL,
    SIMPLE_PREDICATE IN MDSYS.SDO_STRING_ARRAY DEFAULT NULL,
    LAYER_NAME       IN VARCHAR2 DEFAULT 'LAYER',
    TILE_EXTENT      IN NUMBER DEFAULT 4096,
    GOOGLE_TS        IN BOOLEAN DEFAULT TRUE,
    MAX_FEATURES     IN NUMBER DEFAULT 20000,
    ROWID_FLG        IN BOOLEAN DEFAULT FALSE
    ) RETURN BLOB;

or

FUNCTION GET_VECTORTILE(
  CUR              IN SYS_REFCURSOR,
  TILE_X           IN NUMBER,
  TILE_Y           IN NUMBER,
  TILE_ZOOM        IN NUMBER,
  LAYER_NAME       IN VARCHAR2 DEFAULT 'LAYER',
  TILE_EXTENT      IN NUMBER DEFAULT 4096,
  GOOGLE_TS        IN BOOLEAN DEFAULT TRUE,
  MAX_FEATURES     IN NUMBER DEFAULT 20000
) RETURN BLOB;

Description

Generates a BLOB that represents a Mapbox Vector Tile (MVT).

Parameters

table_name

Name of a valid table or view containing the named geometry column and all of the columns named in the attribute column list.

Ensure that you have READ access to the table (or view). If you do not have the necessary privilege, or if the table does not exist, then an exception is raised.

geom_col_name

Name of the SDO_GEOMETRY type column in table_name or a function-based spatial index.

If the column does not exist in table_name, or if it is not of SDO_GEOMETRY type, then an exception is raised.

cur

CURSOR for the SQL query used in the APEX application.

The format is CURSOR(<user_SQL_query>).

Also, note the following for the SQL query:

If the CURSOR is NULL or the query provided is invalid, then an exception is raised.

tile_x

The X ordinate of the origin point of the tile being fetched.

The minimum valid value for this parameter is zero. The maximum valid value is dependent on the zoom specified and can be computed as max(X) = (2^tile_zoom) - 1. Note that specifying a X ordinate outside the valid range raises an exception.

tile_y

The Y ordinate (in NUMBER format) of the origin point of the tile being fetched.

The minimum valid value for this parameter is zero. The maximum valid value is dependent on the zoom specified and can be computed as max(Y) = (2^tile_zoom) - 1. Note that specifying a Y ordinate outside the valid range raises an exception.

tile_y_pbf

The Y ordinate (in VARCHAR2 format) of the origin point of the tile being fetched.

The tile_y_pbf value is a VARCHAR2 string that specifies a number followed by any file extension. The Y ordinate value is extracted after stripping the file extension in the string. For example, consider the string value '23.pbf' or '23.XYZ'. Then 23 will be extracted as the Y ordinate value and everything after the ‘.’ will be stripped and ignored.

The minimum valid value for this parameter is zero. The maximum valid value is dependent on the zoom specified and can be computed as max(Y) = (2^tile_zoom) - 1. Note that specifying a Y ordinate outside the valid range raises an exception.

tile_zoom

Determines the number of tiles required to divide a map. These segments can be joined at a higher resolution without having to read in the entire map.

For instance, a tile_zoom of zero is a single tile of the entire map without a lot of details. A tile_zoom of n breaks the map into 2^n x 2^n tiles (that is, the number of tiles along the X axis times the number of tiles along the Y axis). For example, at a tile_zoom of 8 there would be 65,536 tiles. A higher value of n implies more details in a tile. Note that the parameter values outside the valid range raise an exception.

att_col_names

Attribute column names that contain non-spatial data for a feature.

This is an optional parameter. For example, when querying county polygons, you may also be interested in the county name, state where it resides, and the total population of that county. These columns are specified as an SDO_STRING_ARRAY as in ATT_COL_NAMES => sdo_string_array('COUNTY','STATE','TOTPOP').

Each item in the array must be a unique and a valid column name in the table (table_name). However, violating these restrictions is not fatal because:

Also, note that the item values listed in the array follow standard Oracle casing rules where 'COUNTY', 'County', and 'county' are considered duplicates, and these all resolve to 'COUNTY'. However, '"COUNTY"', '"County"', and '"county"' are not considered duplicates, and therefore all these three can be columns names in a table without conflict.

The following table lists the data types, both ANSI and Oracle built-ins, that are supported for the columns specified in the att_col_names list.

Table: Supported Data Types for Columns in att_col_names List

Category Data Type
Character Data Types
  • CHAR
  • CHARACTER
  • CHAR VARYING
  • CHARACTER VARYING
  • NATIONAL CHAR
  • NATIONAL CHARACTER
  • NATIONAL CHAR VARYING
  • NATIONAL CHARACTER VARYING
  • NCHAR
  • NCHAR VARYING
  • NVARCHAR2
  • VARCHAR
  • VARCHAR2
Numeric Data Types
  • DEC
  • DECIMAL
  • DOUBLE PRECISION
  • FLOAT
  • INT
  • INTEGER
  • NUMBER
  • NUMERIC
  • REAL
  • SMALLINT
Date and Time
  • DATE
  • TIMESTAMP

See Also: Data Types in Oracle AI Database SQL Language Reference for more information on Oracle SQL data types.

Supported Data Types in att_col_names with Limitations

Columns of type BLOB, CLOB, and RAW can be included in the att_col_names list, subject to the following limitations:

Unsupported Data Types in att_col_names

The following lists the unsupported data types for the columns specified in the att_col_names list.

Note:

simple_predicate

A formatted SDO_STRING array of triplets (operand, operator, value) which form a predicate.

Note the following regarding the elements that form the triplet:

layer_name

Name of the layer in the vector tile.

This is an optional parameter. Only a single layer having a default 'LAYER' value is supported in the vector tile.

tile_extent

Integer coordinates that describe the width and height of the tile.

This is an optional parameter. The default value of 4096 indicates that the vector tile is 4096 units high and 4096 units wide. The actual size of these units varies based on the tiles zoom level. The distance between two coordinates within a tile will be much greater at zoom level zero than it would be at zoom level 12. It is recommended that you change the default parameter value only if using a non-standard tiling scheme.

google_ts

Boolean value that indicates if the Google tiling scheme is used.

This is an optional parameter. The default value is TRUE which indicates a GOOGLE tiling scheme.

The following two tiling schemes are supported when reading tile addresses:

max_features

Maximum number of features included in a layer.

This is an optional parameter with a default value 20000.

A vector tile is made up of layers. A layer is made up of features. A feature is a geometry that interacts with the tile envelope and any of its attribute columns. For large tile envelopes covering an entire state or an entire country, the number of buildings and their attributes can be very large. Processing overly large numbers of features can be resource intensive both on the CPU and memory.

Therefore, using this parameter allows you to control the maximum number of features to be included in a layer.

rowid_flg

Boolean value that indicates if ROWIDs should be retrieved as an attribute column.

This is an optional parameter and the default value is FALSE.

If FALSE, then the ROWID is not included as a column attribute in the tiles features.

If TRUE, then the ROWID is included as a column attribute in the tiles features.

See the Usage Notes for more information.

Usage Notes

The SDO_UTIL.GET_VECTORTILE function supports three formats (as described at the beginning of the section):

Note that each feature in the BLOB comprises a geometry that interacts with the tile envelope and any requested attribute columns associated with the geometry.

A tile is a polygon (a square) that describes a piece of a map. A tile envelope is also a square that covers the tile with an extra 2.5% added to each end of both the X and Y axis. From the user perspective, both tile and tile envelope are the same. The tile envelope is used as the filter in a call to SDO_RELATE with MASK=ANYINTERACT set.

Note that in the SDO_UTIL.GET_VECTORTILE function, the X, Y origin, zoom level, and tile scheme are passed instead of the tile envelope. The function internally calls SDO_UTIL.GET_TILE_ENVELOPE which forms the tile envelope.

If you are calling the SDO_UTIL.GET_VECTORTILE function inside a SQL query, then you must consider the following:

Examples

The following example obtains the vector tile data using both X and Y ordinates in NUMBER format at a specific zoom level. The example also maintains a size limit of 10000 records for each vector tile.

SQL> SELECT SDO_UTIL.GET_VECTORTILE(
       TABLE_NAME=>'states',
       GEOM_COL_NAME=>'geom',
       ATT_COL_NAMES=>sdo_string_array('totpop', 'name', 'state_abbrv'),
       SIMPLE_PREDICATE=>sdo_string_array('totpop','>', '50000'),
       TILE_X=>1192,
       TILE_Y=>1579,
       TILE_ZOOM=>12,
       MAX_FEATURES=>10000);

SDO_UTIL.GET_VECTORTILE(TABLE_NAME=>'US_STATE',GEOM_COL_NAME=>'GEOM',ATT_COL_NAMES=>SDO_STRING_ARRAY('TOTPOP','NAME','STATE_ABBRV'),SIMPLE_PREDICATE=>SDO_STRING
----------------------------------------------------------------------------------------------------------------------------------------------------------------
1A150A0E084E59414E205350415449414C0828802078021ABE010A054C41594552122C080012060000010102021803221E09CB01AE233AA80919AE2845B2010CFC06FD0B94098912009F069743000F12

The following example describes the steps to obtain the vector tile data using a SQL query.

SELECT SDO_UTIL.GET_VECTORTILE(
       CURSOR(
         WITH tet(tec) AS
             (SELECT SDO_UTIL.GET_TILE_ENVELOPE(tile_zoom=>12, tile_x=>1192, tile_y=>1579))
         SELECT geom, totpop, state, state_abrv
             FROM states, tet
             WHERE totpop > 200 and ROWNUM <= 10000 and
              SDO_RELATE(geom, tet.tec,
                 'mask=anyinteract min_resolution=9.7839 bypass_point=true')),
         tile_zoom=>12, tile_x=>1192, tile_y=>1579) FROM DUAL;

SDO_UTIL.GET_VECTORTILE(CURSOR(WITHTET(TEC)AS(SELECTSDO_UTIL.GET_TILE_ENVELOPE(T
--------------------------------------------------------------------------------
1A150A0E084E59414E205350415449414C0828802078021A620A054C41594552121C080012060000
010102021803220E09CB01B81B128E10851D8D10000F1A06544F54504F501A0553544154451A0A53
544154455F4142525622091900000000673D5241220A0A084D6172796C616E6422040A024D442880
207802

In the preceding example, the min_resolution value is computed as shown:

SQL> SELECT ((40075016.0/POWER(2, 12)) * .001) FROM DUAL;

((40075016.0/POWER(2,12))*.001)
-------------------------------
                     9.78393945

The following example returns the ROWIDs as a column attribute in the tiles features. The vector tiles are generated using a SQL query (having a cursor input) as shown.

SELECT SDO_UTIL.GET_VECTORTILE(
       cursor(
         WITH tet(tec) AS
             (SELECT SDO_UTIL.GET_TILE_ENVELOPE(tile_zoom=>12, tile_x=>1192, tile_y=>1579))
         SELECT geom, totpop, state, state_abrv, ROWIDTOCHAR(vtt.rowid) "ROWID"
             FROM states, tet
             WHERE totpop > 200 and ROWNUM <= 10000 and
                   SDO_RELATE(geom, tet.tec,
                     'mask=anyinteract min_resolution=9.7839 bypass_point=true')),
       tile_zoom=>12, tile_x=>1192, tile_y=>1579) FROM DUAL;

In the following example the ROWID_FLG parameter is set to TRUE and therefore the procedure returns the ROWIDs as a column attribute in the tiles features.

SELECT SDO_UTIL.GET_VECTORTILE(
       TABLE_NAME=>'states',
       GEOM_COL_NAME=>'geom',
       ATT_COL_NAMES=>sdo_string_array('totpop', 'state', 'state_abbrv'),
       SIMPLE_PREDICATE=>sdo_string_array('totpop','>', '200'),
       TILE_X=>1192,
       TILE_Y=>1579,
       TILE_ZOOM=>12,
       ROWID_FLG=>TRUE,
       MAX_FEATURES=>10000);