Spatial Analysis

WKT (Well-Known Text) and WKB (Well-Known Binary) are two formats for representing geometric data, typically used in geographic information systems (GIS) and spatial databases. WKT is a human-readable text format — a point might be written POINT(10 20) — making it popular for exchanging geometric data between different software. WKB is a compact binary format, machine-readable and more efficient to store and transmit; the same point might be represented as a byte string such as "010100000000000000000024400000000000003440". WKB is generally preferred in GIS applications for storing and transmitting large volumes of geometric data, while WKT suits smaller datasets or situations where human readability matters. Other formats exist too, such as GeoJSON, GML, and KML, each with its own tradeoffs.

Geometric Object Specification

A geometry can be specified directly using the type name followed by one or more coordinate pairs:

[MULTI]
POINT | LINESTRING | POLYGON | GEOM[ETRY]COLLECTION
(...)

INSERT INTO my_lines (l) VALUES
  (MULTILINESTRING(LINESTRING(POINT(1,2), POINT(1,32)),
             LINESTRING(POINT(3,2), POINT(2,30)),
             LINESTRING(POINT(4,5), POINT(1,12))));

Alternatively, geometries can be built from WKT or WKB strings using the ST_ conversion functions:

CREATE TABLE my_polygons2 (
  id INT AUTO_INCREMENT PRIMARY KEY,
  polygon POLYGON
);
INSERT INTO my_polygons2 (polygon) VALUES
  (ST_POLYGONFROMTEXT('POLYGON((1 2, 3 4, 5 6, 1 2))'));

SRID

An SRID (Spatial Reference ID) identifies the spatial reference system (SRS) — the coordinate system used to accurately represent geographic features on the Earth's surface. Common SRID values include EPSG:4326 (WGS 84, used for global positioning and navigation), EPSG:3857 (Web Mercator, used for web mapping), EPSG:26918 (UTM zone 18N, used for mapping in the United States), EPSG:23030 (ED50 / UTM zone 30N, used in Europe), EPSG:32633 (WGS 84 / UTM zone 33N, used in northern Africa), and EPSG:4322 (WGS 72, the geographic system the US Defense Mapping Agency used before replacing it with WGS 84). Custom SRIDs can be defined with CREATE SPATIAL REFERENCE SYSTEM.

Format Conversion

ST_AsBinary(g [, options]) (synonym: ST_AsWKB()) converts internal geometry to its WKB representation. ST_AsText(g [, options]) (synonym: ST_AsWKT()) converts to WKT. ST_SwapXY(g) swaps the X and Y value of every coordinate pair.

SET @g = ST_LineFromText('LINESTRING(0 5,5 10,10 15)', 4326);
SELECT ST_AsText(ST_GeomFromWKB(ST_AsWKB(@g)));
SELECT ST_AsText(ST_GeomFromWKB(ST_AsWKB(@g, 'axis-order=long-lat')));

LINESTRING(5 0,10 5,15 10) — SRID 4326 defaults to lat-long axis order, so the coordinates come back swapped from how they were entered; requesting 'axis-order=long-lat' preserves the original LINESTRING(0 5,5 10,10 15).

SET @g = ST_LineFromText('LINESTRING(0 5,5 10,10 15)', 4326);

SELECT ST_AsText(ST_GeomFromWKB(ST_AsWKB(@g)));

SELECT ST_AsText(ST_GeomFromWKB(ST_AsWKB(@g, 'axis-order=long-lat')));

SET @g = ST_LineFromText('LINESTRING(0 5,5 10,10 15)', 4326);

SELECT ST_AsText(ST_GeomFromWKB(ST_AsWKB(@g)));

SELECT ST_AsText(ST_GeomFromWKB(ST_AsWKB(@g, 'axis-order=long-lat')));

SELECT ST_AsText(ST_GeomFromWKB(ST_AsWKB(@g))):

ST_AsText(ST_GeomFromWKB(ST_AsWKB(@g)))
LINESTRING(5 0,10 5,15 10)

SELECT ST_AsText(ST_GeomFromWKB(ST_AsWKB(@g, 'axis-order=long-lat'))):

ST_AsText(ST_GeomFromWKB(ST_AsWKB(@g, 'axis-order=long-lat')))
LINESTRING(0 5,5 10,10 15)

Property Functions

