This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

Expressions

Standard Expressions are basic level operations that can be added across the platform such as finding the max value in a column, extracting the year from a date field, or removing the leading zeroes in a text field.

1: Expression Library
2: MADLib Expressions (ML)

2.1: Data Type Transformations

2.1.1: Array Operations
2.1.2: Encoding Categorical Variables
2.1.3: Low-Rank Matrix Factorization
2.1.4: Matrix Operations
2.1.5: Norms and Distance Functions
2.1.6: Path
2.1.7: Pivot
2.1.8: Sessionize
2.1.9: Single Value Decomposition
2.1.10: Sparse Vectors
2.1.11: Stemming

2.2: Deep Learning
2.3: Machine Learning

3: PostGIS Expressions (Geospatial)

3.1: Affine Transformations

3.1.1: func.ST_TransScale
3.1.2: func.ST_Translate
3.1.3: func.ST_Scale
3.1.4: func.ST_RotateZ
3.1.5: func.ST_RotateY
3.1.6: func.ST_RotateX
3.1.7: func.ST_Rotate
3.1.8: func.ST_Affine

3.2: Bounding Box Functions

3.2.1: func.ST_ZMin
3.2.2: func.ST_ZMax
3.2.3: func.ST_YMin
3.2.4: func.ST_YMax
3.2.5: func.ST_XMin
3.2.6: func.ST_XMax
3.2.7: func.ST_3DMakeBox
3.2.8: func.ST_MakeBox2D
3.2.9: func.ST_3DExtent
3.2.10: func.ST_Extent
3.2.11: func.ST_Expand
3.2.12: func.ST_EstimatedExtent
3.2.13: func.Box3D
3.2.14: func.Box2D

3.3: Clustering Functions

3.3.1: func.ST_ClusterWithin
3.3.2: func.ST_ClusterIntersecting

3.4: Geometry Accessors

3.4.1: func.ST_Zmflag
3.4.2: func.ST_Z
3.4.3: func.ST_Y
3.4.4: func.ST_X
3.4.5: func.ST_Summary
3.4.6: func.ST_StartPoint
3.4.7: func.ST_PointN
3.4.8: func.ST_PatchN
3.4.9: func.ST_NumPoints
3.4.10: func.ST_NumPatches
3.4.11: func.ST_NumInteriorRing
3.4.12: func.ST_NumInteriorRings
3.4.13: func.ST_NumGeometries
3.4.14: func.ST_NRings
3.4.15: func.ST_NPoints
3.4.16: func.ST_NDims
3.4.17: func.ST_MemSize
3.4.18: func.ST_M
3.4.19: func.ST_IsSimple
3.4.20: func.ST_IsRing
3.4.21: func.ST_IsCollection
3.4.22: func.ST_IsClosed
3.4.23: func.ST_InteriorRingN
3.4.24: func.ST_HasArc
3.4.25: func.ST_GeometryN
3.4.26: func.ST_ExteriorRing
3.4.27: func.ST_Envelope
3.4.28: func.ST_BoundingDiagonal
3.4.29: func.ST_EndPoint
3.4.30: func.ST_DumpRings
3.4.31: func.ST_DumpPoints
3.4.32: func.ST_Dump
3.4.33: func.ST_Dimension
3.4.34: func.ST_CoordDim
3.4.35: func.ST_Boundary
3.4.36: func.ST_GeometryType
3.4.37: func.ST_IsEmpty

3.5: Geometry Constructors

3.5.1: func.ST_Collect
3.5.2: func.ST_LineFromMultiPoint
3.5.3: func.ST_MakeEnvelope
3.5.4: func.ST_MakeLine
3.5.5: func.ST_MakePoint
3.5.6: func.ST_MakePointM
3.5.7: func.ST_MakePolygon
3.5.8: func.ST_Point
3.5.9: func.ST_Polygon

3.6: Geometry Editors

3.6.1: func.ST_SwapOrdinates
3.6.2: func.ST_Snap
3.6.3: func.ST_SnapToGrid
3.6.4: func.ST_ShiftLongitude
3.6.5: func.ST_SetPoint
3.6.6: func.ST_Segmentize
3.6.7: func.ST_Reverse
3.6.8: func.ST_RemoveRepeatedPoints
3.6.9: func.ST_RemovePoint
3.6.10: func.ST_Multi
3.6.11: func.ST_LineToCurve
3.6.12: func.ST_LineMerge
3.6.13: func.ST_ForceCurve
3.6.14: func.ST_ForceRHR
3.6.15: func.ST_ForceSFS
3.6.16: func.ST_ForceCollection
3.6.17: func.ST_Force4D
3.6.18: func.ST_Force3DM
3.6.19: func.ST_Force3DZ
3.6.20: func.ST_Force3D
3.6.21: func.ST_Force2D
3.6.22: func.ST_FlipCoordinates
3.6.23: func.ST_CurveToLine
3.6.24: func.ST_CollectionHomogenize
3.6.25: func.ST_CollectionExtract
3.6.26: func.ST_AddPoint

3.7: Geometry Input

3.7.1: func.ST_PointFromGeoHash
3.7.2: func.ST_LineFromEncodedPolyline
3.7.3: func.ST_GMLToSQL
3.7.4: func.ST_GeomFromKML
3.7.5: func.ST_GeomFromGeoJSON
3.7.6: func.ST_GeomFromGML
3.7.7: func.ST_GeomFromGeoHash
3.7.8: func.ST_Box2dFromGeoHash
3.7.9: func.ST_WKBToSQL
3.7.10: func.ST_PointFromWKB
3.7.11: func.ST_LinestringFromWKB
3.7.12: func.ST_LineFromWKB
3.7.13: func.ST_GeomFromWKB
3.7.14: func.ST_GeomFromEWKB
3.7.15: func.ST_GeogFromWKB
3.7.16: func.ST_WKTToSQL
3.7.17: func.ST_PolygonFromText
3.7.18: func.ST_PointFromText
3.7.19: func.ST_MPolyFromText
3.7.20: func.ST_MPointFromText
3.7.21: func.ST_MLineFromText
3.7.22: func.ST_LineFromText
3.7.23: func.ST_GeomFromText
3.7.24: func.ST_GeometryFromText
3.7.25: func.ST_GeomFromEWKT
3.7.26: func.ST_GeomCollFromText
3.7.27: func.ST_GeographyFromText
3.7.28: func.ST_GeogFromText
3.7.29: func.ST_BdMPolyFromText
3.7.30: func.ST_BdPolyFromText
3.7.31: func.GeometryType

3.8: Geometry Output

3.8.1: func.ST_GeoHash
3.8.2: func.ST_AsX3D
3.8.3: func.ST_AsSVG
3.8.4: func.ST_AsTWKB
3.8.5: func.ST_AsLatLonText
3.8.6: func.ST_AsKML
3.8.7: func.ST_AsGML
3.8.8: func.ST_AsGeoJSON
3.8.9: func.ST_AsEncodedPolyline
3.8.10: func.ST_AsHEXEWKB
3.8.11: func.ST_AsEWKB
3.8.12: func.ST_AsBinary
3.8.13: func.ST_AsText
3.8.14: func.ST_AsEWKT

3.9: Geometry Processing

3.9.1: func.ST_SetEffectiveArea
3.9.2: func.ST_SimplifyVW
3.9.3: func.ST_SimplifyPreserveTopology
3.9.4: func.ST_Simplify
3.9.5: func.ST_SharedPaths
3.9.6: func.ST_Polygonize
3.9.7: func.ST_PointOnSurface
3.9.8: func.ST_OffsetCurve
3.9.9: func.ST_MinimumBoundingCircle
3.9.10: func.ST_DelaunayTriangles
3.9.11: func.ST_ConvexHull
3.9.12: func.ST_ConcaveHull
3.9.13: func.ST_Centroid
3.9.14: func.ST_BuildArea
3.9.15: func.ST_Buffer
3.9.16: func.St_Accum

3.10: Geometry Validation

3.10.1: func.ST_MakeValid
3.10.2: func.ST_IsValidReason
3.10.3: func.ST_IsValidDetail
3.10.4: func.ST_IsValid

3.11: Linear Referencing

3.11.1: func.ST_AddMeasure
3.11.2: func.ST_InterpolatePoint
3.11.3: func.ST_LocateBetweenElevations
3.11.4: func.ST_LocateBetween
3.11.5: func.ST_LocateAlong
3.11.6: func.ST_LineSubstring
3.11.7: func.ST_LineLocatePoint
3.11.8: func.ST_LineInterpolatePoint

3.12: Measurement Functions

3.12.1: func.ST_ShortestLine
3.12.2: func.ST_Project
3.12.3: func.ST_Perimeter2D
3.12.4: func.ST_Perimeter
3.12.5: func.ST_MaxDistance
3.12.6: func.ST_LongestLine
3.12.7: func.ST_3DShortestLine
3.12.8: func.ST_3DPerimeter
3.12.9: func.ST_3DMaxDistance
3.12.10: func.ST_LengthSpheroid
3.12.11: func.ST_3DLongestLine
3.12.12: func.ST_3DLength
3.12.13: func.ST_Length2D
3.12.14: func.ST_Length
3.12.15: func.ST_HausdorffDistance
3.12.16: func.ST_DistanceSpheroid
3.12.17: func.ST_Distance
3.12.18: func.ST_3DClosestPoint
3.12.19: func.ST_ClosestPoint
3.12.20: func.ST_Azimuth
3.12.21: func.ST_Area
3.12.22: func.ST_Length2D_Spheroid

3.13: Overlay Functions

3.13.1: func.ST_UnaryUnion
3.13.2: func.ST_Union
3.13.3: func.ST_SymDifference
3.13.4: func.ST_Subdivide
3.13.5: func.ST_Split
3.13.6: func.ST_Node
3.13.7: func.ST_MemUnion
3.13.8: func.ST_Intersection
3.13.9: func.ST_Difference
3.13.10: func.ST_ClipByBox2D

3.14: Spatial Reference System Functions

3.14.1: func.ST_Transform
3.14.2: func.ST_SRID
3.14.3: func.ST_SetSRID
3.14.4: func.Find_SRID

3.15: Spatial Relationships

3.15.1: func.ST_PointInsideCircle
3.15.2: func.ST_DWithin
3.15.3: func.ST_DFullyWithin
3.15.4: func.ST_Within
3.15.5: func.ST_Touches
3.15.6: func.ST_RelateMatch
3.15.7: func.ST_Relate
3.15.8: func.ST_OrderingEquals
3.15.9: func.ST_Overlaps
3.15.10: func.ST_Intersects
3.15.11: func.ST_Equals
3.15.12: func.ST_Disjoint
3.15.13: func.ST_LineCrossingDirection
3.15.14: func.ST_3DDWithin
3.15.15: func.ST_Crosses
3.15.16: func.ST_3DDFullyWithin
3.15.17: func.ST_CoveredBy
3.15.18: func.ST_ContainsProperly
3.15.19: func.ST_Covers
3.15.20: func.ST_Contains
3.15.21: func.ST_3DIntersects

3.16: Trajectory Functions

3.16.1: func.ST_DistanceCPA
3.16.2: func.ST_CPAWithin
3.16.3: func.ST_ClosestPointOfApproach
3.16.4: func.ST_IsValidTrajectory

1 - Expression Library

A reference library of all expressions that can be used in PlaidCloud

Description

An expression is a basic function that does a conversion, calculation, cast to another data type, or other action on data in a column or in a dashboard chart object. Examples are startswith, max, or current_date. PlaidCloud expressions are based on PostgreSQL. For a more in depth tutorial or reference guide, please see: tutorial

There are three primary areas to apply expressions - metrics and calculated columns in datasets, and chart objects in dashboards.

Navigating to a dataset

In order to view and edit metrics and calculated expressions:

Sign into plaidcloud.com and navigate to Dasboards. Select the dashboard you want to work in.
Select Data > Datasets from the menu.
Search for a dataset to view or modify
Hover over the dataset with the cursor and you will see icons in the actions column.
Click the edit icon beneath Actions

Viewing a chart object and adding an expression

You can add expressions to chart objects on a dashboard. For example, if you want to add an expression to a table object (a calculated column), you can:

Open the chart object by opening a dashboard, clicking on the three dot icon, and selecting "View chart in Explore".
Now that you are editing the chart, you can add a new Dimension or Metric, and do a SIMPLE expression, or a CUSTOM SQL expression

Now that you have located where you want to add an expression, you can use the table below as a guide to determining what expression you are looking for.

