GanosBase functions-AnalyticDB(AnalyticDB)-阿里云帮助中心

GanosBase is the spatio-temporal engine built into AnalyticDB for PostgreSQL. It provides data types, functions, and stored procedures for storing, indexing, querying, analyzing, and computing spatial and spatio-temporal data against Object Storage Service (OSS).

For an overview of GanosBase, see Overview.

Raster SQL references

Raster creation and import/export

Function	Description
ST_CreateRast	Creates a raster object backed by OSS.
ST_ImportFrom	Imports an OSS object into the database as a raster object.
ST_ExportTo	Exports a raster object to OSS.

Pyramid operations

Function	Description
ST_BuildPyramid	Builds a pyramid for a raster object.
ST_DeletePyramid	Deletes the pyramid of a raster object.
ST_BestPyramidLevel	Returns the optimal pyramid level for a viewport defined by world space, width, and height.

Coordinate system conversion

Function	Description
ST_Rast2WorldCoord	Converts pixel coordinates and a pyramid level to world coordinates using an affine transformation.
ST_World2RastCoord	Converts world coordinates and a pyramid level to pixel coordinates using the inverse affine transformation.

Pixel value operations

Function	Description
ST_ClipDimension	Returns the raster coordinates of a clipped area.
ST_Clip	Clips a raster object by a geometry or pixel range.
ST_ClipToRast	Clips a raster object by a geometry and returns the result as a new raster object.
ST_AddZ	Assigns Z coordinates to a geometry from the corresponding raster band values.
ST_MosaicFrom	Combines multiple raster objects into a single raster object by mosaic.

DEM operations

Function	Description
ST_Slope	Returns the slope of an elevation raster band in degrees.

Raster accessors

Function	Description
ST_Name	Returns the name of a raster object, or null if no name is set.
ST_RasterID	Returns the UUID of a raster object.
ST_Width	Returns the width of a raster object in pixels. For chunk width, see ST_ChunkWidth.
ST_Height	Returns the height of a raster object in pixels.
ST_NumBands	Returns the number of bands in a raster object.
ST_CellDepth	Returns the pixel depth of a raster object. Valid values: 0 (unknown), 1, 2, 4, 8, 16, 32, or 64.
ST_CellType	Returns the pixel type of a raster object. Valid values: 8BSI, 8BUI, 16BSI, 16BUI, 32BSI, 32BUI, 32BF, or 64BF.
ST_InterleavingType	Returns the interleaving type of a raster object. Valid values: BSQ, BIL, or BIP.
ST_TopPyramidLevel	Returns the highest pyramid level of a raster object.
ST_Extent	Returns the coordinate range of a raster object in `'((maxX,maxY),(minX,minY))'` format.
ST_ConvexHull	Returns the convex hull geometry of a raster object.
ST_Envelope	Returns the bounding box of a raster object.
ST_Srid	Returns the spatial reference system identifier (SRID) of a raster object. SRIDs are stored in the `spatial_ref_sys` table.
ST_ScaleX	Returns the pixel width on the X axis in the spatial reference system.
ST_ScaleY	Returns the pixel width on the Y axis in the spatial reference system.
ST_SkewX	Returns the skew on the X axis in the spatial reference system.
ST_SkewY	Returns the skew on the Y axis in the spatial reference system.
ST_UpperLeftX	Returns the upper-left X coordinate in the spatial reference system.
ST_UpperLeftY	Returns the upper-left Y coordinate in the spatial reference system.
ST_PixelWidth	Returns the pixel width in the spatial reference system.
ST_PixelHeight	Returns the pixel height in the spatial reference system.
ST_Georeference	Returns the georeference information as affine parameters in `"A,B,C,D,E,F"` text format.
ST_IsGeoreferenced	Tests whether a raster object is georeferenced. Returns `t` or `f`.
ST_XMin	Returns the minimum X coordinate of a raster object.
ST_YMin	Returns the minimum Y coordinate of a raster object.
ST_XMax	Returns the maximum X coordinate of a raster object.
ST_YMax	Returns the maximum Y coordinate of a raster object.
ST_ChunkHeight	Returns the chunk height of a raster object.
ST_ChunkWidth	Returns the chunk width of a raster object.
ST_ChunkBands	Returns the number of bands per chunk of a raster object.
ST_MetaData	Returns the metadata of a raster object as JSON.
ST_MetaItems	Returns the custom metadata items of a raster object.
ST_BeginDateTime	Returns the start time of a raster object.
ST_EndDateTime	Returns the end time of a raster object.
ST_DateTime	Returns the time information of a raster object or band.

