Table of Contents
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.
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'.
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.
Set the SRID on a geometry to a particular integer value. Useful in constructing bounding boxes for queries.
Return the cartesian distance between two geometries in projected units.
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
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****')
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 ))
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
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******')
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***')
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
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)
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)
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
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
Returns the centroid of the geometry as a point.
Computation will be more accurate if performed by the GEOS module (enabled at compile time).
Returns the area of the geometry if it is a polygon or multi-polygon.
The length of this Curve in its associated spatial reference.
synonym for length2d()
OGC SPEC 2.1.5.1
Return a Point guaranteed to lie on the surface
Implemented using GEOS
OGC SPEC 3.2.14.2 and 3.2.18.2 -
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
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
Returns a geometry that represents the convex hull of this Geometry.
Performed by the GEOS module
OGC SPEC s2.1.1.3
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
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
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
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
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
Same as the above, only memory-friendly (uses less memory and more processor time).
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
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')
Returns the integer SRID number of the spatial reference system of the geometry.
OGC SPEC s2.1.1.1
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
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.
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
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
Returns true of the geometry start and end points are coincident.
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
If geometry is a GEOMETRYCOLLECTION (or MULTI*) return the number of geometries, otherwise return NULL.
Return the N'th geometry if the geometry is a GEOMETRYCOLLECTION, MULTIPOINT, MULTILINESTRING or MULTIPOLYGON. Otherwise, return NULL.
1 is 1st 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.
Return the N'th point in the first linestring in the geometry. Return NULL if there is no linestring in the geometry.
Return the exterior ring of the polygon geometry. Return NULL if the geometry is not a polygon.
Return the number of interior rings of the first polygon in the geometry. Return NULL if there is no polygon in the geometry.
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 (1-based).
Returns the last point of the LineString geometry as a point.
Returns the first point of the LineString geometry as a point.
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.
Find and return the X coordinate of the first point in the geometry. Return NULL if there is no point in the geometry.
Find and return the Y coordinate of the first point in the geometry. Return NULL if there is no point in the geometry.
Find and return the Z coordinate of the first point in the geometry. Return NULL if there is no point in the geometry.
Makes a Geometry from WKT with the given SRID.
OGC SPEC 3.2.6.2 - option SRID is from the conformance suite
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Drops a table and all its references in geometry_columns. Note: uses current_schema() on schema-aware pgsql installations if schema is not provided.
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 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".
Returns the version number of the PostGIS functions installed in this database (deprecated, use postgis_full_version() instead).
Returns the version number of the PostGIS library.
Returns build date of the PostGIS library.
Returns build date of the PostGIS scripts.
Returns the version number of the lwpostgis.sql script installed in this database.
Returns the version number of the lwpostgis.sql script released with the installed postgis lib.
Returns the version number of the GEOS library, or NULL if GEOS support is not enabled.
Returns the version number of the PROJ4 library, or NULL if PROJ4 support is not enabled.
Returns true if STATS usage has been enabled, false otherwise.
Reports full postgis version and build configuration infos.
The "&<" operator returns true if A's bounding box overlaps or is to the left of B's bounding box.
The "&>" operator returns true if A's bounding box overlaps or is to the right of B's bounding box.
The "<<" operator returns true if A's bounding box is strictly to the left of B's bounding box.
The ">>" operator returns true if A's bounding box is strictly to the right of B's bounding box.
The "&<|" operator returns true if A's bounding box overlaps or is below B's bounding box.
The "|&>" operator returns true if A's bounding box overlaps or is above B's bounding box.
The "<<|" operator returns true if A's bounding box is strictly below B's bounding box.
The "|>>" operator returns true if A's bounding box is strictly above B's bounding box.
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.
The "@" operator returns true if A's bounding box is completely contained by B's bounding box.
The "~" operator returns true if A's bounding box completely contains B's bounding box.
The "&&" operator is the "overlaps" operator. If A's bounding boux overlaps B's bounding box the operator returns true.
Returns the area of the geometry if it is a polygon or multi-polygon.
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.
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.
Returns the 2-dimensional length of the geometry if it is a linestring or multi-linestring.
Returns the 3-dimensional length of the geometry if it is a linestring or multi-linestring.
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;
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.
Returns the smaller distance between two geometries.
Returns the largest distance between two line strings.
Returns the 2-dimensional perimeter of the geometry, if it is a polygon or multi-polygon.
Returns the 2-dimensional perimeter of the geometry, if it is a polygon or multi-polygon.
Returns the 3-dimensional perimeter of the geometry, if it is a polygon or multi-polygon.
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.
Returns a Geometry in EWKT format (as text).
Returns a Geometry in EWKB format (as bytea) using either little-endian (NDR) or big-endian (XDR) encoding.
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.
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).
Makes a Geometry from EWKT.
Makes a Geometry from EWKB.
Creates a 2d,3dz or 4d point geometry.
Creates a 3dm point geometry.
Creates a BOX2D defined by the given point geometries.
Creates a BOX3D defined by the given point geometries.
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.
Creates a Linestring from the two given point geometries.
Creates a LineString from a MultiPoint geometry.
Adds a point to a LineString at position <pos>. Third parameter can be omitted or set to -1 for appending.
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).
Aggregate. Creates a GeometryCollection containing possible polygons formed from the costituent linework of a set of geometries. Only available when compiled against GEOS >= 2.1.0.
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.
This function returns a geometry being a collection of two input geometries. Output type can be a MULTI* or a GEOMETRYCOLLECTION.
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
Add bounding box to the geometry. This would make bounding box based queries faster, but will increase the size of the geometry.
Drop the bounding box cache from the geometry. This reduces geometry size, but makes bounding-box based queries slower.
Converts the geometry into a GEOMETRYCOLLECTION. This is useful for simplifying the WKB representation.
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).
Forces the geometries into XYZ mode.
Forces the geometries into XYM mode.
Forces the geometries into XYZM mode.
Returns the geometry as a MULTI* geometry. If the geometry is already a MULTI*, it is returned unchanged.
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.
Translates the geometry to a new location using the numeric parameters as offsets. Ie: translate(geom,X,Y,Z).
Returns the geometry with vertex order reversed.
Force polygons of the collection to obey Right-Hand-Rule.
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)
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 that returned geometry might loose its simplicity (see IsSimple).
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.
Returns a text summary of the contents of the geometry.
Returns a BOX2D representing the maximum extents of the geometry.
Returns a BOX3D representing the maximum extents of the geometry.
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.
Returns ZM (dimension semantic) flag of the geometries as a small int. Values are: 0=2d, 1=3dm, 2=3dz, 3=4d.
Returns TRUE if the bbox of this geometry is cached, FALSE otherwise. Use addBBOX() and dropBBOX() to control caching.
Returns number of dimensions of the geometry as a small int. Values are: 2,3 or 4.
If the geometry is a polygon or multi-polygon returns the number of rings.
Returns the number of points in the geometry.
returns true if this geometry is valid.
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.
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.
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.
Returns the amount of space (in bytes) the geometry takes.
Returns the number of objects stored in the geometry. This is useful for MULTI-geometries and GEOMETRYCOLLECTIONs.
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.
Returns the requested minima of a bounding box.
Returns the requested maxima of a bounding box.
Interpolates a point along a line. First argument must be a LINESTRING. Second argument is a float between 0 and 1. Returns a point.
Aggregate. Constructs an array of geometries.