Chapter 6. PostGIS Reference

Table of Contents

6.1. OpenGIS Functions
6.1.1. Management Functions
6.1.2. Geometry Relationship Functions
6.1.3. Geometry Processing Functions
6.1.4. Geometry Accessors
6.1.5. Geometry Constructors
6.2. Postgis Extensions
6.2.1. Management Functions
6.2.2. Operators
6.2.3. Measurement Functions
6.2.4. Geometry Outputs
6.2.5. Geometry Constructors
6.2.6. Geometry Editors
6.2.7. Linear Referencing
6.2.8. Misc

The functions given below are the ones which a user of PostGIS is likely to need. There are other functions which are required support functions to the PostGIS objects which are not of use to a general user.

6.1. OpenGIS Functions

6.1.1. Management Functions

AddGeometryColumn(varchar, varchar, varchar, integer, varchar, integer)

Syntax: AddGeometryColumn(<schema_name>, <table_name>, <column_name>, <srid>, <type>, <dimension>). Adds a geometry column to an existing table of attributes. The schema_name is the name of the table schema (unused for pre-schema PostgreSQL installations). The srid must be an integer value reference to an entry in the SPATIAL_REF_SYS table. The type must be an uppercase string corresponding to the geometry type, eg, 'POLYGON' or 'MULTILINESTRING'.

DropGeometryColumn(varchar, varchar, varchar)

Syntax: DropGeometryColumn(<schema_name>, <table_name>, <column_name>). Remove a geometry column from a spatial table. Note that schema_name will need to match the f_schema_name field of the table's row in the geometry_columns table.

SetSRID(geometry, integer)

Set the SRID on a geometry to a particular integer value. Useful in constructing bounding boxes for queries.

6.1.2. Geometry Relationship Functions

Distance(geometry, geometry)

Return the cartesian distance between two geometries in projected units.

Equals(geometry, geometry)

Returns 1 (TRUE) if the given Geometries are "spatially equal". Use this for a 'better' answer than '='. equals('LINESTRING(0 0, 10 10)','LINESTRING(0 0, 5 5, 10 10)') is true.

Performed by the GEOS module

OGC SPEC s2.1.1.2

Disjoint(geometry, geometry)

Returns 1 (TRUE) if the Geometries are "spatially disjoint".

Performed by the GEOS module

Do not call with a GeometryCollection as an argument

NOTE: this is the "allowable" version that returns a boolean, not an integer.

OGC SPEC s2.1.1.2 //s2.1.13.3 - a.Relate(b, 'FF*FF****')

Intersects(geometry, geometry)

Returns 1 (TRUE) if the Geometries "spatially intersect".

Performed by the GEOS module

Do not call with a GeometryCollection as an argument

NOTE: this is the "allowable" version that returns a boolean, not an integer.

OGC SPEC s2.1.1.2 //s2.1.13.3 - Intersects(g1, g2 ) --> Not (Disjoint(g1, g2 ))

Touches(geometry, geometry)

Returns 1 (TRUE) if the Geometries "spatially touch".

Performed by the GEOS module

Do not call with a GeometryCollection as an argument

NOTE: this is the "allowable" version that returns a boolean, not an integer.

OGC SPEC s2.1.1.2 // s2.1.13.3- a.Touches(b) -> (I(a) intersection I(b) = {empty set} ) and (a intersection b) not empty

Crosses(geometry, geometry)

Returns 1 (TRUE) if the Geometries "spatially cross".

Performed by the GEOS module

Do not call with a GeometryCollection as an argument

NOTE: this is the "allowable" version that returns a boolean, not an integer.

OGC SPEC s2.1.1.2 // s2.1.13.3 - a.Relate(b, 'T*T******')

Within(geometry A, geometry B)

Returns 1 (TRUE) if Geometry A is "spatially within" Geometry B.

Performed by the GEOS module

Do not call with a GeometryCollection as an argument

NOTE: this is the "allowable" version that returns a boolean, not an integer.

OGC SPEC s2.1.1.2 // s2.1.13.3 - a.Relate(b, 'T*F**F***')

Overlaps(geometry, geometry)

Returns 1 (TRUE) if the Geometries "spatially overlap".

Performed by the GEOS module

Do not call with a GeometryCollection as an argument

NOTE: this is the "allowable" version that returns a boolean, not an integer.

OGC SPEC s2.1.1.2 // s2.1.13.3

Contains(geometry A, geometry B)

Returns 1 (TRUE) if Geometry A "spatially contains" Geometry B.

Performed by the GEOS module

Do not call with a GeometryCollection as an argument