Band accessors

Function	Description
ST_Value	Returns the pixel value of a cell at the specified band, row, and column.
ST_NoData	Returns the predefined NoData value for a band.
ST_ColorTable	Returns the color table of a band as JSON.
ST_Statistics	Returns the statistics for a band as JSON.
ST_SummaryStats	Returns statistics computed across a specific band set.
ST_ColorInterp	Returns the color interpretation type of a band.
ST_Histogram	Returns the histogram of a band in text format, or null if no histogram exists.
ST_BuildHistogram	Computes and returns the histogram for a specific band set.
ST_StatsQuantile	Returns the quantiles of a raster object from stored statistics.
ST_Quantile	Returns the pixel values at specified quantiles for a raster object.
ST_MD5Sum	Returns the MD5 hash string of a raster object.

Raster setters

Function	Description
ST_SetName	Sets the name of a raster object.
ST_SetSrid	Sets the SRID of a raster object. SRIDs are stored in the `spatial_ref_sys` table.
ST_SetScale	Sets the pixel widths on the X and Y axes in the spatial reference system.
ST_SetSkew	Sets the skews on the X and Y axes in the spatial reference system.
ST_SetUpperLeft	Sets the upper-left X and Y coordinates in the spatial reference system.
ST_SetGeoreference	Sets the georeference information for a raster object.
ST_UnGeoreference	Removes the georeference information from a raster object.
ST_SetNoData	Sets the NoData value for a specific band.
ST_SetColorTable	Sets the color table in JSON format for a specific band.
ST_SetColorInterp	Sets the color interpretation type for a specific band.
ST_SetStatistics	Sets the statistics for a specific band.
ST_SetHistogram	Sets the histogram in JSON format for a specific band.
ST_SetMD5Sum	Sets the MD5 hash string for a raster object.
ST_SetMetaData	Sets a metadata item for a raster object or band.
ST_SetBeginDateTime	Sets the start time of a raster object.
ST_SetEndDateTime	Sets the end time of a raster object.
ST_SetDateTime	Sets the start and end time of a raster object, or the time of a specific band.

Operators

Operator	Description
Equal to (=)	Tests whether the UUID of the first raster object equals that of the second.
Greater than (>)	Tests whether the UUID of the first raster object is greater than that of the second.
Less than (<)	Tests whether the UUID of the first raster object is less than that of the second.
Greater than or equal to (>=)	Tests whether the UUID of the first raster object is greater than or equal to that of the second.
Less than or equal to (<=)	Tests whether the UUID of the first raster object is less than or equal to that of the second.

Spatial relationship functions

Function	Description
ST_Intersects	Tests whether two raster objects, or a raster object and a geometry, intersect.
ST_Contains	Tests whether the first object completely contains the second.
ST_ContainsProperly	Tests whether the first object properly contains the second (no boundary overlap).
ST_Covers	Tests whether the first object covers the second.
ST_CoveredBy	Tests whether the first object is covered by the second.
ST_Disjoint	Tests whether two objects share no points in common.
ST_overlaps	Tests whether two objects overlap (each has at least one point not in the other).
ST_Touches	Tests whether two objects share at least one boundary point but their interiors do not intersect.
ST_Within	Tests whether the first object is completely enclosed within the second.

Auxiliary functions

Function	Description
ST_CheckGPU	Tests whether a GPU is available in the current environment.
ST_AKId	Returns the AccessKey ID used to access a raster object in OSS. Supports batch modification.
ST_SetAccessKey	Sets the AccessKey pair (ID and secret) used to access a raster object in OSS.
ST_SetAKId	Sets the AccessKey ID used to access a raster object in OSS.
ST_SetAKSecret	Sets the AccessKey secret used to access a raster object in OSS.

Configuration variables

The following session-level variables control raster engine behavior.

Variable	Description
ganos.raster.calculate_md5	Controls whether the MD5 hash is calculated and saved to metadata when a raster object is imported.
ganos.raster.md5sum_chunk_size	Sets the per-read cache size (in MB) used during MD5 calculation.
ganos.raster.mosaic_must_same_nodata	Controls whether all source rasters must share the same NoData value during mosaic. The values of NoData are not changed during the mosaic operation. Setting this to `false` may change pixel semantics after the mosaic.

SpatialRef SQL references

Function	Description
ST_SrEqual	Tests whether two spatial reference systems are identical.
ST_SrReg	Registers a spatial reference system.
ST_SrFromEsriWkt	Converts a string from Esri well-known text (WKT) format to Open Geospatial Consortium (OGC) WKT format.