Category	Expression	Structure	Example	Description
Conditional	case	case((expression, truevalue), else_ = falsevalue)	case((table.first_name.isnot(None), func.concat(table.first_name, table.last_name)), else_ = table.last_name) Additional Examples	a switch or a conditional control structure that allows the program to evaluate an expression and perform different actions based on the value of that expression
Conditional	coalesce	func.coalesce(column1, column2, ...)	func.coalesce(table.nickname, table.first_name) Additional Examples	Returns the first non-null value in a set of columns. In the example, if there is a nickname it returns that, otherwise it returns the first name.
Conversion	cast	func.cast(value, datatype)	func.cast(123, Text) Additional Examples	Converts the value to a specific data type. In the example, it takes an Integer (123) and returns it as a string "123".
Conversion	to_char	func.to_char(timestamp, text) See More	func.to_char(current_timestamp, 'HH12:MI:S S') Additional Examples	Converts an object type to a char (text). In the example, it converts a timestamp to text
Conversion	to_date	func.to_date(text, format)	func.to_date(table.Created_on, 'DD-MM-YYYY')	Convert a text field into a date formatted how you like
Conversion	to_number	func.to_number(text, format)	func.to_number ('12,454.8 -', '99G999D9S')	Convert a string to a numeric value
Conversion	to_timestamp	func.to_timestamp(text, format) See More	func.to_timestamp('05 Dec 2000', 'DD Mon YYYY') Additional Examples	Convert a string to a timestamp
Time	age	func.age(timestamp, timestamp)	age(timestamp ‘2001-04-1 0’, timestamp ‘1957-06-1 3’)=43 years 9 months 27 days	Subtracts the second timestamp from the first one and returns an interval as a result
Time	age	func.age(timestamp)	age(timestamp ‘1957-06-1 3’)=43 years 8 months 3 days	Returns the interval between the current date and the argument provided
Time	clock_timestamp	func.clock_timestamp()	func.clock_timestamp()	Returns a timestamp for the current date and time which changes during execution
Time	current_date	func.current_date()	func.current_date() get_column(table, 'Created On')>=(func.current_date()-120)	Returns the a date object with the current date
Time	current_time	func.current_time()	func.current_time()	Returns a time object with the current time and timezone
Time	current_timestamp	func.current_timestamp()	func.current_timestamp()	Returns a timestamp object with the current date and time at the beginning of execution
Time	date_part	func.date_part(text, timestamp)	func.date_part('hour', timestamp '2001-02-1 6 20:38:40')=20	Returns the part of the timestamp you are looking for (month, year, etc.) See more options
Time	date_part	func.date_part(text, interval)	func.date_part('month', interval '2 years 3 months')=3	Returns the part of the interval you are looking for (month, year, etc.) See more options
Time	date_trunc	func.date_trunc(text, timestamp)	func.date_trunc('hour', timestamp '2001-02-1 6 20:38:40')=36938.8333333333 Additional Examples	Truncate to specified precision
Time	extract	func.extract(field from timestamp)	func.extract(hour from timestamp '2001-02-1 6 20:38:40')=20	Get a field of a timestamp or an interval e.g., year, month, day, etc.
Time	extract	func.extract(field from interval)	func.extract(month from interval '2 years 3 months')=3	Get a field of a timestamp or an interval e.g., year, month, day, etc.
Time	isfinite	func.isfinite(timestamp)	func.isfinite(timestamp '2001-02-1 6 21:28:30')=TRUE	Check if a date, a timestamp, or an interval is finite or not (not +/-infinity)
Time	isfinite	func.isfinite(interval)	func.isfinite(interval '4 hours')=TRUE	Check if a date, a timestamp, or an interval is finite or not (not +/-infinity)
Time	justify_days	func.justify_days(interval)	func.justify_days(interval '30 days')=1 month	Adjust interval so 30-day time periods are represented as months
Time	justify_hours	func.justify_hours(interval)	func.justify_hours(interval '24 hours')=1 day	Adjust interval so 24-hour time periods are represented as days
Time	justify_interval	func.justify_interval(interval)	func.justify_interval(interval '1 mon -1 hour')=29 days 23:00:00	Adjust interval using justify_days and justify_hours, with additional sign adjustments
Time	now	func.now()	func.now()	Return the date and time with time zone at which the current transaction start
Time	statement_timestamp	func.statement_timestamp()	func.statement_timestamp()	Return the current date and time at which the current statement executes
Time	timeofday	func.timeofday()	func.timeofday()	Return the current date and time, like clock_timestamp, as a text string
Time	transaction_timestamp	func.transaction_timestamp()	func.transaction_timestamp()	Return the date and time with time zone at which the current transaction start
General Usage	>	>	table.column > 23	Greater Than
General Usage	<	<	table.column < 23	Less Than
General Usage	>=	>=	table.column >= 23	Greater than or equal to
General Usage	<=	<=	table.column <= 23	Less than or equal to
General Usage	==	==	table.column == 23	Equal to
General Usage	!=	!=	table.column != 23	Not Equal to
General Usage	and_	and_()	and_(table.a > 23, table.b == u'blue') Additional Examples	Creates an AND SQL condition
General Usage	any_	any_()	table.column.any(('red', 'blue', 'yellow'))	Applies the SQL ANY() condition to a column
General Usage	between	between	table.column.between(23, 46) get_column(table, 'LAST_CHANGED_DATE').between({start_date}, {end_date})	Applies the SQL BETWEEN condition
General Usage	contains	contains	table.column.contains('mno') table.SOURCE_SYSTEM.contains('TEST')	Applies the SQL LIKE '%%'
General Usage	endswith	endswith	table.column.endswith('xyz') table.Parent.endswith(':EBITX') table.PERIOD.endswith("01")	Applies the SQL LIKE '%%'
General Usage	FALSE	FALSE	FALSE	False, false, FALSE - Alias for Python False
General Usage	ilike	ilike	table.column.ilike('%foobar%')	Applies the SQL ILIKE method
General Usage	in_	in_()	table.column.in_((1, 2, 3)) get_column(table, 'Source Country').in_(['CN','SG','BR']) table.MONTH.in_(['01','02','03','04','05','06','07','08','09'])	Test if values are with a tuple of values
General Usage	is_	is_	table.column.is_(None) get_column(table, 'Min SafetyStock').is_(None) get_column(table, 'date_pod').is_(None)	Applies the SQL is the IS for things like IS NULL
General Usage	isnot	isnot	table.column.isnot(None)	Applies the SQL is the IS for things like IS NOT NULL
General Usage	like	like	table.column.like('%foobar%') table.SOURCE_SYSTEM.like('%Adjustments%')	Applies the SQL LIKE method
General Usage	not_	not_()	not_(and_(table.a > 23, table.b == u'blue')) Additional Examples	Inverts the condition
General Usage	notilike	notilike	table.column.notilike('%foobar%')	Applies the SQL NOT ILIKE method
General Usage	notin	notin	table.column.notin((1, 2, 3)) table.LE.notin_(['12345','67890'])	Inverts the IN condition
General Usage	notlike	notlike	table.column.notlike('%foobar%')	Applies the SQL NOT LIKE method
General Usage	NULL	NULL	NULL	Null, null, NULL - Alias for Python None
General Usage	or_	or_()	or_(table.a > 23, table.b == u'blue') Additional Examples	Creates an OR SQL condition
General Usage	startswith	startswith	table.column.startswith('abc') get_column(table, 'Zip Code').startswith('9') get_column(table1, 'GL Account').startswith('CORP')	Applies the SQL LIKE '%'
General Usage	TRUE	TRUE	TRUE	True, true, TRUE - Alias for Python True
Math Expressions	+	+	+	2+3=5
Math Expressions	–	–	-	2–3=-1
Math Expressions	*	*	*	2*3=6
Math Expressions	/	/	/	4/2=2
Math Expressions	column.op	column.op(operator)	column.op('%')	5%4=1
Math Expressions	column.op	column.op(operator)	column.op('^')	2.0^3.0=8
Math Expressions	column.op	column.op(operator)	column.op('!')	5!=120
Math Expressions	column.op	column.op(operator)	column.op('!!')	!!5=120
Math Expressions	column.op	column.op(operator)	column.op('@')	@-5.0=5
Math Expressions	column.op	column.op(operator)	column.op('&')	91&15=11
Math Expressions	column.op	column.op(operator)	column.op('#')	17##5=20
Math Expressions	column.op	column.op(operator)	column.op('~')	~1=-2
Math Expressions	column.op	column.op(operator)	column.op('<<')	1<<4=16
Math Expressions	column.op	column.op(operator)	column.op('>>')	8>>2=2
Math Functions	abs	func.abs(x)	abs(-17.4)=17.4 func.abs(get_column(table, 'RPA Value'))	absolute value (return type: Same as input)
Math Functions	cbrt	func.cbrt(dp)	cbrt(27.0)=3	cube root (return type: Big Float)
Math Functions	ceil	func.ceil(dp or numeric)	ceil(-42.8)=-42 func.ceil(func.extract('seconds', table.OutlierTime) / 60)	smallest integer not less than argument (return type: Same as input)
Math Functions	ceiling	func.ceiling(dp or numeric)	ceiling(-95.3)=-95	smallest integer not less than argument (return type: Same as input)
Math Functions	degrees	func.degrees(dp)	degrees(0.5)=28.6478897565412	radians to degrees (return type: Big Float)
Math Functions	exp	func.exp(dp or numeric)	exp(1.0)=2.71828182845905	exponential (return type: Same as input)
Math Functions	floor	func.floor(dp or numeric)	floor(-42.8)=-43	largest integer not greater than argument (return type: Same as input)
Math Functions	greatest	func.greatest(value …)		Select the largest value from a list. NULL values in the list are ignored. The result will be NULL only if all values are NULL. (return type: Same as input)
Math Functions	least	func.least(value…)		Select the smallest value from a list. NULL values in the list are ignored. The result will be NULL only if all values are NULL. (return type: Same as input)
Math Functions	ln	func.ln(dp or numeric)	ln(2.0)=0.693147180559945	natural logarithm (return type: Same as input)
Math Functions	log	func.log(dp or numeric)	log(100.0)=2	base 10 logarithm (return type: Same as input)
Math Functions	log	func.log(b numeric, x numeric)	log(2.0,64.0)=6	logarithm to base b (return type: Numeric)
Math Functions	mod	func.mod(y, x)	mod(9,4)=1	remainder of y/x (return type: Same as input)
Math Functions	pi	func.pi()	pi()=3.14159265358979	“π” constant (return type: Big Float)
Math Functions	power	func.power(a dp, b dp)	power(9.0,3.0)=729	a raised to the power of b (return type: Big Float)
Math Functions	power	func.power(a numeric, b numeric)	power(9.0,3.0)=729	a raised to the power of b (return type: Numeric)
Math Functions	radians	func.radians(dp)	radians(4 5.0)=0.785398163397448	degrees to radians (return type: Big Float)
Math Functions	random	func.random()	random()	random value in the range 0.0 <= x < 1.0 (return type: Big Float)
Math Functions	round	func.round(dp or numeric)	round(42.4)=42	round to nearest integer (return type: Same as input)
Math Functions	round	func.round(v numeric, s int)	round(42.4382, 2)=42.44 func.round(table.RATE, 5) func.round((get_column(table, 'Order Quantity')/3), 0)	round to s decimal places (return type: Numeric)
Math Functions	safe_divide	func.safe_divide(numerator numeric, denominator numeric, divide_by_zero_value)	func.safe_divide(get_column(table, 'VALUE__MC'), table.RATE, 0.0) func.safe_divide(get_column(table, 'Total_Weight'), (table.PickHours + table.BreakHours), 0.00)	Equivalent to the division operator (X / Y), but returns NULL if an error occurs, such as a division by zero error
Math Functions	setseed	func.setseed(dp)	setseed(0 .54823)=1177314959	set seed for subsequent random() calls (value between 0 and 1.0) (return type: Integer)
Math Functions	sign	func.sign(dp or numeric)	sign(-8.4)=-1	sign of the argument (-1, 0, +1) (return type: Same as input)
Math Functions	sqrt	func.sqrt(dp or numeric)	sqrt(2.0)=1.4142135623731	square root (return type: Same as input)
Math Functions	trunc	func.trunc(dp or numeric)	trunc(42. 8)=42	truncate toward zero (return type: Same as input)
Math Functions	trunc	func.trunc(v numeric, s int)	trunc(42.4382, 2)=42.43	truncate to s decimal places (return type: Numeric)
Math Functions	width_bucket	func.width_bucket( op numeric, b1 numeric, b2 numeric, count int)	width_bucket(5.35, 0.024, 10.06, 5)=3	return the bucket to which operand would be assigned in an equidepth histogram with count buckets, in the range b1 to b2 (return type: Integer)
Math Trig	acos	func.acos(x)		inverse cosine
Math Trig	asin	func.asin(x)		inverse sine
Math Trig	atan	func.atan(x)		inverse tangent
Math Trig	atan2	func.atan2(x,y)		inverse tangent of x/y
Math Trig	cos	func.cos(x)		cosine
Math Trig	cot	func.cot(x)		cotangent
Math Trig	sin	func.sin(x)		sine
Math Trig	tan	func.tan(x)		tangent
Geometry / PostGIS	ST_3DMakeBox	box3d ST_3DMakeBox(geometry point3DLowLeftBottom, geometry point3DUpRightTop);	Example	Creates a BOX3D defined by the given 3d point geometries.
Geometry / PostGIS	ST_BdMPolyFromText	geometry ST_BdMPolyFromText(text WKT, integer srid);	Example	Construct a MultiPolygon given an arbitrary collection of closed linestrings as a MultiLineString text representation Well-Known text representation.
Geometry / PostGIS	ST_BdPolyFromText	geometry ST_BdPolyFromText(text WKT, integer srid);	Example	Construct a Polygon given an arbitrary collection of closed linestrings as a MultiLineString Well-Known text representation.
Geometry / PostGIS	ST_Box2dFromGeoHash	box2d ST_Box2dFromGeoHash(text geohash, integer precision=full_precision_of_geohash);	Example	Return a BOX2D from a GeoHash string.
Geometry / PostGIS	ST_GeogFromText	geography ST_GeogFromText(text EWKT);	Example	Return a specified geography value from Well-Known Text representation or extended (WKT).
Geometry / PostGIS	ST_GeogFromWKB	geography ST_GeogFromWKB(bytea wkb);	Example	Creates a geography instance from a Well-Known Binary geometry representation (WKB) or extended Well Known Binary (EWKB).
Geometry / PostGIS	ST_GeographyFromText	geography ST_GeographyFromText(text EWKT);	Example	Return a specified geography value from Well-Known Text representation or extended (WKT).
Geometry / PostGIS	ST_GeomCollFromText	geometry ST_GeomCollFromText(text WKT, integer srid);	Example	Makes a collection Geometry from collection WKT with the given SRID. If SRID is not given, it defaults to 0.
Geometry / PostGIS	ST_GeometryFromText	geometry ST_GeometryFromText(text WKT, integer srid);	Example	Return a specified ST_Geometry value from Well-Known Text representation (WKT). This is an alias name for ST_GeomFromText
Geometry / PostGIS	ST_GeomFromEWKB	geometry ST_GeomFromEWKB(bytea EWKB);	Example	Return a specified ST_Geometry value from Extended Well-Known Binary representation (EWKB).
Geometry / PostGIS	ST_GeomFromEWKT	geometry ST_GeomFromEWKT(text EWKT);	Example	Return a specified ST_Geometry value from Extended Well-Known Text representation (EWKT).
Geometry / PostGIS	ST_GeomFromGeoHash	geometry ST_GeomFromGeoHash(text geohash, integer precision=full_precision_of_geohash);	Example	Return a geometry from a GeoHash string.
Geometry / PostGIS	ST_GeomFromGML	geometry ST_GeomFromGML(text geomgml, integer srid);	Example	Takes as input GML representation of geometry and outputs a PostGIS geometry object
Geometry / PostGIS	ST_GeomFromGML	geometry ST_GeomFromGML(text geomgml, integer srid);	Example	Takes as input GML representation of geometry and outputs a PostGIS geometry object
Geometry / PostGIS	ST_GeomFromKML	geometry ST_GeomFromKML(text geomkml);	Example	Takes as input KML representation of geometry and outputs a PostGIS geometry object
Geometry / PostGIS	ST_GeomFromText	geometry ST_GeomFromText(text WKT, integer srid);	Example	Return a specified ST_Geometry value from Well-Known Text representation (WKT).
Geometry / PostGIS	ST_GeomFromWKB	geometry ST_GeomFromWKB(bytea geom, integer srid);	Example	Creates a geometry instance from a Well-Known Binary geometry representation (WKB) and optional SRID.
Geometry / PostGIS	ST_GMLToSQL	geometry ST_GMLToSQL(text geomgml, integer srid);	Example	Return a specified ST_Geometry value from GML representation. This is an alias name for ST_GeomFromGML
Geometry / PostGIS	ST_LineFromEncodedPolyline	geometry ST_LineFromEncodedPolyline(text polyline, integer precision=5);	Example	Creates a LineString from an Encoded Polyline.
Geometry / PostGIS	ST_LineFromMultiPoint	geometry ST_LineFromMultiPoint(geometry aMultiPoint);	Example	Creates a LineString from a MultiPoint geometry.
Geometry / PostGIS	ST_LineFromText	geometry ST_LineFromText(text WKT, integer srid);	Example	Makes a Geometry from WKT representation with the given SRID. If SRID is not given, it defaults to 0.
Geometry / PostGIS	ST_LineFromWKB	geometry ST_LineFromWKB(bytea WKB, integer srid);	Example	Makes a LINESTRING from WKB with the given SRID
Geometry / PostGIS	ST_LinestringFromWKB	geometry ST_LinestringFromWKB(bytea WKB, integer srid);	Example	Makes a geometry from WKB with the given SRID.
Geometry / PostGIS	ST_MakeBox2D	box2d ST_MakeBox2D(geometry pointLowLeft, geometry pointUpRight);	Example	Creates a BOX2D defined by the given point geometries.
Geometry / PostGIS	ST_MakeEnvelope	geometry ST_MakeEnvelope(double precision xmin, double precision ymin, double precision xmax, double precision ymax, integer srid=unknown);	Example	Creates a rectangular Polygon formed from the given minimums and maximums. Input values must be in SRS specified by the SRID
Geometry / PostGIS	ST_MakeLine	geometry ST_MakeLine(geometry geom1, geometry geom2);	Example	Creates a Linestring from point or line geometries.
Geometry / PostGIS	ST_MakePoint	geometry ST_MakePoint(double precision x, double precision y, double precision z, double precision m);	Example	Creates a 2D,3DZ or 4D point geometry.
Geometry / PostGIS	ST_MakePointM	geometry ST_MakePointM(float x, float y, float m);	Example	Creates a point geometry with an x, y, and m coordinate.
Geometry / PostGIS	ST_MakePolygon	geometry ST_MakePolygon(geometry outerlinestring, geometry[] interiorlinestrings);	Example	Creates a Polygon formed by the given shell. Input geometries must be closed LINESTRINGS.
Geometry / PostGIS	ST_MLineFromText	geometry ST_MLineFromText(text WKT, integer srid);	Example	Return a specified ST_MultiLineString value from WKT representation.
Geometry / PostGIS	ST_MPointFromText	geometry ST_MPointFromText(text WKT, integer srid);	Example	Makes a Geometry from WKT with the given SRID. If SRID is not give, it defaults to 0.
Geometry / PostGIS	ST_MPolyFromText	geometry ST_MPolyFromText(text WKT, integer srid);	Example	Makes a MultiPolygon Geometry from WKT with the given SRID. If SRID is not give, it defaults to 0.
Geometry / PostGIS	ST_Point	geometry ST_Point(float x_lon, float y_lat);	Example	Returns an ST_Point with the given coordinate values. OGC alias for ST_MakePoint.
Geometry / PostGIS	ST_PointFromGeoHash	point ST_PointFromGeoHash(text geohash, integer precision=full_precision_of_geohash);	Example	Return a point from a GeoHash string.
Geometry / PostGIS	ST_PointFromText	geometry ST_PointFromText(text WKT, integer srid);	Example	Makes a point Geometry from WKT with the given SRID. If SRID is not given, it defaults to unknown.
Geometry / PostGIS	ST_PointFromWKB	geometry ST_GeomFromWKB(bytea geom, integer srid);	Example	Makes a geometry from WKB with the given SRID
Geometry / PostGIS	ST_Polygon	geometry ST_Polygon(geometry aLineString, integer srid);	Example	Returns a polygon built from the specified linestring and SRID.
Geometry / PostGIS	ST_PolygonFromText	geometry ST_PolygonFromText(text WKT, integer srid);	Example	Makes a Geometry from WKT with the given SRID. If SRID is not give, it defaults to 0.
Geometry / PostGIS	ST_WKBToSQL	geometry ST_WKBToSQL(bytea WKB);	Example	Return a specified ST_Geometry value from Well-Known Binary representation (WKB). This is an alias name for ST_GeomFromWKB that takes no srid
Geometry / PostGIS	ST_WKTToSQL	geometry ST_WKTToSQL(text WKT);	Example	Return a specified ST_Geometry value from Well-Known Text representation (WKT). This is an alias name for ST_GeomFromText
Text Expression	ascii	func.ascii(string) returns int	ascii('x')=120 func.ascii(get_column(table, 'TAX_SEGMENT'))	ASCII code of the first byte of the argument
Text Expression	bit_length	func.bit_length(string) returns int	bit_length('jose')=32	Number of bits in string
Text Expression	btrim	func.btrim(string text [, characters text]) returns Text	btrim('xyx johnyyx', 'xy')=john	Remove the longest string consisting only of characters in characters (a space by default) from the start and end of string
Text Expression	char_length	func.char_length(string) or func.character_length(string) returns int	char_leng th('jose')=4	Number of characters in string
Text Expression	chr	func.chr(int) returns Text	chr(65)=A	Character with the given ASCII code
Text Expression	concat	func.concat(string, string) returns Text	concat('Post', 'greSQL')=PostgreSQL func.concat(table.YEAR,'_', table.PERIOD)	String concatenation
Text Expression	convert	func.convert(string text, [src_encoding name,]dest_encoding name)	convert('text_in_utf8', 'UTF8', 'LATIN1')=text_in_utf8 represented in ISO 8859-1 encoding	Convert string to dest_encoding. The original encoding is specified by src_encoding. If src_encoding is omitted, database encoding is assumed.
Text Expression	convert	func.convert(string using conversion_name)	convert('PostgreSQL' using iso_8859_1_to_utf8)	Change encoding using specified conversion name. Conversions can be defined by CREATE CONVERSION. Also there are some pre-defined conversion names. See here for available conversion names.
Text Expression	decode	func.decode(string text, type text)		Decode binary data from string previously encoded with encode. Parameter type is same as in encode.
Text Expression	initcap	func.initcap(string) returns Text	initcap('hi THOMAS')=Hi Thomas	Convert the first letter of each word to uppercase and the rest to lowercase. Words are sequences of alphanumeric characters separated by non-alphanumeric characters
Text Expression	integerize_truncate	func.integerize_truncate(string)	func.integerize_truncate('30.66')=30	Takes a single numeric argument x and returns a numeric vector containing the integers formed by truncating the values in x toward 0
Text Expression	integerize_round	func.integerize_round(string)	func.integerize_round('30.66') --> 31	Rounds the values in its first argument to the specified number of decimal places
Text Expression	length	func.length(string) returns int	length('jose')=4 func.length(get_column(table, 'arrival_date_actual'))	Number of characters in string
Text Expression	lower	func.lower(string) returns Text	lower('TOM ')=tom	Convert string to lower case
Text Expression	lpad	func.lpad(string text, length int [, fill text]) returns Text	lpad('hi', 5, 'xy')=xyxhi func.lpad('stringtofillup', 10, 'X')=stringtofi	Fill up the string to length length by prepending the characters fill (a space by default). If the string is already longer than length then it is truncated (on the right)
Text Expression	ltrim	func.ltrim(string text [, characters text]) returns Text	ltrim('zzz yjohn', 'xyz')=john func.ltrim('texttotrimplaidcloud', 'texttotrim')=plaidcloud func.ltrim('plaidcloud')=plaidcloud	Remove the longest string containing only characters from characters (a space by default) from the start of string
Text Expression	md5	func.md5(string) returns Text	md5('abc')=900150983cd24fb0d6963f7d28e17f72	Calculates the MD5 hash of string, returning the result in hexadecimal
Text Expression	metric_multiply	func.metric_multiply(string)		The Multiply function can take multiple metrics as inputs and multiply the values of the metrics
Text Expression	numericize	func.numericize(string)	func.numericize('100')=100	Attempts to coerce a non-numeric R object to natomic_object() or list of {natomic_object}
Text Expression	octet_length	func.octet_length(string) returns int	octet_length('jose')=4	Number of bytes in string
Text Expression	overlay	func.overlay(string placing string from int [forint]) returns Text	overlay('Txxxxas' placing 'hom' from 2 for 4)=Thomas	Replace a substring (returns: Text)
Text Expression	position	func.position(substring in string) returns int	position('om' in 'Thomas')=3	Location of specified substring
Text Expression	quote_literal	func.quote_literal(string) returns Text	quote_literal('O'Reilly')='O''Reilly' func.quote_literal('plaidcloud')='plaidcloud'	Return the given string suitably quoted to be used as a string literal in an SQL statement string. Embedded single-quotes and backslashes are properly doubled.
Text Expression	regexp_replace	func.regexp_replace(string text, pattern text, replacement text [,flags text]) returns Text	regexp_replace('Thomas', '.[mN]a.', 'M')=ThM More Examples	Replace substring matching POSIX regular expression.
Text Expression	repeat	func.repeat(string text, number int) returns Text	repeat('Pg', 4)=PgPgPgPg	Repeat string the specified number of times
Text Expression	replace	func.replace(string text, from text, to text) returns Text	replace('abcdefabc def', 'cd', 'XX')=abXXefabX Xef func.replace('string_to_replace_with_spaces','_',' ') --> string to replace with spaces	Replace all occurrences in string of substring from with substring to
Text Expression	rpad	func.rpad(string text, length int [, fill text]) returns Text	rpad('hi', 5, 'xy')=hixyx	Fill up the string to length length by appending the characters fill (a space by default). If the string is already longer than length then it is truncated
Text Expression	rtrim	func.rtrim(string text [, characters text]) returns Text	rtrim('johnxxxx', 'x')=john	Remove the longest string containing only characters from characters (a space by default) from the end of string
Text Expression	split_part	func.split_part(string text, delimiter text, field int) returns Text	split_part('abc~@~def~@~ghi', '~@~', 2)=def func.split_part(table.PERIOD, '_', 1)	Split string on delimiter and return the given field (counting from one)
Text Expression	strpos	func.strpos(string, substring) returns int	strpos('high', 'ig')=2	Location of specified substring (same as position(subst ring in string), but note the reversed argument order)
Text Expression	substr	func.substr(string, from [, count]) returns Text	substr('alphabet', 3, 2)=ph	Extract substring (same as substring(string from from for count))
Text Expression	substring	func.substring(string [from int] [for int]) returns Text	substring('Thomas' from 2 for 3)=hom func.substring(table.ship_to_postal_code, 1, 5)	Extract substring
Text Expression	substring	func.substring(string frompattern) returns Text	substring( 'Thomas' from '…$')=mas	Extract substring matching POSIX regular expression
Text Expression	substring	func.substring(string frompatternforescape) returns Text	substring( 'Thomas' from '%#”o_a#” _' for '#')=oma	Extract substring matching SQL regular expression
Text Expression	text_to_bigint	func.text_to_bigint(string)		This function allows you to convert a string of character values into a large range integer
Text Expression	text_to_bool	func.text_to_bool(string)		Converts the input text or numeric expression to a Boolean value
Text Expression	text_to_integer	func.text_to_integer(string)		Convert text to integer
Text Expression	text_to_numeric	func.text_to_numeric(string)		This function converts a character string to a numeric value
Text Expression	text_to_smallint	func.text_to_smallint(string)		A 2-byte integer data type used in CREATE TABLE and ALTER TABLE statements
Text Expression	to_ascii	func.to_ascii(string text [, encoding text]) returns Text	to_ascii('Karel')=Karel	Convert string to ASCII from another encoding (only supports conversion from LATIN1, LATIN2, LATIN9, and WIN1250 encodings)
Text Expression	to_hex	func.to_hex(number int or bigint) returns Text	to_hex(2147483647)=7fffffff	Convert number to its equivalent hexadecimal representation
Text Expression	translate	func.translate(string text, from text, to text) returns Text	translate( '12345', '14', 'ax')=a23x5	Any character in the string that matches a character in the from set is replaced by the corresponding character in the to set
Text Expression	trim	func.trim([leading, trailing, both] [characters] from string) returns Text	trim(both 'x' from 'xTomxx')=Tom	Remove the longest string containing only the characters (a space by default) from the start/end/both ends of the string
Text Expression	upper	func.upper(string) returns Text	upper('tom')=TOM	Convert string to uppercase
Arrays	string_to_array	func.string_to_array(text, delimiter)	Examples	This function is used to split a string into array elements using supplied delimiter and optional null string
Arrays	unnest	func.unnest(text)	Examples	This function is used to expand an array to a set of rows
Grouping / Summarization	first	func.first(field)		This function returns the value of a specified field in the first record of the result set returned by a query
Grouping / Summarization	last	func.last(field)		This function returns the value of a specified field in the last record of the result set returned by a query
Grouping / Summarization	max	func.max(field)		The MAX function is an aggregate function that returns the maximum value in a set of values
Grouping / Summarization	median	func.median(field)		This function will calculate the middle value of a given set of numbers
Grouping / Summarization	stdev	func.stdev(field)		The STDEV function calculates the standard deviation for a sample set of data
Grouping / Summarization	stdev_pop	func.stdev_pop(field)		STDDEV_POP computes the population standard deviation and returns the square root of the population variance
Grouping / Summarization	stdev_samp	func.stdev_samp(field)		STDDEV_SAMP() function returns the sample standard deviation of an expression
Grouping / Summarization	var_pop	func.var_pop(field)		VAR_POP returns the population variance of a set of numbers after discarding the nulls in this set
Grouping / Summarization	var_samp	func.var_samp(field)		VAR_SAMP returns the sample variance of a set of numbers after discarding the nulls in this set
Grouping / Summarization	variance	func.variance(field)		This function is used to determine how far a set of values is spread out based on a sample of the population
JSON	array_to_json	func.array_to_json(array)		Returns the array as JSON. A PostgreSQL multidimensional array becomes a JSON array of arrays.
JSON	json_array_elements	func.json_array_elements(json)		Expands a JSON array to a set of JSON elements.
JSON	json_each	func.json_each(json)		Expands the outermost JSON object into a set of key/value pairs
JSON	json_each_text	func.json_each_text(json)		Expands the outermost JSON object into a set of key/value pairs. The returned value will be of type text.
JSON	json_extract_path	func.json_extract_path(json, key_1, key_2, ...)		Returns JSON object pointed to by path elements. The return value will be a type of JSON.
JSON	json_extract_path_text	func.json_extract_path_text(json, key_1, key_2, ...)		Returns JSON object pointed to by path elements. The return value will be a type of text.
JSON	json_object_keys	func.json_object_keys(json)		Returns set of keys in the JSON object. Only the "outer" object will be displayed.
Window Functions	avg	func.avg().over(partition_by=field, order_by=field)		This function returns the average of the values in a group. It ignores null values
Window Functions	count	func.count().over(partition_by=field, order_by=field)	See Examples	An aggregate function that returns the number of rows, or the number of non-NULL rows
Window Functions	cume_dist	func.cume_dist().over(partition_by=field, order_by=field)		This function calculates the cumulative distribution of a value within a group of values
Window Functions	dense_rank	func.dense_rank().over(partition_by=field, order_by=field)		The DENSE_RANK() is a window function that assigns a rank to each row within a partition of a result set
Window Functions	first_value	func.first_value(field).over(partition_by=field, order_by=field)	See Examples	FIRST_VALUE is a function that returns the first value in an ordered set of values
Window Functions	lag	func.lag(field, 1).over(partition_by=field, order_by=field)	See Examples	This function lets you query more than one row in a table at a time without having to join the table to itself
Window Functions	last_value	func.last_value(field).over(partition_by=field, order_by=field)	See Examples	The LAST_VALUE() function is a window function that returns the last value in an ordered partition of a result set
Window Functions	lead	func.lead(field, 1).over(partition_by=field, order_by=field)		This function provides access to more than one row of a table at the same time without a self join
Window Functions	min	func.min().over(partition_by=field, order_by=field)		The min() function returns the item with the lowest value, or the item with the lowest value in an iterable
Window Functions	ntile	func.ntile(4).over(partition_by=field, order_by=field)		This is a function that distributes rows of an ordered partition into a specified number of approximately equal groups, or buckets
Window Functions	percent_rank	func.percent_rank().over(partition_by=field, order_by=field)		The PERCENT_RANK() function evaluates the relative standing of a value within a partition of a result set
Window Functions	rank	func.rank().over(partition_by=field, order_by=field)		This is a function that assigns a rank to each row within a partition of a result set
Window Functions	row_number	func.row_number().over(partition_by=field, order_by=field)		This function is used to provide consecutive numbering of the rows in the result by the order selected in the OVER clause for each partition
Window Functions	sum	func.sum().over(partition_by=field, order_by=field)	See Examples	The SUM function adds values. You can add individual values, cell references or ranges or a mix of all three

Data Types

There are a wide variety of standard data types (dtypes) to support your requirements. As datasets become larger, determining smaller size dtypes for value storage can shrink the size of the table and improve performance. The following dtypes are available:

Boolean
Text
Numbers
- SmallFloat (6 Digits)
- Float (15 Digits)
- BigFloat
- SmallInteger (16 bit) (-32768 to 32767)
- Integer (32 bit) (-2147483648 to 2147483647)
- BigInteger (64 bit) (-9223372036854775808 to 9223372036854775807)
- Numeric
Dates and Times
- Date
- Timestamp
- Time Interval

You can convert from one dtype to another using the func.cast() process.

Note: Casting to an incompatible dtype may cause errors. For example, casting ‘hello’ to an integer will not work.

Case Examples

A simple example

This example returns a person's name. It starts off searching to see if the first name column has a value (the "if"). If there is a value, concatenate the first name with the last name and return it (the "then"). If there isn't a first name, then return the last name only (the "else").

case(
        (table.first_name.isnot(None), func.concat(table.first_name, table.last_name)), 
        else_ = table.last_name
    )

A more complex example with multiple conditions

This example returns a price based on quantity. "If" the quantity in the order is more than 100, then give the customer the special price. If it doesn't satisfy the first condition, go to the second. If the quantity is greater than 10 (11-100), then give the customer the bulk price. Otherwise give the customer the regular price.

case( 
        (order_table.qty > 100, item_table.specialprice), 
        (order_table.qty > 10, item_table.bulkprice) , 
        else_=item_table.regularprice
    )

This example returns the first initial of the person's first name. If the user's name is wendy, return W. Otherwise if the user's name is jack, return J. Otherwise return E.

case( 
        (users_table.name == "wendy", "W"), 
        (users_table.name == "jack", "J"), 
        else_='E'
    )

The above may also be written in shorthand as:

case(
    {"wendy": "W", "jack": "J"}, 
    value=users_table.name, 
    else_='E' 
)

Other Examples

In this example is from a Table:Lookup step where we are updating the "dock_final" column when the table1. dock_final value is Null.

case(
    (table1.dock_final == Null, table2.dock_final),
    else_ = table1.dock_final
    )

This example is from a Table:Lookup step where we are updating the "Marketing Channel" column when "Marketing Channel" in table1 is not 'none' or the "Serial Number" contains a '_'.

case(
    (get_column(table1, 'Marketing Channel') != 'none', get_column(table1, 'Marketing Channel')),
    (get_column(table1, 'Serial Number').contains('_'), get_column(table1, 'Marketing Channel')),
    (get_column(table2, 'Marketing Channel') != Null, get_column(table2, 'Marketing Channel')), 
    else_ = 'none'
    )

CASE WHEN "sol_otif_pod_missing" = 1 THEN
'POD is missing.'
ELSE
'POD exists.'
END

CASE WHEN
SUM("distance_dc_xd") = 0 THEN 0
ELSE
sum("XD")/sum("distance_dc_xd")
END

sum(CASE WHEN "dc" = 'ALAB' THEN
("sol_otif_infull" * "sol_otif_pgi_ontime")
ELSE
0.0
END) / sum(CASE WHEN "dc" = 'ALAB' THEN
1.0
ELSE
0.000001
END)

func.cast() type conversions

Analyze Expression	Description	Result
func.cast(123, Text)	Integer to Text	‘123’
func.cast(‘123’, Integer)	Text to Integer	123
func.cast(‘78.69’, Float)	Text to Float	78.69
func.cast(‘78.69’, SmallFloat)	Text to Small Float	78.69
func.cast(‘78.69’, Integer)	Text to Integer (Truncate decimals)	78
func.cast(‘78.69’, SmallInteger)	Text to Small Integer (Truncate decimals)	78
func.cast(‘78.69’, BigInteger)	Text to Big Integer (Truncate decimals)	78
func.cast(1, Boolean)	Integer to Boolean	True

Other Examples cast(table.transaction_year, Numeric) cast(get_column(table, 'End_Date'),Text)

func.to() Data Type Conversions

Analyze Expression	Return Type	Description	Example
func.to_char(timestamp, text)	text	convert time stamp to text string	to_char(current_timestamp, ‘HH12:MI:S S’)
func.to_char(interval, text)	text	convert interval to string	to_char(interval ‘15h 2m 12s’, ‘HH24:MI:S S’)
func.to_char(integer, text)	text	convert integer to string	to_char(125, ‘999’)
func.to_char(bigfloat, text)	text	convert real/double precision to string	to_char(125.8::real, ‘999D9’)
func.to_char(numeric, text)	text	convert numeric to string	to_char(-125.8, ‘999D99S’)
func.to_date(text, text)	date	convert string to date	func.to_date(table.Created_on, 'DD-MM-YYYY')
func.to_number(text, text)	numeric	convert string to numeric	to_number (‘12,454.8 -‘, ‘99G999D9S ‘)
func.to_timestamp(text, text)	timestamp with time zone	convert string to time stamp	to_timestamp(‘05 Dec 2000’, ‘DD Mon YYYY’)
func.to_timestamp(bigfloat)	timestamp with time zone	convert UNIX epoch to time stamp	to_timestamp(200120400)

Other Examples

to_char("Sales_Order_w_Status"."WeekName")

func.to_char(func.date_trunc('week', get_column(table, 'date_sol_delivery_required')), 'YYYY-MM-DD')

func.to_date(get_column(table, 'File Creation Date'), 'YYYYMMDD')

result.CreateDate<func.to_date('09022022', 'MMDDYYYY')

to_char("date_delivery", 'YYYY-mm-dd')

Other Date Time Examples

Date Trunc

func.date_trunc('week', get_column(table, 'Date' ))

func.to_char(func.date_trunc('week', get_column(table, 'date_sol_delivery_required')), 'YYYY-MM-DD')

func.to_char(func.date_trunc('week', ((table.Date) - 6)),'MON-DD')

The following patterns can be used to select specific parts of a timestamp or to format date/time as desired.

Pattern	Description
HH	hour of day (01-12)
HH12	hour of day (01-12)
HH24	hour of day (00-23)
MI	minute (00-59)
SS	second (00-59)
MS	millisecond (000-999)
US	microsecond (000000-999999 )
SSSS	seconds past midnight (0-86399)
AM or A.M. or PM or P.M.	meridian indicator (uppercase)
am or a.m. or pm or p.m.	meridian indicator (lowercase)
Y,YYY	year (4 and more digits) with comma
YYYY	year (4 and more digits)
YYY	last 3 digits of year
YY	last 2 digits of year
Y	last digit of year
IYYY	ISO year (4 and more digits)
IYY	last 3 digits of ISO year
IY	last 2 digits of ISO year
I	last digits of ISO year
BC or B.C. or AD or A.D.	era indicator (uppercase)
bc or b.c. or ad or a.d.	era indicator (lowercase)
MONTH	full uppercase month name (blank-padded to 9 chars)
Month	full mixed-case month name (blank-padded to 9 chars)
month	full lowercase month name (blank-padded to 9 chars)
MON	abbreviated uppercase month name (3 chars)
Mon	abbreviated mixed-case month name (3 chars)
mon	abbreviated lowercase month name (3 chars)
MM	month number (01-12)
DAY	full uppercase day name (blank-padded to 9 chars)
Day	full mixed-case day name (blank-padded to 9 chars)
day	full lowercase day name (blank-padded to 9 chars)
DY	abbreviated uppercase day name (3 chars)
Dy	abbreviated mixed-case day name (3 chars)
dy	abbreviated lowercase day name (3 chars)
DDD	day of year (001-366)
DD	day of month (01-31)
D	day of week (1-7; Sunday is 1)
W	week in month (1-5) (The first week starts on the first day of the month.)
WW	week number in year (1-53) (The first week starts on the first day of the year.)
IW	ISO week number of year (The first Thursday of the new year is in week 1.)
CC	century (2 digits)
J	Julian Day (days since January 1, 4712 BC)
Q	quarter
RM	month in Roman numerals (I-XII; I=January) (uppercase)
rm	month in Roman numerals (i-xii; i=January) (lowercase)
TZ	time-zone name (uppercase)
tz	time-zone name (lowercase)

And Operator

Example 1

This example checks if the period is any of the three specified dates.

and_(  
    table.color == 'green',  
    table.shape == 'circle',  
    table.price >= 1.25  
)

Example 2

This example is checking if to ensure the origin_plant is not one of the values specified. This is using the != expression.

and_(  
    table.origin_plant != '5013',  
    table.origin_plant != '5026',  
    table.origin_plant != '5120',  
    table.origin_plant != '5287',  
    table.origin_plant != '5161',  
    table.origin_plant != '5192'  
)

Alternatively, for reference, the above check could be written using the not_ and or_ operators like this:

not_(  
    or_(  
        table.origin_plant == '5013',  
        table.origin_plant == '5026',  
        table.origin_plant == '5120',  
        table.origin_plant == '5287',  
        table.origin_plant == '5161',  
        table.origin_plant == '5192'  
    )  
)

Other Examples

and_(table.origin_plant != '5013',table.origin_plant != '5026')

Not Operator

not_(and_(table.VALUE_FC==0.0, table.VALUE_LC==0.0))

not_(or_(get_column(table, 'GL Account').startswith('7'), get_column(table, 'GL Account').startswith('8')))

Or Operator

Example 1 This example checks if the period is any of the three specified dates.

or_(  
    table.period == '2020_10',  
    table.period == '2020_11',  
    table.period == '2020_12'  
)

Example 2 This example is checking if order_reason_Include is null or has the word KEEP as a value.

or_(  
    table.order_reason_Include == 'KEEP',  
    table.order_reason_Include.is_(None)  
)

Coalesce Examples

func.coalesce(table.UOM,  'none', \n)

func.coalesce(get_column(table2, 'TECHNOLOGY_RATE'), 0.0)

func.coalesce(table_beta.adjusted_price, table_alpha.override_price, table_alpha.price) * table_beta.quantity_sold

Regexp Replace Examples

func.regexp_replace('plaidcloud', 'p', 'P') --> Plaidcloud

func.regexp_replace('remove12345alphabets','[[:alpha:]]','','g') --> 12345

func.regexp_replace('remove12345digits','[[:digit:]]','','g') --> removedigits

First Value Examples

This is an example of using the 'first_value()' capability to calculate the running time of the time series data where each event is on a distinct row.

This assumes you have a table of time series data that looks like this:

location	employee	timestamp
Building A	John Doe	2022-01-05 15:34:31
Building A	John Doe	2022-01-05 15:44:31
Building A	John Doe	2022-01-05 15:46:41

table.timestamp - func.first_value(table.timestamp, 1).over(partition_by=[table.location, table.employee], order_by=table.timestamp)

Adding the expression above to an Interval column called 'run_time' would result in an output table like this:

location	employee	timestamp	run_time
Building A	John Doe	2022-01-05 15:34:31	00:00:00
Building A	John Doe	2022-01-05 15:44:31	00:10:00
Building A	John Doe	2022-01-05 15:46:41	00:12:10

Lag Examples

This is an example of using the 'lag()' capability to calculate the time interval in time series data where each event is on a distinct row.

This assumes you have a table of time series data that looks like this:

location	employee	timestamp
Building A	John Doe	2022-01-05 15:34:31
Building A	John Doe	2022-01-05 15:44:31
Building A	John Doe	2022-01-05 15:46:41

table.timestamp - func.lag(table.timestamp, 1).over(partition_by=[table.location, table.employee], order_by=table.timestamp)

Adding the expression above to an Interval column called 'delta' would result in an output table like this:

location	employee	timestamp	delta
Building A	John Doe	2022-01-05 15:34:31	null
Building A	John Doe	2022-01-05 15:44:31	00:10:00
Building A	John Doe	2022-01-05 15:46:41	00:02:10

Last Value Examples

This is an example of using the 'last_value()' capability to calculate the time remaining in time series data where each event is on a distinct row.

This assumes you have a table of time series data that looks like this:

location	employee	timestamp
Building A	John Doe	2022-01-05 15:34:31
Building A	John Doe	2022-01-05 15:44:31
Building A	John Doe	2022-01-05 15:46:41

func.last_value(table.timestamp, 1).over(partition_by=[table.location, table.employee], order_by=table.timestamp) - table.timestamp

Adding the expression above to an Interval column called 'remaining' would result in an output table like this:

location	employee	timestamp	remaining
Building A	John Doe	2022-01-05 15:34:31	00:12:10
Building A	John Doe	2022-01-05 15:44:31	00:02:10
Building A	John Doe	2022-01-05 15:46:41	00:00:00

Sum Examples

(sum("sol_otif_infull" * "sol_otif_pgi_ontime")) / (count(*) + 0.000001)

sum("sol_otif_qty_filled") / (sum("sol_otif_qty_requested") + 0.000001)

Count Examples

sum("RW")/COUNT(DISTINCT "ship_to_customer")

(sum("sol_otif_infull" * "sol_otif_pgi_ontime")) / (count(*) + 0.000001)

Array Examples

In the examples below, we will use the following table called contacts with the phones column defined with an array of text.

CREATE TABLE contacts (
  id SERIAL PRIMARY KEY, 
  name VARCHAR (100), 
  phones TEXT []
);

The phones column is a one-dimensional array that holds various phone numbers that a contact may have.

To define multiple dimensional array, you add the square brackets.

For example, you can define a two-dimensional array as follows:

column_name data_type [][]

An example of inserting data into that table

INSERT INTO contacts (name, phones)
VALUES('John Doe',ARRAY [ '(408)-589-5846','(408)-589-5555' ]);

INSERT INTO contacts (name, phones)
VALUES('Lily Bush','{"(408)-589-5841"}'),
      ('William Gate','{"(408)-589-5842","(408)-589-5843"}');

Array unnest

The unnest() function expands an array to a list of rows. For example, the following query expands all phone numbers of the phones array.

SELECT 
  name, 
  unnest(phones) 
FROM 
  contacts;

Output:

name	unnest
John Doe	(408)-589-5846
John Doe	(408)-589-5555
Lily Bush	(408)-589-5841
William Gate	(408)-589-5843

STRING_TO_ARRAY() function

This function is used to split a string into array elements using supplied delimiter and optional null string.

Syntax:
string_to_array(text, text [, text])

Return Type:
text[]

Example:

SELECT string_to_array('xx~^~yy~^~zz', '~^~', 'yy');

Output:
{xx,NULL,zz}

2 - MADLib Expressions (ML)

Apache MADlib is an open-source library for scalable in-database analytics. It provides data-parallel implementations of mathematical, statistical, graph and machine learning methods for structured and unstructured data.

2.1 - Data Type Transformations

2.1.1 - Array Operations

Provides support functions enabling fast array operations

PlaidCloud expressions and filters provide use of most non-administrative Apache MADLib methods. Apache MADLib methods are accessed by prefixing the standard method name with func.madlib..

In SQL

madlib.array_add(array1,array2);

In PlaidCloud Expressions & Filters

func.madlib.array_add(array1,array2)

External References

Apache MADLib Official Documentation for these methods can be found here.

Additional capabilities and usage examples can be found in the Apache MADLib documentation.

2.1.2 - Encoding Categorical Variables

Coding categorical variables into one-hot, dummy, effects, orthogonal, and Helmert

PlaidCloud expressions and filters provide use of most non-administrative Apache MADLib methods. Apache MADLib methods are accessed by prefixing the standard method name with func.madlib..

In SQL

madlib.encode_categorical_variables ('abalone', 'abalone_out', 'height::TEXT');

In PlaidCloud Expressions & Filters

func.madlib.encode_categorical_variables ('abalone', 'abalone_out', 'height::TEXT')

External References

Apache MADLib Official Documentation for these methods can be found here.

Additional capabilities and usage examples can be found in the Apache MADLib documentation.

2.1.3 - Low-Rank Matrix Factorization

Represent an incomplete matrix using a low-rank approximation

PlaidCloud expressions and filters provide use of most non-administrative Apache MADLib methods. Apache MADLib methods are accessed by prefixing the standard method name with func.madlib..

In SQL

madlib.lmf_igd_run('lmf_model', 'lmf_data', 'row', 'col', 'val', 999, 10000, 3, 0.1, 2, 10, 1e-9);

In PlaidCloud Expressions & Filters

func.madlib.lmf_igd_run('lmf_model', 'lmf_data', 'row', 'col', 'val', 999, 10000, 3, 0.1, 2, 10, 1e-9)

External References

Apache MADLib Official Documentation for these methods can be found here.

Additional capabilities and usage examples can be found in the Apache MADLib documentation.

2.1.4 - Matrix Operations

Provides basic matrix operations for matrices that are too big to fit in memory

PlaidCloud expressions and filters provide use of most non-administrative Apache MADLib methods. Apache MADLib methods are accessed by prefixing the standard method name with func.madlib..

In SQL

madlib.matrix_trans('"mat_B"', 'row=row_id, val=vector', 'mat_r');

In PlaidCloud Expressions & Filters

func.madlib.matrix_trans('"mat_B"', 'row=row_id, val=vector', 'mat_r')

External References

Apache MADLib Official Documentation for these methods can be found here.

Additional capabilities and usage examples can be found in the Apache MADLib documentation.

2.1.5 - Norms and Distance Functions

Useful utility functions for basic linear algebra operations

PlaidCloud expressions and filters provide use of most non-administrative Apache MADLib methods. Apache MADLib methods are accessed by prefixing the standard method name with func.madlib..

In SQL

madlib.squared_dist_norm2(a, b);

In PlaidCloud Expressions & Filters

func.madlib.squared_dist_norm2(a, b)

External References

Apache MADLib Official Documentation for these methods can be found here.

Additional capabilities and usage examples can be found in the Apache MADLib documentation.

2.1.6 - Path

Performs regular pattern matching over a sequence of rows

PlaidCloud expressions and filters provide use of most non-administrative Apache MADLib methods. Apache MADLib methods are accessed by prefixing the standard method name with func.madlib..

In SQL

madlib.path('eventlog', 'path_output', 'session_id', 'event_timestamp ASC', 'buy:=page=''CHECKOUT''', '(buy)', 'sum(revenue) as checkout_rev', TRUE);

In PlaidCloud Expressions & Filters

func.madlib.path('eventlog', 'path_output', 'session_id', 'event_timestamp ASC', "buy:=page='CHECKOUT'", '(buy)', 'sum(revenue) as checkout_rev', True)

External References

Apache MADLib Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the Apache MADLib documentation.

2.1.7 - Pivot

Perform basic OLAP type operations on data

PlaidCloud expressions and filters provide use of most non-administrative Apache MADLib methods. Apache MADLib methods are accessed by prefixing the standard method name with func.madlib..

In SQL

madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'sum');