NOTE: this is the "allowable" version that returns a boolean, not an integer.

OGC SPEC s2.1.1.2 // s2.1.13.3 - same as within(geometry B, geometry A)

Intersects(geometry, geometry)

Returns 1 (TRUE) if the Geometries "spatially intersect".

Performed by the GEOS module

Do not call with a GeometryCollection as an argument

NOTE: this is the "allowable" version that returns a boolean, not an integer.

OGC SPEC s2.1.1.2 // s2.1.13.3 - NOT disjoint(geometry, geometry)

Relate(geometry, geometry, intersectionPatternMatrix)

Returns 1 (TRUE) if this Geometry is spatially related to anotherGeometry, by testing for intersections between the Interior, Boundary and Exterior of the two geometries as specified by the values in the intersectionPatternMatrix.

Performed by the GEOS module

Do not call with a GeometryCollection as an argument

NOTE: this is the "allowable" version that returns a boolean, not an integer.

OGC SPEC s2.1.1.2 // s2.1.13.3

Relate(geometry, geometry)

returns the DE-9IM (dimensionally extended nine-intersection matrix)

Performed by the GEOS module

Do not call with a GeometryCollection as an argument

not in OGC spec, but implied. see s2.1.13.2

6.1.3. Geometry Processing Functions

Centroid(geometry)

Returns the centroid of the geometry as a point.

Computation will be more accurate if performed by the GEOS module (enabled at compile time).

Area(geometry)

Returns the area of the geometry if it is a polygon or multi-polygon.

Length(geometry)

The length of this Curve in its associated spatial reference.

synonym for length2d()

OGC SPEC 2.1.5.1

PointOnSurface(geometry)

Return a Point guaranteed to lie on the surface

Implemented using GEOS

OGC SPEC 3.2.14.2 and 3.2.18.2 -

Boundary(geometry)

Returns the closure of the combinatorial boundary of this Geometry. The combinatorial boundary is defined as described in section 3.12.3.2 of the OGC SPEC. Because the result of this function is a closure, and hence topologically closed, the resulting boundary can be represented using representational geometry primitives as discussed in the OGC SPEC, section 3.12.2.

Performed by the GEOS module

OGC SPEC s2.1.1.1

Buffer(geometry, double, [integer])

Returns a geometry that represents all points whose distance from this Geometry is less than or equal to distance. Calculations are in the Spatial Reference System of this Geometry. The optional third parameter sets the number of segment used to approximate a quarter circle (defaults to 8).

Performed by the GEOS module

Do not call with a GeometryCollection as an argument

OGC SPEC s2.1.1.3

ConvexHull(geometry)

Returns a geometry that represents the convex hull of this Geometry.

Performed by the GEOS module

OGC SPEC s2.1.1.3

Intersection(geometry, geometry)

Returns a geometry that represents the point set intersection of the Geometies.

Performed by the GEOS module

Do not call with a GeometryCollection as an argument

OGC SPEC s2.1.1.3

SymDifference(geometry A, geometry B)

Returns a geometry that represents the point set symmetric difference of Geometry A with Geometry B.

Performed by the GEOS module

Do not call with a GeometryCollection as an argument

OGC SPEC s2.1.1.3

Difference(geometry A, geometry B)

Returns a geometry that represents the point set symmetric difference of Geometry A with Geometry B.

Performed by the GEOS module

Do not call with a GeometryCollection as an argument

OGC SPEC s2.1.1.3

GeomUnion(geometry, geometry)

Returns a geometry that represents the point set union of the Geometries.

Performed by the GEOS module

Do not call with a GeometryCollection as an argument

NOTE: this is renamed from "union" because union is an SQL reserved word

OGC SPEC s2.1.1.3

GeomUnion(geometry set)

Returns a geometry that represents the point set union of this all Geometries in given set.

Performed by the GEOS module

Do not call with a GeometryCollection in the argument set

Not explicitly defined in OGC SPEC

MemGeomUnion(geometry set)

Same as the above, only memory-friendly (uses less memory and more processor time).

6.1.4. Geometry Accessors

AsText(geometry)

Return the Well-Known Text representation of the geometry. For example: POLYGON(0 0,0 1,1 1,1 0,0 0)

OGC SPEC s2.1.1.1

AsBinary(geometry)

Returns the geometry in the OGC "well-known-binary" format, using the endian encoding of the server on which the database is running. This is useful in binary cursors to pull data out of the database without converting it to a string representation.

OGC SPEC s2.1.1.1 - also see asBinary(<geometry>,'XDR') and asBinary(<geometry>,'NDR')

SRID(geometry)

Returns the integer SRID number of the spatial reference system of the geometry.