Trajectory SQL references

Geometry constructors

Function	Description
ST_makeTrajectory	Constructs a trajectory object.
ST_append	Appends trajectory points or a sub-trajectory to a trajectory object.

Editing and processing

Function	Description
ST_Compress	Compresses a trajectory by removing points within the Euclidean distance offset threshold.
ST_CompressSED	Compresses a trajectory using the synchronous Euclidean distance (SED) offset threshold.
ST_attrDeduplicate	Removes trajectory points with duplicate attribute field values, retaining the start and end points.
ST_sort	Sorts trajectory points by timestamp in ascending order.
ST_deviation	Returns the deviation of a processed trajectory from the original.

Attribute metadata

Function	Description
ST_attrDefinition	Returns the definitions of all attribute fields in a trajectory object.
ST_attrSize	Returns the number of attribute fields in a trajectory object.
ST_attrName	Returns the name of a specific attribute field, or all field names.
ST_attrType	Returns the data type of an attribute field.
ST_attrLength	Returns the defined length of an attribute field.
ST_attrNullable	Tests whether an attribute field allows null values.

Event functions

Function	Description
ST_addEvent	Adds an event to a trajectory object.
ST_eventTimes	Returns the timestamps of all events in a trajectory object.
ST_eventTime	Returns the timestamp of an event at a specific index.
ST_eventTypes	Returns the type IDs of all events in a trajectory object.
ST_eventType	Returns the type ID of an event at a specific index.

Trajectory attribute functions

Function	Description
ST_startTime	Returns the start time of a trajectory object.
ST_endTime	Returns the end time of a trajectory object.
ST_duration	Returns the total duration of a trajectory object.
ST_leafType	Returns the leaf type of a trajectory object.
ST_leafCount	Returns the number of trajectory points in a trajectory object.
ST_trajectorySpatial	Returns the spatial geometry of a trajectory object.
ST_trajectoryTemporal	Returns the timeline of a trajectory object.
ST_trajAttrs	Returns the attribute fields of a trajectory object.
ST_samplingInterval	Returns the sampling interval of a trajectory object.
ST_pointAtTime	Returns the spatial position of a trajectory object at a specific time.
ST_timeAtPoint	Returns the set of times when a trajectory object passes through a specific point.
ST_velocityAtTime	Returns the velocity at a specific time.
ST_accelerationAtTime	Returns the acceleration at a specific time.
ST_timeToDistance	Returns a time-to-distance line chart, with time on the x-axis and Euclidean distance on the y-axis.
ST_timeAtDistance	Returns the time at which a trajectory object has traveled a specific distance from the start point.
ST_cumulativeDistanceAtTime	Returns the cumulative distance traveled from the start point up to a specific time.
ST_timeAtCumulativeDistance	Returns the time at which a trajectory object has covered a specific cumulative distance from the start point.
ST_subTrajectory	Returns the sub-trajectory within a specific time range.
ST_subTrajectorySpatial	Returns the spatial geometry of a trajectory within a specific time range.
ST_attrIntMax	Returns the maximum value of an INTEGER attribute field.
ST_attrIntMin	Returns the minimum value of an INTEGER attribute field.
ST_attrIntAverage	Returns the average value of an INTEGER attribute field.
ST_attrFloatMax	Returns the maximum value of a FLOAT attribute field.
ST_attrFloatMin	Returns the minimum value of a FLOAT attribute field.
ST_attrFloatAverage	Returns the average value of a FLOAT attribute field.
ST_trajAttrsAsText	Returns all values of a TEXT attribute field as an array.
ST_trajAttrsAsInteger	Returns all values of an INTEGER attribute field as an array.
ST_trajAttrsAsDouble	Returns all values of a DOUBLE attribute field as an array.
ST_trajAttrsAsBool	Returns all values of a BOOLEAN attribute field as an array.
ST_trajAttrsAsTimestamp	Returns all values of a TIMESTAMP attribute field as an array.
ST_attrIntFilter	Filters trajectory points by a fixed value or range of an INTEGER attribute field.
ST_attrFloatFilter	Filters trajectory points by a fixed value or range of a FLOAT attribute field.
ST_attrTimestampFilter	Filters trajectory points by a fixed value or range of a TIMESTAMP attribute field.
ST_attrNullFilter	Returns a trajectory of points where a specific attribute field is null.
ST_attrNotNullFilter	Returns a trajectory of points where a specific attribute field is not null.
ST_trajAttrsMeanMax	Returns the maximum average value of an attribute field across time ranges using the MEAN-MAX algorithm.

