1. Spatial Developer's Guide
  2. Reference Information
  3. SDO_UTIL Package (Utility)
  4. SDO_UTIL.GET_VECTORTILE

35.28 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 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. 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 the preceding table.

If the column does not exist, 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:

  • The first column in the SELECT clause must be a geometry column.
  • The WHERE clause must contain a call to SDO_RELATE with the following parameters - mask=anyinteract, min_resolution=' || res || ', and bypass_point=true. Optionally, other predicates from the SQL query may also be included in the WHERE clause.

    Even if the call to the SDO_RELATE method is not specified in the WHERE clause, the resulting tile will still remain the same. However, the cost to compute the tile is far more expensive as all the geometries get processed instead of just the geometries of interest.

  • Optionally, depending on how much distribution information is required in the vector tile, you can include the ORDER BY and FETCH FIRST ROWS ONLY clauses in 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 paramter 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 minimum valid value for this paramter 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:

  • If an item name is not found to be a column in the table, then it is simply ignored.
  • If multiple columns of the same item name are specified, then only the last item value is used.

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.

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:

  • The operand, first element in the triplet, must be a valid column name in the table (table_name). If the column name is not valid, an exception is raised.
  • The operator is the second element in the triplet, and only the following operators are supported:
    • =, !=, <, >, <=, >=
    • IS [NOT] NULL
    • AND
    • [NOT] LIKE
  • The value, third element in the triplet, must be a string or a number. For example, ‘answer’ is treated as a string. However, both ‘42’ and 42 are treated as numbers.
  • Multiple triplets form multiple predicates joined by the AND operator. For example, consider the following SIMPLE_PREDICATE array:

    SIMPLE_PREDICATE => sdo_string_array('STATE', '=', 'WI', 'COUNTY', 'LIKE', 'B*', 'TOTPOP', '>', '50000')

    This forms the predicate where "STATE"='WI' AND "COUNTY" LIKE 'B*' AND "TOTPOP" > 50000.

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:

  • GOOGLE (default): In this tiling scheme, the origin point (X,Y) of a tile is in the northwest corner. The X ordinates increase as tiles are read from west to east. The Y ordinates increase as tiles are read north to south.
  • TMS: In this tiling scheme, the origin point (X,Y) of a tile is in the southwest corner. The X ordinates still increase as tiles are read west to east, but the Y ordinates increase as tiles are read south to north
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):

  • The first format takes as input the X and Y ordinates (both in NUMBER format) at a specific zoom level and returns a BLOB containing all the features that interact with the tile envelope defined by the given tile address.
  • The second format takes as input the X ordinate (in NUMBER format) and the Y ordinate with a .PBF suffix (in VARCHAR2 format) at a specific zoom level and returns a BLOB vector tile data.
  • The third format applies for generating vector tiles using SQL queries. It take a cursor input to fetch the rows needed for building the vector tiles. The output vector tile data is returned as a BLOB.

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:

  • Ensure that the tile address (tile_x, tile_y, and tile_zoom) and the tiling scheme (google_ts) used to create the tile envelope are the same as those passed to the vector tile function. Otherwise, the function may not return any data as different addresses or schemes may provide different tile envelopes.
  • Similarly, if you include max_features in the SQL query to determine the number of rows to be fetched, then ensure that the maximum features value in the query is the same as that is passed to the vector tile function. Also, note the following:
    • If the max_features value specified in the query is greater than the value specified in the vector tile API, then the query will still try to read larger number of rows but processing of the feature will be limited to the max_features value as passed to the API.
    • If the max_features value specified in the query is lesser than the value specified in the vector tile API, then the query fetches the specified number of rows and exits.
  • The rowid_flg parameter is not included in the SDO_UTIL.GET_VECTORTILE function format that supports cursor input. In this case, you can retrieve the ROWID column attribute by adding , ROWIDTOCHAR(<table_alias>.rowid) "ROWID" in the SELECT clause. This attribute column can be present anywhere in the list of columns to be retrieved. It can also be the only column in the list to be retrieved.
    • The “ROWID” psuedo-column name is only a suggestion. You can choose any name of your choice.
    • The ROWIDTOCHAR function call requires the ROWID to be prefixed with the table alias from where the rowids are generated. Since there are two tables in the query, the data table and the tile envelope table, this alias is needed to clarify from which table the rowids are to be generated.
    • If you wish to limit the number of rows returned from the query, then you must use WHERE NUMROW <= :x where :x is the value of the max_features paramter.

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') = '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

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') = '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);

Parent topic: SDO_UTIL Package (Utility)