Geometry: ST_Dimension(g), ST_Envelope(g) (the minimum bounding rectangle), ST_GeometryType(g), ST_IsEmpty(g), ST_IsSimple(g), ST_SRID(g [, srid]).

SELECT ST_AsText(ST_Envelope(ST_GeomFromText('LineString(1 1,2 2)')));

POLYGON((1 1,2 1,2 2,1 2,1 1)) — the bounding box.

SELECT ST_AsText(ST_Envelope(ST_GeomFromText('LineString(1 1,2 2)')));

SELECT ST_AsText(ST_Envelope(ST_GeomFromText('LineString(1 1,2 2)')));

ST_AsText(ST_Envelope(ST_GeomFromText('LineString(1 1,2 2)')))
POLYGON((1 1,2 1,2 2,1 2,1 1))

Point: ST_Latitude(p [, new_val]), ST_Longitude(p [, new_val]), ST_X(p [, new_val]), ST_Y(p [, new_val]) — each returns that coordinate, or (with the optional argument) returns a new point with that coordinate replaced.

Line: ST_EndPoint(ls), ST_IsClosed(ls), ST_Length(ls [, unit]), ST_NumPoints(ls), ST_PointN(ls, N), ST_StartPoint(ls).

SET @ls = 'LineString(1 1,2 2,3 3)';
SELECT ST_AsText(ST_PointN(ST_GeomFromText(@ls),2));

POINT(2 2)

Polygon: ST_Area({poly|mpoly}), ST_Centroid({poly|mpoly}), ST_ExteriorRing(poly), ST_InteriorRingN(poly, N), ST_NumInteriorRing(poly) (synonym: ST_NumInteriorRings()).

SET @poly = 'Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))';
SELECT ST_AsText(ST_ExteriorRing(ST_GeomFromText(@poly)));

LINESTRING(0 0,0 3,3 3,3 0,0 0) — the outer ring, not the inner hole.

GeometryCollection: ST_GeometryN(gc, N), ST_NumGeometries(gc).

SET @gc = 'GeometryCollection(Point(1 1),LineString(2 2, 3 3))';
SELECT ST_NumGeometries(ST_GeomFromText(@gc));

SET @ls = 'LineString(1 1,2 2,3 3)';
SELECT ST_AsText(ST_PointN(ST_GeomFromText(@ls),2));

SET @poly = 'Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))';
SELECT ST_AsText(ST_ExteriorRing(ST_GeomFromText(@poly)));

SET @gc = 'GeometryCollection(Point(1 1),LineString(2 2, 3 3))';
SELECT ST_NumGeometries(ST_GeomFromText(@gc));

SET @ls = 'LineString(1 1,2 2,3 3)';
SELECT ST_AsText(ST_PointN(ST_GeomFromText(@ls),2));

SET @poly = 'Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))';
SELECT ST_AsText(ST_ExteriorRing(ST_GeomFromText(@poly)));

SET @gc = 'GeometryCollection(Point(1 1),LineString(2 2, 3 3))';
SELECT ST_NumGeometries(ST_GeomFromText(@gc));

SELECT ST_AsText(ST_PointN(ST_GeomFromText(@ls),2)):

ST_AsText(ST_PointN(ST_GeomFromText(@ls),2))
POINT(2 2)

SELECT ST_AsText(ST_ExteriorRing(ST_GeomFromText(@poly))):

ST_AsText(ST_ExteriorRing(ST_GeomFromText(@poly)))
LINESTRING(0 0,0 3,3 3,3 0,0 0)

SELECT ST_NumGeometries(ST_GeomFromText(@gc)):

ST_NumGeometries(ST_GeomFromText(@gc))
2

Operator Functions

ST_Buffer(g, d [, strategy1 [, strategy2 [, strategy3]]]) returns a geometry representing every point within distance d of g. ST_Buffer_Strategy(strategy [, points_per_circle]) builds a strategy byte string used to influence that computation: point strategies are 'point_circle'/'point_square', join strategies are 'join_round'/'join_miter', and end strategies are 'end_round'/'end_flat'.

SET @ls = ST_GeomFromText('LINESTRING(0 0,0 5,5 5)');
SET @end_strategy = ST_Buffer_Strategy('end_flat');
SET @join_strategy = ST_Buffer_Strategy('join_round', 10);
SELECT ST_AsText(ST_Buffer(@ls, 5, @end_strategy, @join_strategy));