Bounding box functions

Function	Description
ST_MakeBox	Returns the bounding box of an object within a specific time range.
ST_MakeBox{Z\|T\|2D\|2DT\|3D\|3DT}	Builds a bounding box for a specific dimensional configuration.
ST_BoxndfToGeom	Converts a bounding box to a geometry object.
ST_Has{xy\|z\|t}	Tests whether a bounding box has the specified dimension(s).
ST_{X\|Y\|Z\|T}Min	Returns the minimum value of a bounding box along a specific axis.
ST_{X\|Y\|Z\|T}Max	Returns the maximum value of a bounding box along a specific axis.
ST_ExpandSpatial	Expands a bounding box by a given spatial extent.

Bounding box operators

Operator	Description
INTERSECT operators	Tests whether the bounding box of the left operand intersects that of the right operand along a specific axis.
INCLUDE operators	Tests whether the bounding box of the left operand includes that of the right operand along a specific axis.
INCLUDED operators	Tests whether the bounding box of the left operand is included in that of the right operand along a specific axis.

Spatial relationship functions

Function	Description
ST_intersects	Tests whether a trajectory and a geometry intersect in space within a specific time range.
ST_equals	Tests whether a trajectory and a geometry are spatially equal within a specific time range.
ST_distanceWithin	Tests whether a trajectory (within a time range) and a geometry are within a given distance of each other.

Spatial processing

Function	Description
ST_intersection	Returns a new trajectory representing the intersection of a trajectory (within a time range) and a geometry.
ST_difference	Returns a new trajectory representing the difference between a trajectory (within a time range) and a geometry.

Spatial statistics

Function	Description
ST_nearestApproachPoint	Returns the point on a trajectory nearest to a geometry within a specific time range.
ST_nearestApproachDistance	Returns the minimum distance between a trajectory and a geometry within a specific time range.

Spatio-temporal relationship functions

Function	Description
ST_intersects	Tests whether two trajectories intersect in space within a specific time range.
ST_equals	Tests whether two trajectories are spatially equal within a specific time range.
ST_distanceWithin	Tests whether two trajectories are within a given distance of each other within a specific time range.
ST_durationWithin	Tests whether two trajectories intersect at the same spatial point within a specific time range, where the time difference is shorter than a given interval.
ST_{Z\|T\|2D\|2DT\|3D\|3DT}Intersects	Tests whether two objects intersect along a specific axis.
ST_{2D\|2DT\|3D\|3DT}Intersects_IndexLeft	Tests whether two objects intersect along a specific axis, using the index on the first object's column.
ST_{2D\|2DT\|3D\|3DT}DWithin	Tests whether the distance between two objects along a specific axis is within a given threshold.
ST_{2D\|2DT\|3D\|3DT}DWithin_IndexLeft	Tests whether the distance between two objects along a specific axis is within a given threshold, using the index on the first object's column.
ST_{T\|2D\|2DT\|3D\|3DT}Contains	Tests whether the first object includes the second along a specific axis.
ST_{T\|2D\|2DT\|3D\|3DT}Within	Tests whether the first object is included in the second along a specific axis.

Spatio-temporal processing

Function	Description
ST_intersection	Returns a new trajectory representing the intersection of two trajectories within a specific time range.

Spatio-temporal statistics

Function	Description
ST_nearestApproachPoint	Returns the point on trajectory 1 nearest to trajectory 2 within a specific time range.
ST_nearestApproachDistance	Returns the minimum distance between two trajectories within a specific time range.

Distance measurement

Function	Description
ST_length	Returns the total length of a trajectory object in meters.
ST_euclideanDistance	Returns the Euclidean distance between two trajectories at the same time point.
ST_mdistance	Returns an array of Euclidean distances between two trajectories at the same time point.

Similarity analysis

Function	Description
ST_lcsSimilarity	Returns the similarity score between two trajectories using the longest common sub-sequence (LCSS) algorithm.
ST_lcsDistance	Returns the distance and time difference between two trajectories based on the LCSS algorithm.
ST_lcsSubDistance	Returns the distance between an LCSS trajectory and its sub-trajectory.

Indexing

Index type	Description
GiST indexing	Creates a GiST index on a column that stores trajectory data.
TrajGiST indexing	Creates a TrajGiST index on a column that stores trajectory data. TrajGiST is optimized for spatio-temporal queries.

Configuration variables

Variable	Description
ganos.trajectory.attr_string_length	Sets the default length for STRING attribute fields.