In release 4.1 MySQL introduces spatial extensions, which allow generation, storage and analysis of geographic features. This chapter describes:
This section describes what geographic features are and the general approach that MySQL takes for representing them.
A geographic feature is anything in the world that has a location.
A feature can be:
You can also find documents that use term geospatial feature to refer to geographic features.
Geometry is another word that denotes a geographic feature. The original meaning of the word geometry denotes a branch of mathematics. Another meaning that comes from cartography, referring to the geometric features that cartographers use to map the world.
We will mean the same thing using all these terms, a geographic feature, or a geospatial feature, or a feature, or a geometry, with geometry as the most used in this documentation.
Let's define a geometry as a point or an aggregate of points representing anything in the world that has a location.
MySQL implements spatial extensions following Open GIS specifications.
The Open GIS Consortium
(OGC), is an international consortium
of more than 250 companies, agencies, universities participating
in the development of publicly available conceptual solutions that can be
useful with all kinds of applications that manage spatial data.
See http://www.opengis.org/.
In 1997, the Open GIS Consortium published the OpenGIS (r) Simple Features Specifications For SQL, which proposes several conceptual ways for extending an SQL RDBMS to support spatial data. MySQL implements a subset of the SQL with Geometry Types environment proposed by OGC. This term refers to an SQL environment that has been extended with a set of geometry types. A geometry-valued SQL column is implemented as a column that has a geometry type. The specifications describe a set of SQL geometry types, as well as functions on those types to create and analyse geometry values.
The set of geometry types proposed by OGC's SQL with Geometry Types environment is based on the OpenGIS Geometry Model. In this model, each geometric object has the following general properties:
The geometry classes define a hierarchy. Each class has properties and may have assertions (rules that define valid class instances). The class hierarchy is as follows:
Geometry
(non-instantiable)
Point
(instantiable)
Curve
(non-instantiable)
LineString
(instantiable)
Line
LinearRing
Surface
(non-instantiable)
Polygon
(instantiable)
GeometryCollection
(instantiable)
MultiPoint
(instantiable)
MultiCurve
(non-instantiable)
MultiLineString
(instantiable)
MultiSurface
(non-instantiable)
MultiPolygon
(instantiable)
Some of these classes are abstract (non-instantiable). That is, it is not possible to create an object of these classes. Other classes are instantiable and objects may be created of them.
Geometry
is the base class. It's an abstract class.
The instantiable subclasses of Geometry
are restricted to zero-, one-,
and two-dimensional geometric objects that exist in
two-dimensional coordinate space. All instantiable geometry classes are
defined so that valid instances of a geometry class are topologically closed
(that is, all defined geometries include their boundary).
The base Geometry
class has subclasses for Point
,
Curve
, Surface
and GeometryCollection
.
Point
stands for zero-dimensional objects.
Curve
stands for one-dimensional objects, and has subclass
LineString
, with sub-subclasses Line
and LinearRing
.
Surface
is designed for two-dimensional objects and
has subclass Polygon
.
GeometryCollection
has specialised zero-, one-, and two-dimensional collection classes named
MultiPoint
, MultiLineString
, and MultiPolygon
for modelling geometries corresponding to collections of
Points
, LineStrings
, and Polygons
, respectively.
MultiCurve
and MultiSurface
are introduced as abstract superclasses
that generalise the collection interfaces to handle Curves
and Surfaces
.
Geometry
, Curve
, Surface
, MultiCurve
,
and MultiSurface
are defined as non-instantiable classes.
They define a common set of methods for their subclasses and
are included for the reason of extensibility.
Point
, LineString
, Polygon
, GeometryCollection
,
MultiPoint
, MultiLineString
, and
MultiPolygon
are instantiable classes.
Geometry
Geometry
is the root class of the hierarchy.
Each geometry is described by a number of its properties.
Particular subclasses of the root class Geometry
have their own
specific properties. Properties that are common for all geometry subclasses,
are described in the list below. Geometry
is a non-instantiable class.
A geometry value has the following properties:
((MINX MINY, MAXX MINY, MAXX MAXY, MINX MAXY, MINX MINY))
LineString
, MultiPoint
,
MultiLineString)
are either simple of non-simple. Each type determines its own assertions
for being simple or non-simple.
LineString
, MultiString
) are
either closed
or not closed. Each type determines its own assertions for being closed
or not closed.
NULL
value.
An empty geometry is defined to be always simple.
An empty geometry has an area of 0.
Point
objects have a dimension of zero. LineString
objects have a dimension of 1. Polygon
objects have a
dimension of 2. The dimensions of MultiPoint
,
MultiLineString
, and MultiPolygon
objects are the
same as the dimensions of the elements they consist of.
Coordinates are related to the SRID. For example, in different coordinate systems, the distance between two objects may differ even when objects have the same coordinates, because the distance on the planar coordinate system and the distance on the geocentric system (coordinates on the Earth's surface) are different things.
Point
A Point
is a geometry that represents a single
location in coordinate space.
Point
ExamplesPoint
PropertiesCurve
A Curve
is a one-dimensional geometry, usually represented by a sequence
of points. Particular subclasses of Curve
specify the form of the interpolation
between points. Curve
is a non-instantiable class.
Curve
PropertiesCurve
is defined as one-dimensional geometry.
Curve
is simple if it does not pass through the same point twice.
Curve
is closed if its start point is equal to its end point.
Curve
is empty.
Curve
consists of its two end points.
Curve
that is simple and closed is a Ring.
LineString
A LineString
is a Curve
with linear interpolation between points.
LineString
ExamplesLineString
objects could represent rivers.
LineString
objects could represent streets.
LineString
PropertiesLineString
segments, defined by each consecutive pair of points.
LineString
is a Line
if it consists of exactly two points.
LineString
is a LinearRing
if it's both closed and simple.
Surface
A Surface
is a two-dimensional geometric object.
Surface
PropertiesSurface
is defined as a two-dimensional geometry.
Surface
as consisting of a
single ``patch'' that is associated with one exterior boundary and zero or
more interior boundaries.
Surface
is the set of closed curves
corresponding to its exterior and interior boundaries.
The only instantiable subclass of Surface
defined in the OpenGIS
specification is Polygon
.
Polygon
A Polygon
is a planar Surface
representing a multisided geometry,
defined by one exterior boundary and zero or more interior boundaries.
Each interior boundary defines a hole in the Polygon
.
Polygon
ExamplesPolygon
objects could represent forests, districts, etc.
Polygon
AssertionsPolygon
consists of a set of LinearRing
s
(that is, LineString
s that are both simple and closed) that make up its
exterior and interior boundaries.
Polygon
may intersect at a Point
but only as a tangent.
Polygon
may not have cut lines, spikes or punctures.
Polygon
is a connected point set.
Polygon
with one or more holes is not connected.
Each hole defines a connected component of the exterior.
In the above assertions, polygons are simple geometries.
GeometryCollection
A GeometryCollection
is a geometry that is a collection of one or more
geometries of any class.
All the elements in a GeometryCollection
must be in
the same Spatial Reference (that is, in the same coordinate system).
GeometryCollection
places no other constraints on its elements.
Subclasses of GeometryCollection
described below may restrict membership
based on:
MultiPoint
A MultiPoint
is a collection whose elements are
restricted to Point
objects. The points are not connected or ordered
in any way.
MultiPoint
ExamplesMultipoint
could represent a chain of small islands.
Multipoint
could represent the outlets for a ticket
office.
MultiPoint
PropertiesMultiPoint
is defined as a zero-dimensional geometry.
MultiPoint
is simple if no two Point
values in the MultiPoint
are
equal (have identical coordinate values).
MultiPoint
is an empty set.
MultiCurve
A MultiCurve
is a geometry collection whose elements are
Curve
values. MultiCurve
is a non-instantiable class.
MultiCurve
PropertiesMultiCurve
is simple if and only if all of its elements are simple,
the only intersections between any two elements occur at points that are
on the boundaries of both elements.
MultiCurve
is obtained by applying the ``mod 2 union
rule'':
A point is in the boundary of a MultiCurve
if it is in the boundaries of
an odd number of elements of the MultiCurve
.
MultiCurve
is defined as a one-dimensional geometry.
MultiCurve
is closed if all of its elements are closed.
MultiCurve
is always empty.
MultiLineString
A MultiLineString
is a MultiCurve
whose elements are
LineString
values.
MultiLineString
ExamplesMultiLineString
could represent a river system or
a highway system.
MultiSurface
A MultiSurface
is a geometric collection whose elements are surfaces.
MultiSurface
is a non-instantiable class.
MultiSurface
AssertionsMultiSurface
may not intersect.
MultiSurface
may
intersect at most at a finite number of points.
The only instantiable subclass of MultiSurface
is MultiPolygon
.
MultiPolygon
A MultiPolygon
is a MultiSurface
whose elements are
Polygon
values.
MultiPolygon
ExamplesMultiPolygon
could represent a system of lakes.
MultiPolygon
AssertionsPolygon
values that are elements of a
MultiPolygon
may not intersect.
Polygon
values that are elements of a
MultiPolygon
may
not cross and may touch at only a finite number of points.
(Note that crossing is already forbidden by the first assertion.)
MultiPolygon
may not have cut lines, spikes or punctures; a
MultiPolygon
is a Regular, Closed point set.
MultiPolygon
with more than one Polygon
is not connected, the number of connected components of the interior
of a MultiPolygon
is equal to the number of Polygon
values in
the MultiPolygon
.
MultiPolygon
PropertiesMultiPolygon
is defined as a two-dimensional geometry.
MultiPolygon
is a set of closed curves
(LineString
values) corresponding to the boundaries of its element
Polygon
values.
Curve
in the boundary of the MultiPolygon
is in the
boundary of exactly one element Polygon
, and every Curve
in the boundary of an element Polygon
is in the boundary of the
MultiPolygon
.
This section describes the standard spatial data formats that are used to represent geometry objects in queries. They are:
Internally, MySQL stores geometry values in a format that is not identical to either WKT or WKB format.
The Well-Known Text (WKT) representation of Geometry is designed to exchange geometry data in ASCII form.
Examples of WKT representations of geometry objects are:
Point
:
POINT(10 10)Note that point coordinates are specified with no separating comma.
LineString
with three points:
LINESTRING(10 10, 20 20, 30 40)
Polygon
with one exterior ring and zero interior rings:
POLYGON((10 10, 10 20, 20 20, 20 15, 10 10))
MultiPoint
with two Point
values:
MULTIPOINT(10 10, 20 20)
MultiLineString
with two LineString
values:
MULTILINESTRING((10 10, 20 20), (15 15, 30 15))
MultiPolygon
with two Polygon
values:
MULTIPOLYGON(((10 10, 10 20, 20 20, 20 15, 10 10)), ((60 60, 70 7, 80 60, 60 60 )))
GeometryCollection
consisting of two Point
values and one
LineString
:
GEOMETRYCOLLECTION(POINT(10 10), POINT(30 30), LINESTRING(15 15, 20 20))
A Backus-Naur grammer that specifies the formal production rules for writing WKT values may be found in the OGC specification document referenced near the beginning of this chapter.
The well-Known Binary (WKB) representation for geometric values is defined by the OpenGIS specifications. It's also defined in the ISO ``SQL/MM Part 3: Spatial'' standard.
WKB is used to exchange geometry data as binary streams represented by
BLOB
values containing geometric WKB information.
The basic WKB type definitions use the structures shown in the following
sections.
// byte : 8-bit unsigned integer (1 byte) // uint32 : 32-bit unsigned integer (4 bytes) // double : double precision number (8 bytes) enum wkbGeometryType { wkbPoint = 1, wkbLineString = 2, wkbPolygon = 3, wkbMultiPoint = 4, wkbMultiLineString = 5, wkbMultiPolygon = 6, wkbGeometryCollection = 7 } enum wkbByteOrder { wkbXDR = 0, // Big Endian wkbNDR = 1 // Little Endian }
// Building Blocks : Point, LinearRing Point { double x; double y; } LinearRing { uint32 numPoints; Point points[numPoints]; }
WKBPoint { byte byteOrder; uint32 wkbType; // 1 Point point; } WKBLineString { byte byteOrder; uint32 wkbType; // 2 uint32 numPoints; Point points[numPoints]; } WKBPolygon { byte byteOrder; uint32 wkbType; // 3 uint32 numRings; LinearRing rings[numRings]; } WKBMultiPoint { byte byteOrder; uint32 wkbType; // 4 uint32 num_wkbPoints; WKBPoint WKBPoints[num_wkbPoints]; } WKBMultiLineString { byte byteOrder; uint32 wkbType; // 5 uint32 num_wkbLineStrings; WKBLineString WKBLineStrings[num_wkbLineStrings]; } wkbMultiPolygon { byte byteOrder; uint32 wkbType; // 6 uint32 num_wkbPolygons; WKBPolygon wkbPolygons[num_wkbPolygons]; } WKBGeometry { union { WKBPoint point; WKBLineString linestring; WKBPolygon polygon; WKBGeometryCollection collection; WKBMultiPoint mpoint; WKBMultiLineString mlinestring; WKBMultiPolygon mpolygon; } } WKBGeometryCollection { byte byte_order; uint32 wkbType; // 7 uint32 num_wkbGeometries; WKBGeometry wkbGeometries[num_wkbGeometries]; }
A WKB that corresponds to POINT(1,1)
has this
sequence of 21 bytes (each represented by two hex digits):
0101000000000000000000F03F000000000000F03F
The sequence may be broken down into the following components:
Byte order : 01 WKB type : 01000000 X : 000000000000F03F Y : 000000000000F03F
MySQL provides a set of datatypes that correspond to classes in the class hierarchy of the OpenGIS Geometry Model:
GEOMETRY
POINT
LINESTRING
POLYGON
MULTIPOINT
MULTILINESTRING
MULTIPOLYGON
GEOMETRYCOLLECTION
GEOMETRY
is the most general of these types; it can store geometry
values of any type.
Other types restrict their values to a particular geometry type.
GEOMETRYCOLLECTION
can store a collection of objects
of any type. Other collection types (those beginning with MULTI
)
restrict collection members to those having a particular geometry type.
This section describes how to create spatial values using Well-Known Text and Well-Known Binary functions that are defined in the OpenGIS standard, and using MySQL-specific functions.
MySQL provides a number of functions that take as input parameters a Well-Known Text representation and, optionally, a spatial reference system identifier (SRID), and return the corresponding geometry.
GeomFromText()
accepts a WKT of any geometry type as its first
argument. For construction of geometry values restricted to a particular
type, an implementation also provides a type-specific construction
function for each geometry type.
GeomFromText(wkt,srid)
GeometryFromText(wkt,srid)
PointFromText(wkt,srid)
POINT
using its WKT representation and SRID.
LineFromText(wkt,srid)
LineStringFromText(wkt,srid)
LINESTRING
using its WKT representation and SRID.
PolyFromText(wkt,srid)
PolygonFromText(wkt,srid)
POLYGON
using its WKT representation and SRID.
MPointFromText(wkt,srid)
MultiPointFromText(wkt,srid)
MULTIPOINT
using its WKT representation and SRID.
MLineFromText(wkt,srid)
MultiLineStringFromText(wkt,srid)
MULTILINESTRING
using its WKT representation and SRID.
MPolyFromText(wkt,srid)
MultiPolygonFromText(wkt,srid)
MULTIPOLYGON
using its WKT representation and SRID.
GeomCollFromText(wkt,srid)
GeometryCollectionFromText(wkt,srid)
GEOMETRYCOLLECTION
using its WKT representation and SRID.
As an optional feature, an implementation may also support building
of Polygon
or MultiPolygon
values, given an arbitrary
collection of possibly intersecting rings or closed LineString
values. Implementations that support this feature should include the
following functions (Note: MySQL does not yet implement these):
BdPolyFromText(multiLineStringTaggedText String, SRID Integer):Polygon
LineString
values,
constructs a Polygon
as a MultiLineString
text representation.
BdMPolyFromText(multiLineStringTaggedText String, SRID Integer):MultiPolygon
LineString
values,
constructs a MultiPolygon
as a MultiLineString
text
representation.
MySQL provides a number of of functions that take as input parameters a
BLOB
containing a Well-Known Binary representation and, optionally,
a spatial reference system identifier (SRID), and return the corresponding
geometry.
GeomFromWKB()
accepts a WKB of any geometry type as its first
argument. For construction of geometry values restricted to a particular
type, an implementation also provides a specific construction function
for each geometry type.
GeomFromWKB(wkb,srid)
GeometryFromWKB(wkt,srid)
PointFromWKB(wkb,srid)
POINT
using its WKB representation and SRID.
LineFromWKB(wkb,srid)
LineStringFromWKB(wkb,srid)
LINESTRING
using its WKB representation and SRID.
PolyFromWKB(wkb,srid)
PolygonFromWKB(wkb,srid)
POLYGON
using its WKB representation and SRID.
MPointFromWKB(wkb,srid)
MultiPointFromWKB(wkb,srid)
MULTIPOINT
using its WKB representation and SRID.
MLineFromWKB(wkb,srid)
MultiLineStringFromWKB(wkb,srid)
MULTILINESTRING
using its WKB representation and SRID.
MPolyFromWKB(wkb,srid)
MultiPolygonFromWKB(wkb,srid)
MULTIPOLYGON
using its WKB representation and SRID.
GeomCollFromWKB(wkb,srid)
GeometryCollectionFromWKB(wkt,srid)
GEOMETRYCOLLECTION
using its WKB representation and SRID.
As an optional feature, an implementation may also support the building
of Polygon
or MultiPolygon
values given an arbitrary
collection of possibly intersecting rings or closed LineString
values. Implementations that support this feature should include the
following functions (Note: MySQL does not yet implement these):
BdPolyFromWKB(WKBMultiLineString Binary,SRID Integer): Polygon
Polygon
given an arbitrary collection of closed
linestrings as a MultiLineString
binary representation.
BdMPolyFromWKB(WKBMultiLineString Binary, SRID Integer):MultiPolygon
MultiPolygon
given an arbitrary collection of closed
linestrings as a MultiLineString
binary representation.
Note: MySQL does not yet implement the functions listed in this section.
MySQL provides a set of useful functions for creating geometry WKB
representations. The functions described in this section are MySQL
extensions to the OpenGIS specifications. The results of these
functions are BLOB
values containing geometry WKB representations (no
SRID).
The results of these functions can be substituted as the first argument
for any function in the GeomFromWKB()
function family.
Point(x,y)
WKBPoint
using its coordinates.
MultiPoint(WKBPoint,WKBPoint,...,WKBPoint)
WKBMultiPoint
using WKBPoint
arguments.
If any argument is not a WKBPoint
, the return value is NULL
.
LineString(WKBPoint,WKBPoint,...,WKBPoint)
WKBLineString
from a number of WKBPoint
arguments. If any argument is not a WKBPoint
, the return value
is NULL
. If the number of WKBPoint
arguments is less than two,
the return value is NULL
.
MultiLineString(WKBLineString,WKBLineString,...,WKBLineString)
WKBMultiLineString
using using WKBLineString
arguments. If any argument is not a WKBLineString
, the return
value is NULL
.
Polygon(WKBLineString,WKBLineString,...,WKBLineString)
Polygon
from a number of WKBLineString
arguments. If any argument is not representing WKB of a LinearRing
(that is, not a closed and simple LineString
) the return value
is NULL
.
MultiPolygon(WKBPolygon,WKBPolygon,...,WKBPolygon)
WKBMultiPolygon
from a set of WKBPolygon
arguments.
If any argument is not a WKBPolygon
, the rerurn value is NULL
.
GeometryCollection(WKBGeometry,WKBGeometry,..,WKBGeometry)
GeometryCollection
. If any argument is not a
well-formed WKB representation of a geometry, the return value is
NULL
.
MySQL provides a standard way of creating spatial columns for
geometry types, for example, with CREATE TABLE
or ALTER TABLE
.
CREATE TABLE
statement to create a table with a spatial column:
mysql> CREATE TABLE g1 (p1 GEOMETRY); Query OK, 0 rows affected (0.02 sec)
ALTER TABLE
statement to add a spatial column to an
existing table:
mysql> ALTER TABLE g1 ADD p2 POINT; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0
After you have created spatial columns, you can populate them with your spatial data.
To populate spatially-enabled columns, MySQL supports two spatial formats (described previously), Well Known Text (WKT) and Well-Known Binary (WKB) representation.
INSERT INTO geom VALUES (GeomFromText('POINT(1 1)')); INSERT INTO geom VALUES (GeomFromText('LINESTRING(0 0,1 1,2 2)')); INSERT INTO geom VALUES (GeomFromText('POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))')); INSERT INTO geom VALUES (GeomFromText('GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(0 0,1 1,2 2,3 3,4 4))')); INSERT INTO geom VALUES (PointFromText('POINT(1 1)')); INSERT INTO geom VALUES (LineStringFromText('LINESTRING(0 0,1 1,2 2)')); INSERT INTO geom VALUES (PolygomFromText('POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))')); INSERT INTO geom VALUES (GeomCollFromText('GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(0 0,1 1,2 2,3 3,4 4))'));
Note that if a client application program wants to use WKB representations of geometry values, it is responsible for sending correctly formed WKB in queries to the server. There are, however, several ways of satisfying this requirement. For example:
mysql> INSERT INTO geom VALUES -> (GeomFromWKB(0x0101000000000000000000F03F000000000000F03F));
BLOB
type:
INSERT INTO geom VALUES (GeomFromWKB(?))Other programming interfaces may support a similar placeholder mechanism.
mysql_real_escape_string()
and include the result in a query string
that is sent to the server.
See section 9.1.3.43 mysql_real_escape_string()
.
Geometry values stored in a table can be fetched in either WKT or WKB representations.
The AsText()
function provides textual access to geometry values
by converting each into a WKT string.
mysql> SELECT AsText(p1) FROM g1; +-------------------------+ | AsText(p1) | +-------------------------+ | POINT(1 1) | | LINESTRING(0 0,1 1,2 2) | +-------------------------+
The AsBinary()
function provides binary
access to geometry values by converting each into its WKB representation and
returning the BLOB
containing the value.
SELECT AsBinary(g) FROM geom;
After populating spatial columns with values, you are ready to query and analyse them. MySQL provides a set of functions to perform various operations on spatial data. These functions can be labeled into four major groups according to the type of operation they perform:
Spatial analysis functions can be used in many contexts, such as:
mysql
or MySQLCC
MySQL supports the following functions for converting geometry values between internal format and either WKT or WKB format:
GeomFromText(string wkt [,integer srid]): geometry
GeomFromWKB(binary wkb [,integer srid]): geometry
AsText(geometry g): string
mysql> SELECT AsText(GeomFromText('LineString(1 1,2 2,3 3)')); +-------------------------------------------------+ | AsText(GeomFromText('LineString(1 1,2 2,3 3)')) | +-------------------------------------------------+ | LINESTRING(1 1,2 2,3 3) | +-------------------------------------------------+
AsBinary(geometry g): binary
Geometry
Properties
Functions that belong to this group take a geometry value as their
argument and return some quantitive or qualitive property of this
geometry. Some functions restrict their argument type. Such functions
return NULL
if the passed geometry is of an incorrect geometry
type. For example, Area()
returns NULL
if the object
type is neither Polygon
nor MultiPolygon
.
Geometry
PropertiesThese functions don't restrict their argument and accept a geometry value of any type.
GeometryType(geometry g):string
mysql> SELECT GeometryType(GeomFromText('POINT(1 1)')); +------------------------------------------+ | GeometryType(GeomFromText('POINT(1 1)')) | +------------------------------------------+ | POINT | +------------------------------------------+
Dimension(geometry g):integer
Geometry
.)
mysql> SELECT Dimension(GeomFromText('LineString(1 1,2 2)')); +------------------------------------------------+ | Dimension(GeomFromText('LineString(1 1,2 2)')) | +------------------------------------------------+ | 1 | +------------------------------------------------+
SRID(geometry g):integer
mysql> SELECT SRID(GeomFromText('LineString(1 1,2 2)',101)); +-----------------------------------------------+ | SRID(GeomFromText('LineString(1 1,2 2)',101)) | +-----------------------------------------------+ | 101 | +-----------------------------------------------+
Envelope(geometry g):geometry
POLYGON((MINX MINY, MAXX MINY, MAXX MAXY, MINX MAXY, MINX MINY))
mysql> SELECT AsText(Envelope(GeomFromText('LineString(1 1,2 2)'))); +-------------------------------------------------------+ | AsText(Envelope(GeomFromText('LineString(1 1,2 2)'))) | +-------------------------------------------------------+ | POLYGON((10.41032409668 1,1 2,1 2,2 1,2 1)) | +-------------------------------------------------------+
Note: MySQL does not yet implement the following functions:
Boundary(g:Geometry):Geometry
Geometry
.
IsEmpty(geometry g):Integer
Geometry
is the empty geometry.
If true, then this Geometry
represents the empty point set.
IsSimple(geometry g):Integer
Geometry
has no anomalous geometric points,
such as self intersection or self tangency. The description of each
instantiable geometric class includes the specific conditions that
cause an instance of that class to be classified as not simple.
Point
Properties
A Point
consists of its X and Y coordinates, which may be obtained
using the following functions:
X(point p):Double
mysql> SELECT X(GeomFromText('Point(56.7 53.34)')); +--------------------------------------+ | X(GeomFromText('Point(56.7 53.34)')) | +--------------------------------------+ | 56.7 | +--------------------------------------+
Y(point p):Double
mysql> SELECT Y(GeomFromText('Point(56.7 53.34)')); +--------------------------------------+ | Y(GeomFromText('Point(56.7 53.34)')) | +--------------------------------------+ | 53.34 | +--------------------------------------+
LineString
Properties
A LineString
consists of Point
values. You can extract
particular points, count the number of points, or obtain the length of a
LineString
.
EndPoint(LineString l):Point
LineString
.
mysql> SELECT AsText(EndPoint(GeomFromText('LineString(1 1,2 2,3 3)'))); +------------------------------------------------------------+ | AsText(EndPoint(GeomFromText('LineString(1 1,2 2,3 3)'))) | +------------------------------------------------------------+ | POINT(3 3) | +------------------------------------------------------------+
GLength(LineString l):Double
LineString
in its associated spatial reference.
mysql> SELECT GLength(GeomFromText('LineString(1 1,2 2,3 3)')); +--------------------------------------------------+ | GLength(GeomFromText('LineString(1 1,2 2,3 3)')) | +--------------------------------------------------+ | 2.8284271247462 | +--------------------------------------------------+
IsClosed(LineString l):Integer
LineString
is closed
(the StartPoint()
and EndPoint()
values are the same).
mysql> SELECT IsClosed(GeomFromText('LineString(1 1,2 2,3 3)')); +---------------------------------------------------+ | IsClosed(GeomFromText('LineString(1 1,2 2,3 3)')) | +---------------------------------------------------+ | 0 | +---------------------------------------------------+
NumPoints(LineString l):Integer
LineString
.
mysql> SELECT NumPoints(GeomFromText('LineString(1 1,2 2,3 3)')); +----------------------------------------------------+ | NumPoints(GeomFromText('LineString(1 1,2 2,3 3)')) | +----------------------------------------------------+ | 3 | +----------------------------------------------------+
PointN(LineString l,integer n):Point
Linestring
.
Point numbers begin at 1.
mysql> SELECT AsText(PointN(GeomFromText('LineString(1 1,2 2,3 3)'),2)); +-----------------------------------------------------------+ | AsText(PointN(GeomFromText('LineString(1 1,2 2,3 3)'),2)) | +-----------------------------------------------------------+ | POINT(2 2) | +-----------------------------------------------------------+
StartPoint(LineString l):Point
LineString
.
mysql> SELECT AsText(StartPoint(GeomFromText('LineString(1 1,2 2,3 3)'))); +-------------------------------------------------------------+ | AsText(StartPoint(GeomFromText('LineString(1 1,2 2,3 3)'))) | +-------------------------------------------------------------+ | POINT(1 1) | +-------------------------------------------------------------+
Note: MySQL does not yet implement the following function:
IsRing(LineString l):Integer
LineString
is closed
(the StartPoint()
and EndPoint()
values are the same)
and is simple (does not pass through the same point more than once).
MultiLineString
PropertiesGLength(MultiLineString m):Double
MultiLineString
, which is equal to the sum of
the lengths of the elements.
mysql> SELECT GLength(GeomFromText('MultiLineString((1 1,2 2,3 3),(4 4,5 5))')); +-------------------------------------------------------------------+ | GLength(GeomFromText('MultiLineString((1 1,2 2,3 3),(4 4,5 5))')) | +-------------------------------------------------------------------+ | 4.2426406871193 | +-------------------------------------------------------------------+
IsClosed(MultiLineString m):Integer
MultiLineString
is closed
(the StartPoint()
and EndPoint()
values are the same
for each LineString
in this MultiLineString
).
mysql> SELECT IsClosed(GeomFromText('MultiLineString((1 1,2 2,3 3),(4 4,5 5))')); +--------------------------------------------------------------------+ | IsClosed(GeomFromText('MultiLineString((1 1,2 2,3 3),(4 4,5 5))')) | +--------------------------------------------------------------------+ | 0 | +--------------------------------------------------------------------+
Polygon
PropertiesArea(Polygon p):Double
Polygon
, as measured in its spatial reference system.
mysql> SELECT Area(GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))')); +----------------------------------------------------------------------------+ | Area(GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))')) | +----------------------------------------------------------------------------+ | 8 | +----------------------------------------------------------------------------+
NumInteriorRings(Polygon p):Integer
mysql> SELECT NumInteriorRings(GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))')); +----------------------------------------------------------------------------------------+ | NumInteriorRings(GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))')) | +----------------------------------------------------------------------------------------+ | 1 | +----------------------------------------------------------------------------------------+
ExteriorRing(Polygon p):LineString
Polygon
as a LineString
.
mysql> SELECT AsText(ExteriorRing(GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))'))); +--------------------------------------------------------------------------------------------+ | AsText(ExteriorRing(GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))'))) | +--------------------------------------------------------------------------------------------+ | LINESTRING(0 0,0 3,3 3,3 0,0 0) | +--------------------------------------------------------------------------------------------+
InteriorRingN(Polygon p, Integer n):LineString
Polygon
as a LineString
.
Ring numbers begin at 1.
mysql> SELECT AsText(InteriorRingN(GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))'),1)); +-----------------------------------------------------------------------------------------------+ | AsText(InteriorRingN(GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))'),1)) | +-----------------------------------------------------------------------------------------------+ | LINESTRING(1 1,1 2,2 2,2 1,1 1) | +-----------------------------------------------------------------------------------------------+
Note: MySQL does not yet implement the following functions:
Centroid(Polygon p):Point
PointOnSurface(p:Polygon):Point
MultiPolygon
PropertiesArea(MultiPolygon m):Double
MultiPolygon
, as measured in its spatial
reference system.
mysql> SELECT Area(GeomFromText('MultiPolygon(((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1)))')); +-----------------------------------------------------------------------------------+ | Area(GeomFromText('MultiPolygon(((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1)))')) | +-----------------------------------------------------------------------------------+ | 8 | +-----------------------------------------------------------------------------------+
Note: MySQL does not yet implement the following functions:
Centroid(MultiPolygon p):Point
MultiPolygon
as a Point
.
The result is not guaranteed to be on this MultiPolygon
.
PointOnSurface(MultiPolygon m):Point
Point
guaranteed to be on this MultiPolygon
.
GeometryCollection
PropertiesNumGeometries(GeometryCollection g):Integer
GeometryCollection
.
mysql> SELECT NumGeometries(GeomFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))')); +------------------------------------------------------------------------------------+ | NumGeometries(GeomFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))')) | +------------------------------------------------------------------------------------+ | 2 | +------------------------------------------------------------------------------------+
GeometryN(GeometryCollection g,integer N):Geometry
GeometryCollection
.
Geometry numbers begin at 1.
mysql> SELECT AsText(GeometryN(GeomFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))'),1)); +------------------------------------------------------------------------------------------+ | AsText(GeometryN(GeomFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))'),1)) | +------------------------------------------------------------------------------------------+ | POINT(1 1) | +------------------------------------------------------------------------------------------+
In the section section 10.5.2 Functions To Analyse Geometry
Properties,
we've already discussed some functions that can construct new geometries
from the existing ones:
Envelope(geometry g):geometry
StartPoint(LineString l):Point
EndPoint(LineString l):Point
PointN(LineString l,integer n):Point
ExteriorRing(Polygon p):LineString
InteriorRingN(Polygon p, Integer n):LineString
GeometryN(GeometryCollection g,integer n):Geometry
OpenGIS proposes a number of other functions that can produce geometries. They are designed to implement Spatial Operators.
Note: These functions are not yet implemented. They should appear in future releases.
Intersection(Geometry g1,g2):Geometry
g1
with
g2
.
Union(Geometry g1,g2):Geometry
g1
with g2
.
Difference(Geometry g1,g2):Geometry
g1
with
g2
.
SymDifference(Geometry g1,g2):Geometry
g1
with g2
.
Buffer(Geometry g, double d):Geometry
g
is less than or equal to distance of d
.
ConvexHull(Geometry g):Geometry
g
.
The functions described in these sections take two geometries as input parameters and return a qualitive or quantitive relation between them.
The current release provides some functions that can test relations between mininal bounding rectangles of two geometries. They include:
MBRContains(g1,g2)
g1
contains the Minimum Bounding Rectangle of g2
.
mysql> SELECT MBRContains(GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'),GeomFromText('Point(1 1)')); +----------------------------------------------------------------------------------------+ | MBRContains(GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'),GeomFromText('Point(1 1)')) | +----------------------------------------------------------------------------------------+ | 1 | +----------------------------------------------------------------------------------------+
MBRWithin(g1,g2)
g1
is within the Minimum Bounding Rectangle of g2
.
mysql> SELECT MBRWithin(GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'),GeomFromText('Polygon((0 0,0 5,5 5,5 0,0 0))')); +----------------------------------------------------------------------------------------------------------+ | MBRWithin(GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'),GeomFromText('Polygon((0 0,0 5,5 5,5 0,0 0))')) | +----------------------------------------------------------------------------------------------------------+ | 1 | +----------------------------------------------------------------------------------------------------------+
MBRDisjoint(g1,g2)
MBREquals(g1,g2)
MBRIntersects(g1,g2)
MBROverlaps(g1,g2)
MBRTouches(g1,g2)
Note: The functions in the following list are not yet implemented. When implemented, they will provide full (not MBR-based only) support for spatial analysis.
Contains(g1,g2)
g1
completely contains
g2
.
Crosses(g1,g2)
g1
spatially crosses g2
.
Returns NULL
if g1
is a polygon or a multipolygon, or if
g2
is a point or a multipoint.
Otherwise, returns 0.
The term spatially crosses denotes a spatial relation between two given
geometries that has the following properties:
Disjoint(g1,g2)
g1
is spatially disjoint
from (does not intersect) g2
.
Equals(g1,g2)
g1
is spatially equal to
g2
.
Intersects(g1,g2)
g1
spatially intersects
g2
.
Overlaps(g1,g2)
g1
spatially overlaps
g2
.
The term spatially overlaps
is used if two
geometries intersect and their intersection results in a geometry of the
same dimension but not equal to either of the given geometries.
Touches(g1,g2)
g1
spatially touches
g2
. Two geometries spatially touch if the interiors of
the geometries do not intersect, but the boundary of one of the geometries
intersects either the boundary or the interior of the other.
Within(g1,g2)
g1
is spatially within
g2
.
Distance(g1:Geometry,g2:Geometry):Double
Related(g1:Geometry,g2:Geometry,pattern_matrix:string):Double
pattern_matrix
exists between g1
and g2
.
The pattern matrix is a string. Its specification will be noted here when this
function is implemented.
It is known that search operations in non-spatial databases can be optimised using indexes. This is true for spatial databases as well. With the help of a great variety of multi-dimensional indexing methods which have already been designed, it's possible to optimise spatial searches, the most typical of which are:
MySQL utilises R-Trees with quadratic splitting to index spatial columns. A spatial index is built using the MBR of a geometry. For most geometries, the MBR is a minimum rectangle that surrounds the geometries. For a horizontal or a vertical linestring, the MBR is a rectangle degenerated into the linestring. For a point, the MBR is a rectangle degenerated into the point.
MySQL can create spatial indexes in the same way it
can create regular indexes. The normal syntax for creating
indexes is extended with the SPATIAL
keyword:
CREATE TABLE
:
mysql> CREATE TABLE g (g GEOMETRY NOT NULL, SPATIAL INDEX(g));
ALTER TABLE
:
mysql> ALTER TABLE g ADD SPATIAL INDEX(g);
CREATE INDEX
:
mysql> CREATE SPATIAL INDEX sp_index ON g (g);
To drop spatial indexes, use ALTER TABLE
or DROP INDEX
:
ALTER TABLE
:
mysql> ALTER TABLE g DROP INDEX g;
DROP INDEX
:
mysql> DROP INDEX sp_index ON g;
Example: Suppose that a table g
contains more than 32000 geometries,
which are stored in the column g
of type GEOMETRY
.
The table also has an AUTO_INCREMENT
column fid
for storing
object IDs.
mysql> SHOW FIELDS FROM g; +-------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+----------------+ | fid | int(11) | | PRI | NULL | auto_increment | | g | geometry | | | | | +-------+----------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) mysql> SELECT COUNT(*) FROM g; +----------+ | count(*) | +----------+ | 32376 | +----------+ 1 row in set (0.00 sec)
To add a spatial index on the column g
, use this statement:
mysql> ALTER TABLE g ADD SPATIAL INDEX(g); Query OK, 32376 rows affected (4.05 sec) Records: 32376 Duplicates: 0 Warnings: 0
The optimiser investigates whether available spatial indexes can
be involved in the search for queries that use a function like
MBRContains()
or MBRWithin()
in the WHERE
clause.
For example, let's say we want to find all objects that are in the
given rectangle:
mysql> SELECT fid,AsText(g) FROM g WHERE mysql> MBRContains(GeomFromText('Polygon((30000 15000,31000 15000,31000 16000,30000 16000,30000 15000))'),g); +-----+-----------------------------------------------------------------------------+ | fid | AsText(g) | +-----+-----------------------------------------------------------------------------+ | 21 | LINESTRING(30350.4 15828.8,30350.6 15845,30333.8 15845,30333.8 15828.8) | | 22 | LINESTRING(30350.6 15871.4,30350.6 15887.8,30334 15887.8,30334 15871.4) | | 23 | LINESTRING(30350.6 15914.2,30350.6 15930.4,30334 15930.4,30334 15914.2) | | 24 | LINESTRING(30290.2 15823,30290.2 15839.4,30273.4 15839.4,30273.4 15823) | | 25 | LINESTRING(30291.4 15866.2,30291.6 15882.4,30274.8 15882.4,30274.8 15866.2) | | 26 | LINESTRING(30291.6 15918.2,30291.6 15934.4,30275 15934.4,30275 15918.2) | | 249 | LINESTRING(30337.8 15938.6,30337.8 15946.8,30320.4 15946.8,30320.4 15938.4) | | 1 | LINESTRING(30250.4 15129.2,30248.8 15138.4,30238.2 15136.4,30240 15127.2) | | 2 | LINESTRING(30220.2 15122.8,30217.2 15137.8,30207.6 15136,30210.4 15121) | | 3 | LINESTRING(30179 15114.4,30176.6 15129.4,30167 15128,30169 15113) | | 4 | LINESTRING(30155.2 15121.4,30140.4 15118.6,30142 15109,30157 15111.6) | | 5 | LINESTRING(30192.4 15085,30177.6 15082.2,30179.2 15072.4,30194.2 15075.2) | | 6 | LINESTRING(30244 15087,30229 15086.2,30229.4 15076.4,30244.6 15077) | | 7 | LINESTRING(30200.6 15059.4,30185.6 15058.6,30186 15048.8,30201.2 15049.4) | | 10 | LINESTRING(30179.6 15017.8,30181 15002.8,30190.8 15003.6,30189.6 15019) | | 11 | LINESTRING(30154.2 15000.4,30168.6 15004.8,30166 15014.2,30151.2 15009.8) | | 13 | LINESTRING(30105 15065.8,30108.4 15050.8,30118 15053,30114.6 15067.8) | | 154 | LINESTRING(30276.2 15143.8,30261.4 15141,30263 15131.4,30278 15134) | | 155 | LINESTRING(30269.8 15084,30269.4 15093.4,30258.6 15093,30259 15083.4) | | 157 | LINESTRING(30128.2 15011,30113.2 15010.2,30113.6 15000.4,30128.8 15001) | +-----+-----------------------------------------------------------------------------+ 20 rows in set (0.00 sec)
Now let's check the way this query is executed, using EXPLAIN
:
mysql> EXPLAIN SELECT fid,AsText(g) FROM g WHERE mysql> MBRContains(GeomFromText('Polygon((30000 15000,31000 15000,31000 16000,30000 16000,30000 15000))'),g); +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | g | range | g | g | 32 | NULL | 50 | Using where | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
Now let's check what would happen if we didn't have a spatial index:
mysql> EXPLAIN SELECT fid,AsText(g) FROM g IGNORE INDEX (g) WHERE mysql> MBRContains(GeomFromText('Polygon((30000 15000,31000 15000,31000 16000,30000 16000,30000 15000))'),g); +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ | 1 | SIMPLE | g | ALL | NULL | NULL | NULL | NULL | 32376 | Using where | +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ 1 row in set (0.00 sec)
Let's execute the above query, ignoring the spatial key we have:
mysql> SELECT fid,AsText(g) FROM g IGNORE INDEX (g) WHERE mysql> MBRContains(GeomFromText('Polygon((30000 15000,31000 15000,31000 16000,30000 16000,30000 15000))'),g); +-----+-----------------------------------------------------------------------------+ | fid | AsText(g) | +-----+-----------------------------------------------------------------------------+ | 1 | LINESTRING(30250.4 15129.2,30248.8 15138.4,30238.2 15136.4,30240 15127.2) | | 2 | LINESTRING(30220.2 15122.8,30217.2 15137.8,30207.6 15136,30210.4 15121) | | 3 | LINESTRING(30179 15114.4,30176.6 15129.4,30167 15128,30169 15113) | | 4 | LINESTRING(30155.2 15121.4,30140.4 15118.6,30142 15109,30157 15111.6) | | 5 | LINESTRING(30192.4 15085,30177.6 15082.2,30179.2 15072.4,30194.2 15075.2) | | 6 | LINESTRING(30244 15087,30229 15086.2,30229.4 15076.4,30244.6 15077) | | 7 | LINESTRING(30200.6 15059.4,30185.6 15058.6,30186 15048.8,30201.2 15049.4) | | 10 | LINESTRING(30179.6 15017.8,30181 15002.8,30190.8 15003.6,30189.6 15019) | | 11 | LINESTRING(30154.2 15000.4,30168.6 15004.8,30166 15014.2,30151.2 15009.8) | | 13 | LINESTRING(30105 15065.8,30108.4 15050.8,30118 15053,30114.6 15067.8) | | 21 | LINESTRING(30350.4 15828.8,30350.6 15845,30333.8 15845,30333.8 15828.8) | | 22 | LINESTRING(30350.6 15871.4,30350.6 15887.8,30334 15887.8,30334 15871.4) | | 23 | LINESTRING(30350.6 15914.2,30350.6 15930.4,30334 15930.4,30334 15914.2) | | 24 | LINESTRING(30290.2 15823,30290.2 15839.4,30273.4 15839.4,30273.4 15823) | | 25 | LINESTRING(30291.4 15866.2,30291.6 15882.4,30274.8 15882.4,30274.8 15866.2) | | 26 | LINESTRING(30291.6 15918.2,30291.6 15934.4,30275 15934.4,30275 15918.2) | | 154 | LINESTRING(30276.2 15143.8,30261.4 15141,30263 15131.4,30278 15134) | | 155 | LINESTRING(30269.8 15084,30269.4 15093.4,30258.6 15093,30259 15083.4) | | 157 | LINESTRING(30128.2 15011,30113.2 15010.2,30113.6 15000.4,30128.8 15001) | | 249 | LINESTRING(30337.8 15938.6,30337.8 15946.8,30320.4 15946.8,30320.4 15938.4) | +-----+-----------------------------------------------------------------------------+ 20 rows in set (0.46 sec)
When the index is not used, the execution time for this query rises from 0.00 seconds to 0.46 seconds.
In future releases, spatial indexes will also be used for optimising other functions. See section 10.5.4 Functions For Testing Spatial Relations Between Geometric Objects.
AddGeometryColumn()
and DropGeometryColumn()
functions. In MySQL, this is done using the ALTER TABLE
,
CREATE INDEX
, and DROP INDEX
statements instead.
Length()
and Area()
assume a planar
coordinate system.
Length()
on LineString
and MultiLineString
currently should be called in MySQL as GLength()
Length()
that calculates the length of string values,
and sometimes it's not possible to distinguish whether the function is
called in a textual or spatial context. We need either to solve this
somehow, or decide on another function name.
Go to the first, previous, next, last section, table of contents.