POLYGON((5 5,5 10,0 10,-3.5355339059327373 8.535533905932738,-5 5,-5 0,0 0,5 0,5 5))

SET @ls = ST_GeomFromText('LINESTRING(0 0,0 5,5 5)');
SET @end_strategy = ST_Buffer_Strategy('end_flat');
SET @join_strategy = ST_Buffer_Strategy('join_round', 10);
SELECT ST_AsText(ST_Buffer(@ls, 5, @end_strategy, @join_strategy));

SET @ls = ST_GeomFromText('LINESTRING(0 0,0 5,5 5)');
SET @end_strategy = ST_Buffer_Strategy('end_flat');
SET @join_strategy = ST_Buffer_Strategy('join_round', 10);
SELECT ST_AsText(ST_Buffer(@ls, 5, @end_strategy, @join_strategy));

ST_AsText(ST_Buffer(@ls, 5, @end_strategy, @join_strategy))
POLYGON((5 5,5 10,0 10,-3.5355339059327373 8.535533905932738,-5 5,-5 0,0 0,5 0,5 5))

ST_LineInterpolatePoint(ls, fraction) returns the point along a LineString at the given fractional distance (0.0–1.0) from its start. ST_LineInterpolatePoints(ls, fraction) returns a MultiPoint of the start point plus every point at each multiple of that fraction. ST_PointAtDistance(ls, distance) returns the point at an absolute distance (in the LineString's SRS units) from its start.

SET @ls1 = ST_GeomFromText('LINESTRING(0 0,0 5,5 5)');
SELECT ST_AsText(ST_LineInterpolatePoint(@ls1, .75));
SELECT ST_AsText(ST_LineInterpolatePoints(@ls1, .25));

POINT(2.5 5); MULTIPOINT((0 2.5),(0 5),(2.5 5),(5 5))

SET @ls1 = ST_GeomFromText('LINESTRING(0 0,0 5,5 5)');

SELECT ST_AsText(ST_LineInterpolatePoint(@ls1, .75));

SELECT ST_AsText(ST_LineInterpolatePoints(@ls1, .25));

SET @ls1 = ST_GeomFromText('LINESTRING(0 0,0 5,5 5)');

SELECT ST_AsText(ST_LineInterpolatePoint(@ls1, .75));

SELECT ST_AsText(ST_LineInterpolatePoints(@ls1, .25));

SELECT ST_AsText(ST_LineInterpolatePoint(@ls1, .75)):

ST_AsText(ST_LineInterpolatePoint(@ls1, .75))
POINT(2.5 5)

SELECT ST_AsText(ST_LineInterpolatePoints(@ls1, .25)):

ST_AsText(ST_LineInterpolatePoints(@ls1, .25))
MULTIPOINT((0 2.5),(0 5),(2.5 5),(5 5))

Set operations: ST_Intersection(g1, g2), ST_Union(g1, g2), ST_Difference(g1, g2), and ST_SymDifference(g1, g2), where ST_SymDifference(g1, g2) = ST_Difference(ST_Union(g1, g2), ST_Intersection(g1, g2)). ST_ConvexHull(g) computes the smallest convex polygon (or line, if the points are colinear) enclosing every vertex of g; for a geometry collection, it works over all vertices of every member.

SET @g = 'MULTIPOINT(5 0,25 0,15 10,15 25)';
SELECT ST_AsText(ST_ConvexHull(ST_GeomFromText(@g)));

POLYGON((5 0,25 0,15 25,5 0))

ST_Transform(g, target_srid) reprojects a geometry from one spatial reference system to another.

SET @p = ST_GeomFromText('POINT(52.381389 13.064444)', 4326);
SET @p = ST_Transform(@p, 4230);
SELECT ST_AsText(@p);

POINT(52.38208611407426 13.065520672345304)

SET @g = 'MULTIPOINT(5 0,25 0,15 10,15 25)';
SELECT ST_AsText(ST_ConvexHull(ST_GeomFromText(@g)));

SET @p = ST_GeomFromText('POINT(52.381389 13.064444)', 4326);
SET @p = ST_Transform(@p, 4230);
SELECT ST_AsText(@p);

SET @g = 'MULTIPOINT(5 0,25 0,15 10,15 25)';
SELECT ST_AsText(ST_ConvexHull(ST_GeomFromText(@g)));

SET @p = ST_GeomFromText('POINT(52.381389 13.064444)', 4326);
SET @p = ST_Transform(@p, 4230);
SELECT ST_AsText(@p);

SELECT ST_AsText(ST_ConvexHull(ST_GeomFromText(@g))):

ST_AsText(ST_ConvexHull(ST_GeomFromText(@g)))
POLYGON((5 0,25 0,15 25,5 0))

SELECT ST_AsText(@p) (after ST_Transform to SRID 4230):

ST_AsText(@p)
POINT(52.38208611407426 13.065520672345304)

Aggregation

ST_Collect(g) aggregates a set of geometries into a single MultiPoint/MultiGeometry, and works both as a GROUP BY aggregate and as a window function with OVER().

SELECT year, ST_AsText(ST_Collect(DISTINCT location))
FROM product GROUP BY year;

One MULTIPOINT per year, combining every distinct location recorded that year.

CREATE TABLE product (
  year INTEGER,
  product VARCHAR(256),
  location Geometry
);
INSERT INTO product (year, product, location) VALUES
  (2000, "Calculator", ST_GeomFromText('point(60 -24)',4326)),
  (2000, "Computer", ST_GeomFromText('point(28 -77)',4326)),
  (2000, "Abacus", ST_GeomFromText('point(28 -77)',4326)),
  (2000, "TV", ST_GeomFromText('point(38 60)',4326)),
  (2001, "Calculator", ST_GeomFromText('point(60 -24)',4326)),
  (2001, "Computer", ST_GeomFromText('point(28 -77)',4326));

SELECT year, ST_AsText(ST_Collect(DISTINCT location)) FROM product GROUP BY year;

SELECT ST_AsText(ST_Collect(location)
         OVER (ORDER BY year, product ROWS BETWEEN 1 PRECEDING AND CURRENT ROW))
    FROM product;

CREATE TABLE product (
  year INTEGER,
  product VARCHAR(256),
  location Geometry
);
INSERT INTO product (year, product, location) VALUES
  (2000, "Calculator", ST_GeomFromText('point(60 -24)',4326)),
  (2000, "Computer", ST_GeomFromText('point(28 -77)',4326)),
  (2000, "Abacus", ST_GeomFromText('point(28 -77)',4326)),
  (2000, "TV", ST_GeomFromText('point(38 60)',4326)),
  (2001, "Calculator", ST_GeomFromText('point(60 -24)',4326)),
  (2001, "Computer", ST_GeomFromText('point(28 -77)',4326));

SELECT year, ST_AsText(ST_Collect(DISTINCT location)) FROM product GROUP BY year;

SELECT ST_AsText(ST_Collect(location)
         OVER (ORDER BY year, product ROWS BETWEEN 1 PRECEDING AND CURRENT ROW))
    FROM product;

SELECT year, ST_AsText(ST_Collect(DISTINCT location)) FROM product GROUP BY year:

year	ST_AsText(ST_Collect(DISTINCT location))
2000	MULTIPOINT((60 -24),(28 -77),(38 60))
2001	MULTIPOINT((60 -24),(28 -77))

SELECT ST_AsText(ST_Collect(location) OVER (ORDER BY year, product ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)) FROM product:

ST_AsText(ST_Collect(location) OVER (...))
MULTIPOINT((28 -77))
MULTIPOINT((28 -77),(60 -24))
MULTIPOINT((60 -24),(28 -77))
MULTIPOINT((28 -77),(38 60))
MULTIPOINT((38 60),(60 -24))
MULTIPOINT((60 -24),(28 -77))

GeoJSON and GeoHash

ST_AsGeoJSON(g [, max_dec_digits [, options]]) generates a GeoJSON object from a geometry. ST_GeomFromGeoJSON(str [, options [, srid]]) parses a GeoJSON string into a geometry.

SELECT ST_AsGeoJSON(ST_GeomFromText('POINT(11.11111 12.22222)'),2);

{"type": "Point", "coordinates": [11.11, 12.22]}

SET @json = '{ "type": "Point", "coordinates": [102.0, 0.0]}';
SELECT ST_AsText(ST_GeomFromGeoJSON(@json));
SELECT ST_SRID(ST_GeomFromGeoJSON(@json));
SELECT ST_AsText(ST_SRID(ST_GeomFromGeoJSON(@json),0));

POINT(0 102) (GeoJSON is longitude-latitude, but the default SRID 4326 is lat-long, so the coordinates come back swapped); 4326 (the default SRID assigned by ST_GeomFromGeoJSON()); POINT(102 0) (overriding SRID to 0 keeps the coordinates in their original GeoJSON order).

A geohash encodes a geographic location as a short alphanumeric string. ST_GeoHash(longitude, latitude, max_length) (or ST_GeoHash(point, max_length)) returns the geohash string. ST_LatFromGeoHash(str) and ST_LongFromGeoHash(str) decode the latitude/longitude. ST_PointFromGeoHash(str, srid) decodes a full POINT.

SELECT ST_GeoHash(180,0,10), ST_GeoHash(-180,-90,15);
SELECT ST_LatFromGeoHash(ST_GeoHash(45,-20,10));
SELECT ST_LongFromGeoHash(ST_GeoHash(45,-20,10));
SET @gh = ST_GeoHash(45,-20,10);
SELECT ST_AsText(ST_PointFromGeoHash(@gh,0));

'xbpbpbpbpb', '000000000000000'; -20; 45; POINT(45 -20)

SELECT ST_AsGeoJSON(ST_GeomFromText('POINT(11.11111 12.22222)'),2);

SET @json = '{ "type": "Point", "coordinates": [102.0, 0.0]}';
SELECT ST_AsText(ST_GeomFromGeoJSON(@json));
SELECT ST_SRID(ST_GeomFromGeoJSON(@json));
SELECT ST_AsText(ST_SRID(ST_GeomFromGeoJSON(@json),0));

SELECT ST_GeoHash(180,0,10), ST_GeoHash(-180,-90,15);
SELECT ST_LatFromGeoHash(ST_GeoHash(45,-20,10));
SELECT ST_LongFromGeoHash(ST_GeoHash(45,-20,10));

SET @gh = ST_GeoHash(45,-20,10);
SELECT ST_AsText(ST_PointFromGeoHash(@gh,0));

SELECT ST_AsGeoJSON(ST_GeomFromText('POINT(11.11111 12.22222)'),2);

SET @json = '{ "type": "Point", "coordinates": [102.0, 0.0]}';
SELECT ST_AsText(ST_GeomFromGeoJSON(@json));
SELECT ST_SRID(ST_GeomFromGeoJSON(@json));
SELECT ST_AsText(ST_SRID(ST_GeomFromGeoJSON(@json),0));

SELECT ST_GeoHash(180,0,10), ST_GeoHash(-180,-90,15);
SELECT ST_LatFromGeoHash(ST_GeoHash(45,-20,10));
SELECT ST_LongFromGeoHash(ST_GeoHash(45,-20,10));

SET @gh = ST_GeoHash(45,-20,10);
SELECT ST_AsText(ST_PointFromGeoHash(@gh,0));

SELECT ST_AsGeoJSON(ST_GeomFromText('POINT(11.11111 12.22222)'),2):

ST_AsGeoJSON(...)
{"type": "Point", "coordinates": [11.11, 12.22]}

SELECT ST_AsText(ST_GeomFromGeoJSON(@json)):

ST_AsText(ST_GeomFromGeoJSON(@json))
POINT(0 102)

SELECT ST_SRID(ST_GeomFromGeoJSON(@json)):

ST_SRID(ST_GeomFromGeoJSON(@json))
4326

SELECT ST_AsText(ST_SRID(ST_GeomFromGeoJSON(@json),0)):

ST_AsText(ST_SRID(ST_GeomFromGeoJSON(@json),0))
POINT(102 0)

SELECT ST_GeoHash(180,0,10), ST_GeoHash(-180,-90,15):

ST_GeoHash(180,0,10)	ST_GeoHash(-180,-90,15)
xbpbpbpbpb	000000000000000

SELECT ST_LatFromGeoHash(ST_GeoHash(45,-20,10)):

ST_LatFromGeoHash(ST_GeoHash(45,-20,10))
-20

SELECT ST_LongFromGeoHash(ST_GeoHash(45,-20,10)):

ST_LongFromGeoHash(ST_GeoHash(45,-20,10))
45

SELECT ST_AsText(ST_PointFromGeoHash(@gh,0)):

ST_AsText(ST_PointFromGeoHash(@gh,0))
POINT(45 -20)

Convenience Functions

ST_Distance_Sphere(g1, g2 [, radius]) returns the minimum spherical distance in meters between Point/MultiPoint arguments, using an optional radius (default 6,370,986 meters, Earth's mean radius); for general-purpose distance, use ST_Distance() below. ST_IsValid(g) returns 1 if g is geometrically valid. ST_MakeEnvelope(pt1, pt2) returns the rectangle enclosing two points. ST_Simplify(g, max_distance) simplifies a geometry using the Douglas–Peucker algorithm. ST_Validate(g) validates a geometry against the OGC specification, returning NULL for one that is syntactically well-formed but geometrically invalid.

SET @pt1 = ST_GeomFromText('POINT(0 0)');
SET @pt2 = ST_GeomFromText('POINT(180 0)');
SELECT ST_Distance_Sphere(@pt1, @pt2);

20015042.813723423 — roughly half of Earth's circumference.

SET @ls1 = ST_GeomFromText('LINESTRING(0 0,-0.00 0,0.0 0)');
SET @ls2 = ST_GeomFromText('LINESTRING(0 0, 1 1)');
SELECT ST_IsValid(@ls1);
SELECT ST_IsValid(@ls2);

0 (a LineString collapsed to a single repeated point is not valid); 1

SET @pt1 = ST_GeomFromText('POINT(0 0)');
SET @pt2 = ST_GeomFromText('POINT(1 1)');
SELECT ST_AsText(ST_MakeEnvelope(@pt1, @pt2));

POLYGON((0 0,1 0,1 1,0 1,0 0))

SET @g = ST_GeomFromText('LINESTRING(0 0,0 1,1 1,1 2,2 2,2 3,3 3)');
SELECT ST_AsText(ST_Simplify(@g, 0.5));
SELECT ST_AsText(ST_Simplify(@g, 1.0));

LINESTRING(0 0,0 1,1 1,2 3,3 3) (a modest tolerance drops only the points closest to the simplified path); LINESTRING(0 0,3 3) — a large tolerance collapses the line to its endpoints.

SET @ls1 = ST_GeomFromText('LINESTRING(0 0)');
SET @ls2 = ST_GeomFromText('LINESTRING(0 0, 1 1)');
SELECT ST_AsText(ST_Validate(@ls1));
SELECT ST_AsText(ST_Validate(@ls2));

NULL (a single-point LineString is syntactically parseable but not a valid LineString); LINESTRING(0 0,1 1)

SET @pt1 = ST_GeomFromText('POINT(0 0)');
SET @pt2 = ST_GeomFromText('POINT(180 0)');
SELECT ST_Distance_Sphere(@pt1, @pt2);

SET @ls1 = ST_GeomFromText('LINESTRING(0 0,-0.00 0,0.0 0)');
SET @ls2 = ST_GeomFromText('LINESTRING(0 0, 1 1)');
SELECT ST_IsValid(@ls1);
SELECT ST_IsValid(@ls2);

SET @pt1 = ST_GeomFromText('POINT(0 0)');
SET @pt2 = ST_GeomFromText('POINT(1 1)');
SELECT ST_AsText(ST_MakeEnvelope(@pt1, @pt2));

SET @g = ST_GeomFromText('LINESTRING(0 0,0 1,1 1,1 2,2 2,2 3,3 3)');
SELECT ST_AsText(ST_Simplify(@g, 0.5));
SELECT ST_AsText(ST_Simplify(@g, 1.0));

SET @ls1 = ST_GeomFromText('LINESTRING(0 0)');
SET @ls2 = ST_GeomFromText('LINESTRING(0 0, 1 1)');
SELECT ST_AsText(ST_Validate(@ls1));
SELECT ST_AsText(ST_Validate(@ls2));

SET @pt1 = ST_GeomFromText('POINT(0 0)');
SET @pt2 = ST_GeomFromText('POINT(180 0)');
SELECT ST_Distance_Sphere(@pt1, @pt2);

SET @ls1 = ST_GeomFromText('LINESTRING(0 0,-0.00 0,0.0 0)');
SET @ls2 = ST_GeomFromText('LINESTRING(0 0, 1 1)');
SELECT ST_IsValid(@ls1);
SELECT ST_IsValid(@ls2);

SET @pt1 = ST_GeomFromText('POINT(0 0)');
SET @pt2 = ST_GeomFromText('POINT(1 1)');
SELECT ST_AsText(ST_MakeEnvelope(@pt1, @pt2));

SET @g = ST_GeomFromText('LINESTRING(0 0,0 1,1 1,1 2,2 2,2 3,3 3)');
SELECT ST_AsText(ST_Simplify(@g, 0.5));
SELECT ST_AsText(ST_Simplify(@g, 1.0));

SET @ls1 = ST_GeomFromText('LINESTRING(0 0)');
SET @ls2 = ST_GeomFromText('LINESTRING(0 0, 1 1)');
SELECT ST_AsText(ST_Validate(@ls1));
SELECT ST_AsText(ST_Validate(@ls2));

SELECT ST_Distance_Sphere(@pt1, @pt2):

ST_Distance_Sphere(@pt1, @pt2)
20015042.813723423

SELECT ST_IsValid(@ls1), ST_IsValid(@ls2):

ST_IsValid(@ls1)	ST_IsValid(@ls2)
0	1

SELECT ST_AsText(ST_MakeEnvelope(@pt1, @pt2)):

ST_AsText(ST_MakeEnvelope(@pt1, @pt2))
POLYGON((0 0,1 0,1 1,0 1,0 0))

SELECT ST_AsText(ST_Simplify(@g, 0.5)), ST_AsText(ST_Simplify(@g, 1.0)):

ST_AsText(ST_Simplify(@g, 0.5))	ST_AsText(ST_Simplify(@g, 1.0))
LINESTRING(0 0,0 1,1 1,2 3,3 3)	LINESTRING(0 0,3 3)

SELECT ST_AsText(ST_Validate(@ls1)), ST_AsText(ST_Validate(@ls2)):

ST_AsText(ST_Validate(@ls1))	ST_AsText(ST_Validate(@ls2))
NULL	LINESTRING(0 0,1 1)

Relation Functions

These test the relationship between two geometries g1 and g2, either using minimum bounding rectangles (MBR prefix) or precise object shapes (ST_ prefix); the return value is 1 (true) or 0 (false), except for the distance functions, which return a distance. {MBR|ST_}Contains(g1, g2), {MBR|ST_}Within(g1, g2), MBRCovers(g1, g2), MBRCoveredBy(g1, g2).

SET @g1 = ST_GeomFromText('Polygon((0 0,1 2,2 0,0 0))');
SET @g2 = ST_GeomFromText('Point(0 1)'); -- outside the triangle but inside its MBR
SET @g3 = ST_GeomFromText('Point(1 1)'); -- center of the triangle
SELECT MBRContains(@g1,@g2), MBRWithin(@g2,@g1), MBRCovers(@g1,@g2), MBRCoveredBy(@g2,@g1);
SELECT MBRContains(@g1,@g3), MBRWithin(@g3,@g1), MBRCovers(@g1,@g3), MBRCoveredBy(@g3,@g1);

0, 0, 1, 1 — the point lies inside the bounding rectangle but outside the actual triangle; 1, 1, 1, 1 — the center point lies inside both the triangle and its MBR.

SET @g1 = ST_GeomFromText('Polygon((0 0,1 2,2 0,0 0))');
SET @g2 = ST_GeomFromText('Point(0 1)'); -- outside the triangle but inside its MBR
SET @g3 = ST_GeomFromText('Point(1 1)'); -- center of the triangle

SELECT MBRContains(@g1,@g2), MBRWithin(@g2,@g1),
       MBRCovers(@g1,@g2), MBRCoveredBy(@g2,@g1);

SELECT MBRContains(@g1,@g3), MBRWithin(@g3,@g1),
       MBRCovers(@g1,@g3), MBRCoveredBy(@g3,@g1);

SET @g1 = ST_GeomFromText('Polygon((0 0,1 2,2 0,0 0))');
SET @g2 = ST_GeomFromText('Point(0 1)'); -- outside the triangle but inside its MBR
SET @g3 = ST_GeomFromText('Point(1 1)'); -- center of the triangle

SELECT MBRContains(@g1,@g2), MBRWithin(@g2,@g1),
       MBRCovers(@g1,@g2), MBRCoveredBy(@g2,@g1);

SELECT MBRContains(@g1,@g3), MBRWithin(@g3,@g1),
       MBRCovers(@g1,@g3), MBRCoveredBy(@g3,@g1);

SELECT MBRContains(@g1,@g2), MBRWithin(@g2,@g1), MBRCovers(@g1,@g2), MBRCoveredBy(@g2,@g1):

MBRContains(@g1,@g2)	MBRWithin(@g2,@g1)	MBRCovers(@g1,@g2)	MBRCoveredBy(@g2,@g1)
0	0	1	1

SELECT MBRContains(@g1,@g3), MBRWithin(@g3,@g1), MBRCovers(@g1,@g3), MBRCoveredBy(@g3,@g1):

MBRContains(@g1,@g3)	MBRWithin(@g3,@g1)	MBRCovers(@g1,@g3)	MBRCoveredBy(@g3,@g1)
1	1	1	1

[MBR|ST_]Equals(g1, g2), {MBR|ST}_Disjoint(g1, g2), {MBR|ST}_Intersects(g1, g2), {MBR|ST}_Overlaps(g1, g2), {MBR|ST}_Touches(g1, g2), ST_Crosses(g1, g2). ST_Intersects() is true whenever the geometries share at least one point, including full containment. ST_Overlaps() is true only when they share a non-empty interior, not merely a boundary. ST_Touches() is true only when they share a boundary point but are otherwise disjoint. ST_Crosses() is true only when they intersect at a point without one containing the other and without merely sharing a boundary.

ST_Distance(g1, g2 [, unit]) returns the minimum planar distance between two geometries. ST_FrechetDistance(g1, g2 [, unit]) is a measure of similarity between two curves, defined as the minimum distance between corresponding points as both curves are traversed together in parallel. ST_HausdorffDistance(g1, g2 [, unit]) measures how far apart two point sets are, defined as the greatest of the distances from a point in one set to its nearest point in the other. For all three, unit can be 'm', 'km', 'mi', 'ft', or 'yd'.

CREATE TABLE my_polygons2 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    polygon POLYGON
);
INSERT INTO my_polygons2 (polygon) VALUES
    (ST_PolygonFromText('POLYGON((1 2, 3 4, 5 6, 1 2))'));

CREATE TABLE my_polygons2 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    polygon POLYGON
);
INSERT INTO my_polygons2 (polygon) VALUES
    (ST_PolygonFromText('POLYGON((1 2, 3 4, 5 6, 1 2))'));

