LINESTRING
LINESTRING(0 0,1 1,1 2)
MULTIPOLYGON
MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2 2,1 2,1 1)), ((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1)))
POINT
POINT(0 0)
POLYGON
POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))
ST_SETSRID(column_name, SRID)
. For example, ST_SETSRID(a.pt,4326)
.ST_DISTANCE
on two geometry objects, the engine returns the shortest straight-line planar distance, in degrees, between those points. For example, the following query returns the shortest distance between the point(s) in p1
and the polygon(s) in poly1
:ST_Centroid
ST_GeomFromText(WKT)
ST_GeomFromText(WKT, SRID)
ST_GeogFromText(WKT)
ST_GeogFromText(WKT, SRID)
ST_Point(double lon, double lat)
ST_Contains(poly4326, ST_SetSRID(ST_Point(lon, lat), 4326))
ST_Buffer
SELECT ST_Buffer('LINESTRING(0 0, 10 0, 10 10)', 1.0);
SELECT ST_Buffer(poly4326, 10.0) FROM tbl;
ST_Centroid
SELECT ST_Buffer(poly4326, 10.0) FROM tbl;
.ST_Centroid
Computes the geometric center of a geometry as a POINT.ST_TRANSFORM
ST_DISTANCE(
ST_TRANSFORM(ST_GeomFromText('POINT(-71.064544 42.28787)', 4326), 900913),
ST_GeomFromText('POINT(-13189665.9329505 3960189.38265416)', 900913)
)
ST_TRANSFORM
is not currently supported in projections. It can be used only to transform geo inputs to other functions, such as ST_DISTANCE.
ST_SETSRID
ST_TRANSFORM(
ST_SETSRID(ST_GeomFromText('POINT(-71.064544 42.28787)'), 4326), 900913 )
ST_X
ST_Y
ST_XMIN
ST_XMAX
ST_YMIN
ST_YMAX
ST_STARTPOINT
ST_ENDPOINT
ST_POINTN
ST_NPOINTS
ST_NRINGS
ST_SRID
​
ST_INTERSECTION
ST_INTERSECTION
can potentially return points, lines, and polygons from a single intersection operation.
Lower-dimension intersecting features such as points and line strings are returned as very small buffers around those features. If needed, true points can be recovered by applying the ST_CENTROID method to point intersection results. In addition, ST_PERIMETER/2 of resulting line intersection polygons can be used to approximate line length.
Empty/NULL geometry outputs are not currently supported.SELECT ST_Intersection('POLYGON((0 0,3 0,3 3,0 3))', 'POLYGON((1 1,4 1,4 4,1 4))');
SELECT ST_Area(ST_Intersection(poly, 'POLYGON((1 1,3 1,3 3,1 3,1 1))')) FROM tbl;
ST_DIFFERENCE
ST_INTERSECTION
. Similar post-processing methods can be applied if needed.
Empty/NULL geometry outputs are not currently supported.SELECT ST_Difference('POLYGON((0 0,3 0,3 3,0 3))', 'POLYGON((1 1,4 1,4 4,1 4))');
SELECT ST_Area(ST_Difference(poly, 'POLYGON((1 1,3 1,3 3,1 3,1 1))')) FROM tbl;
ST_UNION
ST_INTERSECTION
. Similar post-processing methods can be applied if needed.
Empty/NULL geometry outputs are not currently supported.SELECT ST_UNION('POLYGON((0 0,3 0,3 3,0 3))', 'POLYGON((1 1,4 1,4 4,1 4))');
SELECT ST_AREA(ST_UNION(poly, 'POLYGON((1 1,3 1,3 3,1 3,1 1))')) FROM tbl;
ST_DISTANCE
ST_DISTANCE(poly1, ST_GeomFromText('POINT(0 0)'))
Returns shortest geodesic distance between two points, in meters, if given two point geographies. Point geographies can be specified through casts from point geometries or as literals. For example:
ST_DISTANCE(
CastToGeography(p2),
ST_GeogFromText('POINT(2.5559 49.0083)', 4326)
)
SELECT a.name,
ST_DISTANCE(
CAST(a.pt AS GEOGRAPHY),
CAST(b.pt AS GEOGRAPHY)
) AS dist_meters
FROM starting_point a, destination_points b;
SELECT a.poly_name FROM poly a, point b WHERE ST_DISTANCE(
ST_TRANSFORM(b.location,900913),
ST_TRANSFORM(a.heavyai_geo,900913)
) < 1000;
ST_EQUALS
SRID=4326
are compressed by default.
SELECT COUNT(*) FROM tbl WHERE ST_EQUALS('POINT(2 2)', pt);
SELECT ST_EQUALS('POLYGON ((0 0,1 0,0 1))', 'POLYGON ((0 0,0 0.5,0 1,1 0,0 0))');
ST_MAXDISTANCE
ST_CONTAINS
ST_CONTAINS
to:SELECT count(*) FROM geo1 WHERE ST_CONTAINS(poly1, 'POINT(0 0)');
SELECT a.name FROM polys a, points b WHERE ST_CONTAINS(a.heavyai_geo, b.location);
SELECT name FROM poly WHERE ST_CONTAINS(
heavyai_geo, ST_GeomFromText('POINT(-98.4886935 29.4260508)', 4326)
);
ST_INTERSECTS
SELECT ST_INTERSECTS(
'POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))',
'POINT(1 1)'
) FROM tbl;
ST_AREA
SELECT ST_AREA(
'POLYGON((1 0, 0 1, -1 0, 0 -1, 1 0),(0.1 0, 0 0.1, -0.1 0, 0 -0.1, 0.1 0))'
) FROM tbl;
ST_AREA
does not support calculation of geographic areas, but rather uses planar coordinates. Geographies must first be projected in order to use ST_AREA
. You can do this ahead of time before import or at runtime, ideally using an equal area projection (for example, a national equal-area Lambert projection). The area is calculated in the projection's units. For example, you might use Web Mercator runtime projection to get the area of a polygon in square meters:ST_AREA(
ST_TRANSFORM(
ST_GeomFromText(
'POLYGON((-76.6168198439371 39.9703199555959,
-80.5189990254673 40.6493554919257,
-82.5189990254673 42.6493554919257,
-76.6168198439371 39.9703199555959)
)', 4326
),
900913)
)
ST_PERIMETER
SELECT ST_PERIMETER('POLYGON(
(1 0, 0 1, -1 0, 0 -1, 1 0),
(0.1 0, 0 0.1, -0.1 0, 0 -0.1, 0.1 0)
)'
)
from tbl;
It will also return the geodesic perimeter of POLYGON and MULTIPOLYGON geometries. For example:SELECT ST_PERIMETER(
ST_GeogFromText(
'POLYGON(
(-76.6168198439371 39.9703199555959,
-80.5189990254673 40.6493554919257,
-82.5189990254673 42.6493554919257,
-76.6168198439371 39.9703199555959)
)',
4326)
)
from tbl;
ST_LENGTH
SELECT ST_LENGTH('LINESTRING(1 0, 0 1, -1 0, 0 -1, 1 0)') FROM tbl;
It also returns the geodesic length of LINESTRING geographies. For example:SELECT ST_LENGTH(
ST_GeogFromText('LINESTRING(
-76.6168198439371 39.9703199555959,
-80.5189990254673 40.6493554919257,
-82.5189990254673 42.6493554919257)',
4326)
) FROM tbl;
ST_WITHIN
SELECT
statement returns true:SELECT ST_WITHIN(
'POLYGON ((1 1, 1 2, 2 2, 2 1))',
'POLYGON ((0 0, 0 3, 3 3, 3 0))'
) FROM tbl;
ST_DWITHIN
SELECT ST_DWITHIN(
'POINT(1 1)',
'LINESTRING (1 2,10 10,3 3)', 2.0
) FROM tbl;
ST_DWITHIN
supports geodesic distances between geographies, currently limited to geographic points. For example, you can check whether Los Angeles and Paris, specified as WGS84 geographic point literals, are within 10,000km of one another.SELECT ST_DWITHIN(
ST_GeogFromText(
'POINT(-118.4079 33.9434)', 4326),
ST_GeogFromText('POINT(2.5559 49.0083)',
4326 ),
10000000.0) FROM tbl;
ST_DFULLYWITHIN
SELECT ST_DFULLYWITHIN(
'POINT(1 1)',
'LINESTRING (1 2,10 10,3 3)',
10.0) FROM tbl;
This function supports:ST_DFULLYWITHIN(POINT, LINESTRING, distance)
ST_DFULLYWITHIN(LINESTRING, POINT, distance)
ST_DISJOINT
SELECT ST_DISJOINT(
'POINT(1 1)',
'LINESTRING (0 0,3 3)'
) FROM tbl;
CREATE TABLE AS SELECT
is not currently supported for geo data types in distributed mode.GROUP BY
is not supported for POINT
, LINESTRING
, POLYGON
, or MULTIPOLYGON
.\d table_name
to determine if the SRID is set for the geo field:ST_SETSRID(column_name, SRID)
. For example, ST_SETSRID(myPoint, 4326)
.