MySQL 4.1 introduces spatial extensions to allow the
generation, storage, and analysis of geographic features.
Currently, these features are available for MyISAM
tables only.
This chapter covers the following topics:
MySQL implements spatial extensions following the specification of
the Open GIS Consortium
(OGC). This is an international consortium
of more than 250 companies, agencies, and universities participating
in the development of publicly available conceptual solutions that can be
useful with all kinds of applications that manage spatial data.
The OGC maintains a Web site at http://www.opengis.org/.
In 1997, the Open GIS Consortium published the OpenGIS (R) Simple Features Specifications For SQL, a document that proposes several conceptual ways for extending an SQL RDBMS to support spatial data. This specification is available from the Open GIS Web site at http://www.opengis.org/docs/99-049.pdf. It contains additional information relevant to this chapter.
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 analyze geometry values.
A geographic feature is anything in the world that has a location. A feature can be:
You can also find documents that use the term geospatial feature to refer to geographic features.
Geometry is another word that denotes a geographic feature. Originally the word geometry meant measurement of the earth. Another meaning comes from cartography, referring to the geometric features that cartographers use to map the world.
This chapter uses all of these terms synonymously: geographic feature, geospatial feature, feature, or geometry. The term most commonly used here is geometry.
Let's define a geometry as a point or an aggregate of points representing anything in the world that has a location.
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 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)
It is not possible to create objects in non-instantiable classes. It is possible to create objects in instantiable classes. All classes have properties, and instantiable classes may also have assertions (rules that define valid class instances).
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
represents zero-dimensional objects.
Curve
represents 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 specialized zero-, one-, and two-dimensional collection classes named
MultiPoint
, MultiLineString
, and MultiPolygon
for modeling geometries corresponding to collections of
Points
, LineStrings
, and Polygons
, respectively.
MultiCurve
and MultiSurface
are introduced as abstract superclasses
that generalize 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 extensibility.
Point
, LineString
, Polygon
, GeometryCollection
,
MultiPoint
, MultiLineString
, and
MultiPolygon
are instantiable classes.
Geometry
Geometry
is the root class of the hierarchy. It is a
non-instantiable class but has a number of properties that are common to
all geometry values created from any of the Geometry
subclasses.
These properties are described in the following list. (Particular
subclasses have their own specific properties, described later.)
A geometry value has the following properties:
((MINX MINY, MAXX MINY, MAXX MAXY, MINX MAXY, MINX MINY))
LineString
, MultiPoint
,
MultiLineString
)
are either simple or 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 and 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.
Point
A Point
is a geometry that represents a single
location in coordinate space.
Point
ExamplesPoint
object could represent each city.
Point
object could represent a bus stop.
Point
PropertiesPoint
is defined as a zero-dimensional geometry.
Point
is the empty set.
Curve
A Curve
is a one-dimensional geometry, usually represented by a sequence
of points. Particular subclasses of Curve
define the type of
interpolation between points. Curve
is a non-instantiable class.
Curve
PropertiesCurve
has the coordinates of its points.
Curve
is defined as a 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 LinearRing
.
LineString
A LineString
is a Curve
with linear interpolation between points.
LineString
ExamplesLineString
objects could represent rivers.
LineString
objects could represent streets.
LineString
PropertiesLineString
has coordinates of 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 is both closed and simple.
Surface
A Surface
is a two-dimensional geometry. It is a non-instantiable
class. Its only instantiable subclass is Polygon
.
Surface
PropertiesSurface
is defined as a two-dimensional geometry.
Surface
as a geometry that
consists of a single ``patch'' that is associated with a single exterior
boundary and zero or more interior boundaries.
Surface
is the set of closed curves
corresponding to its exterior and interior boundaries.
Polygon
A Polygon
is a planar Surface
representing a multisided
geometry. It is defined by a single exterior boundary and zero or more
interior boundaries, where
each interior boundary defines a hole in the Polygon
.
Polygon
ExamplesPolygon
objects could represent forests, districts, an
so on.
Polygon
AssertionsPolygon
consists of a set of LinearRing
objects
(that is, LineString
objects that are both simple and closed) that make up its
exterior and interior boundaries.
Polygon
has no rings that cross. The rings in the boundary of a
Polygon
may intersect at a Point
, but only as a tangent.
Polygon
has no lines, spikes, or punctures.
Polygon
has an interior that is a connected point set.
Polygon
may have holes.
The exterior of a Polygon
with holes is not connected.
Each hole defines a connected component of the exterior.
The preceding assertions make a Polygon
a simple geometry.
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 System (that is, in the same coordinate system).
There are no other constraints on the elements of a GeometryCollection
,
although the
subclasses of GeometryCollection
described in the following sections
may restrict membership. Restrictions may be based on:
MultiPoint
may contain only Point
elements)
MultiPoint
A MultiPoint
is a geometry collection composed of
Point
elements. 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 a zero-dimensional geometry.
MultiPoint
is simple if no two of its Point
values are
equal (have identical coordinate values).
MultiPoint
is the empty set.
MultiCurve
A MultiCurve
is a geometry collection composed of
Curve
elements. MultiCurve
is a non-instantiable class.
MultiCurve
PropertiesMultiCurve
is a one-dimensional geometry.
MultiCurve
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
boundary is obtained by applying the ``mod 2 union
rule'' (also known as the ``odd-even rule''):
A point is in the boundary of a MultiCurve
if it is in the
boundaries of an odd number of MultiCurve
elements.
MultiCurve
is closed if all of its elements are closed.
MultiCurve
is always empty.
MultiLineString
A MultiLineString
is a MultiCurve
geometry collection composed
of LineString
elements.
MultiLineString
ExamplesMultiLineString
could represent a river system or
a highway system.
MultiSurface
A MultiSurface
is a geometry collection composed of surface elements.
MultiSurface
is a non-instantiable class. Its only instantiable
subclass is MultiPolygon
.
MultiSurface
AssertionsMultiSurface
surfaces have no interiors that intersect.
MultiSurface
elements have boundaries that
intersect at most at a finite number of points.
MultiPolygon
A MultiPolygon
is a MultiSurface
object composed of
Polygon
elements.
MultiPolygon
ExamplesMultiPolygon
could represent a system of lakes.
MultiPolygon
AssertionsMultiPolygon
has no two Polygon
elements with interiors
that intersect.
MultiPolygon
has no two Polygon
elements that cross
(crossing is also forbidden by the previous assertion), or that
touch at an infinite number of points.
MultiPolygon
may not have cut lines, spikes, or punctures. A
MultiPolygon
is a regular, closed point set.
MultiPolygon
that has more than one Polygon
has an
interior that 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 a two-dimensional geometry.
MultiPolygon
boundary is a set of closed curves
(LineString
values) corresponding to the boundaries of its
Polygon
elements.
Curve
in the boundary of the MultiPolygon
is in the
boundary of exactly one Polygon
element.
Curve
in the boundary of an Polygon
element 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(15 20)Note that point coordinates are specified with no separating comma.
LineString
with four points:
LINESTRING(0 0, 10 10, 20 25, 50 60)Note that point coordinate pairs are separated by commas.
Polygon
with one exterior ring and one interior ring:
POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))
MultiPoint
with three Point
values:
MULTIPOINT(0 0, 20 20, 60 60)
MultiLineString
with two LineString
values:
MULTILINESTRING((10 10, 20 20), (15 15, 30 15))
MultiPolygon
with two Polygon
values:
MULTIPOLYGON(((0 0,10 0,10 10,0 10,0 0)),((5 5,7 5,7 7,5 7, 5 5)))
GeometryCollection
consisting of two Point
values and one
LineString
:
GEOMETRYCOLLECTION(POINT(10 10), POINT(30 30), LINESTRING(15 15, 20 20))
A Backus-Naur grammar that specifies the formal production rules for writing WKT values can 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 is 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.
WKB uses one-byte unsigned integers, four-byte unsigned integers, and eight-byte double-precision numbers (IEEE 754 format). A byte is eight bits.
For example, a WKB value that corresponds to POINT(1 1)
consists of
this sequence of 21 bytes (each represented here by two hex digits):
0101000000000000000000F03F000000000000F03F
The sequence may be broken down into these components:
Byte order : 01 WKB type : 01000000 X : 000000000000F03F Y : 000000000000F03F
Component representation is as follows:
Point
,
LineString
,
Polygon
,
MultiPoint
,
MultiLineString
,
MultiPolygon
,
and
GeometryCollection
.
Point
value has X and Y coordinates, each represented as a
double-precision value.
WKB values for more complex geometry values are represented by more complex data structures, as detailed in the OpenGIS specification.
This section describes the data types you can use for representing spatial data in MySQL, and the functions available for creating and retrieving spatial values.
MySQL has data types that correspond to OpenGIS classes. Some of these types hold single geometry values:
GEOMETRY
POINT
LINESTRING
POLYGON
GEOMETRY
can store geometry values of any type.
The other single-value types,
POINT
and LINESTRING
and POLYGON
,
restrict their values to a particular geometry type.
The other data types hold collections of values:
MULTIPOINT
MULTILINESTRING
MULTIPOLYGON
GEOMETRYCOLLECTION
GEOMETRYCOLLECTION
can store a collection of objects
of any type. The other collection types,
MULTIPOINT
and MULTILINESTRING
and MULTIPOLYGON
and GEOMETRYCOLLECTION
,
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). They return the corresponding geometry.
GeomFromText()
accepts a WKT of any geometry type as its first
argument. An implementation also provides type-specific construction
functions for construction of geometry values of each geometry type.
GeomCollFromText(wkt[,srid])
GeometryCollectionFromText(wkt[,srid])
GEOMETRYCOLLECTION
value using its WKT representation and SRID.
GeomFromText(wkt[,srid])
GeometryFromText(wkt[,srid])
LineFromText(wkt[,srid])
LineStringFromText(wkt[,srid])
LINESTRING
value using its WKT representation and SRID.
MLineFromText(wkt[,srid])
MultiLineStringFromText(wkt[,srid])
MULTILINESTRING
value using its WKT representation and SRID.
MPointFromText(wkt[,srid])
MultiPointFromText(wkt[,srid])
MULTIPOINT
value using its WKT representation and SRID.
MPolyFromText(wkt[,srid])
MultiPolygonFromText(wkt[,srid])
MULTIPOLYGON
value using its WKT representation and SRID.
PointFromText(wkt[,srid])
POINT
value using its WKT representation and SRID.
PolyFromText(wkt[,srid])
PolygonFromText(wkt[,srid])
POLYGON
value using its WKT representation and SRID.
The OpenGIS specification also describes optional functions for constructing
Polygon
or MultiPolygon
values based on the WKT representation
of a collection of rings or closed LineString
values. These values
may intersect. MySQL does not implement these functions:
BdMPolyFromText(wkt,srid)
MultiPolygon
value from a
MultiLineString
value in WKT format containing
an arbitrary collection of closed LineString
values.
BdPolyFromText(wkt,srid)
Polygon
value from a
MultiLineString
value in WKT format containing
an arbitrary collection of closed LineString
values.
MySQL provides a number of functions that take as input parameters a
BLOB
containing a Well-Known Binary representation
and, optionally, a spatial reference
system identifier (SRID). They return the corresponding geometry.
GeomFromWKT()
accepts a WKB of any geometry type as its first
argument. An implementation also provides type-specific construction
functions for construction of geometry values of each geometry type.
GeomCollFromWKB(wkb[,srid])
GeometryCollectionFromWKB(wkt[,srid])
GEOMETRYCOLLECTION
value using its WKB representation and SRID.
GeomFromWKB(wkb[,srid])
GeometryFromWKB(wkt[,srid])
LineFromWKB(wkb[,srid])
LineStringFromWKB(wkb[,srid])
LINESTRING
value using its WKB representation and SRID.
MLineFromWKB(wkb[,srid])
MultiLineStringFromWKB(wkb[,srid])
MULTILINESTRING
value using its WKB representation and SRID.
MPointFromWKB(wkb[,srid])
MultiPointFromWKB(wkb[,srid])
MULTIPOINT
value using its WKB representation and SRID.
MPolyFromWKB(wkb[,srid])
MultiPolygonFromWKB(wkb[,srid])
MULTIPOLYGON
value using its WKB representation and SRID.
PointFromWKB(wkb[,srid])
POINT
value using its WKB representation and SRID.
PolyFromWKB(wkb[,srid])
PolygonFromWKB(wkb[,srid])
POLYGON
value using its WKB representation and SRID.
The OpenGIS specification also describes optional functions for constructing
Polygon
or MultiPolygon
values based on the WKB representation
of a collection of rings or closed LineString
values. These values
may intersect. MySQL does not implement these functions:
BdMPolyFromWKB(wkb,srid)
MultiPolygon
value from a
MultiLineString
value in WKB format containing
an arbitrary collection of closed LineString
values.
BdPolyFromWKB(wkb,srid)
Polygon
value from a
MultiLineString
value in WKB format containing
an arbitrary collection of closed LineString
values.
Note: MySQL does not 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 WKB representations of geometry
values with no SRID.
The results of these functions can be substituted as the first argument
for any function in the GeomFromWKB()
function family.
GeometryCollection(g1,g2,...)
GeometryCollection
. If any argument is not a
well-formed WKB representation of a geometry, the return value is
NULL
.
LineString(pt1,pt2,...)
LineString
value from a number of WKB Point
arguments. If any argument is not a WKB Point
, the return value
is NULL
. If the number of Point
arguments is less than two,
the return value is NULL
.
MultiLineString(ls1,ls2,...)
MultiLineString
value using using WKB LineString
arguments. If any argument is not a WKB LineString
, the return
value is NULL
.
MultiPoint(pt1,pt2,...)
MultiPoint
value using WKB Point
arguments.
If any argument is not a WKB Point
, the return value is NULL
.
MultiPolygon(poly1,poly2,...)
MultiPolygon
value from a set of WKB Polygon
arguments.
If any argument is not a WKB Polygon
, the rerurn value is NULL
.
Point(x,y)
Point
using its coordinates.
Polygon(ls1,ls2,...)
Polygon
value from a number of WKB LineString
arguments. If any argument does not represent the WKB of a LinearRing
(that is, not a closed and simple LineString
) 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
.
Currently, spatial columns are supported only for MyISAM
tables.
CREATE TABLE
statement to create a table with a spatial column:
mysql> CREATE TABLE geom (g GEOMETRY); Query OK, 0 rows affected (0.02 sec)
ALTER TABLE
statement to add or drop a spatial column to or
from an existing table:
mysql> ALTER TABLE geom ADD pt POINT; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE geom DROP pt; 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 spatial data.
Values should be stored in internal geometry format, but you can convert them to that format from either Well-Known Text (WKT) or Well-Known Binary (WKB) format. The following examples demonstrate how to insert geometry values into a table by converting WKT values into internal geometry format.
You can perform the conversion directly in the INSERT
statement:
INSERT INTO geom VALUES (GeomFromText('POINT(1 1)')); SET @g = 'POINT(1 1)'; INSERT INTO geom VALUES (GeomFromText(@g));
Or you can perform the conversion prior to the INSERT
:
SET @g = GeomFromText('POINT(1 1)'); INSERT INTO geom VALUES (@g);
The following examples insert more complex geometries into the table:
SET @g = 'LINESTRING(0 0,1 1,2 2)'; INSERT INTO geom VALUES (GeomFromText(@g)); SET @g = '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(@g)); SET @g = 'GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(0 0,1 1,2 2,3 3,4 4))'; INSERT INTO geom VALUES (GeomFromText(@g));
The preceding examples all use GeomFromText()
to create geometry
values. You can also use type-specific functions:
SET @g = 'POINT(1 1)'; INSERT INTO geom VALUES (PointFromText(@g)); SET @g = 'LINESTRING(0 0,1 1,2 2)'; INSERT INTO geom VALUES (LineStringFromText(@g)); SET @g = '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 (PolygonFromText(@g)); SET @g = 'GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(0 0,1 1,2 2,3 3,4 4))'; INSERT INTO geom VALUES (GeomCollFromText(@g));
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. However, there are several ways of satisfying this requirement. For example:
POINT(1 1)
value with hex literal syntax:
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 21.2.3.44 mysql_real_escape_string()
.
Geometry values stored in a table can be fetched in internal format. You can also convert them into WKT or WKB format.
Fetching geometry values using internal format can be useful in table-to-table transfers:
CREATE TABLE geom2 (g GEOMETRY) SELECT g FROM geom;
The AsText()
function converts a geometry from internal format into a WKT string.
mysql> SELECT AsText(g) FROM geom; +-------------------------+ | AsText(p1) | +-------------------------+ | POINT(1 1) | | LINESTRING(0 0,1 1,2 2) | +-------------------------+
The AsBinary()
function converts a geometry from internal format into a BLOB
containing
the WKB value.
SELECT AsBinary(g) FROM geom;
After populating spatial columns with values, you are ready to query and analyze them. MySQL provides a set of functions to perform various operations on spatial data. These functions can be grouped into four major categories 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:
AsBinary(g)
AsText(g)
mysql> SET @g = 'LineString(1 1,2 2,3 3)'; mysql> SELECT AsText(GeomFromText(@g)); +--------------------------+ | AsText(GeomFromText(@G)) | +--------------------------+ | LINESTRING(1 1,2 2,3 3) | +--------------------------+
GeomFromText(wkt[,srid])
PointFromText()
and LineFromText()
; see
section 19.4.2.1 Creating Geometry Values Using WKT Functions.
GeomFromWKB(wkb[,srid])
PointFromWKB()
and LineFromWKB()
; see
section 19.4.2.2 Creating Geometry Values Using WKB Functions.
Geometry
Functions
Each function that belongs to this group takes a geometry value as its
argument and returns some quantitative or qualitative property of the
geometry. Some functions restrict their argument type. Such functions
return NULL
if the argument is of an incorrect geometry
type. For example, Area()
returns NULL
if the object
type is neither Polygon
nor MultiPolygon
.
The functions listed in this section do not restrict their argument and accept a geometry value of any type.
Dimension(g)
Geometry
.)
mysql> SELECT Dimension(GeomFromText('LineString(1 1,2 2)')); +------------------------------------------------+ | Dimension(GeomFromText('LineString(1 1,2 2)')) | +------------------------------------------------+ | 1 | +------------------------------------------------+
Envelope(g)
Polygon
value.
mysql> SELECT AsText(Envelope(GeomFromText('LineString(1 1,2 2)'))); +-------------------------------------------------------+ | AsText(Envelope(GeomFromText('LineString(1 1,2 2)'))) | +-------------------------------------------------------+ | POLYGON((1 1,2 1,2 2,1 2,1 1)) | +-------------------------------------------------------+The polygon is defined by the corner points of the bounding box:
POLYGON((MINX MINY, MAXX MINY, MAXX MAXY, MINX MAXY, MINX MINY))
GeometryType(g)
Geometry
subclasses.
mysql> SELECT GeometryType(GeomFromText('POINT(1 1)')); +------------------------------------------+ | GeometryType(GeomFromText('POINT(1 1)')) | +------------------------------------------+ | POINT | +------------------------------------------+
SRID(g)
mysql> SELECT SRID(GeomFromText('LineString(1 1,2 2)',101)); +-----------------------------------------------+ | SRID(GeomFromText('LineString(1 1,2 2)',101)) | +-----------------------------------------------+ | 101 | +-----------------------------------------------+
The OpenGIS specification also defines the following functions, which MySQL does not implement:
Boundary(g)
IsEmpty(g)
NULL
.
If the geometry is empty, it represents the empty point set.
IsSimple(g)
IsSimple()
returns 0 if the
argument is not simple, and -1 if it is NULL
.
The description of each instantiable geometric class given earlier in
the chapter includes the specific conditions that cause an instance of
that class to be classified as not simple.
Point
Functions
A Point
consists of X and Y coordinates, which may be obtained
using the following functions:
X(p)
mysql> SELECT X(GeomFromText('Point(56.7 53.34)')); +--------------------------------------+ | X(GeomFromText('Point(56.7 53.34)')) | +--------------------------------------+ | 56.7 | +--------------------------------------+
Y(p)
mysql> SELECT Y(GeomFromText('Point(56.7 53.34)')); +--------------------------------------+ | Y(GeomFromText('Point(56.7 53.34)')) | +--------------------------------------+ | 53.34 | +--------------------------------------+
LineString
Functions
A LineString
consists of Point
values. You can extract
particular points of a LineString
, count the number of points that it
contains, or obtain its length.
EndPoint(ls)
Point
that is the end point of the LineString
value
ls.
mysql> SET @ls = 'LineString(1 1,2 2,3 3)'; mysql> SELECT AsText(EndPoint(GeomFromText(@ls))); +-------------------------------------+ | AsText(EndPoint(GeomFromText(@ls))) | +-------------------------------------+ | POINT(3 3) | +-------------------------------------+
GLength(ls)
LineString
value ls in its associated spatial reference.
mysql> SET @ls = 'LineString(1 1,2 2,3 3)'; mysql> SELECT GLength(GeomFromText(@ls)); +----------------------------+ | GLength(GeomFromText(@ls)) | +----------------------------+ | 2.8284271247462 | +----------------------------+
IsClosed(ls)
LineString
value ls is closed
(that is, its StartPoint()
and EndPoint()
values are the same).
Returns 0 if ls is not closed, and -1 if it is NULL
.
mysql> SET @ls = 'LineString(1 1,2 2,3 3)'; mysql> SELECT IsClosed(GeomFromText(@ls)); +-----------------------------+ | IsClosed(GeomFromText(@ls)) | +-----------------------------+ | 0 | +-----------------------------+
NumPoints(ls)
LineString
value ls.
mysql> SET @ls = 'LineString(1 1,2 2,3 3)'; mysql> SELECT NumPoints(GeomFromText(@ls)); +------------------------------+ | NumPoints(GeomFromText(@ls)) | +------------------------------+ | 3 | +------------------------------+
PointN(ls,n)
Linestring
value ls.
Point numbers begin at 1.
mysql> SET @ls = 'LineString(1 1,2 2,3 3)'; mysql> SELECT AsText(PointN(GeomFromText(@ls),2)); +-------------------------------------+ | AsText(PointN(GeomFromText(@ls),2)) | +-------------------------------------+ | POINT(2 2) | +-------------------------------------+
StartPoint(ls)
Point
that is the start point of the LineString
value
ls.
mysql> SET @ls = 'LineString(1 1,2 2,3 3)'; mysql> SELECT AsText(StartPoint(GeomFromText(@ls))); +---------------------------------------+ | AsText(StartPoint(GeomFromText(@ls))) | +---------------------------------------+ | POINT(1 1) | +---------------------------------------+
The OpenGIS specification also defines the following function, which MySQL does not implement:
IsRing(ls)
LineString
value ls is closed
(that is, its StartPoint()
and EndPoint()
values are the same)
and is simple (does not pass through the same point more than once).
Returns 0 if ls is not a ring, and -1 if it is NULL
.
MultiLineString
FunctionsGLength(mls)
MultiLineString
value mls. The length of
mls is equal to the sum of the lengths of its elements.
mysql> SET @mls = 'MultiLineString((1 1,2 2,3 3),(4 4,5 5))'; mysql> SELECT GLength(GeomFromText(@mls)); +-----------------------------+ | GLength(GeomFromText(@mls)) | +-----------------------------+ | 4.2426406871193 | +-----------------------------+
IsClosed(mls)
MultiLineString
value mls is closed
(that is, the StartPoint()
and EndPoint()
values are the same
for each LineString
in mls).
Returns 0 if mls is not closed, and -1 if it is NULL
.
mysql> SET @mls = 'MultiLineString((1 1,2 2,3 3),(4 4,5 5))'; mysql> SELECT IsClosed(GeomFromText(@mls)); +------------------------------+ | IsClosed(GeomFromText(@mls)) | +------------------------------+ | 0 | +------------------------------+
Polygon
FunctionsArea(poly)
Polygon
value
poly, as measured in its spatial reference system.
mysql> SET @poly = 'Polygon((0 0,0 3,3 0,0 0),(1 1,1 2,2 1,1 1))'; mysql> SELECT Area(GeomFromText(@poly)); +---------------------------+ | Area(GeomFromText(@poly)) | +---------------------------+ | 4 | +---------------------------+
ExteriorRing(poly)
Polygon
value poly
as a LineString
.
mysql> SET @poly = -> 'Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))'; mysql> SELECT AsText(ExteriorRing(GeomFromText(@poly))); +-------------------------------------------+ | AsText(ExteriorRing(GeomFromText(@poly))) | +-------------------------------------------+ | LINESTRING(0 0,0 3,3 3,3 0,0 0) | +-------------------------------------------+
InteriorRingN(poly,n)
Polygon
value
poly as a LineString
.
Ring numbers begin at 1.
mysql> SET @poly = -> 'Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))'; mysql> SELECT AsText(InteriorRingN(GeomFromText(@poly),1)); +----------------------------------------------+ | AsText(InteriorRingN(GeomFromText(@poly),1)) | +----------------------------------------------+ | LINESTRING(1 1,1 2,2 2,2 1,1 1) | +----------------------------------------------+
NumInteriorRings(poly)
Polygon
value poly.
mysql> SET @poly = -> 'Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))'; mysql> SELECT NumInteriorRings(GeomFromText(@poly)); +---------------------------------------+ | NumInteriorRings(GeomFromText(@poly)) | +---------------------------------------+ | 1 | +---------------------------------------+
MultiPolygon
FunctionsArea(mpoly)
MultiPolygon
value mpoly, as measured in its spatial reference system.
mysql> SET @mpoly = -> 'MultiPolygon(((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1)))'; mysql> SELECT Area(GeomFromText(@mpoly)); +----------------------------+ | Area(GeomFromText(@mpoly)) | +----------------------------+ | 8 | +----------------------------+
The OpenGIS specification also defines the following functions, which MySQL does not implement:
Centroid(mpoly)
MultiPolygon
value
mpoly as a Point
. The result is not guaranteed to be on
the MultiPolygon
.
PointOnSurface(mpoly)
Point
value that is guaranteed to be on the
MultiPolygon
value mpoly.
GeometryCollection
FunctionsGeometryN(gc,n)
GeometryCollection
value
gc. Geometry numbers begin at 1.
mysql> SET @gc = 'GeometryCollection(Point(1 1),LineString(2 2, 3 3))'; mysql> SELECT AsText(GeometryN(GeomFromText(@gc),1)); +----------------------------------------+ | AsText(GeometryN(GeomFromText(@gc),1)) | +----------------------------------------+ | POINT(1 1) | +----------------------------------------+
NumGeometries(gc)
GeometryCollection
value
gc.
mysql> SET @gc = 'GeometryCollection(Point(1 1),LineString(2 2, 3 3))'; mysql> SELECT NumGeometries(GeomFromText(@gc)); +----------------------------------+ | NumGeometries(GeomFromText(@gc)) | +----------------------------------+ | 2 | +----------------------------------+
In the section section 19.5.2 Geometry
Functions,
we've already discussed some functions that can construct new geometries
from the existing ones:
Envelope(g)
StartPoint(ls)
EndPoint(ls)
PointN(ls,n)
ExteriorRing(poly)
InteriorRingN(poly,n)
GeometryN(gc,n)
OpenGIS proposes a number of other functions that can produce geometries. They are designed to implement spatial operators.
These functions are not implemented in MySQL. They may appear in future releases.
Buffer(g,d)
ConvexHull(g)
Difference(g1,g2)
Intersection(g1,g2)
SymDifference(g1,g2)
Union(g1,g2)
The functions described in these sections take two geometries as input parameters and return a qualitative or quantitative relation between them.
MySQL provides some functions that can test relations
between minimal bounding rectangles of two geometries g1
and g2
.
They include:
MBRContains(g1,g2)
mysql> SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'); mysql> SET @g2 = GeomFromText('Point(1 1)'); mysql> SELECT MBRContains(@g1,@g2), MBRContains(@g2,@g1); ----------------------+----------------------+ | MBRContains(@g1,@g2) | MBRContains(@g2,@g1) | +----------------------+----------------------+ | 1 | 0 | +----------------------+----------------------+
MBRDisjoint(g1,g2)
MBREqual(g1,g2)
MBRIntersects(g1,g2)
MBROverlaps(g1,g2)
MBRTouches(g1,g2)
MBRWithin(g1,g2)
mysql> SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'); mysql> SET @g2 = GeomFromText('Polygon((0 0,0 5,5 5,5 0,0 0))'); mysql> SELECT MBRWithin(@g1,@g2), MBRWithin(@g2,@g1); +--------------------+--------------------+ | MBRWithin(@g1,@g2) | MBRWithin(@g2,@g1) | +--------------------+--------------------+ | 1 | 0 | +--------------------+--------------------+
The OpenGIS specification defines the following functions. Currently,
MySQL does not implement them according to the specification. Those that
are implemented return the same result as the corresponding
MBR-based functions. This includes functions in the following list
other than Distance()
and Related()
.
These functions may be implemented in future releases with full support for spatial analysis, not just MBR-based support.
The functions operate on two geometry values g1
and g2
.
Contains(g1,g2)
Crosses(g1,g2)
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)
Distance(g1,g2)
Equals(g1,g2)
Intersects(g1,g2)
Overlaps(g1,g2)
Related(g1,g2,pattern_matrix)
NULL
.
The pattern matrix is a string. Its specification will be noted here if this
function is implemented.
Touches(g1,g2)
Within(g1,g2)
Search operations in non-spatial databases can be optimized using indexes. This is true for spatial databases as well. With the help of a great variety of multi-dimensional indexing methods that have already been designed, it is possible to optimize spatial searches. The most typical of these are:
MySQL uses 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 using syntax similar to that for creating
regular indexes, but extended with the SPATIAL
keyword.
Spatial columns that are indexed currently must be declared NOT NULL
.
The following examples demonstrate how to create spatial indexes.
CREATE TABLE
:
mysql> CREATE TABLE geom (g GEOMETRY NOT NULL, SPATIAL INDEX(g));
ALTER TABLE
:
mysql> ALTER TABLE geom ADD SPATIAL INDEX(g);
CREATE INDEX
:
mysql> CREATE SPATIAL INDEX sp_index ON geom (g);
To drop spatial indexes, use ALTER TABLE
or DROP INDEX
:
ALTER TABLE
:
mysql> ALTER TABLE geom DROP INDEX g;
DROP INDEX
:
mysql> DROP INDEX sp_index ON geom;
Example: Suppose that a table geom
contains more than 32,000 geometries,
which are stored in the column g
of type GEOMETRY
.
The table also has an AUTO_INCREMENT
column fid
for storing
object ID values.
mysql> DESCRIBE geom; +-------+----------+------+-----+---------+----------------+ | 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 geom; +----------+ | count(*) | +----------+ | 32376 | +----------+ 1 row in set (0.00 sec)
To add a spatial index on the column g
, use this statement:
mysql> ALTER TABLE geom ADD SPATIAL INDEX(g); Query OK, 32376 rows affected (4.05 sec) Records: 32376 Duplicates: 0 Warnings: 0
The optimizer investigates whether available spatial indexes can
be involved in the search for queries that use a function such as
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 geom 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 use EXPLAIN
to check the way this query is executed (the
id
column has been removed so the output better fits the page):
mysql> EXPLAIN SELECT fid,AsText(g) FROM geom WHERE mysql> MBRContains(GeomFromText('Polygon((30000 15000,31000 15000,31000 16000,30000 16000,30000 15000))'),g); +-------------+-------+-------+---------------+------+---------+------+------+-------------+ | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------------+-------+-------+---------------+------+---------+------+------+-------------+ | SIMPLE | geom | range | g | g | 32 | NULL | 50 | Using where | +-------------+-------+-------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
Now let's check what would happen without 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); +-------------+-------+------+---------------+------+---------+------+-------+-------------+ | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------------+-------+------+---------------+------+---------+------+-------+-------------+ | SIMPLE | geom | ALL | NULL | NULL | NULL | NULL | 32376 | Using where | +-------------+-------+------+---------------+------+---------+------+-------+-------------+ 1 row in set (0.00 sec)
Let's execute the SELECT
statement, ignoring the spatial key we have:
mysql> SELECT fid,AsText(g) FROM geom 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 may also be used for optimizing other functions. See section 19.5.4 Functions for Testing Spatial Relations Between Geometric Objects.
GEOMETRY_COLUMNS
contains a
description of geometry columns, one row for each geometry column
in the database.
Length()
on LineString
and MultiLineString
currently should be called in MySQL as GLength()
Length()
which calculates the length of string values,
and sometimes it is 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.