SET @g = ST_GeomFromText('LineString(1 1,2 2)');
SELECT ST_AsText(ST_Envelope(@g)) AS bounding_box,
       ST_Dimension(@g) AS dims,
       ST_GeometryType(@g) AS geom_type;

SET @poly = ST_GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))');
SELECT ST_Area(@poly) AS area;

SET @g = ST_GeomFromText('LineString(1 1,2 2)');
SELECT ST_AsText(ST_Envelope(@g)) AS bounding_box,
       ST_Dimension(@g) AS dims,
       ST_GeometryType(@g) AS geom_type;

SET @poly = ST_GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))');
SELECT ST_Area(@poly) AS area;

SELECT ST_AsText(ST_Envelope(@g)) AS bounding_box, ST_Dimension(@g) AS dims, ST_GeometryType(@g) AS geom_type:

bounding_box	dims	geom_type
POLYGON((1 1,2 1,2 2,1 2,1 1))	1	LINESTRING

SELECT ST_Area(@poly) AS area:

area
8

SET @g1 = ST_GeomFromText('Polygon((0 0,1 2,2 0,0 0))');
SET @g3 = ST_GeomFromText('Point(1 1)'); -- center of the triangle

SELECT ST_Contains(@g1,@g3), ST_Within(@g3,@g1);

SET @g1 = ST_GeomFromText('Polygon((0 0,1 2,2 0,0 0))');
SET @g3 = ST_GeomFromText('Point(1 1)'); -- center of the triangle

SELECT ST_Contains(@g1,@g3), ST_Within(@g3,@g1);

ST_Contains(@g1,@g3)	ST_Within(@g3,@g1)
1	1

MENU