OGC SPEC s2.1.1.1

Dimension(geometry)

The inherent dimension of this Geometry object, which must be less than or equal to the coordinate dimension. OGC SPEC s2.1.1.1 - returns 0 for points, 1 for lines, 2 for polygons, and the largest dimension of the components of a GEOMETRYCOLLECTION.

select dimension('GEOMETRYCOLLECTION(LINESTRING(1 1,0 0),POINT(0 0)'); 
dimension 
-----------
1
Envelope(geometry)

Returns a POLYGON representing the bounding box of the geometry.

OGC SPEC s2.1.1.1 - The minimum bounding box for this Geometry, returned as a Geometry. The polygon is defined by the corner points of the bounding box ((MINX, MINY), (MAXX, MINY), (MAXX, MAXY), (MINX, MAXY), (MINX, MINY)).

NOTE:PostGIS will add a Zmin/Zmax coordinate as well.

IsEmpty(geometry)

Returns 1 (TRUE) if this Geometry is the empty geometry . If true, then this Geometry represents the empty point set - i.e. GEOMETRYCOLLECTION(EMPTY).

OGC SPEC s2.1.1.1

IsSimple(geometry)

Returns 1 (TRUE) if this Geometry has no anomalous geometric points, such as self intersection or self tangency.

Performed by the GEOS module

OGC SPEC s2.1.1.1

IsClosed(geometry)

Returns true of the geometry start and end points are coincident.

IsRing(geometry)

Returns 1 (TRUE) if this Curve is closed (StartPoint ( ) = EndPoint ( )) and this Curve is simple (does not pass through the same point more than once).

performed by GEOS

OGC spec 2.1.5.1

NumGeometries(geometry)

If geometry is a GEOMETRYCOLLECTION (or MULTI*) return the number of geometries, otherwise return NULL.

GeometryN(geometry,int)

Return the N'th geometry if the geometry is a GEOMETRYCOLLECTION, MULTIPOINT, MULTILINESTRING or MULTIPOLYGON. Otherwise, return NULL.

Note

Index is 1-based as for OGC specs since version 0.8.0. Previous versions implemented this as 0-based instead.

NumPoints(geometry)

Find and return the number of points in the first linestring in the geometry. Return NULL if there is no linestring in the geometry.

PointN(geometry,integer)

Return the N'th point in the first linestring in the geometry. Return NULL if there is no linestring in the geometry.

Note

Index is 1-based as for OGC specs since version 0.8.0. Previous versions implemented this as 0-based instead.

ExteriorRing(geometry)

Return the exterior ring of the polygon geometry. Return NULL if the geometry is not a polygon.

NumInteriorRings(geometry)

Return the number of interior rings of the first polygon in the geometry. Return NULL if there is no polygon in the geometry.

InteriorRingN(geometry,integer)

Return the N'th interior ring of the polygon geometry. Return NULL if the geometry is not a polygon or the given N is out of range.

Note

Index is 1-based as for OGC specs since version 0.8.0. Previous versions implemented this as 0-based instead.

EndPoint(geometry)

Returns the last point of the LineString geometry as a point.

StartPoint(geometry)

Returns the first point of the LineString geometry as a point.

GeometryType(geometry)

Returns the type of the geometry as a string. Eg: 'LINESTRING', 'POLYGON', 'MULTIPOINT', etc.

OGC SPEC s2.1.1.1 - Returns the name of the instantiable subtype of Geometry of which this Geometry instance is a member. The name of the instantiable subtype of Geometry is returned as a string.

X(geometry)

Return the X coordinate of the point. Input must be a point.

Y(geometry)

Return the Y coordinate of the point. Input must be a point.

Z(geometry)

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

M(geometry)

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

Note

This is not (yet) part of the OGC spec, but is listed here to complete the point coordinate extractor function list.

6.1.5. Geometry Constructors

GeomFromText(text,[<srid>])

Makes a Geometry from WKT with the given SRID.

OGC SPEC 3.2.6.2 - option SRID is from the conformance suite

PointFromText(text,[<srid>])

Makes a Geometry from WKT with the given SRID. If SRID is not give, it defaults to -1.

OGC SPEC 3.2.6.2 - option SRID is from the conformance suite

Throws an error if the WKT is not a Point

LineFromText(text,[<srid>])

Makes a Geometry from WKT with the given SRID. If SRID is not give, it defaults to -1.

OGC SPEC 3.2.6.2 - option SRID is from the conformance suite

Throws an error if the WKT is not a Line

LinestringFromText(text,[<srid>])

Makes a Geometry from WKT with the given SRID. If SRID is not give, it defaults to -1.