In PlaidCloud Expressions & Filters

func.madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'sum')

External References

Apache MADLib Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the Apache MADLib documentation.

2.1.8 - Sessionize

Performs time-oriented session reconstruction on a data set comprising a sequence of events

PlaidCloud expressions and filters provide use of most non-administrative Apache MADLib methods. Apache MADLib methods are accessed by prefixing the standard method name with func.madlib..

In SQL

madlib.sessionize('eventlog', 'sessionize_output_view', 'user_id', 'event_timestamp', '0:30:0');

In PlaidCloud Expressions & Filters

func.madlib.sessionize('eventlog', 'sessionize_output_view', 'user_id', 'event_timestamp', '0:30:0')

External References

Apache MADLib Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the Apache MADLib documentation.

2.1.9 - Single Value Decomposition

Factorization of a real or complex matrix, with many useful applications in signal processing and statistics

PlaidCloud expressions and filters provide use of most non-administrative Apache MADLib methods. Apache MADLib methods are accessed by prefixing the standard method name with func.madlib..

In SQL

madlib.matrix_sparsify('mat', 'row=row_id, val=row_vec', 'mat_sparse', 'row=row_id, col=col_id, val=value');

In PlaidCloud Expressions & Filters

func.madlib.matrix_sparsify('mat', 'row=row_id, val=row_vec', 'mat_sparse', 'row=row_id, col=col_id, val=value')

