In release 4.1 MySQL introduces spatial extensions, which allow generating, storing and analysing of geographic features.
A geographic feature is anything in the world that has a location.
A feature can be:
You can also find documents which 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 OpenGIS specifications.
The OpenGIS 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 OpenGIS 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 of 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 of OpenGIS Geometry Model. In this model,
each geometric object:
Point
LineString
Polygon
GeometryCollection
MultiPoint
MultiLineString
MultiPolygon
Geometry is the base class. It's an abstract (non-instantiable) 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 (i.e. all defined geometries include their boundary).
The base Geometry class has subclasses for Point,
Curve, Surface and GeometryCollection.
Curve stands for 1-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 0, 1 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,
it is not possible to create an object of these
classes. They define a common set of methods for its subclasses and
included for the reason of extensibility.
Point, LineString, Polygon, GeometryCollection, MultiPoint, MultiLineString, MultiPolygon are instantiable classes (marked bold in the hierarchy tree).
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, which are common for all geometry subclasses, are described in the list below. Geometry is a non-instantiable class.
type that a geometry belongs to.
Each geometry belongs to one of instantiable classes in the hierarchy.
SRID, the identifier of a geometry's associated Spatial Reference System
which describes the coordinate space in which the geometry
object is defined.
coordinates in its Spatial Reference System,
represented as double precision (8 byte) numbers. All non-empty geometries
include at least one pair of (X,Y) coordinates. Empty geometries contain
no coordinates.
interior, boundary and exterior.
All geometries occupy some position in space. The exterior of
a geometry is all space not occupied by the geometry. The interior
is the space occupied by the geometry. The boundary is the
interface between geometry's interior and exterior.
MBR, or Envelope, the geometry's Minimum Bounding Rectangle.
This is the bounding geometry, formed by the minimum and maximum (X,Y)
coordinates:
((MINX MINY, MAXX MINY, MAXX MAXY, MINX MAXY, MINX MINY))
simple or non-simple.
Geometry values of some types (LineString, MultyPoint, MultiLineString)
are either simple of non-simple. Each type determines its own assertions
for being simple or non-simple.
closed or not closed.
Geometry values of some types (LineString, MultiString) are either closed
or not closed. Each type determines its own assertions for being closed
or not closed.
empty or not empty
A geometry is empty if it does not have any points.
Exterior, interior and boundary of an empty geometry
are not defined, i.e., they are represented by a NULL value.
An empty geometry is defined to be always simple.
An empty geometry has an area of 0.
dimension A geometry can have a dimension of -1, 0, 1 or 2.
planar coordinate system and the distance on the geocentric
system (coordinates on the Earth's surface) are different things.
A Point is a geometry that represents a single
location in coordinate space.
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.
A LineString is a Curve with linear interpolation between points.
A Surface is a two-dimensional geometric object.
The only instantiable subclass of Surface defined in OpenGIS specification, is 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.
The assertions for polygons (the rules that define valid polygons) are:
In the above assertions, polygons are simple geometries.
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 (i.e. in the same coordinate system). GeometryCollection places no other constraints on its elements.
Subclasses of GeometryCollection described below may restrict membership based on:
A MultiPoint is a collection whose elements are
restricted to Points. The points are not connected or ordered in any way.
A MultiCurve is a geometry collection whose elements are Curves. MultiCurve is a non-instantiable class.
A MultiLineString is a MultiCurve whose elements are LineStrings.
A MultiSurface is a geometric collection whose elements are surfaces. MultiSurface is a non-instantiable class.
The only instantiable subclass of MultiSurface is MultiPolygon.
A MultiPolygon is a MultiSurface whose elements are Polygons.
This section describes the standard spatial data formats that are used to store geometry objects.
They are:
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(10 10)
LINESTRING(10 10, 20 20, 30 40)
POLYGON((10 10, 10 20, 20 20, 20 15, 10 10))
MULTIPOINT(10 10, 20 20)
MULTILINESTRING((10 10, 20 20), (15 15, 30 15))
MULTIPOLYGON(((10 10, 10 20, 20 20, 20 15, 10 10)), ((60 60, 70 7, 80 60, 60 60 )))
GEOMETRYCOLLECTION(POINT(10 10), POINT(30 30), LINESTRING(15 15, 20 20))
The text representation of the implemented instantiable geometric types conforms to this grammar:
<Geometry Tagged Text> :=
<Point Tagged Text>
| <LineString Tagged Text>
| <Polygon Tagged Text>
| <MultiPoint Tagged Text>
| <MultiLineString Tagged Text>
| <MultiPolygon Tagged Text>
| <GeometryCollection Tagged Text>
<Point Tagged Text> := POINT <Point Text>
<LineString Tagged Text> := LINESTRING <LineString Text>
<Polygon Tagged Text> := POLYGON <Polygon Text>
<MultiPoint Tagged Text> := MULTIPOINT <Multipoint Text>
<MultiLineString Tagged Text> := MULTILINESTRING <MultiLineString Text>
<MultiPolygon Tagged Text> := MULTIPOLYGON <MultiPolygon Text>
<GeometryCollection Tagged Text> := GEOMETRYCOLLECTION <GeometryCollection Text>
<Point Text> := EMPTY | ( <Point> )
<Point> := <x> <y>
<x> := double precision literal
<y> := double precision literal
<LineString Text> := EMPTY | ( <Point > {, <Point > }* )
<Polygon Text> := EMPTY | ( <LineString Text > {, < LineString Text > }*)
<Multipoint Text> := EMPTY | ( <Point Text > {, <Point Text > }* )
<MultiLineString Text> := EMPTY | ( <LineString Text > {, < LineString Text > }* )
<MultiPolygon Text> := EMPTY | ( < Polygon Text > {, < Polygon Text > }* )
<GeometryCollection Text> := EMPTY | ( <Geometry Tagged Text> {, <Geometry Tagged Text> }* )
Well-Known Binary (WKB) representation 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 geometic information, according to the structures described below.
WKB uses the following basic type definitions:
// 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 which corresponds to POINT(1,1) looks like this sequence of 21 bytes:
0101000000000000000000F03F000000000000F03F
Where, consequently,
Byte order : 01 WKB type : 01000000 X : 000000000000F03F Y : 000000000000F03F
MySQL provides a hierarchy of datatypes, corresponding to the OpenGIS Geometry Model.
GEOMETRY
POINT
LINESTRING
POLYGON
MULTIPOINT
MULTILINESTRING
MULTIPOLYGON
GEOMETRYCOLLECTION
The GEOMETRY type can store geometries of any type,
other types restrict their values to a partilcular geometry type.
GEOMETRYCOLLECTION can store a collection of objects
of any type, other collection types restrict the type of collection
members to a particular geometry type.
MySQL provides a number of function which take a Well-Known Text representation and, optionally, a spatial reference system identifier as input parameters, 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)
LineFromText(wkt,srid)
LineStringFromText(wkt,srid)
PolyFromText(wkt,srid)
PolygonFromText(wkt,srid)
MPointFromText(wkt,srid)
MultiPointFromText(wkt,srid)
MLineFromText(wkt,srid)
MultiLineStringFromText(wkt,srid)
MPolyFromText(wkt,srid)
MultiPolygonFromText(wkt,srid)
GeomCollFromText(wkt,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
BdMPolyFromText(multiLineStringTaggedText String, SRID Integer):MultiPolygon
MySQL provides a set of functions which take a BLOB containing Well-Known Binary representation and, optionally, a spatial reference system identifier (SRID) as their input parameters, and return the corresponding geometry.
GeomFromWKB can accept 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 type of geometry as described
in the list above.
GeomFromWKB(wkb,srid)
GeometryFromWKB(wkt,srid)
PointFromWKB(wkb,srid)
LineFromWKB(wkb,srid)
LineStringFromWKB(wkb,srid)
PolyFromWKB(wkb,srid)
PolygonFromWKB(wkb,srid)
MPointFromWKB(wkb,srid)
MultiPointFromWKB(wkb,srid)
MLineFromWKB(wkb,srid)
MultiLineStringFromWKB(wkb,srid)
MPolyFromWKB(wkb,srid)
MultiPolygonFromWKB(wkb,srid)
GeomCollFromWKB(wkb,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
BdMPolyFromWKB(WKBMultiLineString Binary, SRID Integer):MultiPolygon
Note: the functions listed in this section are not yet implemented in the current version.
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 BLOBs containing geometry WKB representations.
The results of these functions can be substituted as first argument
for GeomFromWKB() function family.
Point(x,y)
MultiPoint(WKBPoint,WKBPoint,...,WKBPoint)
LineString(WKBPoint,WKBPoint,...,WKBPoint)
MultiLineString(WKBLineString,WKBLineString,...,WKBLineString)
Polygon(WKBLineString,WKBLineString,...,WKBLineString)
MultiPolygon(WKBPolygon,WKBPolygon,...,WKBPolygon)
GeometryCollection(WKBGeometry,WKBGeometry,..,WKBGeometry)
MySQL provides a standard way of creating spatial columns for geometry types.
CREATE TABLE
mysql> CREATE TABLE g1 (p1 GEOMETRY); Query OK, 0 rows affected (0.02 sec) mysql>
ALTER TABLE
mysql> ALTER TABLE g1 ADD p2 POINT; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql>
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, a client application program which wants to use WKB representation of geometry values, is responsible for sending correctly formed WKB in queries to server.
INSERT INTO geom VALUES (GeomFromWKB(0x0101000000000000000000F03F000000000000F03F));
INSERT INTO geom VALUES (GeomFromWKB(?));
mysql_escape_string() in libmysqlclient applications.
INSERT INTO geom VALUES (GeomFromWKB('\0\0\0\0\0\0\0\0\0ð?\0\0\0\0\0\0ð?'));
Geometry values, previously stored in a table, can be fetched in either WKT or WKB representation.
The AsText() function provides textual access to geometry values
by converting them into a WKT string.
mysql> SELECT AsText(p1) FROM g1; +-------------------------+ | AsText(p1) | +-------------------------+ | POINT(1 1) | | LINESTRING(0 0,1 1,2 2) | +-------------------------+ 2 rows in set (0.00 sec)
The AsBinary() and AsWKB() functions provide binary access to geometry
values by converting them into a BLOB containing WKB.
SELECT AsBinary(g) FROM geom; SELECT AsWKB(g) FROM geom;
AsBinary() and AsWKB() return a BLOB with a geometry in its WKB
representation.
After populating spatial columns with values, you are ready to query and analyse them. Spatial analysis can be performed using spatial functions in:
mysql or MySQLCC.
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:
As discussed (see section 9.4.1 MySQL Spatial Data Types, see section 9.4.4 Populating Spatial Columns), MySQL understands Well-Known Text (WKT) and Well-Known Binary (WKB) geometry representations through support of these functions:
GeomFromWKT(string wkt [,integer srid]): geometry
GeomFromWKB(binary wkb [,integer srid]): geometry
AsWKT(geometry g): string
AsWKB(geometry g): binary
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) |
+-------------------------------------------------+
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.
These functions don't restrict their argument and accept a geometry of any type.
GeometryType(geometry g):string
mysql> SELECT GeometryType(GeomFromText('POINT(1 1)'));
+------------------------------------------+
| GeometryType(GeomFromText('POINT(1 1)')) |
+------------------------------------------+
| POINT |
+------------------------------------------+
Dimension(geometry g):integer
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)',101)));
+-----------------------------------------------------------+
| AsText(Envelope(GeomFromText('LineString(1 1,2 2)',101))) |
+-----------------------------------------------------------+
| POLYGON((1 1,2 1,2 2,1 2,1 1)) |
+-----------------------------------------------------------+
Note: MySQL does not yet implement the following functions:
Boundary(g:Geometry):Geometry
IsEmpty(geometry g):Integer
IsSimple(geometry g):Integer
X(point p):Double
mysql> SELECT X(GeomFromText('Point(56.7 53.34)',101));
+------------------------------------------+
| X(GeomFromText('Point(56.7 53.34)',101)) |
+------------------------------------------+
| 56.7 |
+------------------------------------------+
Y(point p):Double
mysql> SELECT Y(GeomFromText('Point(56.7 53.34)',101));
+------------------------------------------+
| Y(GeomFromText('Point(56.7 53.34)',101)) |
+------------------------------------------+
| 53.34 |
+------------------------------------------+
StartPoint(LineString l):Point
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) |
+-------------------------------------------------------------+
EndPoint(LineString l):Point
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) |
+------------------------------------------------------------+
PointN(LineString l,integer n):Point
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) |
+-----------------------------------------------------------+
GLength(LineString l):Double
mysql> SELECT GLength(GeomFromText('LineString(1 1,2 2,3 3)'));
+--------------------------------------------------+
| GLength(GeomFromText('LineString(1 1,2 2,3 3)')) |
+--------------------------------------------------+
| 2.8284271247462 |
+--------------------------------------------------+
NumPoints(LineString l):Integer
mysql> SELECT NumPoints(GeomFromText('LineString(1 1,2 2,3 3)'));
+----------------------------------------------------+
| NumPoints(GeomFromText('LineString(1 1,2 2,3 3)')) |
+----------------------------------------------------+
| 3 |
+----------------------------------------------------+
Note: MySQL does not yet implement the following functions:
IsRing(LineString l):Integer
IsClosed(LineString l):Integer
mysql> SELECT IsClosed(GeomFromText('LineString(1 1,2 2,3 3)'));
+---------------------------------------------------+
| IsClosed(GeomFromText('LineString(1 1,2 2,3 3)')) |
+---------------------------------------------------+
| 0 |
+---------------------------------------------------+
GLength(MultiLineString m):Double
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
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 |
+--------------------------------------------------------------------+
Area(Polygon p):Double
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 |
+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
ExteriorRing(Polygon p):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
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
Area(MultiPolygon m):Double
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(MultyPolygon p):Point
PointOnSurface(MultiPolygon m):Point
NumGeometries(GeometryCollection g):Integer
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
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) |
+------------------------------------------------------------------------------------------+
Note: Functions for specific geometry type return NULL
if the passed geometry is of wrong geometry type.
For example Area() returns NULL if object type is neither
Polygon nor MultiPolygon.
In the section (see section 9.5.2 Functions To Analyse Geometry Properties), we've already discussed some functions that can construct new geometries from the exising 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 the future releases.
Intersection(Geometry g1,g2):Geometry
Union(Geometry g1,g2):Geometry
Difference(Geometry g1,g2):Geometry
SymDifference(Geometry g1,g2):Geometry
Buffer(Geometry g, double d):Geometry
g
is less than or equal to distance of d.
ConvexHull(Geometry g):Geometry
The functions described in this 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(geom1,geom2)
geom1
contains the Minimum Bounding Rectangle of geom2.
Otherwise, 0 is returned.
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(geom1,geom2)
geom1
is within the Minimum Bounding Rectangle of geom2.
Otherwise, 0 is returned.
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(geom1,geom2)
MBREqual(geom1,geom2)
MBRIntersects(geom1,geom2)
MBROverlaps(geom1,geom2)
MBRTouches(geom1,geom2)
Note: The functions given in the list below are not yet implemented. These functions will provide full (not MBR-based only) support for spatial analysis.
Contains(geom1,geom2)
geom1 completely contains geom2,
otherwise 0 is returned.
Crosses(geom1,geom2)
geom1 spatially crosses geom2.
If geom1 is a polygon or a multipolygon, NULL is returned.
If geom2 is a point or a multipoint, NULL is returned.
Otherwise 0 is returned.
The term spatially crosses denotes a spatial relation when two given
geometries intersect, and their intersection results in a geometry that has
a dimension that is one less than the maximum dimension of the two given
geometries, and the intersection is not equal to any of the two given geometries.
Disjoint(geom1,geom2)
geom1 is spatially disjoint from geom2,
i.e. if given geometries do not intersect. Otherwise, 0 is returned.
Equal(geom1,geom2)
geom1 is spatially equal to geom2,
otherwise 0 is returned.
Intersects(geom1,geom2)
geom1 spatially intersects geom2,
otherwise 0 is returned.
Overlaps(geom1,geom2)
geom1 spatially overlaps geom2,
otherwise, 0 is returned. 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(geom1,geom2)
geom1 spatially touches geom2,
otherwise, 0 is returned. Two geometries spatially touch if the interior
of both geometries do not intersect, but the boundary of one of the
geometries intersects either the boundary or the interioir of the
geometries.
Within(geom1,geom2)
geom1 is spatially within geom2,
otherwise, 0 is returned.
Distance(geom1:Geometry,geom2:Geometry):Double
It is known that search operations in usual databases can be optimised using indexes. This is still true for spatial databases. With help of great variery of multi-dimensional indexing methods which have already been designed in the world, 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 as well as for a point, the MBR is a degenerated rectangle,
into the linestring and the point respectively.
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:
CREATE TABLE g (g GEOMETRY NOT NULL, SPATIAL INDEX(g));
CREATE INDEX:
CREATE SPATIAL INDEX sp_index ON g (g);
ALTER TABLE:
ALTER TABLE g (ADD SPATIAL KEY(g));
Let's say we have a database with more than 32000 geometries.
Geometries are stored in the field g of type GEOMETRY.
The table also has a field fid, storing object IDs, with the
AUTO_INCREMENT attribute.
mysql> SHOW FIELDS FROM g; +-------+----------+-----------+------+-----+---------+----------------+ | Field | Type | Collation | Null | Key | Default | Extra | +-------+----------+-----------+------+-----+---------+----------------+ | fid | int(11) | binary | | PRI | NULL | auto_increment | | g | geometry | binary | | | | | +-------+----------+-----------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) mysql> SELECT COUNT(*) FROM g; +----------+ | count(*) | +----------+ | 32376 | +----------+ 1 row in set (0.00 sec)
Let's add a spatial index:
mysql> ALTER TABLE g ADD SPATIAL KEY(g); Query OK, 32376 rows affected (4.05 sec) Records: 32376 Duplicates: 0 Warnings: 0
The optimiser investigates if available spatial indexes can be involved
in the search whenever a query with a function like MBRContains()
or MBRWithin() in the WHERE clause is executed.
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)
Lets 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)
The execution time for this query rises from 0.00 seconds to 0.46 seconds, when the index is not used.
In the future releases, spatial indexes will also be used for optimising other functions. See section 9.5.4 Functions For Testing Spatial Relations Between Geometric Objects.
AddGeometryColumn() and DropGeometryColumn()
functions correspondently. In MySQL this is to be done using the
regular ALTER TABLE command instead.
Length() and Area() assume planar
coordinate system.
Length() on LineString and MultiLineString currently should be called as GLength().
Length()
and sometimes it's not possible to distinguish if the function was
called in the 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.