from the conformance suite

Throws an error if the WKT is not a Line

PolyFromText(text,[<srid>])

Makes a Geometry from WKT with the given SRID. If SRID is not give, it defaults to -1.

OGC SPEC 3.2.6.2 - option SRID is from the conformance suite

Throws an error if the WKT is not a Polygon

PolygonFromText(text,[<srid>])

Makes a Geometry from WKT with the given SRID. If SRID is not give, it defaults to -1.

from the conformance suite

Throws an error if the WKT is not a Polygon

MPointFromText(text,[<srid>])

Makes a Geometry from WKT with the given SRID. If SRID is not give, it defaults to -1.

OGC SPEC 3.2.6.2 - option SRID is from the conformance suite

Throws an error if the WKT is not a MULTIPOINT

MLineFromText(text,[<srid>])

Makes a Geometry from WKT with the given SRID. If SRID is not give, it defaults to -1.

OGC SPEC 3.2.6.2 - option SRID is from the conformance suite

Throws an error if the WKT is not a MULTILINESTRING

MPolyFromText(text,[<srid>])

Makes a Geometry from WKT with the given SRID. If SRID is not give, it defaults to -1.

OGC SPEC 3.2.6.2 - option SRID is from the conformance suite

Throws an error if the WKT is not a MULTIPOLYGON

GeomCollFromText(text,[<srid>])

Makes a Geometry from WKT with the given SRID. If SRID is not give, it defaults to -1.

OGC SPEC 3.2.6.2 - option SRID is from the conformance suite

Throws an error if the WKT is not a GEOMETRYCOLLECTION

GeomFromWKB(bytea,[<srid>])

Makes a Geometry from WKB with the given SRID. If SRID is not give, it defaults to -1.

OGC SPEC 3.2.6.2 - option SRID is from the conformance suite

GeomFromWKB(bytea,[<srid>])

Makes a Geometry from WKB with the given SRID. If SRID is not give, it defaults to -1.

OGC SPEC 3.2.7.2 - option SRID is from the conformance suite

PointFromWKB(bytea,[<srid>])

Makes a Geometry from WKB with the given SRID. If SRID is not give, it defaults to -1.

OGC SPEC 3.2.7.2 - option SRID is from the conformance suite

throws an error if WKB is not a POINT

LineFromWKB(bytea,[<srid>])

Makes a Geometry from WKB with the given SRID. If SRID is not give, it defaults to -1.

OGC SPEC 3.2.7.2 - option SRID is from the conformance suite

throws an error if WKB is not a LINESTRING

LinestringFromWKB(bytea,[<srid>])

Makes a Geometry from WKB with the given SRID. If SRID is not give, it defaults to -1.

from the conformance suite

throws an error if WKB is not a LINESTRING

PolyFromWKB(bytea,[<srid>])

Makes a Geometry from WKB with the given SRID. If SRID is not give, it defaults to -1.

OGC SPEC 3.2.7.2 - option SRID is from the conformance suite

throws an error if WKB is not a POLYGON

PolygonFromWKB(bytea,[<srid>])

Makes a Geometry from WKB with the given SRID. If SRID is not give, it defaults to -1.

from the conformance suite

throws an error if WKB is not a POLYGON

MPointFromWKB(bytea,[<srid>])

Makes a Geometry from WKB with the given SRID. If SRID is not give, it defaults to -1.

OGC SPEC 3.2.7.2 - option SRID is from the conformance suite

throws an error if WKB is not a MULTIPOINT

MLineFromWKB(bytea,[<srid>])

Makes a Geometry from WKB with the given SRID. If SRID is not give, it defaults to -1.

OGC SPEC 3.2.7.2 - option SRID is from the conformance suite

throws an error if WKB is not a MULTILINESTRING

MPolyFromWKB(bytea,[<srid>])

Makes a Geometry from WKB with the given SRID. If SRID is not give, it defaults to -1.

OGC SPEC 3.2.7.2 - option SRID is from the conformance suite

throws an error if WKB is not a MULTIPOLYGON

GeomCollFromWKB(bytea,[<srid>])

Makes a Geometry from WKB with the given SRID. If SRID is not give, it defaults to -1.

OGC SPEC 3.2.7.2 - option SRID is from the conformance suite

throws an error if WKB is not a GEOMETRYCOLLECTION

BdPolyFromText(text WKT, integer SRID)

Construct a Polygon given an arbitrary collection of closed linestrings as a MultiLineString text representation.

Throws an error if WKT is not a MULTILINESTRING. Throws an error if output is a MULTIPOLYGON; use BdMPolyFromText in that case, or see BuildArea() for a postgis-specific approach.