External References

Apache MADLib Official Documentation for these methods can be found here.

Additional capabilities and usage examples can be found in the Apache MADLib documentation.

2.1.10 - Sparse Vectors

Provides compressed storage of vectors that have many duplicate elements

PlaidCloud expressions and filters provide use of most non-administrative Apache MADLib methods. Apache MADLib methods are accessed by prefixing the standard method name with func.madlib..

In SQL

madlib.gen_doc_svecs('svec_output', 'dictionary_table', 'id', 'term', 'documents_table', 'id', 'term', 'count');

In PlaidCloud Expressions & Filters

func.madlib.gen_doc_svecs('svec_output', 'dictionary_table', 'id', 'term', 'documents_table', 'id', 'term', 'count')

External References

Apache MADLib Official Documentation for these methods can be found here.

Additional capabilities and usage examples can be found in the Apache MADLib documentation.

2.1.11 - Stemming

Provides a basic stemming operation for text input using the Porter Stemming Algorithm

PlaidCloud expressions and filters provide use of most non-administrative Apache MADLib methods. Apache MADLib methods are accessed by prefixing the standard method name with func.madlib..

In SQL

madlib.stem_token(word)

In PlaidCloud Expressions & Filters

func.madlib.stem_token(word)

External References

Apache MADLib Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the Apache MADLib documentation.

