Table of Contents
The functions in this section answer a question about a geometry rather than about a temporal type. Each states how it reads a geometry bounded by circular arcs, since an answer built from the arcs themselves and an answer built from a polygonal approximation of them are not the same answer.
Return the Well-Known Text (WKT) or the Extended Well-Known Text (EWKT) representation
asText({geometry,geography},maxdecimaldigits integer=15) → text
asEWKT({geometry,geography},maxdecimaldigits integer=15) → text
Each answers as the PostGIS function of the same name with the prefix ST_.
SELECT asText(geometry 'SRID=3857;Point(1.123456789 2)', 3); -- POINT(1.123 2) SELECT asText(geography 'Linestring(1 2,3 4)'); -- LINESTRING(1 2,3 4) SELECT asEWKT(geometry 'SRID=3857;Point(1 2)'); -- SRID=3857;POINT(1 2) SELECT asEWKT(geography 'Linestring(1 2,3 4)'); -- SRID=4326;LINESTRING(1 2,3 4)
Return the GeoJSON representation
asGeoJSON(geometry,maxdecimaldigits integer=9,options integer=8) → text asGeoJSON(geography,maxdecimaldigits integer=9,options integer=0) → text
The GeoJSON states the coordinate reference system its options ask for, the sum of 1 for the bounding box, 2 for the short name of the system, as in EPSG:3857, 4 for its long name, and 8 for the short name of any system but WGS 84, which is the default for a geometry; a geography takes no option by default. A geometry bounded by circular arcs has no GeoJSON representation and is refused.
SELECT asGeoJSON(geometry 'SRID=3857;Point(1 2)');
-- {"type":"Point","crs":{"type":"name","properties":{"name":"EPSG:3857"}},"coordinates":[1,2]}
SELECT asGeoJSON(geometry 'SRID=4326;Point(1 2)');
-- {"type":"Point","coordinates":[1,2]}
SELECT asGeoJSON(geography 'Point(1 2)', 9, 2);
-- {"type":"Point","crs":{"type":"name","properties":{"name":"EPSG:4326"}},"coordinates":[1,2]}
Return the Well-Known Binary (WKB), the Extended Well-Known Binary (EWKB), the Hexadecimal Well-Known Binary (HexWKB), or the Hexadecimal Extended Well-Known Binary (HexEWKB) representation
asBinary({geometry,geography},endian text='') → bytea
asEWKB({geometry,geography},endian text='') → bytea
asHexWKB({geometry,geography},endian text='') → text
asHexEWKB({geometry,geography},endian text='') → text
The result is encoded using either the little-endian (NDR) or the big-endian (XDR) encoding. If no encoding is specified, then the encoding of the machine is used. A geometry or a geography carries its SRID: asEWKB and asHexEWKB embed it, while the plain asBinary and asHexWKB forms omit it.
SELECT asBinary(geometry 'SRID=3812;Point(1 2)'); -- \x0101000000000000000000f03f0000000000000040 SELECT asEWKB(geometry 'SRID=3812;Point(1 2)'); -- \x0101000020e40e0000000000000000f03f0000000000000040 SELECT asEWKB(geometry 'Point(1 1)', 'XDR'); -- \x00000000013ff00000000000003ff0000000000000 SELECT asHexWKB(geometry 'SRID=3812;Point(1 2)'); -- 0101000000000000000000F03F0000000000000040 SELECT asHexEWKB(geometry 'SRID=3812;Point(1 2)'); -- 0101000020E40E0000000000000000F03F0000000000000040 SELECT asHexEWKB(geography 'Point(4.35 50.85)'); -- 0101000020E61000006666666666661140CDCCCCCCCC6C4940
Input from the Well-Known Text (WKT) or from the Extended Well-Known Text (EWKT) representation
geometryFromText(text) → geometry geometryFromEWKT(text) → geometry geographyFromText(text) → geography geographyFromEWKT(text) → geography
Each reads the WKT representation, optionally prefixed with an SRID, and no other representation. A geography refuses an SRID that is not a longitude and latitude system and is of SRID 4326 where the text states none.
SELECT asEWKT(geometryFromText(text 'Point(1 2)')); -- POINT(1 2) SELECT asEWKT(geometryFromEWKT(text 'SRID=3812;Point(1 2)')); -- SRID=3812;POINT(1 2) SELECT asEWKT(geographyFromText(text 'Linestring(1 2,3 4)')); -- SRID=4326;LINESTRING(1 2,3 4) SELECT asEWKT(geographyFromEWKT(text 'SRID=7844;Point(1 2)')); -- SRID=7844;POINT(1 2) SELECT geographyFromEWKT(text 'SRID=3812;Point(1 2)'); -- ERROR: Only lon/lat coordinate systems are supported SELECT geometryFromText(text '0101000000000000000000F03F0000000000000040'); -- ERROR: parse error - invalid geometry
Input from the GeoJSON representation
geometryFromGeoJSON(text) → geometry geographyFromGeoJSON(text) → geography
The SRID is the one of the coordinate reference system the value names, and 4326 where it names none, as the GeoJSON specification states.
SELECT asEWKT(geometryFromGeoJSON(text '{"type":"Point","coordinates":[1,2]}'));
-- SRID=4326;POINT(1 2)
SELECT asEWKT(geometryFromGeoJSON(text '{"type":"Point",
"crs":{"type":"name","properties":{"name":"EPSG:3857"}},"coordinates":[1,2]}'));
-- SRID=3857;POINT(1 2)
SELECT asEWKT(geographyFromGeoJSON(text '{"type":"Point","coordinates":[4.35,50.85]}'));
-- SRID=4326;POINT(4.35 50.85)
Input from the Well-Known Binary (WKB), from the Extended Well-Known Binary (EWKB), from the Hexadecimal Well-Known Binary (HexWKB), or from the Hexadecimal Extended Well-Known Binary (HexEWKB) representation
geometryFromBinary(bytea) → geometry geometryFromEWKB(bytea) → geometry geometryFromHexWKB(text) → geometry geometryFromHexEWKB(text) → geometry geographyFromBinary(bytea) → geography geographyFromEWKB(bytea) → geography geographyFromHexWKB(text) → geography geographyFromHexEWKB(text) → geography
The hexadecimal readers read hexadecimal bytes only. A geography refuses an SRID that is not a longitude and latitude system and is of SRID 4326 where the value states none.
SELECT asEWKT(geometryFromBinary('\x0101000000000000000000f03f0000000000000040'));
-- POINT(1 2)
SELECT asEWKT(geometryFromEWKB('\x0101000020e40e0000000000000000f03f0000000000000040'));
-- SRID=3812;POINT(1 2)
SELECT asEWKT(geometryFromHexWKB('0101000000000000000000F03F0000000000000040'));
-- POINT(1 2)
SELECT asEWKT(geometryFromHexEWKB('0101000020E40E0000000000000000F03F0000000000000040'));
-- SRID=3812;POINT(1 2)
SELECT asEWKT(geographyFromHexEWKB('0101000020E61000006666666666661140CDCCCCCCCC6C4940'));
-- SRID=4326;POINT(4.35 50.85)
Return the rectangle of minimum area enclosing a geometry
orientedEnvelope(geometry) → geometry
The rectangle is at the angle that makes it smallest, which is not in general the angle of the axes. It is answered as a line when the geometry is contained in one and as a point when the geometry is one. A geometry carrying a circular arc is answered on the arc itself, the rectangle being placed against the circle the arc lies on rather than against the chain of segments approximating it.
SELECT ST_AsText(round(orientedEnvelope(geometry 'Polygon((0 0,3 4,-1 7,-4 3,0 0))'), 6)); -- POLYGON((0 0,3 4,-1 7,-4 3,0 0)) SELECT ST_AsText(round(orientedEnvelope(geometry 'Linestring(0 0,10 0)'), 6)); -- LINESTRING(0 0,10 0) SELECT ST_AsText(round(orientedEnvelope(geometry 'Circularstring(0 0,0.5 0.1,1 0)'), 6)); -- POLYGON((1 0.1,0 0.1,0 0,1 0,1 0.1))
Return the smallest convex geometry enclosing a geometry
convexHull(geometry) → geometry
The result is a polygon whose every corner is a point of the geometry, a line when the geometry is contained in one, and a point when the geometry is one. A geometry carrying a circular arc is answered on the arc itself, so the hull is bounded by the arc and is returned as a curved geometry rather than as a polygon approximating it.
SELECT ST_AsText(round(convexHull(geometry 'Multipoint(0 0,10 0,10 10,0 10,5 5)'), 6)); -- POLYGON((0 0,0 10,10 10,10 0,0 0)) SELECT ST_AsText(round(convexHull(geometry 'Polygon((0 0,10 0,10 10,5 5,0 10,0 0))'), 6)); -- POLYGON((0 0,0 10,10 10,10 0,0 0)) SELECT ST_AsText(round(convexHull(geometry 'Circularstring(5 0,0 5,-5 0)'), 6)); -- CURVEPOLYGON(COMPOUNDCURVE((5 0,-5 0),CIRCULARSTRING(-5 0,0 5,5 0)))
Return true if a geometry has no point at which it crosses or touches itself
isSimple(geometry) → boolean
A point is always simple, a multipoint is simple when it repeats no point, a line is simple when it meets itself only where two of its segments follow one another and, when it closes, at the point where it closes, and an areal geometry is simple when each of its rings is. Two lines of a multiline may additionally meet at a point that ends both. A geometry carrying a circular arc meets itself along an arc rather than at a point, which the segment intersection this rests on does not read, so such a geometry is answered on the chain of segments approximating its arcs.
SELECT isSimple(geometry 'Linestring(0 0,10 10,10 0,0 10)'); -- false SELECT isSimple(geometry 'Linestring(0 0,10 0,10 10,0 0)'); -- true SELECT isSimple(geometry 'Multilinestring((0 0,10 0),(10 0,10 10))'); -- true
Return the geometry holding every point within a distance of a geometry
buffer(geometry,float,options text='') → geometry
The boundary of the result is made of the two offsets of the geometry at the given distance, of the join filling the outer side of each turn, and of the cap closing each end of an open line. A negative distance shrinks an areal geometry and answers an empty geometry for one of any other dimension. The options string carries the styles as space-separated key=value pairs: endcap is one of round, flat or square, join one of round, mitre or bevel, and mitre_limit the ratio at which a mitre join falls back to a bevel.
SELECT ST_AsText(round(buffer(geometry 'Point(0 0)', 1), 6)); -- CURVEPOLYGON(CIRCULARSTRING(-1 0,1 0,-1 0)) SELECT ST_AsText(round(buffer(geometry 'Polygon((0 0,10 0,10 10,0 10,0 0))', -1, 'join=mitre'), 6)); -- CURVEPOLYGON(COMPOUNDCURVE((1 1,9 1),(9 1,9 9),(9 9,1 9),(1 9,1 1))) SELECT ST_AsText(round(buffer(geometry 'Curvepolygon(Circularstring(0 0,2 2,4 0,2 -2, 0 0))', 1), 6)); -- CURVEPOLYGON(COMPOUNDCURVE(CIRCULARSTRING(-1 0,2 3,5 0),CIRCULARSTRING(5 0,2 -3,-1 0)))
Return the DE-9IM intersection matrix of two geometries or whether it matches a pattern
relate(geometry,geometry) → text relate(geometry,geometry,text) → boolean
The matrix states, for each pair drawn from the interior, the boundary and the exterior of the first geometry and of the second, the dimension of what they share: F where they share nothing, 0, 1 or 2 where they share points, a curve or an area. A circular arc is read on its own circle rather than on a chain of segments approximating it, and the interior and the boundary of a geometry collection are those of the union of its members. With a pattern, the function answers whether the matrix matches it, as ST_Relate with three arguments does: a character T of the pattern matches any of 0, 1 and 2, and a character * matches anything. A pattern is nine characters among T, F, *, 0, 1 and 2, read in any case; a pattern of another length or holding another character raises an error.
SELECT relate(geometry 'Polygon((0 0,4 0,4 4,0 4,0 0))', geometry 'Linestring(2 2,6 2)'); -- 1020F1102 SELECT relate(geometry 'Polygon((0 0,2 0,2 2,0 2,0 0))', geometry 'Polygon((2 0,4 0,4 2,2 2,2 0))'); -- FF2F11212 SELECT relate(geometry 'Curvepolygon(Circularstring(0 0,2 2,4 0,2 -2,0 0))', geometry 'Point(2 1.9)'); -- 0F2FF1FF2 SELECT relate(geometry 'Polygon((0 0,4 0,4 4,0 4,0 0))', geometry 'Point(2 2)', 'T*****FF*'); -- true
Return true if two geometries satisfy a spatial relationship
contains(geometry,geometry) → boolean
covers(geometry,geometry) → boolean
touches(geometry,geometry) → boolean
geoEquals(geometry,geometry) → boolean
disjoint({geometry,geography},{geometry,geography}) → boolean
intersects({geometry,geography},{geometry,geography}) → boolean
Each answers as the PostGIS function of the same name with the prefix ST_ on the same arguments, without calling GEOS. Over two geometries with Z, disjoint and intersects are tested in 3D, as ST_3DIntersects, and in 2D when one of them has no Z. The containment relationships contains, covers and touches have no 3D form and refuse a geometry with Z. Two geographies intersect on the WGS 84 spheroid, as ST_Intersects decides it. The equality of two sets of points is named geoEquals in every engine, since PostGIS declares equals(geometry, geometry) as a deprecated alias of ST_Equals; it reads the points, where the operator = compares the vertices.
SELECT contains(geometry 'Polygon((0 0,4 0,4 4,0 4,0 0))', geometry 'Point(2 2)'); -- true SELECT touches(geometry 'Polygon((0 0,4 0,4 4,0 4,0 0))', geometry 'Point(4 2)'); -- true SELECT intersects(geometry 'Point(1 1 1)', geometry 'Point(1 1 2)'); -- false SELECT geoEquals(geometry 'Linestring(0 0,2 0)', geometry 'Linestring(0 0,1 0,2 0)'); -- true
Return true if two geometries have the same vertices in the same order
orderingEquals(geometry,geometry) → boolean
It answers as the PostGIS function ST_OrderingEquals, where geoEquals compares the sets of points.
SELECT orderingEquals(geometry 'Linestring(0 0,1 1)', geometry 'Linestring(0 0,1 1)'); -- true SELECT orderingEquals(geometry 'Linestring(0 0,1 1)', geometry 'Linestring(1 1,0 0)'); -- false SELECT geoEquals(geometry 'Linestring(0 0,1 1)', geometry 'Linestring(1 1,0 0)'); -- true
Return true if two geometries are within a distance
dwithin(geometry,geometry,float) → boolean dwithin(geography,geography,float,spheroid boolean=true) → boolean
Two geometries with Z are measured in 3D, as ST_3DDWithin, and in 2D when one of them has no Z, see the section called “Operands of Different Dimensions”. For geographies, the computation is done on the WGS 84 spheroid by default. If the last argument is false, a faster spherical calculation is used.
SELECT dwithin(geometry 'Point(0 0 0)', geometry 'Point(0 0 2)', 1.5); -- false SELECT dwithin(geometry 'Point(0 0 0)', geometry 'Point(0 0)', 1.5); -- true SELECT dwithin(geography 'Point(0 0)', geography 'Point(0 1)', 110800); -- true SELECT dwithin(geography 'Point(0 0)', geography 'Point(0 1)', 110800, false); -- false
Return the area, the perimeter or the centroid of a geometry or a geography
area(geometry) → float area(geography,spheroid boolean=true) → float perimeter(geometry) → float perimeter(geography,spheroid boolean=true) → float centroid(geometry) → geometry centroid(geography,spheroid boolean=true) → geography
Each answers as the PostGIS function of the same name with the prefix ST_, without calling GEOS, except that the area of a surface bounded by circular arcs is measured on its arcs, by the closed form of the circular segment each arc adds to its chord: for the disc of radius 1 below the area is π, which ST_Area, replacing each quarter circle by 32 segments, reads as 3.1403. A geography is measured in square meters or in meters on the WGS 84 spheroid by default, and on the sphere, a faster calculation, if the last argument is false. On the sphere, the area is the one the great circles joining the vertices bound, which ST_Area over a geography on the sphere approximates: for the square of one degree at the equator below, the approximation is 0.67 percent smaller.
SELECT area(geometry 'Polygon((0 0,4 0,4 4,0 4,0 0))'); -- 16 SELECT area(geometry 'CurvePolygon(CircularString(0 0,1 1,2 0,1 -1,0 0))'); -- 3.141592653589793 SELECT ST_AsText(centroid(geometry 'Polygon((0 0,4 0,4 4,0 4,0 0))')); -- POINT(2 2) SELECT round(area(geography 'Polygon((0 0,1 0,1 1,0 1,0 0))')::numeric); -- 12308778361 SELECT round(area(geography 'Polygon((0 0,1 0,1 1,0 1,0 0))', false)::numeric); -- 12364031799
Return the distance, the line joining the closest points, or the maximum distance of two geometries or geographies
distance(geometry,geometry) → float distance(geography,geography,spheroid boolean=true) → float shortestLine(geometry,geometry) → geometry shortestLine(geography,geography,spheroid boolean=true) → geography maxDistance(geometry,geometry) → float
Two geometries with Z are measured in 3D, as the PostGIS functions with the prefix ST_3D, and a geometry with Z and one without are refused. For geographies, the computation is done on the WGS 84 spheroid by default. If the last argument is false, a faster spherical calculation is used. The shortest line of two geographies joins their closest points on the sphere, as ST_ShortestLine over geographies joins them.
SELECT distance(geometry 'Point(0 0 0)', geometry 'Point(3 4 12)'); -- 13 SELECT ST_AsText(shortestLine(geometry 'Point(0 0 0)', geometry 'Linestring(1 -1 1,1 1 1)')); -- LINESTRING Z (0 0 0,1 0 1) SELECT round(maxDistance(geometry 'Linestring(0 0 0,1 0 0)', geometry 'Point(0 0 2)')::numeric, 6); -- 2.236068 SELECT round(distance(geography 'Point(0 0)', geography 'Point(0 1)', false)::numeric, 3); -- 111195.080
Return the boundary, the reversal or the parts of a geometry
boundary(geometry) → geometry reverse(geometry) → geometry numGeometries(geometry) → integer geometryN(geometry,integer) → geometry numPoints(geometry) → integer
Each answers as the PostGIS function of the same name with the prefix ST_, numPoints counting every vertex as ST_NPoints does and geometryN counting from 1. The boundary of an empty geometry is the empty geometry of the dimension of a boundary. The boundary of a compound curve is its two end points, or empty when it closes, and the boundary of a multisurface is the boundaries of its members; ST_Boundary refuses both.
SELECT ST_AsText(boundary(geometry 'Polygon((0 0,4 0,4 4,0 4,0 0))')); -- LINESTRING(0 0,4 0,4 4,0 4,0 0) SELECT ST_AsText(reverse(geometry 'Linestring(0 0,4 0,4 3)')); -- LINESTRING(4 3,4 0,0 0) SELECT numGeometries(geometry 'MultiPoint(1 1,5 5)'); -- 2 SELECT ST_AsText(geometryN(geometry 'MultiPoint(1 1,5 5)', 2)); -- POINT(5 5) SELECT numPoints(geometry 'Polygon((0 0,4 0,4 4,0 4,0 0))'); -- 5
Return or set the spatial reference identifier of a geometry or a geography
SRID({geometry,geography}) → integer
setSRID({geometry,geography},integer) → {geometry,geography}
Each answers as the PostGIS function of the same name with the prefix ST_: a negative SRID is set as the unknown SRID 0 and an SRID beyond the reserved range is folded into it. The invalid SRID is refused.
SELECT SRID(geometry 'SRID=3812;Point(1 1)'); -- 3812 SELECT SRID(geography 'Point(4.35 50.85)'); -- 4326 SELECT ST_AsEWKT(setSRID(geometry 'Point(1 1)', 3812)); -- SRID=3812;POINT(1 1) SELECT setSRID(geometry 'Point(1 1)', 1000001); -- ERROR: The SRID is invalid
Return the size in bytes of a geometry or a geography
memSize({geometry,geography}) → integer
It answers as the PostGIS function ST_MemSize.
SELECT memSize(geometry 'Point(1 2)'); -- 32 SELECT memSize(geometry 'Linestring(0 0,4 0,4 3)'); -- 80
Return true if a geometry or a geography is empty
isEmpty({geometry,geography}) → boolean
It answers as the PostGIS function ST_IsEmpty.
SELECT isEmpty(geometry 'Point empty'); -- true SELECT isEmpty(geography 'Point(4.35 50.85)'); -- false
Return the vertices of a geometry or one of them
points(geometry) → geometry pointN(geometry,integer) → geometry
Each answers as the PostGIS function of the same name with the prefix ST_: points is a multipoint holding every vertex, and pointN counts the vertices of a line from 1, a negative number counting from its end, and answers NULL beyond them.
SELECT ST_AsText(points(geometry 'Polygon((0 0,4 0,4 4,0 4,0 0))')); -- MULTIPOINT((0 0),(4 0),(4 4),(0 4),(0 0)) SELECT ST_AsText(pointN(geometry 'Linestring(2 2,6 2,6 5)', 2)); -- POINT(6 2) SELECT ST_AsText(pointN(geometry 'Linestring(2 2,6 2,6 5)', -1)); -- POINT(6 5)
Return the azimuth of the segment joining two points
azimuth(geometry,geometry) → float
The azimuth is the angle in radians clockwise from the north, as the PostGIS function ST_Azimuth answers it, and is NULL for two equal points.
SELECT round(azimuth(geometry 'Point(0 0)', geometry 'Point(1 1)')::numeric, 6); -- 0.785398 SELECT round(azimuth(geometry 'Point(0 0)', geometry 'Point(-1 0)')::numeric, 6); -- 4.712389 SELECT azimuth(geometry 'Point(1 1)', geometry 'Point(1 1)'); -- NULL
Return the point at a fraction of a line, the part between two fractions, or the fraction closest to a point
lineInterpolatePoint(geometry,float) → geometry lineSubstring(geometry,float,float) → geometry lineLocatePoint(geometry,geometry) → float
Each answers as the PostGIS function of the same name with the prefix ST_, a fraction being a share of the length of the line between 0 and 1.
SELECT ST_AsText(lineInterpolatePoint(geometry 'Linestring(0 0,4 0,4 3)', 0.25)); -- POINT(1.75 0) SELECT ST_AsText(lineSubstring(geometry 'Linestring(0 0,4 0,4 3)', 0.25, 0.8)); -- LINESTRING(1.75 0,4 0,4 1.6) SELECT lineLocatePoint(geometry 'Linestring(0 0,4 0,4 3)', geometry 'Point(4 4)'); -- 1
Return the collection of an array of geometries or the line joining them
collect(geometry[]) → geometry makeLine(geometry[]) → geometry
A collection takes its type from the types of the geometries alone, as the PostGIS function ST_Collect gives it, so an array of one point answers a multipoint, and a line joins the points and lines of the array in their order, as ST_MakeLine joins them. An empty array answers NULL.
SELECT ST_AsText(collect(ARRAY[geometry 'Point(1 1)'])); -- MULTIPOINT((1 1)) SELECT ST_AsText(collect(ARRAY[geometry 'Point(1 1)', geometry 'Linestring(0 0,1 1)'])); -- GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(0 0,1 1)) SELECT ST_AsText(makeLine(ARRAY[geometry 'Point(1 1)', geometry 'Point(2 2)'])); -- LINESTRING(1 1,2 2)
Return a geometry point from its coordinates and SRID
makePoint(x float, y float, srid integer) → geometry makePoint(x float, y float, z float, srid integer) → geometry
The arguments come in the order of the PostGIS functions ST_Point and ST_PointZ, the SRID last, and the form stating a z coordinate answers a 3D point. A coordinate that is not finite is refused, and an SRID beyond the range of PostGIS is converted as PostGIS converts it. A geography point is the geography of the geometry point, as geography(makePoint(x, y, 4326)).
SELECT ST_AsEWKT(makePoint(4.35, 50.85, 4326)); -- SRID=4326;POINT(4.35 50.85) SELECT ST_AsEWKT(makePoint(1, 2, 3, 3812)); -- SRID=3812;POINT(1 2 3)
Transform a geometry or a geography to a spatial reference identifier
transform(geometry,integer) → geometry transform(geography,integer) → geography
A geometry is transformed as the PostGIS function ST_Transform transforms it. A geography is transformed only into a coordinate system of longitude and latitude, as the cast of a geometry into a geography requires.
SELECT asEWKT(transform(geometry 'SRID=4326;Point(4.35 50.85)', 3857), 2); -- SRID=3857;POINT(484239.78 6594803.23) SELECT asEWKT(transform(geography 'Point(4.35 50.85)', 3857)); -- ERROR: Only lon/lat coordinate systems are supported
Densify a geometry or a geography so that no segment is longer than a distance
segmentize(geometry,float) → geometry segmentize(geography,float) → geography
A geometry is densified in the plane, the distance in the units of its spatial reference system, and a geography on the sphere, the distance in meters, as the PostGIS function ST_Segmentize densifies them. A point, a multipoint and an empty value are returned unchanged, and so are a triangle, a TIN and a polyhedral surface. A distance that is not positive is refused.
SELECT ST_AsText(segmentize(geometry 'Linestring(0 0,3 0)', 1)); -- LINESTRING(0 0,1 0,2 0,3 0) SELECT asText(segmentize(geography 'Linestring(4.35 50.85,2.35 48.85)', 100000), 4); -- LINESTRING(4.35 50.85,3.8343 50.3533,3.3293 49.8543,2.8347 49.3532,2.35 48.85)
Return the clusters of an array of geometries
clusterKMeans(geometry[],k integer) → integer[] clusterDBSCAN(geometry[],eps float,minpoints integer) → integer[] clusterIntersecting(geometry[]) → geometry[] clusterWithin(geometry[],distance float) → geometry[]
clusterKMeans and clusterDBSCAN return the number of the cluster of each geometry, in the order of the array, as the PostGIS window functions ST_ClusterKMeans and ST_ClusterDBSCAN number them over the rows in that order: an empty geometry is in the cluster -1 of k-means, and a geometry DBSCAN places in no cluster has the number NULL. clusterIntersecting and clusterWithin return a geometry collection per cluster, as the PostGIS aggregates ST_ClusterIntersecting and ST_ClusterWithin return them. An empty array answers NULL.
SELECT clusterKMeans(ARRAY[geometry 'Point(0 0)', geometry 'Point(1 0)', geometry 'Point(10 10)',
geometry 'Point(11 10)', geometry 'Point(30 30)'], 2);
-- {1,1,1,1,0}
SELECT clusterDBSCAN(ARRAY[geometry 'Point(0 0)', geometry 'Point(1 0)', geometry 'Point(10 10)',
geometry 'Point(11 10)', geometry 'Point(30 30)'], 1.5, 2);
-- {0,0,1,1,NULL}
SELECT array_length(clusterWithin(ARRAY[geometry 'Point(0 0)', geometry 'Point(1 0)', geometry 'Point(10 10)',
geometry 'Point(11 10)', geometry 'Point(30 30)'], 2), 1);
-- 3
Return the region two geometries share
intersection(geometry,geometry) → geometry
The four Boolean operations of this section answer over the (multi)polygons the temporal types whose values are regions carry, and each takes the name of the PostGIS function it answers for without the ST_ prefix, so a query reaches this engine by dropping the prefix and PostGIS by keeping it. They are planar and two-dimensional: a geography, a Z dimension and a geometry that is not a (multi)polygon are refused. An empty result is answered as NULL.
SELECT ST_AsText(intersection(geometry 'Polygon((1 1,1 5,5 5,5 1,1 1))', geometry 'Polygon((3 3,3 7,7 7,7 3,3 3))')); -- POLYGON((5 3,3 3,3 5,5 5,5 3)) SELECT ST_AsText(intersection(geometry 'Polygon((1 1,1 5,5 5,5 1,1 1))', geometry 'MultiPolygon(((2 2,2 4,3 4,3 2,2 2)),((4 2,4 4,6 4,6 2,4 2)))')); -- MULTIPOLYGON(((3 2,2 2,2 4,3 4,3 2)),((5 2,4 2,4 4,5 4,5 2))) SELECT intersection(geometry 'Polygon((1 1,1 2,2 2,2 1,1 1))', geometry 'Polygon((10 10,10 11,11 11,11 10,10 10))') IS NULL; -- true
Return the region two geometries, or an array of geometries or of geographies, cover together
geoUnion(geometry,geometry) → geometry geoUnion(geometry[]) → geometry geoUnion(geography[]) → geography
UNION is a reserved word of SQL, so this one carries the name of the type it answers over, as setUnion and spanUnion do. Two geometries that do not meet are answered as a multipolygon. The union of an array answers as the PostGIS function ST_Union over an array, and that of an empty array is NULL.
SELECT ST_AsText(geoUnion(geometry 'Polygon((1 1,1 5,5 5,5 1,1 1))', geometry 'Polygon((3 3,3 7,7 7,7 3,3 3))')); -- POLYGON((5 1,1 1,1 5,3 5,3 7,7 7,7 3,5 3,5 1)) SELECT ST_AsText(geoUnion(ARRAY[geometry 'Polygon((0 0,2 0,2 2,0 2,0 0))', 'Polygon((1 1,3 1,3 3,1 3,1 1))', 'Polygon((5 5,6 5,6 6,5 6,5 5))'])); -- MULTIPOLYGON(((5 5,6 5,6 6,5 6,5 5)),((0 0,2 0,2 1,3 1,3 3,1 3,1 2,0 2,0 0))) SELECT ST_AsText(geoUnion(ARRAY[geography 'Point(1 1)', 'Point(2 2)', 'Point(1 1)'])); -- MULTIPOINT((1 1),(2 2))
Return the union of the parts of a geometry
unaryUnion(geometry) → geometry
It answers as the PostGIS function ST_UnaryUnion, and the union of an empty geometry is that geometry.
SELECT ST_AsText(unaryUnion(geometry 'MultiPolygon(((0 0,2 0,2 2,0 2,0 0)),((1 1,3 1,3 3,1 3,1 1)))')); -- POLYGON((2 1,3 1,3 3,1 3,1 2,0 2,0 0,2 0,2 1)) SELECT ST_AsText(unaryUnion(geometry 'MultiLinestring((0 0,2 0),(1 0,3 0))')); -- LINESTRING(0 0,2 0,3 0)
Return the region of the first geometry that the second does not cover
difference(geometry,geometry) → geometry
A second geometry lying inside the first leaves a hole rather than dividing it.
SELECT ST_AsText(difference(geometry 'Polygon((1 1,1 5,5 5,5 1,1 1))', geometry 'Polygon((3 3,3 7,7 7,7 3,3 3))')); -- POLYGON((5 1,1 1,1 5,3 5,3 3,5 3,5 1)) SELECT ST_AsText(difference(geometry 'Polygon((1 1,1 5,5 5,5 1,1 1))', geometry 'Polygon((2 2,2 4,4 4,4 2,2 2))')); -- POLYGON((5 1,1 1,1 5,5 5,5 1),(2 4,2 2,4 2,4 4,2 4))
Return the region exactly one of two geometries covers
symDifference(geometry,geometry) → geometry
It is the union of the two geometries less the region they share, so a geometry against itself is answered as NULL.
SELECT ST_AsText(symDifference(geometry 'Polygon((1 1,1 5,5 5,5 1,1 1))', geometry 'Polygon((3 3,3 7,7 7,7 3,3 3))')); -- MULTIPOLYGON(((7 3,5 3,5 5,3 5,3 7,7 7,7 3)),((5 1,1 1,1 5,3 5,3 3,5 3,5 1))) SELECT symDifference(geometry 'Polygon((1 1,1 3,3 3,3 1,1 1))', geometry 'Polygon((1 1,1 3,3 3,3 1,1 1))') IS NULL; -- true