OGC SFSQL 1.1 - 3.2.6.2

Availability: 1.1.0 - requires GEOS >= 2.1.0.

BdMPolyFromText(text WKT, integer SRID)

Construct a MultiPolygon given an arbitrary collection of closed linestrings as a MultiLineString text representation.

Throws an error if WKT is not a MULTILINESTRING. Forces MULTIPOLYGON output even when result is really only composed by a single POLYGON; use BdPolyFromText if you're sure a single POLYGON will result from operation, or see BuildArea() for a postgis-specific approach.

OGC SFSQL 1.1 - 3.2.6.2

Availability: 1.1.0 - requires GEOS >= 2.1.0.

6.2. Postgis Extensions

6.2.1. Management Functions

DropGeometryTable([<schema_name>], <table_name>)

Drops a table and all its references in geometry_columns. Note: uses current_schema() on schema-aware pgsql installations if schema is not provided.

UpdateGeometrySRID([<schema_name>], <table_name>, <column_name>, <srid>)

Update the SRID of all features in a geometry column updating constraints and reference in geometry_columns. Note: uses current_schema() on schema-aware pgsql installations if schema is not provided.

update_geometry_stats([<table_name>, <column_name>])

Update statistics about spatial tables for use by the query planner. You will also need to run "VACUUM ANALYZE [table_name] [column_name]" for the statistics gathering process to be complete. NOTE: starting with PostgreSQL 8.0 statistics gathering is automatically performed running "VACUUM ANALYZE".

postgis_version()

Returns PostGIS version number and compile-time options

Note

Prior to version 1.1.0 this was a procedural function, thus possibly returning inaccurate information (in case of incomplete database upgrades).

postgis_lib_version()

Returns the version number of the PostGIS library.

Availability: 0.9.0

postgis_lib_build_date()

Returns build date of the PostGIS library.

Availability: 1.0.0RC1

postgis_script_build_date()

Returns build date of the PostGIS scripts.

Availability: 1.0.0RC1

postgis_scripts_installed()

Returns version of the postgis scripts installed in this database.

Note

If the output of this function doesn't match the output of postgis_scripts_released() you probably missed to properly upgrade an existing database. See the Upgrading section for more info.

Availability: 0.9.0

postgis_scripts_released()

Returns the version number of the lwpostgis.sql script released with the installed postgis lib.

Note

Starting with version 1.1.0 this function returns the same value of postgis_lib_version(). Kept for backward compatibility.

Availability: 0.9.0

postgis_geos_version()

Returns the version number of the GEOS library, or NULL if GEOS support is not enabled.

Availability: 0.9.0

postgis_jts_version()

Returns the version number of the JTS library, or NULL if JTS support is not enabled.

Availability: 1.1.0

postgis_proj_version()

Returns the version number of the PROJ4 library, or NULL if PROJ4 support is not enabled.

Availability: 0.9.0

postgis_uses_stats()

Returns true if STATS usage has been enabled, false otherwise.

Availability: 0.9.0

postgis_full_version()

Reports full postgis version and build configuration infos.

Availability: 0.9.0

6.2.2. Operators

A &< B

The "&<" operator returns true if A's bounding box overlaps or is to the left of B's bounding box.

A &> B

The "&>" operator returns true if A's bounding box overlaps or is to the right of B's bounding box.

A << B

The "<<" operator returns true if A's bounding box is strictly to the left of B's bounding box.

A >> B

The ">>" operator returns true if A's bounding box is strictly to the right of B's bounding box.

A &<| B

The "&<|" operator returns true if A's bounding box overlaps or is below B's bounding box.

A |&> B

The "|&>" operator returns true if A's bounding box overlaps or is above B's bounding box.

A <<| B

The "<<|" operator returns true if A's bounding box is strictly below B's bounding box.

A |>> B

The "|>>" operator returns true if A's bounding box is strictly above B's bounding box.

A ~= B

The "~=" operator is the "same as" operator. It tests actual geometric equality of two features. So if A and B are the same feature, vertex-by-vertex, the operator returns true.

A @ B

The "@" operator returns true if A's bounding box is completely contained by B's bounding box.

A ~ B

The "~" operator returns true if A's bounding box completely contains B's bounding box.

A && B

The "&&" operator is the "overlaps" operator. If A's bounding boux overlaps B's bounding box the operator returns true.

6.2.3. Measurement Functions

area2d(geometry)

Returns the area of the geometry if it is a polygon or multi-polygon.

distance_sphere(point, point)