2.2 - Deep Learning

Content coming soon

2.3 - Machine Learning

Make processing your database tables easier with 'MADlib'

Analyze utilizes the expansive and powerful MADLib extension. MADlib helps you take advantage of the investments you’ve made in your database while using its computational power rather than extracting the data into an external system.

Additional documentation on how to use machine learning is coming soon.

3 - PostGIS Expressions (Geospatial)

PostGIS or Geospacial Expressions are basic level functions that can be applied to geographic data, such as spatial relationships, distance, clustering, and other geometric functions.

3.1 - Affine Transformations

3.1.1 - func.ST_TransScale

Translates the geometry using the deltaX and deltaY args, then scales it using the XFactor, YFactor args, working in 2D only

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_TransScale(geometry geomA, float deltaX, float deltaY, float XFactor, float YFactor);

PlaidCloud

func.ST_TransScale(geometry geomA, float deltaX, float deltaY, float XFactor, float YFactor)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.1.2 - func.ST_Translate

Returns a new geometry whose coordinates are translated delta x,delta y,delta z units

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Translate(geometry g1, float deltax, float deltay);

PlaidCloud

func.ST_Translate(geometry g1, float deltax, float deltay)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.1.3 - func.ST_Scale

Scales the geometry to a new size by multiplying the ordinates with the corresponding factor parameters

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Scale(geometry geom, geometry factor);

PlaidCloud

func.ST_Scale(geometry geom, geometry factor)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.1.4 - func.ST_RotateZ

Rotates a geometry geomA - rotRadians about the Z axis

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_RotateZ(geometry geomA, float rotRadians);

PlaidCloud

func.ST_RotateZ(geometry geomA, float rotRadians)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.1.5 - func.ST_RotateY

Rotates a geometry geomA - rotRadians about the y axis

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_RotateY(geometry geomA, float rotRadians);

PlaidCloud

func.ST_RotateY(geometry geomA, float rotRadians)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.1.6 - func.ST_RotateX

Rotates a geometry geomA - rotRadians about the X axis

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_RotateX(geometry geomA, float rotRadians);

PlaidCloud

func.ST_RotateX(geometry geomA, float rotRadians)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.1.7 - func.ST_Rotate

Rotates geometry rotRadians counter-clockwise about the origin point

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Rotate(geometry geomA, float rotRadians);

PlaidCloud

func.ST_Rotate(geometry geomA, float rotRadians)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.1.8 - func.ST_Affine

Applies a 3D affine transformation to the geometry to do things like translate, rotate, scale in one step

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Affine(geometry geomA, float a, float b, float d, float e, float xoff, float yoff);

PlaidCloud

func.ST_Affine(geometry geomA, float a, float b, float d, float e, float xoff, float yoff)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.2 - Bounding Box Functions

3.2.1 - func.ST_ZMin

Returns the Z minima of a 2D or 3D bounding box or a geometry

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_ZMin(box3d aGeomorBox2DorBox3D);

PlaidCloud

func.ST_ZMin(box3d aGeomorBox2DorBox3D)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.2.2 - func.ST_ZMax

Returns the Z maxima of a 2D or 3D bounding box or a geometry

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_ZMax(box3d aGeomorBox2DorBox3D);

PlaidCloud

func.ST_ZMax(box3d aGeomorBox2DorBox3D)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.2.3 - func.ST_YMin

Returns the Y minima of a 2D or 3D bounding box or a geometry

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_YMin(box3d aGeomorBox2DorBox3D);

PlaidCloud

func.ST_YMin(box3d aGeomorBox2DorBox3D)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.2.4 - func.ST_YMax

Returns the Y maxima of a 2D or 3D bounding box or a geometry

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_YMax(box3d aGeomorBox2DorBox3D);

PlaidCloud

func.ST_YMax(box3d aGeomorBox2DorBox3D)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.2.5 - func.ST_XMin

Returns the X minima of a 2D or 3D bounding box or a geometry

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_XMin(box3d aGeomorBox2DorBox3D);

PlaidCloud

func.ST_XMin(box3d aGeomorBox2DorBox3D)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.2.6 - func.ST_XMax

Returns the X maxima of a 2D or 3D bounding box or a geometry

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_XMax(box3d aGeomorBox2DorBox3D);

PlaidCloud

func.ST_XMax(box3d aGeomorBox2DorBox3D)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.2.7 - func.ST_3DMakeBox

Creates a BOX3D defined by the given two 3D point geometries

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_3DMakeBox(geometry point3DLowLeftBottom, geometry point3DUpRightTop);

PlaidCloud

func.ST_3DMakeBox(geometry point3DLowLeftBottom, geometry point3DUpRightTop)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.2.8 - func.ST_MakeBox2D

Creates a BOX2D defined by the given two point geometries

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_MakeBox2D(geometry pointLowLeft, geometry pointUpRight);

PlaidCloud

func.ST_MakeBox2D(geometry pointLowLeft, geometry pointUpRight)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.2.9 - func.ST_3DExtent

ST_3DExtent returns a box3d (includes Z coordinate) bounding box that encloses a set of geometries

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_3DExtent(geometry set geomfield);

PlaidCloud

func.ST_3DExtent(geometry set geomfield)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.2.10 - func.ST_Extent

ST_Extent returns a bounding box that encloses a set of geometries

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Extent(geometry set geomfield);

PlaidCloud

func.ST_Extent(geometry set geomfield)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.2.11 - func.ST_Expand

This function returns a bounding box expanded from the bounding box of the input

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Expand(geometry geom, float units_to_expand);

PlaidCloud

func.ST_Expand(geometry geom, float units_to_expand)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.2.12 - func.ST_EstimatedExtent

Return the 'estimated' extent of the given spatial table

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_EstimatedExtent(text table_name, text geocolumn_name);

PlaidCloud

func.ST_EstimatedExtent(text table_name, text geocolumn_name)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.2.13 - func.Box3D

Returns a BOX3D representing the 3D extent of the geometry

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

Box3D(geometry geomA);

PlaidCloud

func.Box3D(geometry geomA)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.2.14 - func.Box2D

Returns a BOX2D representing the 2D extent of the geometry

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

Box2D(geometry geomA);

PlaidCloud

func.Box2D(geometry geomA)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.3 - Clustering Functions

3.3.1 - func.ST_ClusterWithin

Aggregate function that returns an array of GeometryCollections that represent a set of geometries separated by a specified distance

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_ClusterWithin(geometry set g, float8 distance);

PlaidCloud

func.ST_ClusterWithin(geometry set g, float8 distance)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.3.2 - func.ST_ClusterIntersecting

ClusterIntersecting is an aggregate function that returns an array of GeometryCollections that represent an interconnected set of geometries

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_ClusterIntersecting(geometry set g);

PlaidCloud

func.ST_ClusterIntersecting(geometry set g)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.4 - Geometry Accessors

3.4.1 - func.ST_Zmflag

Returns a code indicating the ZM coordinate dimension of a geometry

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Zmflag(geometry geomA);

PlaidCloud

func.ST_Zmflag(geometry geomA)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.4.2 - func.ST_Z

Return the Z coordinate of the point, or NULL if not available. Input must be a point

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Z(geometry a_point);

PlaidCloud

func.ST_Z(geometry a_point)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.4.3 - func.ST_Y

Return the Y coordinate of the point, or NULL if not available. Input must be a point

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Y(geometry a_point);

PlaidCloud

func.ST_Y(geometry a_point)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.4.4 - func.ST_X

Return the X coordinate of the point, or NULL if not available. Input must be a point

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_X(geometry a_point);

PlaidCloud

func.ST_X(geometry a_point)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.4.5 - func.ST_Summary

Returns a text summary of the contents of the geometry

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Summary(geometry g);

PlaidCloud

func.ST_Summary(geometry g)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.4.6 - func.ST_StartPoint

Returns the first point of a LINESTRING or CIRCULARLINESTRING geometry as a POINT

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_StartPoint(geometry geomA);

PlaidCloud

ST_StartPoint(geometry geomA)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.4.7 - func.ST_PointN

Return the Nth point in a single linestring or circular linestring in the geometry

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_PointN(geometry a_linestring, integer n);

PlaidCloud

func.ST_PointN(geometry a_linestring, integer n)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.4.8 - func.ST_PatchN

Returns the 1-based Nth geometry (face) if the geometry is a POLYHEDRALSURFACE or POLYHEDRALSURFACEM

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_PatchN(geometry geomA, integer n);

PlaidCloud

ST_PatchN(geometry geomA, integer n)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.4.9 - func.ST_NumPoints

Return the number of points in an ST_LineString or ST_CircularString value

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_NumPoints(geometry g1);

PlaidCloud

func.ST_NumPoints(geometry g1)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.4.10 - func.ST_NumPatches

Return the number of faces on a Polyhedral Surface

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_NumPatches(geometry g1);

PlaidCloud

func.ST_NumPatches(geometry g1)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.4.11 - func.ST_NumInteriorRing

This function returns the number of interior rings of a polygon geom. It returns NULL if the geom is not a polygon.

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_NumInteriorRing(geometry a_polygon);

PlaidCloud

ST_NumInteriorRing(geometry a_polygon)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.4.12 - func.ST_NumInteriorRings

Return the number of interior rings of a polygon geometry

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_NumInteriorRings(geometry a_polygon);

PlaidCloud

func.ST_NumInteriorRings(geometry a_polygon)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.4.13 - func.ST_NumGeometries

Returns the number of Geometries

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_NumGeometries(geometry geom);

PlaidCloud

func.ST_NumGeometries(geometry geom)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.4.14 - func.ST_NRings

If the geometry is a polygon or multi-polygon returns the number of rings

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_NRings(geometry geomA);

PlaidCloud

func.ST_NRings(geometry geomA)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.4.15 - func.ST_NPoints

Return the number of points in a geometry

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_NPoints(geometry g1);

PlaidCloud

func.ST_NPoints(geometry g1)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.4.16 - func.ST_NDims

Returns the coordinate dimension of the geometry

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_NDims(geometry g1);

PlaidCloud

func.ST_NDims(geometry g1)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.4.17 - func.ST_MemSize

Returns the amount of memory space (in bytes) the geometry takes

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_MemSize(geometry geomA);

PlaidCloud

func.ST_MemSize(geometry geomA)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.4.18 - func.ST_M

Return the M coordinate of a Point, or NULL if not available. Input must be a Point

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_M(geometry a_point);

PlaidCloud

func.ST_M(geometry a_point)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.4.19 - func.ST_IsSimple

Returns true if this Geometry has no anomalous geometric points

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_IsSimple(geometry geomA);

PlaidCloud

func.ST_IsSimple(geometry geomA)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.4.20 - func.ST_IsRing

Returns TRUE if this LINESTRING is both ST_IsClosed and ST_IsSimple (does not self intersect)

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_IsRing(geometry g);

PlaidCloud

func.ST_IsRing(geometry g)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.4.21 - func.ST_IsCollection

Returns TRUE if the geometry type of the argument is a geometry collection type

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_IsCollection(geometry g);

PlaidCloud

func.ST_IsCollection(geometry g)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.4.22 - func.ST_IsClosed

Returns TRUE if the LINESTRING's start and end points are coincident

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_IsClosed(geometry g);

PlaidCloud

func.ST_IsClosed(geometry g)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.4.23 - func.ST_InteriorRingN

Returns the Nth interior linestring ring of the polygon geometry

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_InteriorRingN(geometry a_polygon, integer n);

PlaidCloud

func.ST_InteriorRingN(geometry a_polygon, integer n)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.4.24 - func.ST_HasArc

