Chapter 7. Temporal Geometry Types (Part 1)

Table of Contents

Static Geometries
Spatiotemporal Types
Notation
Input and Output
Conversions
Accessors
Transformations

Static Geometries

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