Returns linear distance in meters between two lat/lon points. Uses a spherical earth and radius of 6370986 meters. Faster than distance_spheroid(), but less accurate. Only implemented for points.

distance_spheroid(point, point, spheroid)

Returns linear distance between two lat/lon points given a particular spheroid. See the explanation of spheroids given for length_spheroid(). Currently only implemented for points.

length2d(geometry)

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

length3d(geometry)

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

length_spheroid(geometry,spheroid)

Calculates the length of of a geometry on an elipsoid. This is useful if the coordinates of the geometry are in latitude/longitude and a length is desired without reprojection. The elipsoid is a separate database type and can be constructed as follows:

SPHEROID[<NAME>,<SEMI-MAJOR AXIS>,<INVERSE FLATTENING>]

Eg:

SPHEROID["GRS_1980",6378137,298.257222101]

An example calculation might look like this:

SELECT
 length_spheroid(
  geometry_column,
  'SPHEROID["GRS_1980",6378137,298.257222101]'
 )
FROM geometry_table;

length3d_spheroid(geometry,spheroid)

Calculates the length of of a geometry on an elipsoid, taking the elevation into account. This is just like length_spheroid except vertical coordinates (expressed in the same units as the spheroid axes) are used to calculate the extra distance vertical displacement adds.

distance(geometry, geometry)

Returns the smaller distance between two geometries.

max_distance(linestring,linestring)

Returns the largest distance between two line strings.

perimeter(geometry)

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

perimeter2d(geometry)

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

perimeter3d(geometry)

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

azimuth(geometry, geometry)

Returns the azimuth of the segment defined by the given Point geometries, or NULL if the two points are coincident. Return value is in radians.

Availability: 1.1.0

6.2.4. Geometry Outputs

AsBinary(geometry,{'NDR'|'XDR'})

Returns the geometry in the OGC "well-known-binary" format as a bytea, using little-endian (NDR) or big-endian (XDR) encoding. This is useful in binary cursors to pull data out of the database without converting it to a string representation.

AsEWKT(geometry)

Returns a Geometry in EWKT format (as text).

AsEWKB(geometry, {'NDR'|'XDR'})

Returns a Geometry in EWKB format (as bytea) using either little-endian (NDR) or big-endian (XDR) encoding.

AsSVG(geometry, [rel], [precision])

Return the geometry as an SVG path data. Use 1 as second argument to have the path data implemented in terms of relative moves, the default (or 0) uses absolute moves. Third argument may be used to reduce the maximum number of decimal digits used in output (defaults to 15). Point geometries will be rendered as cx/cy when 'rel' arg is 0, x/y when 'rel' is 1.

AsGML(geometry, [precision])

Return the geometry as a GML element. Second argument may be used to reduce the maximum number of significant digits used in output (defaults to 15).

6.2.5. Geometry Constructors

GeomFromEWKT(text)

Makes a Geometry from EWKT.

GeomFromEWKB(bytea)

Makes a Geometry from EWKB.

MakePoint(<x>, <y>, [<z>], [<m>])

Creates a 2d,3dz or 4d point geometry.

MakePointM(<x>, <y>, <m>)

Creates a 3dm point geometry.

MakeBox2D(<LL>, <UR>)

Creates a BOX2D defined by the given point geometries.

MakeBox3D(<LLB>, <URT>)

Creates a BOX3D defined by the given point geometries.

MakeLine(geometry set)

Creates a Linestring from a set of point geometries. You might want to use a subselect to order points before feeding them to this aggregate.

MakeLine(geometry, geometry)

Creates a Linestring from the two given point geometries.

LineFromMultiPoint(multipoint)

Creates a LineString from a MultiPoint geometry.

MakePolygon(linestring, [linestring[]])

Creates a Polygon formed by the given shell and array of holes. You can construct a geometry array using Accum. Input geometries must be closed LINESTRINGS (see IsClosed and GeometryType).

BuildArea(geometry)

Creates an areal geometry formed by the costituent linework of given geometry. The return type can be a Polygon or MultiPolygon, depending on input. If the input lineworks do not form polygons NULL is returned.

See also BdPolyFromText and BdMPolyFromText - wrappers to this function with standard OGC interface.

Availability: 1.1.0 - requires GEOS >= 2.1.0.

Polygonize(geometry set)

Aggregate. Creates a GeometryCollection containing possible polygons formed from the costituent linework of a set of geometries.

Availability: 1.0.0RC1 - requires GEOS >= 2.1.0.

Collect(geometry set)