Returns true if a geometry or geometry collection contains a circular string

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_HasArc(geometry geomA);

PlaidCloud

func.ST_HasArc(geometry geomA)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.4.25 - func.ST_GeometryN

This section describes functions and operators for examining and manipulating string values

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_GeometryN(geometry geomA, integer n);

PlaidCloud

func.ST_GeometryN(geometry geomA, integer n)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.4.26 - func.ST_ExteriorRing

Returns a line string representing the exterior ring of the POLYGON geometry

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_ExteriorRing(geometry a_polygon);

PlaidCloud

func.ST_ExteriorRing(geometry a_polygon)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.4.27 - func.ST_Envelope

Returns the double-precision (float8) minimum bounding box for the supplied geometry, as a geometry

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Envelope(geometry g1);

PlaidCloud

func.ST_Envelope(geometry g1)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.4.28 - func.ST_BoundingDiagonal

Returns the diagonal of the supplied geometry's bounding box as a LineString

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_BoundingDiagonal(geometry geom, boolean fits=false);

PlaidCloud

func.ST_BoundingDiagonal(geometry geom, boolean fits=False)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.4.29 - func.ST_EndPoint

Returns the last point of a LINESTRING as a POINT. Returns NULL if the input is not a LINESTRING

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_EndPoint(geometry g);

PlaidCloud

func.ST_EndPoint(geometry g)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.4.30 - func.ST_DumpRings

This is a set-returning function (SRF). It returns a set of geometry_dump rows, as an integer and a geometry, aliased "path" and "geom"

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_DumpRings(geometry a_polygon);

PlaidCloud

func.ST_DumpRings(geometry a_polygon)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.4.31 - func.ST_DumpPoints

This set-returning function (SRF) returns a set of geometry_dump rows formed by a geometry (geom) and an array of integers (path)

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_DumpPoints(geometry geom);

PlaidCloud

func.ST_DumpPoints(geometry geom)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.4.32 - func.ST_Dump

This is a set-returning function (SRF). It returns a set of geometry_dump rows, formed by a geometry (geom) and an array of integers (path)

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Dump(geometry g1);

PlaidCloud

func.ST_Dump(geometry g1)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.4.33 - func.ST_Dimension

Return the topological dimension of this Geometry object, which must be less than or equal to the coordinate dimension

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Dimension(geometry g);

PlaidCloud

func.ST_Dimension(geometry g)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.4.34 - func.ST_CoordDim

Return the coordinate dimension of the ST_Geometry value

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_CoordDim(geometry geomA);

PlaidCloud

func.ST_CoordDim(geometry geomA)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.4.35 - func.ST_Boundary

Returns the closure of the combinatorial boundary of this Geometry

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Boundary(geometry geomA);

PlaidCloud

func.ST_Boundary(geometry geomA)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.4.36 - func.ST_GeometryType

Returns the type of the geometry as a string. 'ST_LineString', 'ST_Polygon','ST_MultiPolygon'

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_GeometryType(geometry g1);

PlaidCloud

func.ST_GeometryType(geometry g1)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.4.37 - func.ST_IsEmpty

Returns true if this Geometry is an empty geometry

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_IsEmpty(geometry geomA);

PlaidCloud

func.ST_IsEmpty(geometry geomA)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.5 - Geometry Constructors

3.5.1 - func.ST_Collect

Collects geometries into a geometry collection

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Collect(geometry g1, geometry g2)

PlaidCloud

func.ST_Collect(geometry g1, geometry g2)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.5.2 - func.ST_LineFromMultiPoint

Creates a LineString from a MultiPoint geometry

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_LineFromMultiPoint(geometry aMultiPoint); PlaidCloud

PlaidCloud

func.ST_LineFromMultiPoint(geometry aMultiPoint)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.5.3 - func.ST_MakeEnvelope

Creates a rectangular Polygon from the minimum and maximum values for X and Y

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_MakeEnvelope(float xmin, float ymin, float xmax, float ymax, integer srid=unknown);

PlaidCloud

func.ST_MakeEnvelope(float xmin, float ymin, float xmax, float ymax, integer srid=unknown);

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.5.4 - func.ST_MakeLine

Creates a LineString containing the points of Point, MultiPoint, or LineString geometries

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_MakeLine(geometry geom1, geometry geom2);

PlaidCloud

func.ST_MakeLine(geometry geom1, geometry geom2)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.5.5 - func.ST_MakePoint

Creates a 2D, 3D Z or 4D ZM Point geometry

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_MakePoint(float x, float y, float z, float m);

PlaidCloud

func.ST_MakePoint(float x, float y, float z, float m)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.5.6 - func.ST_MakePointM

Creates a point with X, Y and M (measure) coordinates

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_MakePointM(float x, float y, float m);

PlaidCloud

func.ST_MakePointM(float x, float y, float m)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.5.7 - func.ST_MakePolygon

Creates a Polygon formed by the given shell and optional array of holes

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_MakePolygon(geometry linestring);

PlaidCloud

func.ST_MakePolygon(geometry linestring)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.5.8 - func.ST_Point

Returns a Point with the given X and Y coordinate values

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Point(float x, float y);

PlaidCloud

func.ST_Point(float x, float y)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.5.9 - func.ST_Polygon

Returns a polygon built from the given LineString and sets the spatial reference system from the SRID

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Polygon(geometry lineString, integer srid);

PlaidCloud

func.ST_Polygon(geometry lineString, integer srid)

External References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.6 - Geometry Editors

3.6.1 - func.ST_SwapOrdinates

Returns a version of the given geometry with given ordinates swapped

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_SwapOrdinates(geometry geom, cstring ords);

PlaidCloud

func.ST_SwapOrdinates(geometry geom, cstring ords)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.6.2 - func.ST_Snap

Snaps the vertices and segments of a geometry to another Geometry's vertices

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Snap(geometry input, geometry reference, float tolerance);

PlaidCloud

func.ST_Snap(geometry input, geometry reference, float tolerance)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.6.3 - func.ST_SnapToGrid

Snap all points of the input geometry to the grid defined by its origin and cell size

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_SnapToGrid(geometry geomA, float originX, float originY, float sizeX, float sizeY);

PlaidCloud

func.ST_SnapToGrid(geometry geomA, float originX, float originY, float sizeX, float sizeY)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.6.4 - func.ST_ShiftLongitude

Reads every point/vertex in a geometry, and if the longitude coordinate is <0, adds 360 to it

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_ShiftLongitude(geometry geom);

PlaidCloud

func.ST_ShiftLongitude(geometry geom)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.6.5 - func.ST_SetPoint

Replace point N of linestring with given point

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_SetPoint(geometry linestring, integer zerobasedposition, geometry point);

PlaidCloud

func.ST_SetPoint(geometry linestring, integer zerobasedposition,   
geometry point)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.6.6 - func.ST_Segmentize

Returns a modified geometry having no segment longer than the given max_segment_length

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Segmentize(geometry geom, float max_segment_length);

PlaidCloud

func.ST_Segmentize(geometry geom, float max_segment_length)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.6.7 - func.ST_Reverse

Can be used on any geometry and reverses the order of the vertexes

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Reverse(geometry g1);

PlaidCloud

func.ST_Reverse(geometry g1)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.6.8 - func.ST_RemoveRepeatedPoints

Returns a version of the given geometry with duplicated points removed

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_RemoveRepeatedPoints(geometry geom, float8 tolerance);

PlaidCloud

func.ST_RemoveRepeatedPoints(geometry geom, float8 tolerance)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.6.9 - func.ST_RemovePoint

Remove a point from a linestring, given its 0-based index

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_RemovePoint(geometry linestring, integer offset);

PlaidCloud

func.ST_RemovePoint(geometry linestring, integer offset)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.6.10 - func.ST_Multi

Returns the geometry as a MULTI* geometry

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Multi(geometry g1);

PlaidCloud

func.ST_Multi(geometry g1)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.6.11 - func.ST_LineToCurve

Converts plain LINESTRING/POLYGON to CIRCULAR STRINGs and Curved Polygons

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_LineToCurve(geometry geomANoncircular);

PlaidCloud

func.ST_LineToCurve(geometry geomANoncircular)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.6.12 - func.ST_LineMerge

Returns a (set of) LineString(s) formed by sewing together the constituent line work of a MULTILINESTRING

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_LineMerge(geometry amultilinestring);

PlaidCloud

func.ST_LineMerge(geometry amultilinestring)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.6.13 - func.ST_ForceCurve

Turns a geometry into its curved representation

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_ForceCurve(geometry g);

PlaidCloud

func.ST_ForceCurve(geometry g)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.6.14 - func.ST_ForceRHR

Forces the orientation of the vertices in a polygon to follow the area that is bounded by the polygon is to the right of the boundary

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_ForceRHR(geometry g);

PlaidCloud

func.ST_ForceRHR(geometry g)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.6.15 - func.ST_ForceSFS

This function supports Polyhedral surfaces, Triangles and Triangulated Irregular Network Surfaces

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_ForceSFS(geometry geomA);

PlaidCloud

func.ST_ForceSFS(geometry geomA)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.6.16 - func.ST_ForceCollection

Converts the geometry into a GEOMETRYCOLLECTION

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_ForceCollection(geometry geomA);

PlaidCloud

func.ST_ForceCollection(geometry geomA)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.6.17 - func.ST_Force4D

Forces the geometries into XYZM mode

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Force4D(geometry geomA, float Zvalue = 0.0, float Mvalue = 0.0);

PlaidCloud

ST_Force4D(geometry geomA, float Zvalue = 0.0, float Mvalue = 0.0)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.6.18 - func.ST_Force3DM

Forces the geometries into XYM mode

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Force3DM(geometry geomA, float Mvalue = 0.0);

PlaidCloud

func.ST_Force3DM(geometry geomA, float Mvalue = 0.0)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.6.19 - func.ST_Force3DZ

This function forces the geoms into XYZ mode. If a geom has no 'Z' compenent, then a 'Z coordinate' is automatically added

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Force3DZ(geometry geomA, float Zvalue = 0.0);

PlaidCloud

func.ST_Force3DZ(geometry geomA, float Zvalue = 0.0)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.6.20 - func.ST_Force3D

Forces the geometries into XYZ mode

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Force3D(geometry geomA, float Zvalue = 0.0);

PlaidCloud

func.ST_Force3D(geometry geomA, float Zvalue = 0.0)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.6.21 - func.ST_Force2D

Forces the geometries into a "2-dimensional mode" so that all output representations will only have the X and Y coordinates

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Force2D(geometry geomA);

PlaidCloud

func.ST_Force2D(geometry geomA)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.6.22 - func.ST_FlipCoordinates

Returns a version of the given geometry with X and Y axis flipped

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_FlipCoordinates(geometry geom);

PlaidCloud

func.ST_FlipCoordinates(geometry geom)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.6.23 - func.ST_CurveToLine

Converts a CIRCULAR STRING to LINESTRING or CURVEPOLYGON to POLYGON or MULTISURFACE to MULTIPOLYGON

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_CurveToLine(geometry curveGeom, float tolerance, integer tolerance_type, integer flags);

PlaidCloud

func.ST_CurveToLine(geometry curveGeom, float tolerance, integer tolerance_type, integer flags)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.6.24 - func.ST_CollectionHomogenize

Given a geometry collection, returns the "simplest" representation of the contents

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_CollectionHomogenize(geometry collection);

PlaidCloud

func.ST_CollectionHomogenize(geometry collection)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.6.25 - func.ST_CollectionExtract

Given a geometry collection, return a homogeneous multi-geometry

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_CollectionExtract(geometry collection);

PlaidCloud

func.ST_CollectionExtract(geometry collection)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.6.26 - func.ST_AddPoint

Adds a point to a LineString before point (0-based index)

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_AddPoint(geometry linestring, geometry point);

PlaidCloud

func.ST_AddPoint(geometry linestring, geometry point)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.7 - Geometry Input

3.7.1 - func.ST_PointFromGeoHash

Return a point from a GeoHash string

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_PointFromGeoHash(text geohash, integer precision=full_precision_of_geohash);

PlaidCloud

func.ST_PointFromGeoHash(text geohash, integer precision=full_precision_of_geohash)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.7.2 - func.ST_LineFromEncodedPolyline

Creates a LineString from an Encoded Polyline string

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_LineFromEncodedPolyline(text polyline, integer precision=5);

PlaidCloud

func.ST_LineFromEncodedPolyline(text polyline, integer precision=5)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.7.3 - func.ST_GMLToSQL

This method implements the SQL/MM specification. SQL-MM 3 5.1.50 (except for curves support)

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_GMLToSQL(text geomgml);

PlaidCloud

func.ST_GMLToSQL(text geomgml)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.7.4 - func.ST_GeomFromKML

Constructs a PostGIS ST_Geometry object from the OGC KML representation

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_GeomFromKML(text geomkml);

PlaidCloud

func.ST_GeomFromKML(text geomkml)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.7.5 - func.ST_GeomFromGeoJSON

Constructs a PostGIS geometry object from the GeoJSON representation

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_GeomFromGeoJSON(text geomjson);

PlaidCloud

func.ST_GeomFromGeoJSON(text geomjson)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.7.6 - func.ST_GeomFromGML

Constructs a PostGIS ST_Geometry object from the OGC GML representation

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_GeomFromGML(text geomgml);

PlaidCloud

func.ST_GeomFromGML(text geomgml)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.7.7 - func.ST_GeomFromGeoHash

Return a geometry from a GeoHash string.

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_GeomFromGeoHash(text geohash, integer precision=full_precision_of_geohash);

PlaidCloud

func.ST_GeomFromGeoHash(text geohash, integer precision=full_precision_of_geohash)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.7.8 - func.ST_Box2dFromGeoHash

Return a BOX2D from a GeoHash string

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Box2dFromGeoHash(text geohash, integer precision=full_precision_of_geohash);

PlaidCloud

func.ST_Box2dFromGeoHash(text geohash, integer precision=full_precision_of_geohash)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.7.9 - func.ST_WKBToSQL

This method implements the SQL/MM specification. SQL-MM 3 5.1.36

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_WKBToSQL(bytea WKB);

PlaidCloud

func.ST_WKBToSQL(bytea WKB)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.7.10 - func.ST_PointFromWKB

This function, takes a binary representation of geometry and a (SRID) and creates the appropriate geometry type - POINT GEOMETRY

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_PointFromWKB(bytea wkb);

PlaidCloud

func.ST_PointFromWKB(bytea wkb);

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.7.11 - func.ST_LinestringFromWKB

This function, takes a binary representation of geometry and a (SRID) and creates the appropriate geometry type -LINESTRING GEOMETRY

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_LinestringFromWKB(bytea WKB);

PlaidCloud

func.ST_LinestringFromWKB(bytea WKB);

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.7.12 - func.ST_LineFromWKB

This function, takes a binary representation of geometry and a Spatial Reference System ID (SRID) and creates the appropriate geometry type

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_LineFromWKB(bytea WKB)

PlaidCloud

func.ST_LineFromWKB(bytea WKB)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.7.13 - func.ST_GeomFromWKB

This function takes a binary representation of a geometry and a Spatial Reference System ID (SRID) and creates the appropriate geometry type

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_GeomFromWKB(bytea geom);

PlaidCloud

func.ST_GeomFromWKB(bytea geom);

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.7.14 - func.ST_GeomFromEWKB

Constructs a PostGIS ST_Geometry object from the OGC Extended Well-Known binary (EWKT) representation

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_GeomFromEWKB(bytea EWKB);

PlaidCloud

func.ST_GeomFromEWKB(bytea EWKB)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.7.15 - func.ST_GeogFromWKB

This function, takes a well-known binary representation (WKB) of a geometry and creates an instance of the appropriate geography type

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_GeogFromWKB(bytea wkb);

PlaidCloud

func.ST_GeogFromWKB(bytea wkb)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.7.16 - func.ST_WKTToSQL

This method implements the SQL/MM specification. SQL-MM 3 5.1.34

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_WKTToSQL(text WKT);

PlaidCloud

func.ST_WKTToSQL(text WKT)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.7.17 - func.ST_PolygonFromText

Makes a Polygon Geometry from WKT with the given SRID

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_PolygonFromText(text WKT);

PlaidCloud

func.ST_PolygonFromText(text WKT)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.7.18 - func.ST_PointFromText

Constructs a PostGIS ST_Geometry point object from the OGC Well-Known text representation

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_PointFromText(text WKT);

PlaidCloud

func.ST_PointFromText(text WKT)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.7.19 - func.ST_MPolyFromText

Makes a MultiPolygon from WKT with the given SRID

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_MPolyFromText(text WKT);

PlaidCloud

func.ST_MPolyFromText(text WKT)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.7.20 - func.ST_MPointFromText

Makes a Multi-Point Geometry from WKT with the given SRID

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_MPointFromText(text WKT);

PlaidCloud

func.ST_MPointFromText(text WKT)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.7.21 - func.ST_MLineFromText

Makes a Multi-Line Geometry from Well-Known-Text (WKT) with the given SRID

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_MLineFromText(text WKT);

PlaidCloud

func.ST_MLineFromText(text WKT)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.7.22 - func.ST_LineFromText

Makes a Linestring Geometry from WKT with the given SRID

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_LineFromText(text WKT);

PlaidCloud

func.ST_LineFromText(text WKT)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.7.23 - func.ST_GeomFromText

Constructs a PostGIS ST_Geometry object from the OGC Well-Known text representation

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_GeomFromText(text WKT);

PlaidCloud

func.ST_GeomFromText(text WKT)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.7.24 - func.ST_GeometryFromText

This method implements the SQL/MM specification and OpenGIS simple features implementation

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_GeometryFromText(text WKT);

PlaidCloud

func.ST_GeometryFromText(text WKT)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.7.25 - func.ST_GeomFromEWKT

Constructs a PostGIS ST_Geometry object from the OGC Extended Well-Known text (EWKT) representation

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_GeomFromEWKT(text EWKT);

PlaidCloud

func.ST_GeomFromEWKT(text EWKT)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.7.26 - func.ST_GeomCollFromText

Makes a collection Geometry from the Well-Known-Text (WKT) representation with the given SRID

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_GeomCollFromText(text WKT, integer srid);

PlaidCloud

func.ST_GeomCollFromText(text WKT, integer srid)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.7.27 - func.ST_GeographyFromText

Returns a geography object from the well-known text representation. SRID 4326 is assumed if unspecified

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_GeographyFromText(text EWKT);

PlaidCloud

func.ST_GeographyFromText(text EWKT)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.7.28 - func.ST_GeogFromText

Returns a geography object from the well-known text or extended well-known representation

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_GeogFromText(text EWKT);

PlaidCloud

func.ST_GeogFromText(text EWKT)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.7.29 - func.ST_BdMPolyFromText

Construct a Polygon given an arbitrary collection of closed linestrings, polygons, MultiLineStrings as Well-Known text representation

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_BdMPolyFromText(text WKT, integer srid);

PlaidCloud

func.ST_BdMPolyFromText(text WKT, integer srid)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.7.30 - func.ST_BdPolyFromText

Construct a Polygon given an arbitrary collection of closed linestrings as a MultiLineString Well-Known text representation

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_BdPolyFromText(text WKT, integer srid);

PlaidCloud

func.ST_BdPolyFromText(text WKT, integer srid)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.7.31 - func.GeometryType

Returns the type of the geometry as a string

Syntax

func.GeometryType()

Examples

Documentation for func.GeometryType is coming soon.

References

PostgreSQL Documentation

3.8 - Geometry Output

3.8.1 - func.ST_GeoHash