This function returns a GEOMETRYCOLLECTION or a MULTI object from a set of geometries. The collect() function is an "aggregate" function in the terminology of PostgreSQL. That means that it operators on lists of data, in the same way the sum() and mean() functions do. For example, "SELECT COLLECT(GEOM) FROM GEOMTABLE GROUP BY ATTRCOLUMN" will return a separate GEOMETRYCOLLECTION for each distinct value of ATTRCOLUMN.

Collect(geometry, geometry)

This function returns a geometry being a collection of two input geometries. Output type can be a MULTI* or a GEOMETRYCOLLECTION.

Dump(geometry)

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). When the input geometry is a simple type (POINT,LINESTRING,POLYGON) a single record will be returned with an empty path array and the input geometry as geom. When the input geometry is a collection or multi it will return a record for each of the collection components, and the path will express the position of the component inside the collection.

NOTE: this function is not available for builds against PostgreSQL 7.2.x

6.2.6. Geometry Editors

AddBBOX(geometry)

Add bounding box to the geometry. This would make bounding box based queries faster, but will increase the size of the geometry.

DropBBOX(geometry)

Drop the bounding box cache from the geometry. This reduces geometry size, but makes bounding-box based queries slower.

AddPoint(linestring, point, [<position>])

Adds a point to a LineString before point <pos> (0-based index). Third parameter can be omitted or set to -1 for appending.

RemovePoint(linestring, offset)

Removes point from a linestring. Offset is 0-based.

Availability: 1.1.0

SetPoint(linestring, N, point)

Replace point N of linestring with given point. Index is 0-based.

Availability: 1.1.0

Force_collection(geometry)

Converts the geometry into a GEOMETRYCOLLECTION. This is useful for simplifying the WKB representation.

Force_2d(geometry)

Forces the geometries into a "2-dimensional mode" so that all output representations will only have the X and Y coordinates. This is useful for force OGC-compliant output (since OGC only specifies 2-D geometries).

Force_3dz(geometry), Force_3d(geometry),

Forces the geometries into XYZ mode.

Force_3dm(geometry)

Forces the geometries into XYM mode.

Force_4d(geometry)

Forces the geometries into XYZM mode.

Multi(geometry)

Returns the geometry as a MULTI* geometry. If the geometry is already a MULTI*, it is returned unchanged.

Transform(geometry,integer)

Returns a new geometry with its coordinates transformed to the SRID referenced by the integer parameter. The destination SRID must exist in the SPATIAL_REF_SYS table.

Translate(geometry,float8,float8,float8)

Translates the geometry to a new location using the numeric parameters as offsets. Ie: translate(geom, X, Y, Z).

Scale(geometry,float8,float8,float8)

scales the geometry to a new size by multiplying the ordinates with the parameters. Ie: scale(geom, Xfactor, Yfactor, Zfactor).

Availability: 1.1.0

TransScale(geometry,float8,float8,float8,float8)

First, translates the geometry using the first two floats, then scales it using the second two floats, working in 2D only. Using transscale(geom, X, Y, XFactor, YFactor) effectively is an efficient shortcut for scale(translate(geom,X,Y,0),Xfactor,YFactor,1).

Availability: 1.1.0

Reverse(geometry)

Returns the geometry with vertex order reversed.

ForceRHR(geometry)

Force polygons of the collection to obey Right-Hand-Rule.

Simplify(geometry, tolerance)

Returns a "simplified" version of the given geometry using the Douglas-Peuker algorithm. Will actually do something only with (multi)lines and (multi)polygons but you can safely call it with any kind of geometry. Since simplification occurs on a object-by-object basis you can also feed a GeometryCollection to this function. Note that returned geometry might loose its simplicity (see IsSimple)

SnapToGrid(geometry, originX, originY, sizeX, sizeY), SnapToGrid(geometry, sizeX, sizeY), SnapToGrid(geometry, size),

Snap all points of the input geometry to the grid defined by its origin and cell size. Remove consecutive points falling on the same cell, eventually returning NULL if output points are not enough to define a geometry of the given type. Collapsed geometries in a collection are stripped from it.

Note

The returned geometry might loose its simplicity (see IsSimple).

Note

Before release 1.1.0 this function always returned a 2d geometry. Starting at 1.1.0 the returned geometry will have same dimensionality as the input one with higher dimension values untouched. Use the version taking a second geometry argument to define all grid dimensions.

Availability: 1.0.0RC1

SnapToGrid(geometry, geometry, sizeX, sizeY, sizeZ, sizeM)

Snap all points of the input geometry to the grid defined by its origin (the second argument, must be a point) and cell sizes. Specify 0 as size for any dimension you don't want to snap to a grid.

Availability: 1.1.0

Segmentize(geometry, maxlength)

Return a modified [multi]polygon having no ring segment longer then the given distance. Interpolated points will have Z and M values (if needed) set to 0. Distance computation is performed in 2d only.

LineMerge(geometry)

Returns a (set of) LineString(s) formed by sewing togheter costituent linework of input.

Availability: 1.1.0 - requires GEOS >= 2.1.0

6.2.7. Linear Referencing

line_interpolate_point(linestring, location)

Interpolates a point along a line. First argument must be a LINESTRING. Second argument is a float8 between 0 and 1 representing fraction of total 2d length the point has to be located.

See line_locate_point() for computing the line location nearest to a Point.

Availability: 0.8.2

line_substring(linestring, start, end)

Return a linestring being a substring of the input one starting and ending at the given fractions of total 2d length. Second and third arguments are float8 values between 0 and 1.

See line_locate_point() for computing the line location nearest to a Point.

Availability: 1.1.0

line_locate_point(LineString, Point)

Returns a float between 0 and 1 representing the location of the closest point on LineString to the given Point, as a fraction of total 2d line length.

You can use the returned location to extract a Point (line_interpolate_point) or a substring (line_substring).

Availability: 1.1.0

locate_along_measure(geometry, float8)

Return a derived geometry collection value with elements that match the specified measure. Polygonal elements are not supported.

Semantic is specified by: ISO/IEC CD 13249-3:200x(E) - Text for Continuation CD Editing Meeting

Availability: 1.1.0

locate_between_measures(geometry, float8, float8)

Return a derived geometry collection value with elements that match the specified range of measures inclusively. Polygonal elements are not supported.

Semantic is specified by: ISO/IEC CD 13249-3:200x(E) - Text for Continuation CD Editing Meeting

Availability: 1.1.0

6.2.8. Misc

Summary(geometry)

Returns a text summary of the contents of the geometry.

box2d(geometry)

Returns a BOX2D representing the maximum extents of the geometry.

box3d(geometry)

Returns a BOX3D representing the maximum extents of the geometry.

extent(geometry set)

The extent() function is an "aggregate" function in the terminology of PostgreSQL. That means that it operators on lists of data, in the same way the sum() and mean() functions do. For example, "SELECT EXTENT(GEOM) FROM GEOMTABLE" will return a BOX3D giving the maximum extend of all features in the table. Similarly, "SELECT EXTENT(GEOM) FROM GEOMTABLE GROUP BY CATEGORY" will return one extent result for each category.

zmflag(geometry)

Returns ZM (dimension semantic) flag of the geometries as a small int. Values are: 0=2d, 1=3dm, 2=3dz, 3=4d.

HasBBOX(geometry)

Returns TRUE if the bbox of this geometry is cached, FALSE otherwise. Use addBBOX() and dropBBOX() to control caching.

ndims(geometry)

Returns number of dimensions of the geometry as a small int. Values are: 2,3 or 4.

nrings(geometry)

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

npoints(geometry)

Returns the number of points in the geometry.

isvalid(geometry)

returns true if this geometry is valid.

expand(geometry, float)

This function returns a bounding box expanded in all directions from the bounding box of the input geometry, by an amount specified in the second argument. Very useful for distance() queries, to add an index filter to the query.

estimated_extent([schema], table, geocolumn)

Return the 'estimated' extent of the given spatial table. The estimated is taken from the geometry column's statistics. The current schema will be used if not specified.

For PostgreSQL>=8.0.0 statistics are gathered by VACUUM ANALYZE and resulting extent will be about 95% of the real one.

For PostgreSQL<8.0.0 statistics are gathered by update_geometry_stats() and resulting extent will be exact.

find_srid(varchar,varchar,varchar)

The syntax is find_srid(<db/schema>, <table>, <column>) and the function returns the integer SRID of the specified column by searching through the GEOMETRY_COLUMNS table. If the geometry column has not been properly added with the AddGeometryColumns() function, this function will not work either.

mem_size(geometry)

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

numb_sub_objects(geometry)

Returns the number of objects stored in the geometry. This is useful for MULTI-geometries and GEOMETRYCOLLECTIONs.

point_inside_circle(geometry,float,float,float)

The syntax for this functions is point_inside_circle(<geometry>,<circle_center_x>,<circle_center_y>,<radius>). Returns the true if the geometry is a point and is inside the circle. Returns false otherwise.

xmin(box3d) ymin(box3d) zmin(box3d)

Returns the requested minima of a bounding box.

xmax(box3d) ymax(box3d) zmax(box3d)

Returns the requested maxima of a bounding box.

Accum(geometry set)

Aggregate. Constructs an array of geometries.