Return a GeoHash representation (http://en.wikipedia.org/wiki/Geohash) of the geometry

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_GeoHash(geometry geom, integer maxchars=full_precision_of_point);

PlaidCloud

func.ST_GeoHash(geometry geom, integer maxchars=full_precision_of_point)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.8.2 - func.ST_AsX3D

Returns a geometry as an X3D xml formatted node element

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_AsX3D(geometry g1, integer maxdecimaldigits=15, integer options=0);

PlaidCloud

func.ST_AsX3D(geometry g1, integer maxdecimaldigits=15, integer options=0)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.8.3 - func.ST_AsSVG

Return the geometry as Scalar Vector Graphics (SVG) path data

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_AsSVG(geometry geom, integer rel=0, integer maxdecimaldigits=15);

PlaidCloud

func.ST_AsSVG(geometry geom, integer rel=0, integer maxdecimaldigits=15)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.8.4 - func.ST_AsTWKB

Returns the geometry in TWKB (Tiny Well-Known Binary) format

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_AsTWKB(geometry g1, integer decimaldigits_xy=0, integer decimaldigits_z=0, integer decimaldigits_m=0, boolean include_sizes=false, boolean include_bounding boxes=false);

PlaidCloud

func.ST_AsTWKB(geometry g1, integer decimaldigits_xy=0, integer decimaldigits_z=0, integer decimaldigits_m=0, boolean include_sizes=false, boolean include_bounding boxes=false)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.8.5 - func.ST_AsLatLonText

Returns the Degrees, Minutes, Seconds representation of the point

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_AsLatLonText(geometry pt, text format='');

PlaidCloud

func.ST_AsLatLonText(geometry pt, text format='')

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.8.6 - func.ST_AsKML

Return the geometry as a Keyhole Markup Language (KML) element

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_AsKML(geometry geom, integer maxdecimaldigits=15, text nprefix=NULL);

PlaidCloud

func.ST_AsKML(geometry geom, integer maxdecimaldigits=15, text nprefix=NULL)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.8.7 - func.ST_AsGML

Return the geometry as a Geography Markup Language (GML) element

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_AsGML(geometry geom, integer maxdecimaldigits=15, integer options=0);

PlaidCloud

func.ST_AsGML(geometry geom, integer maxdecimaldigits=15, integer options=0)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.8.8 - func.ST_AsGeoJSON

Return the geometry as a GeoJSON "geometry" object, or the row as a GeoJSON "feature" object

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_AsGeoJSON(geography geog, integer maxdecimaldigits=9, integer options=0);

PlaidCloud

func.ST_AsGeoJSON(geography geog, integer maxdecimaldigits=9, integer options=0)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.8.9 - func.ST_AsEncodedPolyline

Returns the geometry as an Encoded Polyline

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_AsEncodedPolyline(geometry geom, integer precision=5);

PlaidCloud

func.ST_AsEncodedPolyline(geometry geom, integer precision=5)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.8.10 - func.ST_AsHEXEWKB

Returns a Geometry in HEXEWKB format (as text) using either little-endian (NDR) or big-endian (XDR) encoding

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_AsHEXEWKB(geometry g1);

PlaidCloud

func.ST_AsHEXEWKB(geometry g1)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.8.11 - func.ST_AsEWKB

Returns the Well-Known Binary representation of the geometry with SRID metadata

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_AsEWKB(geometry g1);

PlaidCloud

func.ST_AsEWKB(geometry g1)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.8.12 - func.ST_AsBinary

Returns the Well-Known Binary representation of the geometry

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_AsBinary(geometry g1);

PlaidCloud

func.ST_AsBinary(geometry g1)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.8.13 - func.ST_AsText

Returns the Well-Known Text representation of the geometry/geography

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_AsText(geometry g1);

PlaidCloud

func.ST_AsText(geometry g1)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.8.14 - func.ST_AsEWKT

Returns the Well-Known Text representation of the geometry prefixed with the SRID

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_AsEWKT(geometry g1);

PlaidCloud

func.ST_AsEWKT(geometry g1)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.9 - Geometry Processing

3.9.1 - func.ST_SetEffectiveArea

Sets the effective area for each vertex, using the Visvalingam-Whyatt algorithm

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_SetEffectiveArea(geometry geomA, float threshold = 0, integer set_area = 1);

PlaidCloud

func.ST_SetEffectiveArea(geometry geomA, float threshold = 0, integer set_area = 1)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.9.2 - func.ST_SimplifyVW

Returns a "simplified" version of the given geometry using the Visvalingam-Whyatt algorithm

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_SimplifyVW(geometry geomA, float tolerance);

PlaidCloud

func.ST_SimplifyVW(geometry geomA, float tolerance)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.9.3 - func.ST_SimplifyPreserveTopology

Simplifies a geometry by removing points that would fall within a specified distance tolerance.

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_SimplifyPreserveTopology(geometry geomA, float tolerance);

PlaidCloud

func.ST_SimplifyPreserveTopology(geometry geomA, float tolerance)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.9.4 - func.ST_Simplify

Returns a "simplified" version of the given geometry using the Douglas-Peucker algorithm

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Simplify(geometry geomA, float tolerance, boolean preserveCollapsed);

PlaidCloud

func.ST_Simplify(geometry geomA, float tolerance, boolean preserveCollapsed)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.9.5 - func.ST_SharedPaths

Returns a collection containing paths shared by the two input geometries

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_SharedPaths(geometry lineal1, geometry lineal2);

PlaidCloud

func.ST_SharedPaths(geometry lineal1, geometry lineal2)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.9.6 - func.ST_Polygonize

Creates a GeometryCollection containing the polygons formed by the constituent linework of a set of geometries

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Polygonize(geometry set geomfield);

PlaidCloud

func.ST_Polygonize(geometry set geomfield)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.9.7 - func.ST_PointOnSurface

Returns a POINT guaranteed to intersect a surface

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_PointOnSurface(geometry g1);

PlaidCloud

func.ST_PointOnSurface(geometry g1)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.9.8 - func.ST_OffsetCurve

Return an offset line at a given distance and side from an input line

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_OffsetCurve(geometry line, float signed_distance, text style_parameters='');

PlaidCloud

func.ST_OffsetCurve(geometry line, float signed_distance, text style_parameters='')

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.9.9 - func.ST_MinimumBoundingCircle

Returns the smallest circle polygon that contains a geometry

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_MinimumBoundingCircle(geometry geomA, integer num_segs_per_qt_circ=48);

PlaidCloud

func.ST_MinimumBoundingCircle(geometry geomA, integer num_segs_per_qt_circ=48);

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.9.10 - func.ST_DelaunayTriangles

Return the Delaunay triangulation of the vertices of the input geometry

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_DelaunayTriangles(geometry g1, float tolerance, int4 flags);

PlaidCloud

func.ST_DelaunayTriangles(geometry g1, float tolerance, int4 flags)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.9.11 - func.ST_ConvexHull

Computes the convex hull of a geometry. The convex hull is the smallest convex geometry that encloses all geometries in the input

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_ConvexHull(geometry geomA);

PlaidCloud

func.ST_ConvexHull(geometry geomA)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.9.12 - func.ST_ConcaveHull

The concave hull of a geometry represents a possibly concave geometry that encloses the input geometry

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_ConcaveHull(geometry geom, float target_percent, boolean allow_holes = false);

PlaidCloud

func.ST_ConcaveHull(geometry geom, float target_percent, boolean allow_holes = false)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.9.13 - func.ST_Centroid

Computes a point which is the geometric center of mass of a geometry

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Centroid(geometry g1);

PlaidCloud

func.ST_Centroid(geometry g1);

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.9.14 - func.ST_BuildArea

Creates an areal geometry formed by the constituent linework of the input geometry

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_BuildArea(geometry geom);

PlaidCloud

func.ST_BuildArea(geometry geom)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.9.15 - func.ST_Buffer

Returns a geometry/geography that represents all points whose distance from this Geometry/geography is less than or equal to distance

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Buffer(geometry g1, float radius_of_buffer, text buffer_style_parameters = '');

PlaidCloud

func.ST_Buffer(geometry g1, float radius_of_buffer, text buffer_style_parameters = '')

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.9.16 - func.St_Accum

Aggregate. Constructs an array of geometries

Syntax

func.ST_Accum()

Examples

Documentation for func.ST_Accum is coming soon.

References

PostgreSQL Documentation

3.10 - Geometry Validation

3.10.1 - func.ST_MakeValid

The function attempts to create a valid representation of a given invalid geometry without losing any of the input vertices

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_MakeValid(geometry input);

PlaidCloud

func.ST_MakeValid(geometry input)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.10.2 - func.ST_IsValidReason

Returns text stating if a geometry is valid or not an if not valid, a reason why

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_IsValidReason(geometry geomA, integer flags);

PlaidCloud

func.ST_IsValidReason(geometry geomA, integer flags)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.10.3 - func.ST_IsValidDetail

Returns a valid_detail row, formed by a boolean (valid) stating whether the geometry is valid or invalid

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_IsValidDetail(geometry geom, integer flags);

PlaidCloud

func.ST_IsValidDetail(geometry geom, integer flags)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.10.4 - func.ST_IsValid

Test if an ST_Geometry value is well-formed in 2D according to the OGC rules

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_IsValid(geometry g);

PlaidCloud

func.ST_IsValid(geometry g)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.11 - Linear Referencing

3.11.1 - func.ST_AddMeasure

Return a derived geometry with measure elements linearly interpolated between the start and end points

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_AddMeasure(geometry geom_mline, float8 measure_start, float8 measure_end);

PlaidCloud

func.ST_AddMeasure(geometry geom_mline, float8 measure_start, float8 measure_end)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.11.2 - func.ST_InterpolatePoint

Return the value of the measure dimension of a geometry at the point closed to the provided point

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_InterpolatePoint(geometry line, geometry point);

PlaidCloud

func.ST_InterpolatePoint(geometry line, geometry point)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.11.3 - func.ST_LocateBetweenElevations

Return a derived geometry (collection) value with elements that intersect the specified range of elevations inclusively

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_LocateBetweenElevations(geometry geom, float8 elevation_start, float8 elevation_end);

PlaidCloud

func.ST_LocateBetweenElevations(geometry geom, float8 elevation_start, float8 elevation_end)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.11.4 - func.ST_LocateBetween

Return a derived geometry collection with elements that match the specified range of measures inclusively

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_LocateBetween(geometry geom, float8 measure_start, float8 measure_end, float8 offset);

PlaidCloud

func.ST_LocateBetween(geometry geom, float8 measure_start, float8 measure_end, float8 offset)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.11.5 - func.ST_LocateAlong

Return a derived geometry collection value with elements that match the specified measure

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_LocateAlong(geometry ageom_with_measure, float8 a_measure, float8 offset);

PlaidCloud

func.ST_LocateAlong(geometry ageom_with_measure, float8 a_measure, float8 offset)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.11.6 - func.ST_LineSubstring

Return a linestring being a substring of the input one starting and ending at the given fractions of total 2d length

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_LineSubstring(geometry a_linestring, float8 startfraction, float8 endfraction);

PlaidCloud

func.ST_LineSubstring(geometry a_linestring, float8 startfraction, float8 endfraction)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.11.7 - func.ST_LineLocatePoint

Returns a float between 0 and 1 representing the location of the closest point on LineString to the given Point

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_LineLocatePoint(geometry a_linestring, geometry a_point);

PlaidCloud

func.ST_LineLocatePoint(geometry a_linestring, geometry a_point)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.11.8 - func.ST_LineInterpolatePoint

Returns a point interpolated along a line

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_LineInterpolatePoint(geometry a_linestring, float8 a_fraction);

PlaidCloud

func.ST_LineInterpolatePoint(geometry a_linestring, float8 a_fraction)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.12 - Measurement Functions

3.12.1 - func.ST_ShortestLine

Returns the 2-dimensional shortest line between two geometries

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_ShortestLine(geometry g1, geometry g2);

PlaidCloud

func.ST_ShortestLine(geometry g1, geometry g2)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.12.2 - func.ST_Project

Returns a point projected from a start point along a geodesic using a given distance and azimuth (bearing)

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Project(geography g1, float distance, float azimuth);

PlaidCloud

func.ST_Project(geography g1, float distance, float azimuth)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.12.3 - func.ST_Perimeter2D

Returns the 2-dimensional perimeter of a polygonal geometry

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Perimeter2D(geometry geomA);

PlaidCloud

func.ST_Perimeter2D(geometry geomA)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.12.4 - func.ST_Perimeter

Returns the 2D perimeter of the geometry/geography if it is a ST_Surface, ST_MultiSurface (Polygon, MultiPolygon)

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Perimeter(geometry g1);

PlaidCloud

func.ST_Perimeter(geometry g1)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.12.5 - func.ST_MaxDistance

Returns the 2-dimensional maximum distance between two geometries, in projected units

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_MaxDistance(geometry g1, geometry g2);

PlaidCloud

func.ST_MaxDistance(geometry g1, geometry g2)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.12.6 - func.ST_LongestLine

Returns the 2-dimensional longest line between the points of two geometries

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_LongestLine(geometry g1, geometry g2);

PlaidCloud

func.ST_LongestLine(geometry g1, geometry g2)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.12.7 - func.ST_3DShortestLine

Returns the 3-dimensional shortest line between two geometries

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_3DShortestLine(geometry g1, geometry g2);

PlaidCloud

func.ST_3DShortestLine(geometry g1, geometry g2)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.12.8 - func.ST_3DPerimeter

Returns the 3-dimensional perimeter of the geometry, if it is a polygon or multi-polygon

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_3DPerimeter(geometry geomA);

PlaidCloud

func.ST_3DPerimeter(geometry geomA)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.12.9 - func.ST_3DMaxDistance

Returns the 3-dimensional maximum cartesian distance between two geometries in projected units

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_3DMaxDistance(geometry g1, geometry g2);

PlaidCloud

func.ST_3DMaxDistance(geometry g1, geometry g2)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.12.10 - func.ST_LengthSpheroid

Returns the 2D or 3D length/perimeter of a lon/lat geometry on a spheroid

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_LengthSpheroid(geometry a_geometry, spheroid a_spheroid);

PlaidCloud

func.ST_LengthSpheroid(geometry a_geometry, spheroid a_spheroid)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.12.11 - func.ST_3DLongestLine

Returns the 3-dimensional longest line between two geometries

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_3DLongestLine(geometry g1, geometry g2);

PlaidCloud

func.ST_3DLongestLine(geometry g1, geometry g2)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.12.12 - func.ST_3DLength

Returns the 3-dimensional or 2-dimensional length of the geometry if it is a linestring or multi-linestring

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_3DLength(geometry a_3dlinestring);

PlaidCloud

func.ST_3DLength(geometry a_3dlinestring)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.12.13 - func.ST_Length2D

Returns the 2D length of the geometry if it is a linestring or multi-linestring

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Length2D(geometry a_2dlinestring);

PlaidCloud

func.ST_Length2D(geometry a_2dlinestring)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.12.14 - func.ST_Length

Returns the 2D Cartesian length of the geometry for geometry types and uses the inverse geodesic calculation for geography types

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Length(geometry a_2dlinestring);

PlaidCloud

func.ST_Length(geometry a_2dlinestring)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.12.15 - func.ST_HausdorffDistance

Returns the Hausdorff distance between two geometries, a measure of how similar or dissimilar 2 geometries are

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_HausdorffDistance(geometry g1, geometry g2);

PlaidCloud

func.ST_HausdorffDistance(geometry g1, geometry g2)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.12.16 - func.ST_DistanceSpheroid

Returns minimum distance in meters between two lon/lat geometries given a particular spheroid

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_DistanceSpheroid(geometry geomlonlatA, geometry geomlonlatB, spheroid measurement_spheroid);

PlaidCloud

func.ST_DistanceSpheroid(geometry geomlonlatA, geometry geomlonlatB, spheroid measurement_spheroid)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.12.17 - func.ST_Distance

For geometry types returns the minimum 2D Cartesian (planar) distance between two geometries, in projected units (spatial ref units)

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Distance(geometry g1, geometry g2);

PlaidCloud

func.ST_Distance(geometry g1, geometry g2)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.12.18 - func.ST_3DClosestPoint

Returns the 3-dimensional point on g1 that is closest to g2

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_3DClosestPoint(geometry g1, geometry g2);

PlaidCloud

func.ST_3DClosestPoint(geometry g1, geometry g2)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.12.19 - func.ST_ClosestPoint

Returns the 2-dimensional point on g1 that is closest to g2

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_ClosestPoint(geometry g1, geometry g2);

PlaidCloud

func.ST_ClosestPoint(geometry g1, geometry g2)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.12.20 - func.ST_Azimuth

Returns the azimuth in radians of the segment defined by the given point geometries, or NULL if the two points are coincident

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Azimuth(geometry pointA, geometry pointB);

PlaidCloud

func.ST_Azimuth(geometry pointA, geometry pointB)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.12.21 - func.ST_Area

Returns the area of a polygonal geometry

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Area(geometry g1);

PlaidCloud

func.ST_Area(geometry g1)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.12.22 - func.ST_Length2D_Spheroid

Returns the 2D length or perimeter of the geometry on a spheroid

Syntax

func.ST_Length2D_Spheroid()

Examples

Documentation for func.ST_Length2D_Spheroid is coming soon.

References

PostgreSQL Documentation

3.13 - Overlay Functions

3.13.1 - func.ST_UnaryUnion

A single-input variant of ST_Union

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_UnaryUnion(geometry geom, float8 gridSize = -1);

PlaidCloud

func.ST_UnaryUnion(geometry geom, float8 gridSize = -1);

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.13.2 - func.ST_Union

Unions the input geometries, merging geometry to produce a result geometry with no overlaps

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Union(geometry g1, geometry g2);

PlaidCloud

func.ST_Union(geometry g1, geometry g2)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.13.3 - func.ST_SymDifference

Returns a geometry representing the portions of geonetries A and B that do not intersect

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_SymDifference(geometry geomA, geometry geomB, float8 gridSize = -1);

PlaidCloud

func.ST_SymDifference(geometry geomA, geometry geomB, float8 gridSize = -1)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.13.4 - func.ST_Subdivide

Divides geometry into parts using rectilinear lines, until each part can be represented using no more than max_vertices

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Subdivide(geometry geom, integer max_vertices=256, float8 gridSize = -1);

PlaidCloud

func.ST_Subdivide(geometry geom, integer max_vertices=256, float8 gridSize = -1)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.13.5 - func.ST_Split

The function supports splitting a line by a (multi)point, (multi)line or (multi)polygon boundary, or a (multi)polygon by line

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Split(geometry input, geometry blade);

PlaidCloud

func.ST_Split(geometry input, geometry blade)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.13.6 - func.ST_Node

Returns a (Multi)LineString representing the fully noded version of a collection of linestrings

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Node(geometry geom);

PlaidCloud

func.ST_Node(geometry geom)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.13.7 - func.ST_MemUnion

Aggregate function which unions geometry in a memory-efficent but slower way

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_MemUnion(geometry set geomfield);

PlaidCloud

func.ST_MemUnion(geometry set geomfield)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.13.8 - func.ST_Intersection

Returns a geometry representing the point-set intersection of two geometries

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Intersection( geography geogA , geography geogB );

PlaidCloud

func.ST_Intersection( geography geogA , geography geogB )

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.13.9 - func.ST_Difference

Returns a geometry representing the part of geometry A that does not intersect geometry B

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Difference(geometry geomA, geometry geomB, float8 gridSize = -1);

PlaidCloud

func.ST_Difference(geometry geomA, geometry geomB, float8 gridSize = -1)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.13.10 - func.ST_ClipByBox2D

Clips a geometry by a 2D box in a fast and tolerant but possibly invalid way

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_ClipByBox2D(geometry geom, box2d box);

PlaidCloud

func.ST_ClipByBox2D(geometry geom, box2d box)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.14 - Spatial Reference System Functions

3.14.1 - func.ST_Transform

Returns a new geometry with its coordinates transformed to a different spatial reference system

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Transform(geometry g1, integer srid);

PlaidCloud

func.ST_Transform(geometry g1, integer srid)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.14.2 - func.ST_SRID

Returns the spatial reference identifier for the ST_Geometry as defined in spatial_ref_sys table

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_SRID(geometry g1);

PlaidCloud

func.ST_SRID(geometry g1)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.14.3 - func.ST_SetSRID

Sets the SRID on a geometry to a particular integer value

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_SetSRID(geometry geom, integer srid);

PlaidCloud

func.ST_SetSRID(geometry geom, integer srid)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.14.4 - func.Find_SRID

Returns the integer SRID of the specified geometry column

Syntax

func.Find_SRID()

Examples

Documentation for func.Find_SRID is coming soon.

References

PostgreSQL Documentation

3.15 - Spatial Relationships

3.15.1 - func.ST_PointInsideCircle

Returns true if the geometry is a point and is inside the circle with center center_x,center_y and radius radius

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_PointInsideCircle(geometry a_point, float center_x, float center_y, float radius);

PlaidCloud

func.ST_PointInsideCircle(geometry a_point, float center_x, float center_y, float radius)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.15.2 - func.ST_DWithin

Returns true if the geometries are within a given distance

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_DWithin(geometry g1, geometry g2, double precision distance_of_srid);

PlaidCloud

func.ST_DWithin(geometry g1, geometry g2, double precision distance_of_srid)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.15.3 - func.ST_DFullyWithin

Returns true if the geometries are entirely within the specified distance of one another

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_DFullyWithin(geometry g1, geometry g2, double precision distance);

PlaidCloud

func.ST_DFullyWithin(geometry g1, geometry g2, double precision distance)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.15.4 - func.ST_Within

Returns TRUE if geometry A is completely inside geometry B

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Within(geometry A, geometry B);

PlaidCloud

func.ST_Within(geometry A, geometry B)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.15.5 - func.ST_Touches

Returns TRUE if the only points in common between g1 and g2 lie in the union of the boundaries of g1 and g2

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Touches(geometry g1, geometry g2);

PlaidCloud

func.ST_Touches(geometry g1, geometry g2)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.15.6 - func.ST_RelateMatch

Tests if a Dimensionally Extended 9-Intersection Model (DE-9IM) intersectionMatrix value satisfies an intersectionMatrixPattern

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_RelateMatch(text intersectionMatrix, text intersectionMatrixPattern);

PlaidCloud

func.ST_RelateMatch(text intersectionMatrix, text intersectionMatrixPattern)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.15.7 - func.ST_Relate

These functions allow testing and evaluating the spatial (topological) relationship between two geometries

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Relate(geometry geomA, geometry geomB);

PlaidCloud

func.ST_Relate(geometry geomA, geometry geomB)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.15.8 - func.ST_OrderingEquals

Compares two geometries and returns it (TRUE) if the geometries are equal and the coordinates are in the same order

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_OrderingEquals(geometry A, geometry B);

PlaidCloud

func.ST_OrderingEquals(geometry A, geometry B)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.15.9 - func.ST_Overlaps

Returns TRUE if the Geometries "spatially overlap"

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Overlaps(geometry A, geometry B);

PlaidCloud

func.ST_Overlaps(geometry A, geometry B)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.15.10 - func.ST_Intersects

If a geometry or geography shares any portion of space then they intersect

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Intersects( geometry geomA , geometry geomB );

PlaidCloud

func.ST_Intersects( geometry geomA , geometry geomB )

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.15.11 - func.ST_Equals

Returns TRUE if the given Geometries are "spatially equal"

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Equals(geometry A, geometry B);

PlaidCloud

func.ST_Equals(geometry A, geometry B)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.15.12 - func.ST_Disjoint

Overlaps, Touches, Within imply geometries are not spatially disjoint, unless they return true, then they are not spatially disjoint

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Disjoint( geometry A , geometry B );

PlaidCloud

func.ST_Disjoint( geometry A , geometry B )

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.15.13 - func.ST_LineCrossingDirection

Given 2 linestrings, returns an integer between -3 and 3 indicating what kind of crossing behavior exists between them

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_LineCrossingDirection(geometry linestringA, geometry linestringB);

PlaidCloud

func.ST_LineCrossingDirection(geometry linestringA, geometry linestringB)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.15.14 - func.ST_3DDWithin

For geometry type returns true if the 3d distance between two objects is within distance_of_srid specified projected units

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_3DDWithin(geometry g1, geometry g2, double precision distance_of_srid);

PlaidCloud

func.ST_3DDWithin(geometry g1, geometry g2, double precision distance_of_srid)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.15.15 - func.ST_Crosses

Takes two geometry objects and returns TRUE if their intersection "spatially cross", but not all interior points in common

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Crosses(geometry g1, geometry g2);

PlaidCloud

func.ST_Crosses(geometry g1, geometry g2)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.15.16 - func.ST_3DDFullyWithin

Returns true if the 3D geometries are fully within the specified distance of one another

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_3DDFullyWithin(geometry g1, geometry g2, double precision distance);

PlaidCloud

func.ST_3DDFullyWithin(geometry g1, geometry g2, double precision distance)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.15.17 - func.ST_CoveredBy

Returns 1 (TRUE) if no point in Geometry/Geography A is outside Geometry/Geography B

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_CoveredBy(geometry geomA, geometry geomB);

PlaidCloud

func.ST_CoveredBy(geometry geomA, geometry geomB)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.15.18 - func.ST_ContainsProperly

Returns true if B intersects the interior of A but not the boundary (or exterior)

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_ContainsProperly(geometry geomA, geometry geomB);

PlaidCloud

func.ST_ContainsProperly(geometry geomA, geometry geomB);

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.15.19 - func.ST_Covers

Returns 1 (TRUE) if no point in Geometry/Geography B is outside Geometry/Geography A

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Covers(geometry geomA, geometry geomB);

PlaidCloud

func.ST_Covers(geometry geomA, geometry geomB)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.15.20 - func.ST_Contains

Geo 'A' contains Geo 'B' ONLY IF no points of B lie in the exterior of A, and at least one point of B interior lies in A interior

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_Contains(geometry geomA, geometry geomB);

PlaidCloud

func.ST_Contains(geometry geomA, geometry geomB)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.15.21 - func.ST_3DIntersects

Overlaps, Touches, Within all imply spatial intersection and if any returns true, then the geometries also spatially intersect

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_3DIntersects( geometry geomA , geometry geomB );

PlaidCloud

func.ST_3DIntersects( geometry geomA , geometry geomB )

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation

3.16 - Trajectory Functions

3.16.1 - func.ST_DistanceCPA

Returns the minimum distance two moving objects have ever been each other

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_DistanceCPA(geometry track1, geometry track2);

PlaidCloud

func.ST_DistanceCPA(geometry track1, geometry track2)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.16.2 - func.ST_CPAWithin

Checks whether two moving objects have ever been within the specified maximum distance

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_CPAWithin(geometry track1, geometry track2, float8 maxdist);

PlaidCloud

func.ST_CPAWithin(geometry track1, geometry track2, float8 maxdist)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.16.3 - func.ST_ClosestPointOfApproach

Returns the smallest measure at which points interpolated along the given trajectories are at the smallest distance

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_ClosestPointOfApproach(geometry track1, geometry track2);

PlaidCloud

func.ST_ClosestPointOfApproach(geometry track1, geometry track2)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.

3.16.4 - func.ST_IsValidTrajectory

Tests if a geometry encodes a valid trajectory

Description

PlaidCloud expressions and filters provide use of most non-administrative PostGIS methods. PostGIS methods are accessed by prefixing the standard method name with func..

Examples

SQL

ST_IsValidTrajectory(geometry line);

PlaidCloud

func.ST_IsValidTrajectory(geometry line)

References

PostGIS Official Documentation for this method can be found here.

Additional capabilities and usage examples can be found in the PostGIS documentation.