<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"><title>Chapter 17. Spatial Extensions</title><link rel="stylesheet" href="mysql-html.css" type="text/css"><meta name="generator" content="DocBook XSL Stylesheets V1.69.1"><link rel="start" href="index.html" title="MySQL 5.0 Reference Manual"><link rel="up" href="index.html" title="MySQL 5.0 Reference Manual"><link rel="prev" href="mysql-cluster.html" title="Chapter 16. MySQL Cluster"><link rel="next" href="stored-procedures.html" title="Chapter 18. Stored Procedures and Functions"></head><body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="3" align="center">Chapter 17. Spatial Extensions</th></tr><tr><td width="20%" align="left"><a accesskey="p" href="mysql-cluster.html">Prev</a> </td><th width="60%" align="center"> </th><td width="20%" align="right"> <a accesskey="n" href="stored-procedures.html">Next</a></td></tr></table><hr></div><div class="chapter" lang="en"><div class="titlepage"><div><div><h2 class="title"><a name="spatial-extensions"></a>Chapter 17. Spatial Extensions</h2></div></div></div><div class="toc"><p><b>Table of Contents</b></p><dl><dt><span class="section"><a href="spatial-extensions.html#gis-introduction">17.1. Introduction to MySQL Spatial Support</a></span></dt><dt><span class="section"><a href="spatial-extensions.html#opengis-geometry-model">17.2. The OpenGIS Geometry Model</a></span></dt><dd><dl><dt><span class="section"><a href="spatial-extensions.html#gis-geometry-class-hierarchy">17.2.1. The Geometry Class Hierarchy</a></span></dt><dt><span class="section"><a href="spatial-extensions.html#gis-class-geometry">17.2.2. Class <code class="literal">Geometry</code></a></span></dt><dt><span class="section"><a href="spatial-extensions.html#gis-class-point">17.2.3. Class <code class="literal">Point</code></a></span></dt><dt><span class="section"><a href="spatial-extensions.html#gis-class-curve">17.2.4. Class <code class="literal">Curve</code></a></span></dt><dt><span class="section"><a href="spatial-extensions.html#gis-class-linestring">17.2.5. Class <code class="literal">LineString</code></a></span></dt><dt><span class="section"><a href="spatial-extensions.html#gis-class-surface">17.2.6. Class <code class="literal">Surface</code></a></span></dt><dt><span class="section"><a href="spatial-extensions.html#gis-class-polygon">17.2.7. Class <code class="literal">Polygon</code></a></span></dt><dt><span class="section"><a href="spatial-extensions.html#gis-class-geometrycollection">17.2.8. Class <code class="literal">GeometryCollection</code></a></span></dt><dt><span class="section"><a href="spatial-extensions.html#gis-class-multipoint">17.2.9. Class <code class="literal">MultiPoint</code></a></span></dt><dt><span class="section"><a href="spatial-extensions.html#gis-class-multicurve">17.2.10. Class <code class="literal">MultiCurve</code></a></span></dt><dt><span class="section"><a href="spatial-extensions.html#gis-class-multilinestring">17.2.11. Class <code class="literal">MultiLineString</code></a></span></dt><dt><span class="section"><a href="spatial-extensions.html#gis-class-multisurface">17.2.12. Class <code class="literal">MultiSurface</code></a></span></dt><dt><span class="section"><a href="spatial-extensions.html#gis-class-multipolygon">17.2.13. Class <code class="literal">MultiPolygon</code></a></span></dt></dl></dd><dt><span class="section"><a href="spatial-extensions.html#supported-spatial-data-formats">17.3. Supported Spatial Data Formats</a></span></dt><dd><dl><dt><span class="section"><a href="spatial-extensions.html#gis-wkt-format">17.3.1. Well-Known Text (WKT) Format</a></span></dt><dt><span class="section"><a href="spatial-extensions.html#gis-wkb-format">17.3.2. Well-Known Binary (WKB) Format</a></span></dt></dl></dd><dt><span class="section"><a href="spatial-extensions.html#creating-a-spatially-enabled-mysql-database">17.4. Creating a Spatially Enabled MySQL Database</a></span></dt><dd><dl><dt><span class="section"><a href="spatial-extensions.html#mysql-spatial-datatypes">17.4.1. MySQL Spatial Data Types</a></span></dt><dt><span class="section"><a href="spatial-extensions.html#creating-spatial-values">17.4.2. Creating Spatial Values</a></span></dt><dt><span class="section"><a href="spatial-extensions.html#creating-spatial-columns">17.4.3. Creating Spatial Columns</a></span></dt><dt><span class="section"><a href="spatial-extensions.html#populating-spatial-columns">17.4.4. Populating Spatial Columns</a></span></dt><dt><span class="section"><a href="spatial-extensions.html#fetching-spatial-data">17.4.5. Fetching Spatial Data</a></span></dt></dl></dd><dt><span class="section"><a href="spatial-extensions.html#analysing-spatial-information">17.5. Analyzing Spatial Information</a></span></dt><dd><dl><dt><span class="section"><a href="spatial-extensions.html#functions-to-convert-geometries-between-formats">17.5.1. Geometry Format Conversion Functions</a></span></dt><dt><span class="section"><a href="spatial-extensions.html#geometry-property-functions">17.5.2. <code class="literal">Geometry</code> Functions</a></span></dt><dt><span class="section"><a href="spatial-extensions.html#functions-that-create-new-geometries-from-existing-ones">17.5.3. Functions That Create New Geometries from Existing Ones</a></span></dt><dt><span class="section"><a href="spatial-extensions.html#functions-for-testing-spatial-relations-between-geometric-objects">17.5.4. Functions for Testing Spatial Relations Between Geometric Objects</a></span></dt><dt><span class="section"><a href="spatial-extensions.html#relations-on-geometry-mbr">17.5.5. Relations on Geometry Minimal Bounding Rectangles (MBRs)</a></span></dt><dt><span class="section"><a href="spatial-extensions.html#functions-that-test-spatial-relationships-between-geometries">17.5.6. Functions That Test Spatial Relationships Between Geometries</a></span></dt></dl></dd><dt><span class="section"><a href="spatial-extensions.html#optimizing-spatial-analysis">17.6. Optimizing Spatial Analysis</a></span></dt><dd><dl><dt><span class="section"><a href="spatial-extensions.html#creating-spatial-indexes">17.6.1. Creating Spatial Indexes</a></span></dt><dt><span class="section"><a href="spatial-extensions.html#using-a-spatial-index">17.6.2. Using a Spatial Index</a></span></dt></dl></dd><dt><span class="section"><a href="spatial-extensions.html#mysql-gis-conformance-and-compatibility">17.7. MySQL Conformance and Compatibility</a></span></dt></dl></div><a class="indexterm" name="id2985368"></a><p>
MySQL supports spatial extensions to allow the generation, storage,
and analysis of geographic features. Before MySQL 5.0.16, these
features are available for <code class="literal">MyISAM</code> tables only. As
of MySQL 5.0.16, <code class="literal">InnoDB</code>, <code class="literal">NDB</code>,
<code class="literal">BDB</code>, and <code class="literal">ARCHIVE</code> also support
spatial features.
</p><p>
For spatial columns, <code class="literal">MyISAM</code> supports both
<code class="literal">SPATIAL</code> and non-<code class="literal">SPATIAL</code>
indexes. Other storage engines support
non-<code class="literal">SPATIAL</code> indexes, as described in
<a href="sql-syntax.html#create-index" title="12.1.4. CREATE INDEX Syntax">Section 12.1.4, “<code class="literal">CREATE INDEX</code> Syntax”</a>.
</p><p>
This chapter covers the following topics:
</p><div class="itemizedlist"><ul type="disc"><li><p>
The basis of these spatial extensions in the OpenGIS geometry
model
</p></li><li><p>
Data formats for representing spatial data
</p></li><li><p>
How to use spatial data in MySQL
</p></li><li><p>
Use of indexing for spatial data
</p></li><li><p>
MySQL differences from the OpenGIS specification
</p></li></ul></div><p>
<span class="bold"><strong>Additional resources</strong></span>
</p><div class="itemizedlist"><ul type="disc"><li><p>
The Open Geospatial Consortium publishes the
<em class="citetitle">OpenGIS® Simple Features Specifications For
SQL</em>, a document that proposes several conceptual
ways for extending an SQL RDBMS to support spatial data. This
specification is available from the OGC Web site at
<a href="http://www.opengis.org/docs/99-049.pdf" target="_top">http://www.opengis.org/docs/99-049.pdf</a>.
</p></li><li><p>
If you have questions or concerns about the use of the spatial
extensions to MySQL, you can discuss them in the GIS forum:
<a href="http://forums.mysql.com/list.php?23" target="_top">http://forums.mysql.com/list.php?23</a>.
</p></li></ul></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="gis-introduction"></a>17.1. Introduction to MySQL Spatial Support</h2></div></div></div><a class="indexterm" name="id2985555"></a><a class="indexterm" name="id2985564"></a><a class="indexterm" name="id2985573"></a><p>
MySQL implements spatial extensions following the specification of
the Open Geospatial 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
<a href="http://www.opengis.org/" target="_top">http://www.opengis.org/</a>.
</p><p>
In 1997, the Open Geospatial Consortium published the
<em class="citetitle">OpenGIS® Simple Features Specifications For
SQL</em>, a document that proposes several conceptual ways
for extending an SQL RDBMS to support spatial data. This
specification is available from the OGC Web site at
<a href="http://www.opengis.org/docs/99-049.pdf" target="_top">http://www.opengis.org/docs/99-049.pdf</a>. It contains
additional information relevant to this chapter.
</p><p>
MySQL implements a subset of the <span class="bold"><strong>SQL with
Geometry Types</strong></span> 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 specification describe a set
of SQL geometry types, as well as functions on those types to
create and analyze geometry values.
</p><a class="indexterm" name="id2985633"></a><p>
A <span class="bold"><strong>geographic feature</strong></span> is anything
in the world that has a location. A feature can be:
</p><div class="itemizedlist"><ul type="disc"><li><p>
An entity. For example, a mountain, a pond, a city.
</p></li><li><p>
A space. For example, town district, the tropics.
</p></li><li><p>
A definable location. For example, a crossroad, as a
particular place where two streets intersect.
</p></li></ul></div><a class="indexterm" name="id2985680"></a><p>
Some documents use the term <span class="bold"><strong>geospatial
feature</strong></span> to refer to geographic features.
</p><a class="indexterm" name="id2985699"></a><p>
<span class="bold"><strong>Geometry</strong></span> is another word that
denotes a geographic feature. Originally the word
<span class="bold"><strong>geometry</strong></span> meant measurement of the
earth. Another meaning comes from cartography, referring to the
geometric features that cartographers use to map the world.
</p><p>
This chapter uses all of these terms synonymously:
<span class="bold"><strong>geographic feature</strong></span>,
<span class="bold"><strong>geospatial feature</strong></span>,
<span class="bold"><strong>feature</strong></span>, or
<span class="bold"><strong>geometry</strong></span>. Here, the term most
commonly used is <span class="bold"><strong>geometry</strong></span>,
defined as <span class="emphasis"><em>a point or an aggregate of points
representing anything in the world that has a location</em></span>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="opengis-geometry-model"></a>17.2. The OpenGIS Geometry Model</h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="spatial-extensions.html#gis-geometry-class-hierarchy">17.2.1. The Geometry Class Hierarchy</a></span></dt><dt><span class="section"><a href="spatial-extensions.html#gis-class-geometry">17.2.2. Class <code class="literal">Geometry</code></a></span></dt><dt><span class="section"><a href="spatial-extensions.html#gis-class-point">17.2.3. Class <code class="literal">Point</code></a></span></dt><dt><span class="section"><a href="spatial-extensions.html#gis-class-curve">17.2.4. Class <code class="literal">Curve</code></a></span></dt><dt><span class="section"><a href="spatial-extensions.html#gis-class-linestring">17.2.5. Class <code class="literal">LineString</code></a></span></dt><dt><span class="section"><a href="spatial-extensions.html#gis-class-surface">17.2.6. Class <code class="literal">Surface</code></a></span></dt><dt><span class="section"><a href="spatial-extensions.html#gis-class-polygon">17.2.7. Class <code class="literal">Polygon</code></a></span></dt><dt><span class="section"><a href="spatial-extensions.html#gis-class-geometrycollection">17.2.8. Class <code class="literal">GeometryCollection</code></a></span></dt><dt><span class="section"><a href="spatial-extensions.html#gis-class-multipoint">17.2.9. Class <code class="literal">MultiPoint</code></a></span></dt><dt><span class="section"><a href="spatial-extensions.html#gis-class-multicurve">17.2.10. Class <code class="literal">MultiCurve</code></a></span></dt><dt><span class="section"><a href="spatial-extensions.html#gis-class-multilinestring">17.2.11. Class <code class="literal">MultiLineString</code></a></span></dt><dt><span class="section"><a href="spatial-extensions.html#gis-class-multisurface">17.2.12. Class <code class="literal">MultiSurface</code></a></span></dt><dt><span class="section"><a href="spatial-extensions.html#gis-class-multipolygon">17.2.13. Class <code class="literal">MultiPolygon</code></a></span></dt></dl></div><p>
The set of geometry types proposed by OGC's
<span class="bold"><strong>SQL with Geometry Types</strong></span>
environment is based on the <span class="bold"><strong>OpenGIS Geometry
Model</strong></span>. In this model, each geometric object has the
following general properties:
</p><div class="itemizedlist"><ul type="disc"><li><p>
It is associated with a Spatial Reference System, which
describes the coordinate space in which the object is defined.
</p></li><li><p>
It belongs to some geometry class.
</p></li></ul></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="gis-geometry-class-hierarchy"></a>17.2.1. The Geometry Class Hierarchy</h3></div></div></div><p>
The geometry classes define a hierarchy as follows:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal">Geometry</code> (non-instantiable)
</p><div class="itemizedlist"><ul type="circle"><li><p>
<code class="literal">Point</code> (instantiable)
</p></li><li><p>
<code class="literal">Curve</code> (non-instantiable)
</p><div class="itemizedlist"><ul type="square"><li><p>
<code class="literal">LineString</code> (instantiable)
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal">Line</code>
</p></li><li><p>
<code class="literal">LinearRing</code>
</p></li></ul></div></li></ul></div></li><li><p>
<code class="literal">Surface</code> (non-instantiable)
</p><div class="itemizedlist"><ul type="square"><li><p>
<code class="literal">Polygon</code> (instantiable)
</p></li></ul></div></li><li><p>
<code class="literal">GeometryCollection</code> (instantiable)
</p><div class="itemizedlist"><ul type="square"><li><p>
<code class="literal">MultiPoint</code> (instantiable)
</p></li><li><p>
<code class="literal">MultiCurve</code> (non-instantiable)
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal">MultiLineString</code>
(instantiable)
</p></li></ul></div></li><li><p>
<code class="literal">MultiSurface</code> (non-instantiable)
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal">MultiPolygon</code> (instantiable)
</p></li></ul></div></li></ul></div></li></ul></div></li></ul></div><p>
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).
</p><p>
<code class="literal">Geometry</code> is the base class. It is an abstract
class. The instantiable subclasses of
<code class="literal">Geometry</code> 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).
</p><p>
The base <code class="literal">Geometry</code> class has subclasses for
<code class="literal">Point</code>, <code class="literal">Curve</code>,
<code class="literal">Surface</code>, and
<code class="literal">GeometryCollection</code>:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal">Point</code> represents zero-dimensional
objects.
</p></li><li><p>
<code class="literal">Curve</code> represents one-dimensional objects,
and has subclass <code class="literal">LineString</code>, with
sub-subclasses <code class="literal">Line</code> and
<code class="literal">LinearRing</code>.
</p></li><li><p>
<code class="literal">Surface</code> is designed for two-dimensional
objects and has subclass <code class="literal">Polygon</code>.
</p></li><li><p>
<code class="literal">GeometryCollection</code> has specialized zero-,
one-, and two-dimensional collection classes named
<code class="literal">MultiPoint</code>,
<code class="literal">MultiLineString</code>, and
<code class="literal">MultiPolygon</code> for modeling geometries
corresponding to collections of <code class="literal">Points</code>,
<code class="literal">LineStrings</code>, and
<code class="literal">Polygons</code>, respectively.
<code class="literal">MultiCurve</code> and
<code class="literal">MultiSurface</code> are introduced as abstract
superclasses that generalize the collection interfaces to
handle <code class="literal">Curves</code> and
<code class="literal">Surfaces</code>.
</p></li></ul></div><p>
<code class="literal">Geometry</code>, <code class="literal">Curve</code>,
<code class="literal">Surface</code>, <code class="literal">MultiCurve</code>, and
<code class="literal">MultiSurface</code> are defined as non-instantiable
classes. They define a common set of methods for their
subclasses and are included for extensibility.
</p><p>
<code class="literal">Point</code>, <code class="literal">LineString</code>,
<code class="literal">Polygon</code>,
<code class="literal">GeometryCollection</code>,
<code class="literal">MultiPoint</code>,
<code class="literal">MultiLineString</code>, and
<code class="literal">MultiPolygon</code> are instantiable classes.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="gis-class-geometry"></a>17.2.2. Class <code class="literal">Geometry</code></h3></div></div></div><p>
<code class="literal">Geometry</code> 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
<code class="literal">Geometry</code> subclasses. These properties are
described in the following list. Particular subclasses have
their own specific properties, described later.
</p><p>
<span class="bold"><strong>Geometry Properties</strong></span>
</p><p>
A geometry value has the following properties:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Its <span class="bold"><strong>type</strong></span>. Each geometry
belongs to one of the instantiable classes in the hierarchy.
</p></li><li><p>
Its <span class="bold"><strong>SRID</strong></span>, or Spatial
Reference Identifier. This value identifies the geometry's
associated Spatial Reference System that describes the
coordinate space in which the geometry object is defined.
</p><p>
In MySQL, the SRID value is just an integer associated with
the geometry value. All calculations are done assuming
Euclidean (planar) geometry.
</p></li><li><p>
Its <span class="bold"><strong>coordinates</strong></span> in its
Spatial Reference System, represented as double-precision
(eight-byte) numbers. All non-empty geometries include at
least one pair of (X,Y) coordinates. Empty geometries
contain no coordinates.
</p><p>
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
<span class="bold"><strong>planar</strong></span> coordinate system
and the distance on the
<span class="bold"><strong>geocentric</strong></span> system
(coordinates on the Earth's surface) are different things.
</p></li><li><p>
Its <span class="bold"><strong>interior</strong></span>,
<span class="bold"><strong>boundary</strong></span>, and
<span class="bold"><strong>exterior</strong></span>.
</p><p>
Every geometry occupies 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 the geometry's interior and
exterior.
</p></li><li><p>
Its <span class="bold"><strong>MBR</strong></span> (Minimum Bounding
Rectangle), or Envelope. This is the bounding geometry,
formed by the minimum and maximum (X,Y) coordinates:
</p><pre class="programlisting">((MINX MINY, MAXX MINY, MAXX MAXY, MINX MAXY, MINX MINY))
</pre></li><li><p>
Whether the value is <span class="bold"><strong>simple</strong></span>
or <span class="bold"><strong>non-simple</strong></span>. Geometry
values of types (<code class="literal">LineString</code>,
<code class="literal">MultiPoint</code>,
<code class="literal">MultiLineString</code>) are either simple or
non-simple. Each type determines its own assertions for
being simple or non-simple.
</p></li><li><p>
Whether the value is <span class="bold"><strong>closed</strong></span>
or <span class="bold"><strong>not closed</strong></span>. Geometry
values of types (<code class="literal">LineString</code>,
<code class="literal">MultiString</code>) are either closed or not
closed. Each type determines its own assertions for being
closed or not closed.
</p></li><li><p>
Whether the value is <span class="bold"><strong>empty</strong></span>
or <span class="bold"><strong>non-empty</strong></span> A geometry is
empty if it does not have any points. Exterior, interior,
and boundary of an empty geometry are not defined (that is,
they are represented by a <code class="literal">NULL</code> value). An
empty geometry is defined to be always simple and has an
area of 0.
</p></li><li><p>
Its <span class="bold"><strong>dimension</strong></span>. A geometry
can have a dimension of –1, 0, 1, or 2:
</p><div class="itemizedlist"><ul type="circle"><li><p>
–1 for an empty geometry.
</p></li><li><p>
0 for a geometry with no length and no area.
</p></li><li><p>
1 for a geometry with non-zero length and zero area.
</p></li><li><p>
2 for a geometry with non-zero area.
</p></li></ul></div><p>
<code class="literal">Point</code> objects have a dimension of zero.
<code class="literal">LineString</code> objects have a dimension of 1.
<code class="literal">Polygon</code> objects have a dimension of 2.
The dimensions of <code class="literal">MultiPoint</code>,
<code class="literal">MultiLineString</code>, and
<code class="literal">MultiPolygon</code> objects are the same as the
dimensions of the elements they consist of.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="gis-class-point"></a>17.2.3. Class <code class="literal">Point</code></h3></div></div></div><p>
A <code class="literal">Point</code> is a geometry that represents a
single location in coordinate space.
</p><p>
<span class="bold"><strong><code class="literal">Point</code>
Examples</strong></span>
</p><div class="itemizedlist"><ul type="disc"><li><p>
Imagine a large-scale map of the world with many cities. A
<code class="literal">Point</code> object could represent each city.
</p></li><li><p>
On a city map, a <code class="literal">Point</code> object could
represent a bus stop.
</p></li></ul></div><p>
<span class="bold"><strong><code class="literal">Point</code>
Properties</strong></span>
</p><div class="itemizedlist"><ul type="disc"><li><p>
X-coordinate value.
</p></li><li><p>
Y-coordinate value.
</p></li><li><p>
<code class="literal">Point</code> is defined as a zero-dimensional
geometry.
</p></li><li><p>
The boundary of a <code class="literal">Point</code> is the empty set.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="gis-class-curve"></a>17.2.4. Class <code class="literal">Curve</code></h3></div></div></div><p>
A <code class="literal">Curve</code> is a one-dimensional geometry,
usually represented by a sequence of points. Particular
subclasses of <code class="literal">Curve</code> define the type of
interpolation between points. <code class="literal">Curve</code> is a
non-instantiable class.
</p><p>
<span class="bold"><strong><code class="literal">Curve</code>
Properties</strong></span>
</p><div class="itemizedlist"><ul type="disc"><li><p>
A <code class="literal">Curve</code> has the coordinates of its
points.
</p></li><li><p>
A <code class="literal">Curve</code> is defined as a one-dimensional
geometry.
</p></li><li><p>
A <code class="literal">Curve</code> is simple if it does not pass
through the same point twice.
</p></li><li><p>
A <code class="literal">Curve</code> is closed if its start point is
equal to its endpoint.
</p></li><li><p>
The boundary of a closed <code class="literal">Curve</code> is empty.
</p></li><li><p>
The boundary of a non-closed <code class="literal">Curve</code>
consists of its two endpoints.
</p></li><li><p>
A <code class="literal">Curve</code> that is simple and closed is a
<code class="literal">LinearRing</code>.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="gis-class-linestring"></a>17.2.5. Class <code class="literal">LineString</code></h3></div></div></div><p>
A <code class="literal">LineString</code> is a <code class="literal">Curve</code>
with linear interpolation between points.
</p><p>
<span class="bold"><strong><code class="literal">LineString</code>
Examples</strong></span>
</p><div class="itemizedlist"><ul type="disc"><li><p>
On a world map, <code class="literal">LineString</code> objects could
represent rivers.
</p></li><li><p>
In a city map, <code class="literal">LineString</code> objects could
represent streets.
</p></li></ul></div><p>
<span class="bold"><strong><code class="literal">LineString</code>
Properties</strong></span>
</p><div class="itemizedlist"><ul type="disc"><li><p>
A <code class="literal">LineString</code> has coordinates of segments,
defined by each consecutive pair of points.
</p></li><li><p>
A <code class="literal">LineString</code> is a <code class="literal">Line</code>
if it consists of exactly two points.
</p></li><li><p>
A <code class="literal">LineString</code> is a
<code class="literal">LinearRing</code> if it is both closed and
simple.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="gis-class-surface"></a>17.2.6. Class <code class="literal">Surface</code></h3></div></div></div><p>
A <code class="literal">Surface</code> is a two-dimensional geometry. It
is a non-instantiable class. Its only instantiable subclass is
<code class="literal">Polygon</code>.
</p><p>
<span class="bold"><strong><code class="literal">Surface</code>
Properties</strong></span>
</p><div class="itemizedlist"><ul type="disc"><li><p>
A <code class="literal">Surface</code> is defined as a two-dimensional
geometry.
</p></li><li><p>
The OpenGIS specification defines a simple
<code class="literal">Surface</code> as a geometry that consists of a
single “<span class="quote">patch</span>” that is associated with a single
exterior boundary and zero or more interior boundaries.
</p></li><li><p>
The boundary of a simple <code class="literal">Surface</code> is the
set of closed curves corresponding to its exterior and
interior boundaries.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="gis-class-polygon"></a>17.2.7. Class <code class="literal">Polygon</code></h3></div></div></div><p>
A <code class="literal">Polygon</code> is a planar
<code class="literal">Surface</code> 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 <code class="literal">Polygon</code>.
</p><p>
<span class="bold"><strong><code class="literal">Polygon</code>
Examples</strong></span>
</p><div class="itemizedlist"><ul type="disc"><li><p>
On a region map, <code class="literal">Polygon</code> objects could
represent forests, districts, and so on.
</p></li></ul></div><p>
<span class="bold"><strong><code class="literal">Polygon</code>
Assertions</strong></span>
</p><div class="itemizedlist"><ul type="disc"><li><p>
The boundary of a <code class="literal">Polygon</code> consists of a
set of <code class="literal">LinearRing</code> objects (that is,
<code class="literal">LineString</code> objects that are both simple
and closed) that make up its exterior and interior
boundaries.
</p></li><li><p>
A <code class="literal">Polygon</code> has no rings that cross. The
rings in the boundary of a <code class="literal">Polygon</code> may
intersect at a <code class="literal">Point</code>, but only as a
tangent.
</p></li><li><p>
A <code class="literal">Polygon</code> has no lines, spikes, or
punctures.
</p></li><li><p>
A <code class="literal">Polygon</code> has an interior that is a
connected point set.
</p></li><li><p>
A <code class="literal">Polygon</code> may have holes. The exterior of
a <code class="literal">Polygon</code> with holes is not connected.
Each hole defines a connected component of the exterior.
</p></li></ul></div><p>
The preceding assertions make a <code class="literal">Polygon</code> a
simple geometry.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="gis-class-geometrycollection"></a>17.2.8. Class <code class="literal">GeometryCollection</code></h3></div></div></div><p>
A <code class="literal">GeometryCollection</code> is a geometry that is a
collection of one or more geometries of any class.
</p><p>
All the elements in a <code class="literal">GeometryCollection</code> 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 <code class="literal">GeometryCollection</code>, although
the subclasses of <code class="literal">GeometryCollection</code>
described in the following sections may restrict membership.
Restrictions may be based on:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Element type (for example, a <code class="literal">MultiPoint</code>
may contain only <code class="literal">Point</code> elements)
</p></li><li><p>
Dimension
</p></li><li><p>
Constraints on the degree of spatial overlap between
elements
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="gis-class-multipoint"></a>17.2.9. Class <code class="literal">MultiPoint</code></h3></div></div></div><p>
A <code class="literal">MultiPoint</code> is a geometry collection
composed of <code class="literal">Point</code> elements. The points are
not connected or ordered in any way.
</p><p>
<span class="bold"><strong><code class="literal">MultiPoint</code>
Examples</strong></span>
</p><div class="itemizedlist"><ul type="disc"><li><p>
On a world map, a <code class="literal">MultiPoint</code> could
represent a chain of small islands.
</p></li><li><p>
On a city map, a <code class="literal">MultiPoint</code> could
represent the outlets for a ticket office.
</p></li></ul></div><p>
<span class="bold"><strong><code class="literal">MultiPoint</code>
Properties</strong></span>
</p><div class="itemizedlist"><ul type="disc"><li><p>
A <code class="literal">MultiPoint</code> is a zero-dimensional
geometry.
</p></li><li><p>
A <code class="literal">MultiPoint</code> is simple if no two of its
<code class="literal">Point</code> values are equal (have identical
coordinate values).
</p></li><li><p>
The boundary of a <code class="literal">MultiPoint</code> is the empty
set.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="gis-class-multicurve"></a>17.2.10. Class <code class="literal">MultiCurve</code></h3></div></div></div><p>
A <code class="literal">MultiCurve</code> is a geometry collection
composed of <code class="literal">Curve</code> elements.
<code class="literal">MultiCurve</code> is a non-instantiable class.
</p><p>
<span class="bold"><strong><code class="literal">MultiCurve</code>
Properties</strong></span>
</p><div class="itemizedlist"><ul type="disc"><li><p>
A <code class="literal">MultiCurve</code> is a one-dimensional
geometry.
</p></li><li><p>
A <code class="literal">MultiCurve</code> 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.
</p></li><li><p>
A <code class="literal">MultiCurve</code> boundary is obtained by
applying the “<span class="quote">mod 2 union rule</span>” (also known as
the “<span class="quote">odd-even rule</span>”): A point is in the
boundary of a <code class="literal">MultiCurve</code> if it is in the
boundaries of an odd number of <code class="literal">MultiCurve</code>
elements.
</p></li><li><p>
A <code class="literal">MultiCurve</code> is closed if all of its
elements are closed.
</p></li><li><p>
The boundary of a closed <code class="literal">MultiCurve</code> is
always empty.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="gis-class-multilinestring"></a>17.2.11. Class <code class="literal">MultiLineString</code></h3></div></div></div><p>
A <code class="literal">MultiLineString</code> is a
<code class="literal">MultiCurve</code> geometry collection composed of
<code class="literal">LineString</code> elements.
</p><p>
<span class="bold"><strong><code class="literal">MultiLineString</code>
Examples</strong></span>
</p><div class="itemizedlist"><ul type="disc"><li><p>
On a region map, a <code class="literal">MultiLineString</code> could
represent a river system or a highway system.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="gis-class-multisurface"></a>17.2.12. Class <code class="literal">MultiSurface</code></h3></div></div></div><p>
A <code class="literal">MultiSurface</code> is a geometry collection
composed of surface elements. <code class="literal">MultiSurface</code> is
a non-instantiable class. Its only instantiable subclass is
<code class="literal">MultiPolygon</code>.
</p><p>
<span class="bold"><strong><code class="literal">MultiSurface</code>
Assertions</strong></span>
</p><div class="itemizedlist"><ul type="disc"><li><p>
Two <code class="literal">MultiSurface</code> surfaces have no
interiors that intersect.
</p></li><li><p>
Two <code class="literal">MultiSurface</code> elements have boundaries
that intersect at most at a finite number of points.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="gis-class-multipolygon"></a>17.2.13. Class <code class="literal">MultiPolygon</code></h3></div></div></div><p>
A <code class="literal">MultiPolygon</code> is a
<code class="literal">MultiSurface</code> object composed of
<code class="literal">Polygon</code> elements.
</p><p>
<span class="bold"><strong><code class="literal">MultiPolygon</code>
Examples</strong></span>
</p><div class="itemizedlist"><ul type="disc"><li><p>
On a region map, a <code class="literal">MultiPolygon</code> could
represent a system of lakes.
</p></li></ul></div><p>
<span class="bold"><strong><code class="literal">MultiPolygon</code>
Assertions</strong></span>
</p><div class="itemizedlist"><ul type="disc"><li><p>
A <code class="literal">MultiPolygon</code> has no two
<code class="literal">Polygon</code> elements with interiors that
intersect.
</p></li><li><p>
A <code class="literal">MultiPolygon</code> has no two
<code class="literal">Polygon</code> elements that cross (crossing is
also forbidden by the previous assertion), or that touch at
an infinite number of points.
</p></li><li><p>
A <code class="literal">MultiPolygon</code> may not have cut lines,
spikes, or punctures. A <code class="literal">MultiPolygon</code> is a
regular, closed point set.
</p></li><li><p>
A <code class="literal">MultiPolygon</code> that has more than one
<code class="literal">Polygon</code> has an interior that is not
connected. The number of connected components of the
interior of a <code class="literal">MultiPolygon</code> is equal to
the number of <code class="literal">Polygon</code> values in the
<code class="literal">MultiPolygon</code>.
</p></li></ul></div><p>
<span class="bold"><strong><code class="literal">MultiPolygon</code>
Properties</strong></span>
</p><div class="itemizedlist"><ul type="disc"><li><p>
A <code class="literal">MultiPolygon</code> is a two-dimensional
geometry.
</p></li><li><p>
A <code class="literal">MultiPolygon</code> boundary is a set of
closed curves (<code class="literal">LineString</code> values)
corresponding to the boundaries of its
<code class="literal">Polygon</code> elements.
</p></li><li><p>
Each <code class="literal">Curve</code> in the boundary of the
<code class="literal">MultiPolygon</code> is in the boundary of
exactly one <code class="literal">Polygon</code> element.
</p></li><li><p>
Every <code class="literal">Curve</code> in the boundary of an
<code class="literal">Polygon</code> element is in the boundary of the
<code class="literal">MultiPolygon</code>.
</p></li></ul></div></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="supported-spatial-data-formats"></a>17.3. Supported Spatial Data Formats</h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="spatial-extensions.html#gis-wkt-format">17.3.1. Well-Known Text (WKT) Format</a></span></dt><dt><span class="section"><a href="spatial-extensions.html#gis-wkb-format">17.3.2. Well-Known Binary (WKB) Format</a></span></dt></dl></div><p>
This section describes the standard spatial data formats that are
used to represent geometry objects in queries. They are:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Well-Known Text (WKT) format
</p></li><li><p>
Well-Known Binary (WKB) format
</p></li></ul></div><p>
Internally, MySQL stores geometry values in a format that is not
identical to either WKT or WKB format.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="gis-wkt-format"></a>17.3.1. Well-Known Text (WKT) Format</h3></div></div></div><a class="indexterm" name="id2988391"></a><a class="indexterm" name="id2988400"></a><p>
The Well-Known Text (WKT) representation of Geometry is designed
to exchange geometry data in ASCII form.
</p><p>
Examples of WKT representations of geometry objects:
</p><div class="itemizedlist"><ul type="disc"><li><p>
A <code class="literal">Point</code>:
</p><pre class="programlisting">POINT(15 20)
</pre><p>
Note that point coordinates are specified with no separating
comma.
</p></li><li><p>
A <code class="literal">LineString</code> with four points:
</p><pre class="programlisting">LINESTRING(0 0, 10 10, 20 25, 50 60)
</pre><p>
Note that point coordinate pairs are separated by commas.
</p></li><li><p>
A <code class="literal">Polygon</code> with one exterior ring and one
interior ring:
</p><pre class="programlisting">POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))
</pre></li><li><p>
A <code class="literal">MultiPoint</code> with three
<code class="literal">Point</code> values:
</p><pre class="programlisting">MULTIPOINT(0 0, 20 20, 60 60)
</pre></li><li><p>
A <code class="literal">MultiLineString</code> with two
<code class="literal">LineString</code> values:
</p><pre class="programlisting">MULTILINESTRING((10 10, 20 20), (15 15, 30 15))
</pre></li><li><p>
A <code class="literal">MultiPolygon</code> with two
<code class="literal">Polygon</code> values:
</p><pre class="programlisting">MULTIPOLYGON(((0 0,10 0,10 10,0 10,0 0)),((5 5,7 5,7 7,5 7, 5 5)))
</pre></li><li><p>
A <code class="literal">GeometryCollection</code> consisting of two
<code class="literal">Point</code> values and one
<code class="literal">LineString</code>:
</p><pre class="programlisting">GEOMETRYCOLLECTION(POINT(10 10), POINT(30 30), LINESTRING(15 15, 20 20))
</pre></li></ul></div><p>
A Backus-Naur grammar that specifies the formal production rules
for writing WKT values can be found in the OpenGIS specification
document referenced near the beginning of this chapter.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="gis-wkb-format"></a>17.3.2. Well-Known Binary (WKB) Format</h3></div></div></div><a class="indexterm" name="id2988671"></a><a class="indexterm" name="id2988680"></a><p>
The Well-Known Binary (WKB) representation for geometric values
is defined by the OpenGIS specification. It is also defined in
the ISO <em class="citetitle">SQL/MM Part 3: Spatial</em> standard.
</p><p>
WKB is used to exchange geometry data as binary streams
represented by <code class="literal">BLOB</code> values containing
geometric WKB information.
</p><p>
WKB uses one-byte unsigned integers, four-byte unsigned
integers, and eight-byte double-precision numbers (IEEE 754
format). A byte is eight bits.
</p><p>
For example, a WKB value that corresponds to <code class="literal">POINT(1
1)</code> consists of this sequence of 21 bytes (each
represented here by two hex digits):
</p><pre class="programlisting">0101000000000000000000F03F000000000000F03F
</pre><p>
The sequence may be broken down into these components:
</p><pre class="programlisting">Byte order : 01
WKB type : 01000000
X : 000000000000F03F
Y : 000000000000F03F
</pre><p>
Component representation is as follows:
</p><div class="itemizedlist"><ul type="disc"><li><p>
The byte order may be either 1 or 0 to indicate
little-endian or big-endian storage. The little-endian and
big-endian byte orders are also known as Network Data
Representation (NDR) and External Data Representation (XDR),
respectively.
</p></li><li><p>
The WKB type is a code that indicates the geometry type.
Values from 1 through 7 indicate <code class="literal">Point</code>,
<code class="literal">LineString</code>, <code class="literal">Polygon</code>,
<code class="literal">MultiPoint</code>,
<code class="literal">MultiLineString</code>,
<code class="literal">MultiPolygon</code>, and
<code class="literal">GeometryCollection</code>.
</p></li><li><p>
A <code class="literal">Point</code> value has X and Y coordinates,
each represented as a double-precision value.
</p></li></ul></div><p>
WKB values for more complex geometry values are represented by
more complex data structures, as detailed in the OpenGIS
specification.
</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="creating-a-spatially-enabled-mysql-database"></a>17.4. Creating a Spatially Enabled MySQL Database</h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="spatial-extensions.html#mysql-spatial-datatypes">17.4.1. MySQL Spatial Data Types</a></span></dt><dt><span class="section"><a href="spatial-extensions.html#creating-spatial-values">17.4.2. Creating Spatial Values</a></span></dt><dt><span class="section"><a href="spatial-extensions.html#creating-spatial-columns">17.4.3. Creating Spatial Columns</a></span></dt><dt><span class="section"><a href="spatial-extensions.html#populating-spatial-columns">17.4.4. Populating Spatial Columns</a></span></dt><dt><span class="section"><a href="spatial-extensions.html#fetching-spatial-data">17.4.5. Fetching Spatial Data</a></span></dt></dl></div><p>
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.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="mysql-spatial-datatypes"></a>17.4.1. MySQL Spatial Data Types</h3></div></div></div><a class="indexterm" name="id2988880"></a><a class="indexterm" name="id2988890"></a><a class="indexterm" name="id2988899"></a><a class="indexterm" name="id2988908"></a><a class="indexterm" name="id2988917"></a><a class="indexterm" name="id2988926"></a><a class="indexterm" name="id2988935"></a><a class="indexterm" name="id2988944"></a><a class="indexterm" name="id2988954"></a><a class="indexterm" name="id2988966"></a><a class="indexterm" name="id2988979"></a><a class="indexterm" name="id2988991"></a><a class="indexterm" name="id2989003"></a><a class="indexterm" name="id2989016"></a><a class="indexterm" name="id2989028"></a><a class="indexterm" name="id2989041"></a><p>
MySQL has data types that correspond to OpenGIS classes. Some of
these types hold single geometry values:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal">GEOMETRY</code>
</p></li><li><p>
<code class="literal">POINT</code>
</p></li><li><p>
<code class="literal">LINESTRING</code>
</p></li><li><p>
<code class="literal">POLYGON</code>
</p></li></ul></div><p>
<code class="literal">GEOMETRY</code> can store geometry values of any
type. The other single-value types (<code class="literal">POINT</code>,
<code class="literal">LINESTRING</code>, and <code class="literal">POLYGON</code>)
restrict their values to a particular geometry type.
</p><p>
The other data types hold collections of values:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal">MULTIPOINT</code>
</p></li><li><p>
<code class="literal">MULTILINESTRING</code>
</p></li><li><p>
<code class="literal">MULTIPOLYGON</code>
</p></li><li><p>
<code class="literal">GEOMETRYCOLLECTION</code>
</p></li></ul></div><p>
<code class="literal">GEOMETRYCOLLECTION</code> can store a collection of
objects of any type. The other collection types
(<code class="literal">MULTIPOINT</code>,
<code class="literal">MULTILINESTRING</code>,
<code class="literal">MULTIPOLYGON</code>, and
<code class="literal">GEOMETRYCOLLECTION</code>) restrict collection
members to those having a particular geometry type.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="creating-spatial-values"></a>17.4.2. Creating Spatial Values</h3></div></div></div><div class="toc"><dl><dt><span class="section"><a href="spatial-extensions.html#gis-wkt-functions">17.4.2.1. Creating Geometry Values Using WKT Functions</a></span></dt><dt><span class="section"><a href="spatial-extensions.html#gis-wkb-functions">17.4.2.2. Creating Geometry Values Using WKB Functions</a></span></dt><dt><span class="section"><a href="spatial-extensions.html#gis-mysql-specific-functions">17.4.2.3. Creating Geometry Values Using MySQL-Specific Functions</a></span></dt></dl></div><p>
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.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="gis-wkt-functions"></a>17.4.2.1. Creating Geometry Values Using WKT Functions</h4></div></div></div><p>
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.
</p><p>
<code class="literal">GeomFromText()</code> 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.
</p><div class="itemizedlist"><ul type="disc"><li><p><a name="function_geomcollfromtext"></a>
<a class="indexterm" name="id2989313"></a>
<a class="indexterm" name="id2989322"></a>
<code class="literal">GeomCollFromText(<em class="replaceable"><code>wkt</code></em>[,<em class="replaceable"><code>srid</code></em>])</code>,
<code class="literal">GeometryCollectionFromText(<em class="replaceable"><code>wkt</code></em>[,<em class="replaceable"><code>srid</code></em>])</code>
</p><p>
Constructs a <code class="literal">GEOMETRYCOLLECTION</code> value
using its WKT representation and SRID.
</p></li><li><p><a name="function_geomfromtext"></a>
<a class="indexterm" name="id2989400"></a>
<a class="indexterm" name="id2989409"></a>
<code class="literal">GeomFromText(<em class="replaceable"><code>wkt</code></em>[,<em class="replaceable"><code>srid</code></em>])</code>,
<code class="literal">GeometryFromText(<em class="replaceable"><code>wkt</code></em>[,<em class="replaceable"><code>srid</code></em>])</code>
</p><p>
Constructs a geometry value of any type using its WKT
representation and SRID.
</p></li><li><p><a name="function_linefromtext"></a>
<a class="indexterm" name="id2989480"></a>
<a class="indexterm" name="id2989489"></a>
<code class="literal">LineFromText(<em class="replaceable"><code>wkt</code></em>[,<em class="replaceable"><code>srid</code></em>])</code>,
<code class="literal">LineStringFromText(<em class="replaceable"><code>wkt</code></em>[,<em class="replaceable"><code>srid</code></em>])</code>
</p><p>
Constructs a <code class="literal">LINESTRING</code> value using its
WKT representation and SRID.
</p></li><li><p><a name="function_mlinefromtext"></a>
<a class="indexterm" name="id2989566"></a>
<a class="indexterm" name="id2989576"></a>
<code class="literal">MLineFromText(<em class="replaceable"><code>wkt</code></em>[,<em class="replaceable"><code>srid</code></em>])</code>,
<code class="literal">MultiLineStringFromText(<em class="replaceable"><code>wkt</code></em>[,<em class="replaceable"><code>srid</code></em>])</code>
</p><p>
Constructs a <code class="literal">MULTILINESTRING</code> value
using its WKT representation and SRID.
</p></li><li><p><a name="function_mpointfromtext"></a>
<a class="indexterm" name="id2989653"></a>
<a class="indexterm" name="id2989662"></a>
<code class="literal">MPointFromText(<em class="replaceable"><code>wkt</code></em>[,<em class="replaceable"><code>srid</code></em>])</code>,
<code class="literal">MultiPointFromText(<em class="replaceable"><code>wkt</code></em>[,<em class="replaceable"><code>srid</code></em>])</code>
</p><p>
Constructs a <code class="literal">MULTIPOINT</code> value using its
WKT representation and SRID.
</p></li><li><p><a name="function_mpolyfromtext"></a>
<a class="indexterm" name="id2989739"></a>
<a class="indexterm" name="id2989748"></a>
<code class="literal">MPolyFromText(<em class="replaceable"><code>wkt</code></em>[,<em class="replaceable"><code>srid</code></em>])</code>,
<code class="literal">MultiPolygonFromText(<em class="replaceable"><code>wkt</code></em>[,<em class="replaceable"><code>srid</code></em>])</code>
</p><p>
Constructs a <code class="literal">MULTIPOLYGON</code> value using
its WKT representation and SRID.
</p></li><li><p><a name="function_pointfromtext"></a>
<a class="indexterm" name="id2989819"></a>
<code class="literal">PointFromText(<em class="replaceable"><code>wkt</code></em>[,<em class="replaceable"><code>srid</code></em>])</code>
</p><p>
Constructs a <code class="literal">POINT</code> value using its WKT
representation and SRID.
</p></li><li><p><a name="function_polyfromtext"></a>
<a class="indexterm" name="id2989883"></a>
<a class="indexterm" name="id2989892"></a>
<code class="literal">PolyFromText(<em class="replaceable"><code>wkt</code></em>[,<em class="replaceable"><code>srid</code></em>])</code>,
<code class="literal">PolygonFromText(<em class="replaceable"><code>wkt</code></em>[,<em class="replaceable"><code>srid</code></em>])</code>
</p><p>
Constructs a <code class="literal">POLYGON</code> value using its
WKT representation and SRID.
</p></li></ul></div><p>
The OpenGIS specification also defines the following optional
functions, which MySQL does not implement. These functions
construct <code class="literal">Polygon</code> or
<code class="literal">MultiPolygon</code> values based on the WKT
representation of a collection of rings or closed
<code class="literal">LineString</code> values. These values may
intersect.
</p><div class="itemizedlist"><ul type="disc"><li><p>
<a class="indexterm" name="id2989979"></a>
<code class="literal">BdMPolyFromText(<em class="replaceable"><code>wkt</code></em>,<em class="replaceable"><code>srid</code></em>)</code>
</p><p>
Constructs a <code class="literal">MultiPolygon</code> value from a
<code class="literal">MultiLineString</code> value in WKT format
containing an arbitrary collection of closed
<code class="literal">LineString</code> values.
</p></li><li><p>
<a class="indexterm" name="id2990031"></a>
<code class="literal">BdPolyFromText(<em class="replaceable"><code>wkt</code></em>,<em class="replaceable"><code>srid</code></em>)</code>
</p><p>
Constructs a <code class="literal">Polygon</code> value from a
<code class="literal">MultiLineString</code> value in WKT format
containing an arbitrary collection of closed
<code class="literal">LineString</code> values.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="gis-wkb-functions"></a>17.4.2.2. Creating Geometry Values Using WKB Functions</h4></div></div></div><p>
MySQL provides a number of functions that take as input
parameters a <code class="literal">BLOB</code> containing a Well-Known
Binary representation and, optionally, a spatial reference
system identifier (SRID). They return the corresponding
geometry.
</p><p>
<code class="literal">GeomFromWKB()</code> 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.
</p><div class="itemizedlist"><ul type="disc"><li><p><a name="function_geomgollfromwkb"></a>
<a class="indexterm" name="id2990150"></a>
<a class="indexterm" name="id2990160"></a>
<code class="literal">GeomCollFromWKB(<em class="replaceable"><code>wkb</code></em>[,<em class="replaceable"><code>srid</code></em>])</code>,
<code class="literal">GeometryCollectionFromWKB(<em class="replaceable"><code>wkb</code></em>[,<em class="replaceable"><code>srid</code></em>])</code>
</p><p>
Constructs a <code class="literal">GEOMETRYCOLLECTION</code> value
using its WKB representation and SRID.
</p></li><li><p><a name="function_geomfromwkb"></a>
<a class="indexterm" name="id2990237"></a>
<a class="indexterm" name="id2990246"></a>
<code class="literal">GeomFromWKB(<em class="replaceable"><code>wkb</code></em>[,<em class="replaceable"><code>srid</code></em>])</code>,
<code class="literal">GeometryFromWKB(<em class="replaceable"><code>wkb</code></em>[,<em class="replaceable"><code>srid</code></em>])</code>
</p><p>
Constructs a geometry value of any type using its WKB
representation and SRID.
</p></li><li><p><a name="function_linefromwkb"></a>
<a class="indexterm" name="id2990317"></a>
<a class="indexterm" name="id2990326"></a>
<code class="literal">LineFromWKB(<em class="replaceable"><code>wkb</code></em>[,<em class="replaceable"><code>srid</code></em>])</code>,
<code class="literal">LineStringFromWKB(<em class="replaceable"><code>wkb</code></em>[,<em class="replaceable"><code>srid</code></em>])</code>
</p><p>
Constructs a <code class="literal">LINESTRING</code> value using its
WKB representation and SRID.
</p></li><li><p><a name="function_mlinefromwkb"></a>
<a class="indexterm" name="id2990403"></a>
<a class="indexterm" name="id2990412"></a>
<code class="literal">MLineFromWKB(<em class="replaceable"><code>wkb</code></em>[,<em class="replaceable"><code>srid</code></em>])</code>,
<code class="literal">MultiLineStringFromWKB(<em class="replaceable"><code>wkb</code></em>[,<em class="replaceable"><code>srid</code></em>])</code>
</p><p>
Constructs a <code class="literal">MULTILINESTRING</code> value
using its WKB representation and SRID.
</p></li><li><p><a name="function_mpointfromwkb"></a>
<a class="indexterm" name="id2990490"></a>
<a class="indexterm" name="id2990499"></a>
<code class="literal">MPointFromWKB(<em class="replaceable"><code>wkb</code></em>[,<em class="replaceable"><code>srid</code></em>])</code>,
<code class="literal">MultiPointFromWKB(<em class="replaceable"><code>wkb</code></em>[,<em class="replaceable"><code>srid</code></em>])</code>
</p><p>
Constructs a <code class="literal">MULTIPOINT</code> value using its
WKB representation and SRID.
</p></li><li><p><a name="function_mpolyfromwbk"></a>
<a class="indexterm" name="id2990576"></a>
<a class="indexterm" name="id2990585"></a>
<code class="literal">MPolyFromWKB(<em class="replaceable"><code>wkb</code></em>[,<em class="replaceable"><code>srid</code></em>])</code>,
<code class="literal">MultiPolygonFromWKB(<em class="replaceable"><code>wkb</code></em>[,<em class="replaceable"><code>srid</code></em>])</code>
</p><p>
Constructs a <code class="literal">MULTIPOLYGON</code> value using
its WKB representation and SRID.
</p></li><li><p><a name="function_pointfromwkb"></a>
<a class="indexterm" name="id2990662"></a>
<code class="literal">PointFromWKB(<em class="replaceable"><code>wkb</code></em>[,<em class="replaceable"><code>srid</code></em>])</code>
</p><p>
Constructs a <code class="literal">POINT</code> value using its WKB
representation and SRID.
</p></li><li><p><a name="function_polyfromwkb"></a>
<a class="indexterm" name="id2990726"></a>
<a class="indexterm" name="id2990735"></a>
<code class="literal">PolyFromWKB(<em class="replaceable"><code>wkb</code></em>[,<em class="replaceable"><code>srid</code></em>])</code>,
<code class="literal">PolygonFromWKB(<em class="replaceable"><code>wkb</code></em>[,<em class="replaceable"><code>srid</code></em>])</code>
</p><p>
Constructs a <code class="literal">POLYGON</code> value using its
WKB representation and SRID.
</p></li></ul></div><p>
The OpenGIS specification also describes optional functions
for constructing <code class="literal">Polygon</code> or
<code class="literal">MultiPolygon</code> values based on the WKB
representation of a collection of rings or closed
<code class="literal">LineString</code> values. These values may
intersect. MySQL does not implement these functions:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<a class="indexterm" name="id2990821"></a>
<code class="literal">BdMPolyFromWKB(<em class="replaceable"><code>wkb</code></em>,<em class="replaceable"><code>srid</code></em>)</code>
</p><p>
Constructs a <code class="literal">MultiPolygon</code> value from a
<code class="literal">MultiLineString</code> value in WKB format
containing an arbitrary collection of closed
<code class="literal">LineString</code> values.
</p></li><li><p>
<a class="indexterm" name="id2990873"></a>
<code class="literal">BdPolyFromWKB(<em class="replaceable"><code>wkb</code></em>,<em class="replaceable"><code>srid</code></em>)</code>
</p><p>
Constructs a <code class="literal">Polygon</code> value from a
<code class="literal">MultiLineString</code> value in WKB format
containing an arbitrary collection of closed
<code class="literal">LineString</code> values.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="gis-mysql-specific-functions"></a>17.4.2.3. Creating Geometry Values Using MySQL-Specific Functions</h4></div></div></div><p>
MySQL provides a set of useful non-standard functions for
creating geometry WKB representations. The functions described
in this section are MySQL extensions to the OpenGIS
specification. The results of these functions are
<code class="literal">BLOB</code> 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 <code class="literal">GeomFromWKB()</code> function
family.
</p><div class="itemizedlist"><ul type="disc"><li><p><a name="function_geometrycollection"></a>
<a class="indexterm" name="id2990984"></a>
<code class="literal">GeometryCollection(<em class="replaceable"><code>g1</code></em>,<em class="replaceable"><code>g2</code></em>,...)</code>
</p><p>
Constructs a WKB <code class="literal">GeometryCollection</code>. If
any argument is not a well-formed WKB representation of a
geometry, the return value is <code class="literal">NULL</code>.
</p></li><li><p><a name="function_linestring"></a>
<a class="indexterm" name="id2991049"></a>
<code class="literal">LineString(<em class="replaceable"><code>pt1</code></em>,<em class="replaceable"><code>pt2</code></em>,...)</code>
</p><p>
Constructs a WKB <code class="literal">LineString</code> value from
a number of WKB <code class="literal">Point</code> arguments. If any
argument is not a WKB <code class="literal">Point</code>, the return
value is <code class="literal">NULL</code>. If the number of
<code class="literal">Point</code> arguments is less than two, the
return value is <code class="literal">NULL</code>.
</p></li><li><p><a name="function_multilinestring"></a>
<a class="indexterm" name="id2991139"></a>
<code class="literal">MultiLineString(<em class="replaceable"><code>ls1</code></em>,<em class="replaceable"><code>ls2</code></em>,...)</code>
</p><p>
Constructs a WKB <code class="literal">MultiLineString</code> value
using WKB <code class="literal">LineString</code> arguments. If any
argument is not a WKB <code class="literal">LineString</code>, the
return value is <code class="literal">NULL</code>.
</p></li><li><p><a name="function_multipoint"></a>
<a class="indexterm" name="id2991216"></a>
<code class="literal">MultiPoint(<em class="replaceable"><code>pt1</code></em>,<em class="replaceable"><code>pt2</code></em>,...)</code>
</p><p>
Constructs a WKB <code class="literal">MultiPoint</code> value using
WKB <code class="literal">Point</code> arguments. If any argument is
not a WKB <code class="literal">Point</code>, the return value is
<code class="literal">NULL</code>.
</p></li><li><p><a name="function_multipolygon"></a>
<a class="indexterm" name="id2991292"></a>
<code class="literal">MultiPolygon(<em class="replaceable"><code>poly1</code></em>,<em class="replaceable"><code>poly2</code></em>,...)</code>
</p><p>
Constructs a WKB <code class="literal">MultiPolygon</code> value
from a set of WKB <code class="literal">Polygon</code> arguments. If
any argument is not a WKB <code class="literal">Polygon</code>, the
return value is <code class="literal">NULL</code>.
</p></li><li><p><a name="function_point"></a>
<a class="indexterm" name="id2991369"></a>
<code class="literal">Point(<em class="replaceable"><code>x</code></em>,<em class="replaceable"><code>y</code></em>)</code>
</p><p>
Constructs a WKB <code class="literal">Point</code> using its
coordinates.
</p></li><li><p><a name="function_polygon"></a>
<a class="indexterm" name="id2991426"></a>
<code class="literal">Polygon(<em class="replaceable"><code>ls1</code></em>,<em class="replaceable"><code>ls2</code></em>,...)</code>
</p><p>
Constructs a WKB <code class="literal">Polygon</code> value from a
number of WKB <code class="literal">LineString</code> arguments. If
any argument does not represent the WKB of a
<code class="literal">LinearRing</code> (that is, not a closed and
simple <code class="literal">LineString</code>) the return value is
<code class="literal">NULL</code>.
</p></li></ul></div></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="creating-spatial-columns"></a>17.4.3. Creating Spatial Columns</h3></div></div></div><p>
MySQL provides a standard way of creating spatial columns for
geometry types, for example, with <code class="literal">CREATE
TABLE</code> or <code class="literal">ALTER TABLE</code>. Currently,
spatial columns are supported for <code class="literal">MyISAM</code>,
<code class="literal">InnoDB</code>, <code class="literal">NDB</code>,
<code class="literal">BDB</code>, and <code class="literal">ARCHIVE</code> tables.
(Support for storage engines other than
<code class="literal">MyISAM</code> was added in MySQL 5.0.16.) See also
the annotations about spatial indexes under
<a href="spatial-extensions.html#creating-spatial-indexes" title="17.6.1. Creating Spatial Indexes">Section 17.6.1, “Creating Spatial Indexes”</a>.
</p><div class="itemizedlist"><ul type="disc"><li><p>
Use the <code class="literal">CREATE TABLE</code> statement to create
a table with a spatial column:
</p><pre class="programlisting">CREATE TABLE geom (g GEOMETRY);
</pre></li><li><p>
Use the <code class="literal">ALTER TABLE</code> statement to add or
drop a spatial column to or from an existing table:
</p><pre class="programlisting">ALTER TABLE geom ADD pt POINT;
ALTER TABLE geom DROP pt;
</pre></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="populating-spatial-columns"></a>17.4.4. Populating Spatial Columns</h3></div></div></div><p>
After you have created spatial columns, you can populate them
with spatial data.
</p><p>
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:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Perform the conversion directly in the
<code class="literal">INSERT</code> statement:
</p><pre class="programlisting">INSERT INTO geom VALUES (GeomFromText('POINT(1 1)'));
SET @g = 'POINT(1 1)';
INSERT INTO geom VALUES (GeomFromText(@g));
</pre></li><li><p>
Perform the conversion prior to the
<code class="literal">INSERT</code>:
</p><pre class="programlisting">SET @g = GeomFromText('POINT(1 1)');
INSERT INTO geom VALUES (@g);
</pre></li></ul></div><p>
The following examples insert more complex geometries into the
table:
</p><pre class="programlisting">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));
</pre><p>
The preceding examples all use <code class="literal">GeomFromText()</code>
to create geometry values. You can also use type-specific
functions:
</p><pre class="programlisting">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));
</pre><p>
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:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Inserting a <code class="literal">POINT(1 1)</code> value with hex
literal syntax:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>INSERT INTO geom VALUES</code></strong>
-> <strong class="userinput"><code>(GeomFromWKB(0x0101000000000000000000F03F000000000000F03F));</code></strong>
</pre></li><li><p>
An ODBC application can send a WKB representation, binding
it to a placeholder using an argument of
<code class="literal">BLOB</code> type:
</p><pre class="programlisting">INSERT INTO geom VALUES (GeomFromWKB(?))
</pre><p>
Other programming interfaces may support a similar
placeholder mechanism.
</p></li><li><p>
In a C program, you can escape a binary value using
<a href="apis.html#mysql-real-escape-string" title="23.2.3.53. mysql_real_escape_string()"><code class="literal">mysql_real_escape_string()</code></a>
and include the result in a query string that is sent to the
server. See <a href="apis.html#mysql-real-escape-string" title="23.2.3.53. mysql_real_escape_string()">Section 23.2.3.53, “<code class="literal">mysql_real_escape_string()</code>”</a>.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="fetching-spatial-data"></a>17.4.5. Fetching Spatial Data</h3></div></div></div><p>
Geometry values stored in a table can be fetched in internal
format. You can also convert them into WKT or WKB format.
</p><div class="itemizedlist"><ul type="disc"><li><p>
Fetching spatial data in internal format:
</p><p>
Fetching geometry values using internal format can be useful
in table-to-table transfers:
</p><pre class="programlisting">CREATE TABLE geom2 (g GEOMETRY) SELECT g FROM geom;
</pre></li><li><p>
Fetching spatial data in WKT format:
</p><p>
The <code class="literal">AsText()</code> function converts a geometry
from internal format into a WKT string.
</p><pre class="programlisting">SELECT AsText(g) FROM geom;
</pre></li><li><p>
Fetching spatial data in WKB format:
</p><p>
The <code class="literal">AsBinary()</code> function converts a
geometry from internal format into a <code class="literal">BLOB</code>
containing the WKB value.
</p><pre class="programlisting">SELECT AsBinary(g) FROM geom;
</pre></li></ul></div></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="analysing-spatial-information"></a>17.5. Analyzing Spatial Information</h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="spatial-extensions.html#functions-to-convert-geometries-between-formats">17.5.1. Geometry Format Conversion Functions</a></span></dt><dt><span class="section"><a href="spatial-extensions.html#geometry-property-functions">17.5.2. <code class="literal">Geometry</code> Functions</a></span></dt><dt><span class="section"><a href="spatial-extensions.html#functions-that-create-new-geometries-from-existing-ones">17.5.3. Functions That Create New Geometries from Existing Ones</a></span></dt><dt><span class="section"><a href="spatial-extensions.html#functions-for-testing-spatial-relations-between-geometric-objects">17.5.4. Functions for Testing Spatial Relations Between Geometric Objects</a></span></dt><dt><span class="section"><a href="spatial-extensions.html#relations-on-geometry-mbr">17.5.5. Relations on Geometry Minimal Bounding Rectangles (MBRs)</a></span></dt><dt><span class="section"><a href="spatial-extensions.html#functions-that-test-spatial-relationships-between-geometries">17.5.6. Functions That Test Spatial Relationships Between Geometries</a></span></dt></dl></div><p>
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:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Functions that convert geometries between various formats
</p></li><li><p>
Functions that provide access to qualitative or quantitative
properties of a geometry
</p></li><li><p>
Functions that describe relations between two geometries
</p></li><li><p>
Functions that create new geometries from existing ones
</p></li></ul></div><p>
Spatial analysis functions can be used in many contexts, such as:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Any interactive SQL program, such as <span><strong class="command">mysql</strong></span>
or MySQL Query Browser
</p></li><li><p>
Application programs written in any language that supports a
MySQL client API
</p></li></ul></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="functions-to-convert-geometries-between-formats"></a>17.5.1. Geometry Format Conversion Functions</h3></div></div></div><p>
MySQL supports the following functions for converting geometry
values between internal format and either WKT or WKB format:
</p><div class="itemizedlist"><ul type="disc"><li><p><a name="function_asbinary"></a>
<a class="indexterm" name="id2992058"></a>
<code class="literal">AsBinary(<em class="replaceable"><code>g</code></em>)</code>
</p><p>
Converts a value in internal geometry format to its WKB
representation and returns the binary result.
</p><pre class="programlisting">SELECT AsBinary(g) FROM geom;
</pre></li><li><p><a name="function_astext"></a>
<a class="indexterm" name="id2992124"></a>
<code class="literal">AsText(<em class="replaceable"><code>g</code></em>)</code>
</p><p>
Converts a value in internal geometry format to its WKT
representation and returns the string result.
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET @g = 'LineString(1 1,2 2,3 3)';</code></strong>
mysql> <strong class="userinput"><code>SELECT AsText(GeomFromText(@g));</code></strong>
+--------------------------+
| AsText(GeomFromText(@g)) |
+--------------------------+
| LINESTRING(1 1,2 2,3 3) |
+--------------------------+
</pre></li><li><p>
<a class="indexterm" name="id2992184"></a>
<code class="literal">GeomFromText(<em class="replaceable"><code>wkt</code></em>[,<em class="replaceable"><code>srid</code></em>])</code>
</p><p>
Converts a string value from its WKT representation into
internal geometry format and returns the result. A number of
type-specific functions are also supported, such as
<code class="literal">PointFromText()</code> and
<code class="literal">LineFromText()</code>. See
<a href="spatial-extensions.html#gis-wkt-functions" title="17.4.2.1. Creating Geometry Values Using WKT Functions">Section 17.4.2.1, “Creating Geometry Values Using WKT Functions”</a>.
</p></li><li><p>
<a class="indexterm" name="id2992236"></a>
<code class="literal">GeomFromWKB(<em class="replaceable"><code>wkb</code></em>[,<em class="replaceable"><code>srid</code></em>])</code>
</p><p>
Converts a binary value from its WKB representation into
internal geometry format and returns the result. A number of
type-specific functions are also supported, such as
<code class="literal">PointFromWKB()</code> and
<code class="literal">LineFromWKB()</code>. See
<a href="spatial-extensions.html#gis-wkb-functions" title="17.4.2.2. Creating Geometry Values Using WKB Functions">Section 17.4.2.2, “Creating Geometry Values Using WKB Functions”</a>.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="geometry-property-functions"></a>17.5.2. <code class="literal">Geometry</code> Functions</h3></div></div></div><div class="toc"><dl><dt><span class="section"><a href="spatial-extensions.html#general-geometry-property-functions">17.5.2.1. General Geometry Functions</a></span></dt><dt><span class="section"><a href="spatial-extensions.html#point-property-functions">17.5.2.2. <code class="literal">Point</code> Functions</a></span></dt><dt><span class="section"><a href="spatial-extensions.html#linestring-property-functions">17.5.2.3. <code class="literal">LineString</code> Functions</a></span></dt><dt><span class="section"><a href="spatial-extensions.html#multilinestring-property-functions">17.5.2.4. <code class="literal">MultiLineString</code> Functions</a></span></dt><dt><span class="section"><a href="spatial-extensions.html#polygon-property-functions">17.5.2.5. <code class="literal">Polygon</code> Functions</a></span></dt><dt><span class="section"><a href="spatial-extensions.html#multipolygon-property-functions">17.5.2.6. <code class="literal">MultiPolygon</code> Functions</a></span></dt><dt><span class="section"><a href="spatial-extensions.html#geometrycollection-property-functions">17.5.2.7. <code class="literal">GeometryCollection</code> Functions</a></span></dt></dl></div><p>
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 <code class="literal">NULL</code> if the
argument is of an incorrect geometry type. For example,
<code class="literal">Area()</code> returns <code class="literal">NULL</code> if the
object type is neither <code class="literal">Polygon</code> nor
<code class="literal">MultiPolygon</code>.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="general-geometry-property-functions"></a>17.5.2.1. General Geometry Functions</h4></div></div></div><p>
The functions listed in this section do not restrict their
argument and accept a geometry value of any type.
</p><div class="itemizedlist"><ul type="disc"><li><p><a name="function_dimension"></a>
<a class="indexterm" name="id2992381"></a>
<code class="literal">Dimension(<em class="replaceable"><code>g</code></em>)</code>
</p><p>
Returns the inherent dimension of the geometry value
<em class="replaceable"><code>g</code></em>. The result can be –1,
0, 1, or 2. The meaning of these values is given in
<a href="spatial-extensions.html#gis-class-geometry" title="17.2.2. Class Geometry">Section 17.2.2, “Class <code class="literal">Geometry</code>”</a>.
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT Dimension(GeomFromText('LineString(1 1,2 2)'));</code></strong>
+------------------------------------------------+
| Dimension(GeomFromText('LineString(1 1,2 2)')) |
+------------------------------------------------+
| 1 |
+------------------------------------------------+
</pre></li><li><p><a name="function_envelope"></a>
<a class="indexterm" name="id2992461"></a>
<code class="literal">Envelope(<em class="replaceable"><code>g</code></em>)</code>
</p><p>
Returns the Minimum Bounding Rectangle (MBR) for the
geometry value <em class="replaceable"><code>g</code></em>. The result is
returned as a <code class="literal">Polygon</code> value.
</p><p>
The polygon is defined by the corner points of the
bounding box:
</p><pre class="programlisting">POLYGON((MINX MINY, MAXX MINY, MAXX MAXY, MINX MAXY, MINX MINY))
</pre><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT AsText(Envelope(GeomFromText('LineString(1 1,2 2)')));</code></strong>
+-------------------------------------------------------+
| AsText(Envelope(GeomFromText('LineString(1 1,2 2)'))) |
+-------------------------------------------------------+
| POLYGON((1 1,2 1,2 2,1 2,1 1)) |
+-------------------------------------------------------+
</pre></li><li><p><a name="function_geometrytype"></a>
<a class="indexterm" name="id2992554"></a>
<code class="literal">GeometryType(<em class="replaceable"><code>g</code></em>)</code>
</p><p>
Returns as a string the name of the geometry type of which
the geometry instance <em class="replaceable"><code>g</code></em> is a
member. The name corresponds to one of the instantiable
<code class="literal">Geometry</code> subclasses.
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT GeometryType(GeomFromText('POINT(1 1)'));</code></strong>
+------------------------------------------+
| GeometryType(GeomFromText('POINT(1 1)')) |
+------------------------------------------+
| POINT |
+------------------------------------------+
</pre></li><li><p><a name="function_srid"></a>
<a class="indexterm" name="id2992634"></a>
<code class="literal">SRID(<em class="replaceable"><code>g</code></em>)</code>
</p><p>
Returns an integer indicating the Spatial Reference System
ID for the geometry value <em class="replaceable"><code>g</code></em>.
</p><p>
In MySQL, the SRID value is just an integer associated
with the geometry value. All calculations are done
assuming Euclidean (planar) geometry.
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SELECT SRID(GeomFromText('LineString(1 1,2 2)',101));</code></strong>
+-----------------------------------------------+
| SRID(GeomFromText('LineString(1 1,2 2)',101)) |
+-----------------------------------------------+
| 101 |
+-----------------------------------------------+
</pre></li></ul></div><p>
The OpenGIS specification also defines the following
functions, which MySQL does not implement:
</p><div class="itemizedlist"><ul type="disc"><li><p><a name="function_boundary"></a>
<a class="indexterm" name="id2992722"></a>
<code class="literal">Boundary(<em class="replaceable"><code>g</code></em>)</code>
</p><p>
Returns a geometry that is the closure of the
combinatorial boundary of the geometry value
<em class="replaceable"><code>g</code></em>.
</p></li><li><p><a name="function_isempty"></a>
<a class="indexterm" name="id2992774"></a>
<code class="literal">IsEmpty(<em class="replaceable"><code>g</code></em>)</code>
</p><p>
Returns 1 if the geometry value
<em class="replaceable"><code>g</code></em> is the empty geometry, 0 if
it is not empty, and –1 if the argument is
<code class="literal">NULL</code>. If the geometry is empty, it
represents the empty point set.
</p></li><li><p><a name="function_issimple"></a>
<a class="indexterm" name="id2992835"></a>
<code class="literal">IsSimple(<em class="replaceable"><code>g</code></em>)</code>
</p><p>
Currently, this function is a placeholder and should not
be used. If implemented, its behavior will be as described
in the next paragraph.
</p><p>
Returns 1 if the geometry value
<em class="replaceable"><code>g</code></em> has no anomalous geometric
points, such as self-intersection or self-tangency.
<code class="literal">IsSimple()</code> returns 0 if the argument is
not simple, and –1 if it is <code class="literal">NULL</code>.
</p><p>
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. (See
<a href="spatial-extensions.html#gis-geometry-class-hierarchy" title="17.2.1. The Geometry Class Hierarchy">Section 17.2.1, “The Geometry Class Hierarchy”</a>.)
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="point-property-functions"></a>17.5.2.2. <code class="literal">Point</code> Functions</h4></div></div></div><p>
A <code class="literal">Point</code> consists of X and Y coordinates,
which may be obtained using the following functions:
</p><div class="itemizedlist"><ul type="disc"><li><p><a name="function_x"></a>
<a class="indexterm" name="id2992956"></a>
<code class="literal">X(<em class="replaceable"><code>p</code></em>)</code>
</p><p>
Returns the X-coordinate value for the point
<em class="replaceable"><code>p</code></em> as a double-precision number.
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET @pt = 'Point(56.7 53.34)';</code></strong>
mysql> <strong class="userinput"><code>SELECT X(GeomFromText(@pt));</code></strong>
+----------------------+
| X(GeomFromText(@pt)) |
+----------------------+
| 56.7 |
+----------------------+
</pre></li><li><p><a name="function_y"></a>
<a class="indexterm" name="id2993032"></a>
<code class="literal">Y(<em class="replaceable"><code>p</code></em>)</code>
</p><p>
Returns the Y-coordinate value for the point
<em class="replaceable"><code>p</code></em> as a double-precision number.
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET @pt = 'Point(56.7 53.34)';</code></strong>
mysql> <strong class="userinput"><code>SELECT Y(GeomFromText(@pt));</code></strong>
+----------------------+
| Y(GeomFromText(@pt)) |
+----------------------+
| 53.34 |
+----------------------+
</pre></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="linestring-property-functions"></a>17.5.2.3. <code class="literal">LineString</code> Functions</h4></div></div></div><p>
A <code class="literal">LineString</code> consists of
<code class="literal">Point</code> values. You can extract particular
points of a <code class="literal">LineString</code>, count the number of
points that it contains, or obtain its length.
</p><div class="itemizedlist"><ul type="disc"><li><p><a name="function_endpoint"></a>
<a class="indexterm" name="id2993157"></a>
<code class="literal">EndPoint(<em class="replaceable"><code>ls</code></em>)</code>
</p><p>
Returns the <code class="literal">Point</code> that is the endpoint
of the <code class="literal">LineString</code> value
<em class="replaceable"><code>ls</code></em>.
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET @ls = 'LineString(1 1,2 2,3 3)';</code></strong>
mysql> <strong class="userinput"><code>SELECT AsText(EndPoint(GeomFromText(@ls)));</code></strong>
+-------------------------------------+
| AsText(EndPoint(GeomFromText(@ls))) |
+-------------------------------------+
| POINT(3 3) |
+-------------------------------------+
</pre></li><li><p><a name="function_glength"></a>
<a class="indexterm" name="id2993246"></a>
<code class="literal">GLength(<em class="replaceable"><code>ls</code></em>)</code>
</p><p>
Returns as a double-precision number the length of the
<code class="literal">LineString</code> value
<em class="replaceable"><code>ls</code></em> in its associated spatial
reference.
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET @ls = 'LineString(1 1,2 2,3 3)';</code></strong>
mysql> <strong class="userinput"><code>SELECT GLength(GeomFromText(@ls));</code></strong>
+----------------------------+
| GLength(GeomFromText(@ls)) |
+----------------------------+
| 2.8284271247462 |
+----------------------------+
</pre><p>
<code class="literal">GLength()</code> is a non-standard name. It
corresponds to the OpenGIS <code class="literal">Length()</code>
function.
</p></li><li><p><a name="function_numpoints"></a>
<a class="indexterm" name="id2993347"></a>
<code class="literal">NumPoints(<em class="replaceable"><code>ls</code></em>)</code>
</p><p>
Returns the number of <code class="literal">Point</code> objects in
the <code class="literal">LineString</code> value
<em class="replaceable"><code>ls</code></em>.
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET @ls = 'LineString(1 1,2 2,3 3)';</code></strong>
mysql> <strong class="userinput"><code>SELECT NumPoints(GeomFromText(@ls));</code></strong>
+------------------------------+
| NumPoints(GeomFromText(@ls)) |
+------------------------------+
| 3 |
+------------------------------+
</pre></li><li><p><a name="function_pointn"></a>
<a class="indexterm" name="id2993437"></a>
<code class="literal">PointN(<em class="replaceable"><code>ls</code></em>,<em class="replaceable"><code>N</code></em>)</code>
</p><p>
Returns the <em class="replaceable"><code>N</code></em>-th
<code class="literal">Point</code> in the
<code class="literal">Linestring</code> value
<em class="replaceable"><code>ls</code></em>. Points are numbered
beginning with 1.
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET @ls = 'LineString(1 1,2 2,3 3)';</code></strong>
mysql> <strong class="userinput"><code>SELECT AsText(PointN(GeomFromText(@ls),2));</code></strong>
+-------------------------------------+
| AsText(PointN(GeomFromText(@ls),2)) |
+-------------------------------------+
| POINT(2 2) |
+-------------------------------------+
</pre></li><li><p><a name="function_startpoint"></a>
<a class="indexterm" name="id2993534"></a>
<code class="literal">StartPoint(<em class="replaceable"><code>ls</code></em>)</code>
</p><p>
Returns the <code class="literal">Point</code> that is the start
point of the <code class="literal">LineString</code> value
<em class="replaceable"><code>ls</code></em>.
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET @ls = 'LineString(1 1,2 2,3 3)';</code></strong>
mysql> <strong class="userinput"><code>SELECT AsText(StartPoint(GeomFromText(@ls)));</code></strong>
+---------------------------------------+
| AsText(StartPoint(GeomFromText(@ls))) |
+---------------------------------------+
| POINT(1 1) |
+---------------------------------------+
</pre></li></ul></div><p>
The OpenGIS specification also defines the following function,
which MySQL does not implement:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<a class="indexterm" name="id2993619"></a>
<code class="literal">IsRing(<em class="replaceable"><code>ls</code></em>)</code>
</p><p>
Returns 1 if the <code class="literal">LineString</code> value
<em class="replaceable"><code>ls</code></em> is closed (that is, its
<code class="literal">StartPoint()</code> and
<code class="literal">EndPoint()</code> values are the same) and is
simple (does not pass through the same point more than
once). Returns 0 if <em class="replaceable"><code>ls</code></em> is not a
ring, and –1 if it is <code class="literal">NULL</code>.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="multilinestring-property-functions"></a>17.5.2.4. <code class="literal">MultiLineString</code> Functions</h4></div></div></div><div class="itemizedlist"><ul type="disc"><li><p>
<a class="indexterm" name="id2993715"></a>
<code class="literal">GLength(<em class="replaceable"><code>mls</code></em>)</code>
</p><p>
Returns as a double-precision number the length of the
<code class="literal">MultiLineString</code> value
<em class="replaceable"><code>mls</code></em>. The length of
<em class="replaceable"><code>mls</code></em> is equal to the sum of the
lengths of its elements.
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET @mls = 'MultiLineString((1 1,2 2,3 3),(4 4,5 5))';</code></strong>
mysql> <strong class="userinput"><code>SELECT GLength(GeomFromText(@mls));</code></strong>
+-----------------------------+
| GLength(GeomFromText(@mls)) |
+-----------------------------+
| 4.2426406871193 |
+-----------------------------+
</pre><p>
<code class="literal">GLength()</code> is a non-standard name. It
corresponds to the OpenGIS <code class="literal">Length()</code>
function.
</p></li><li><p>
<a class="indexterm" name="id2993798"></a>
<code class="literal">IsClosed(<em class="replaceable"><code>mls</code></em>)</code>
</p><p>
Returns 1 if the <code class="literal">MultiLineString</code> value
<em class="replaceable"><code>mls</code></em> is closed (that is, the
<code class="literal">StartPoint()</code> and
<code class="literal">EndPoint()</code> values are the same for each
<code class="literal">LineString</code> in
<em class="replaceable"><code>mls</code></em>). Returns 0 if
<em class="replaceable"><code>mls</code></em> is not closed, and –1
if it is <code class="literal">NULL</code>.
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET @mls = 'MultiLineString((1 1,2 2,3 3),(4 4,5 5))';</code></strong>
mysql> <strong class="userinput"><code>SELECT IsClosed(GeomFromText(@mls));</code></strong>
+------------------------------+
| IsClosed(GeomFromText(@mls)) |
+------------------------------+
| 0 |
+------------------------------+
</pre></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="polygon-property-functions"></a>17.5.2.5. <code class="literal">Polygon</code> Functions</h4></div></div></div><div class="itemizedlist"><ul type="disc"><li><p><a name="function_area"></a>
<a class="indexterm" name="id2993937"></a>
<code class="literal">Area(<em class="replaceable"><code>poly</code></em>)</code>
</p><p>
Returns as a double-precision number the area of the
<code class="literal">Polygon</code> value
<em class="replaceable"><code>poly</code></em>, as measured in its
spatial reference system.
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET @poly = 'Polygon((0 0,0 3,3 0,0 0),(1 1,1 2,2 1,1 1))';</code></strong>
mysql> <strong class="userinput"><code>SELECT Area(GeomFromText(@poly));</code></strong>
+---------------------------+
| Area(GeomFromText(@poly)) |
+---------------------------+
| 4 |
+---------------------------+
</pre></li><li><p><a name="function_exteriorring"></a>
<a class="indexterm" name="id2994022"></a>
<code class="literal">ExteriorRing(<em class="replaceable"><code>poly</code></em>)</code>
</p><p>
Returns the exterior ring of the
<code class="literal">Polygon</code> value
<em class="replaceable"><code>poly</code></em> as a
<code class="literal">LineString</code>.
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET @poly =</code></strong>
-> <strong class="userinput"><code>'Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))';</code></strong>
mysql> <strong class="userinput"><code>SELECT AsText(ExteriorRing(GeomFromText(@poly)));</code></strong>
+-------------------------------------------+
| AsText(ExteriorRing(GeomFromText(@poly))) |
+-------------------------------------------+
| LINESTRING(0 0,0 3,3 3,3 0,0 0) |
+-------------------------------------------+
</pre></li><li><p><a name="function_interiorringn"></a>
<a class="indexterm" name="id2994121"></a>
<code class="literal">InteriorRingN(<em class="replaceable"><code>poly</code></em>,<em class="replaceable"><code>N</code></em>)</code>
</p><p>
Returns the <em class="replaceable"><code>N</code></em>-th interior ring
for the <code class="literal">Polygon</code> value
<em class="replaceable"><code>poly</code></em> as a
<code class="literal">LineString</code>. Rings are numbered
beginning with 1.
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET @poly =</code></strong>
-> <strong class="userinput"><code>'Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))';</code></strong>
mysql> <strong class="userinput"><code>SELECT AsText(InteriorRingN(GeomFromText(@poly),1));</code></strong>
+----------------------------------------------+
| AsText(InteriorRingN(GeomFromText(@poly),1)) |
+----------------------------------------------+
| LINESTRING(1 1,1 2,2 2,2 1,1 1) |
+----------------------------------------------+
</pre></li><li><p><a name="function_numinteriorrings"></a>
<a class="indexterm" name="id2994227"></a>
<code class="literal">NumInteriorRings(<em class="replaceable"><code>poly</code></em>)</code>
</p><p>
Returns the number of interior rings in the
<code class="literal">Polygon</code> value
<em class="replaceable"><code>poly</code></em>.
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET @poly =</code></strong>
-> <strong class="userinput"><code>'Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))';</code></strong>
mysql> <strong class="userinput"><code>SELECT NumInteriorRings(GeomFromText(@poly));</code></strong>
+---------------------------------------+
| NumInteriorRings(GeomFromText(@poly)) |
+---------------------------------------+
| 1 |
+---------------------------------------+
</pre></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="multipolygon-property-functions"></a>17.5.2.6. <code class="literal">MultiPolygon</code> Functions</h4></div></div></div><div class="itemizedlist"><ul type="disc"><li><p>
<a class="indexterm" name="id2994328"></a>
<code class="literal">Area(<em class="replaceable"><code>mpoly</code></em>)</code>
</p><p>
Returns as a double-precision number the area of the
<code class="literal">MultiPolygon</code> value
<em class="replaceable"><code>mpoly</code></em>, as measured in its
spatial reference system.
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET @mpoly =</code></strong>
-> <strong class="userinput"><code>'MultiPolygon(((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1)))';</code></strong>
mysql> <strong class="userinput"><code>SELECT Area(GeomFromText(@mpoly));</code></strong>
+----------------------------+
| Area(GeomFromText(@mpoly)) |
+----------------------------+
| 8 |
+----------------------------+
</pre></li></ul></div><p>
The OpenGIS specification also defines the following
functions, which MySQL does not implement:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<a class="indexterm" name="id2994406"></a>
<code class="literal">Centroid(<em class="replaceable"><code>mpoly</code></em>)</code>
</p><p>
Returns the mathematical centroid for the
<code class="literal">MultiPolygon</code> value
<em class="replaceable"><code>mpoly</code></em> as a
<code class="literal">Point</code>. The result is not guaranteed to
be on the <code class="literal">MultiPolygon</code>.
</p></li><li><p>
<a class="indexterm" name="id2994458"></a>
<code class="literal">PointOnSurface(<em class="replaceable"><code>mpoly</code></em>)</code>
</p><p>
Returns a <code class="literal">Point</code> value that is
guaranteed to be on the <code class="literal">MultiPolygon</code>
value <em class="replaceable"><code>mpoly</code></em>.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="geometrycollection-property-functions"></a>17.5.2.7. <code class="literal">GeometryCollection</code> Functions</h4></div></div></div><div class="itemizedlist"><ul type="disc"><li><p><a name="function_geometryn"></a>
<a class="indexterm" name="id2994549"></a>
<code class="literal">GeometryN(<em class="replaceable"><code>gc</code></em>,<em class="replaceable"><code>N</code></em>)</code>
</p><p>
Returns the <em class="replaceable"><code>N</code></em>-th geometry in
the <code class="literal">GeometryCollection</code> value
<em class="replaceable"><code>gc</code></em>. Geometries are numbered
beginning with 1.
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET @gc = 'GeometryCollection(Point(1 1),LineString(2 2, 3 3))';</code></strong>
mysql> <strong class="userinput"><code>SELECT AsText(GeometryN(GeomFromText(@gc),1));</code></strong>
+----------------------------------------+
| AsText(GeometryN(GeomFromText(@gc),1)) |
+----------------------------------------+
| POINT(1 1) |
+----------------------------------------+
</pre></li><li><p><a name="function_numgeometries"></a>
<a class="indexterm" name="id2994641"></a>
<code class="literal">NumGeometries(<em class="replaceable"><code>gc</code></em>)</code>
</p><p>
Returns the number of geometries in the
<code class="literal">GeometryCollection</code> value
<em class="replaceable"><code>gc</code></em>.
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET @gc = 'GeometryCollection(Point(1 1),LineString(2 2, 3 3))';</code></strong>
mysql> <strong class="userinput"><code>SELECT NumGeometries(GeomFromText(@gc));</code></strong>
+----------------------------------+
| NumGeometries(GeomFromText(@gc)) |
+----------------------------------+
| 2 |
+----------------------------------+
</pre></li></ul></div></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="functions-that-create-new-geometries-from-existing-ones"></a>17.5.3. Functions That Create New Geometries from Existing Ones</h3></div></div></div><div class="toc"><dl><dt><span class="section"><a href="spatial-extensions.html#functions-that-produce-new-geometries">17.5.3.1. Geometry Functions That Produce New Geometries</a></span></dt><dt><span class="section"><a href="spatial-extensions.html#spatial-operators">17.5.3.2. Spatial Operators</a></span></dt></dl></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="functions-that-produce-new-geometries"></a>17.5.3.1. Geometry Functions That Produce New Geometries</h4></div></div></div><p>
<a href="spatial-extensions.html#geometry-property-functions" title="17.5.2. Geometry Functions">Section 17.5.2, “<code class="literal">Geometry</code> Functions”</a>, discusses
several functions that construct new geometries from existing
ones. See that section for descriptions of these functions:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal">Envelope(<em class="replaceable"><code>g</code></em>)</code>
</p></li><li><p>
<code class="literal">StartPoint(<em class="replaceable"><code>ls</code></em>)</code>
</p></li><li><p>
<code class="literal">EndPoint(<em class="replaceable"><code>ls</code></em>)</code>
</p></li><li><p>
<code class="literal">PointN(<em class="replaceable"><code>ls</code></em>,<em class="replaceable"><code>N</code></em>)</code>
</p></li><li><p>
<code class="literal">ExteriorRing(<em class="replaceable"><code>poly</code></em>)</code>
</p></li><li><p>
<code class="literal">InteriorRingN(<em class="replaceable"><code>poly</code></em>,<em class="replaceable"><code>N</code></em>)</code>
</p></li><li><p>
<code class="literal">GeometryN(<em class="replaceable"><code>gc</code></em>,<em class="replaceable"><code>N</code></em>)</code>
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="spatial-operators"></a>17.5.3.2. Spatial Operators</h4></div></div></div><p>
OpenGIS proposes a number of other functions that can produce
geometries. They are designed to implement spatial operators.
</p><p>
These functions are not implemented in MySQL. They may appear
in future releases.
</p><div class="itemizedlist"><ul type="disc"><li><p>
<a class="indexterm" name="id2994902"></a>
<code class="literal">Buffer(<em class="replaceable"><code>g</code></em>,<em class="replaceable"><code>d</code></em>)</code>
</p><p>
Returns a geometry that represents all points whose
distance from the geometry value
<em class="replaceable"><code>g</code></em> is less than or equal to a
distance of <em class="replaceable"><code>d</code></em>.
</p></li><li><p>
<a class="indexterm" name="id2994950"></a>
<code class="literal">ConvexHull(<em class="replaceable"><code>g</code></em>)</code>
</p><p>
Returns a geometry that represents the convex hull of the
geometry value <em class="replaceable"><code>g</code></em>.
</p></li><li><p>
<a class="indexterm" name="id2994991"></a>
<code class="literal">Difference(<em class="replaceable"><code>g1</code></em>,<em class="replaceable"><code>g2</code></em>)</code>
</p><p>
Returns a geometry that represents the point set
difference of the geometry value
<em class="replaceable"><code>g1</code></em> with
<em class="replaceable"><code>g2</code></em>.
</p></li><li><p>
<a class="indexterm" name="id2995038"></a>
<code class="literal">Intersection(<em class="replaceable"><code>g1</code></em>,<em class="replaceable"><code>g2</code></em>)</code>
</p><p>
Returns a geometry that represents the point set
intersection of the geometry values
<em class="replaceable"><code>g1</code></em> with
<em class="replaceable"><code>g2</code></em>.
</p></li><li><p>
<a class="indexterm" name="id2995087"></a>
<code class="literal">SymDifference(<em class="replaceable"><code>g1</code></em>,<em class="replaceable"><code>g2</code></em>)</code>
</p><p>
Returns a geometry that represents the point set symmetric
difference of the geometry value
<em class="replaceable"><code>g1</code></em> with
<em class="replaceable"><code>g2</code></em>.
</p></li><li><p>
<a class="indexterm" name="id2995135"></a>
<code class="literal">Union(<em class="replaceable"><code>g1</code></em>,<em class="replaceable"><code>g2</code></em>)</code>
</p><p>
Returns a geometry that represents the point set union of
the geometry values <em class="replaceable"><code>g1</code></em> and
<em class="replaceable"><code>g2</code></em>.
</p></li></ul></div></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="functions-for-testing-spatial-relations-between-geometric-objects"></a>17.5.4. Functions for Testing Spatial Relations Between Geometric Objects</h3></div></div></div><p>
The functions described in these sections take two geometries as
input parameters and return a qualitative or quantitative
relation between them.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="relations-on-geometry-mbr"></a>17.5.5. Relations on Geometry Minimal Bounding Rectangles (MBRs)</h3></div></div></div><a class="indexterm" name="id2995199"></a><a class="indexterm" name="id2995208"></a><p>
MySQL provides several functions that test relations between
minimal bounding rectangles of two geometries
<code class="literal">g1</code> and <code class="literal">g2</code>. The return
values 1 and 0 indicate true and false, respectively.
</p><div class="itemizedlist"><ul type="disc"><li><p><a name="function_mbrcontains"></a>
<a class="indexterm" name="id2995263"></a>
<code class="literal">MBRContains(<em class="replaceable"><code>g1</code></em>,<em class="replaceable"><code>g2</code></em>)</code>
</p><p>
Returns 1 or 0 to indicate whether the Minimum Bounding
Rectangle of <em class="replaceable"><code>g1</code></em> contains the
Minimum Bounding Rectangle of <em class="replaceable"><code>g2</code></em>.
This tests the opposite relationship as
<code class="literal">MBRWithin()</code>.
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))');</code></strong>
mysql> <strong class="userinput"><code>SET @g2 = GeomFromText('Point(1 1)');</code></strong>
mysql> <strong class="userinput"><code>SELECT MBRContains(@g1,@g2), MBRContains(@g2,@g1);</code></strong>
----------------------+----------------------+
| MBRContains(@g1,@g2) | MBRContains(@g2,@g1) |
+----------------------+----------------------+
| 1 | 0 |
+----------------------+----------------------+
</pre></li><li><p><a name="function_mbrdisjoint"></a>
<a class="indexterm" name="id2995364"></a>
<code class="literal">MBRDisjoint(<em class="replaceable"><code>g1</code></em>,<em class="replaceable"><code>g2</code></em>)</code>
</p><p>
Returns 1 or 0 to indicate whether the Minimum Bounding
Rectangles of the two geometries
<em class="replaceable"><code>g1</code></em> and
<em class="replaceable"><code>g2</code></em> are disjoint (do not
intersect).
</p></li><li><p><a name="function_mbrequal"></a>
<a class="indexterm" name="id2995423"></a>
<code class="literal">MBREqual(<em class="replaceable"><code>g1</code></em>,<em class="replaceable"><code>g2</code></em>)</code>
</p><p>
Returns 1 or 0 to indicate whether the Minimum Bounding
Rectangles of the two geometries
<em class="replaceable"><code>g1</code></em> and
<em class="replaceable"><code>g2</code></em> are the same.
</p></li><li><p><a name="function_mbrintersects"></a>
<a class="indexterm" name="id2995482"></a>
<code class="literal">MBRIntersects(<em class="replaceable"><code>g1</code></em>,<em class="replaceable"><code>g2</code></em>)</code>
</p><p>
Returns 1 or 0 to indicate whether the Minimum Bounding
Rectangles of the two geometries
<em class="replaceable"><code>g1</code></em> and
<em class="replaceable"><code>g2</code></em> intersect.
</p></li><li><p><a name="function_mbroverlaps"></a>
<a class="indexterm" name="id2995541"></a>
<code class="literal">MBROverlaps(<em class="replaceable"><code>g1</code></em>,<em class="replaceable"><code>g2</code></em>)</code>
</p><p>
Returns 1 or 0 to indicate whether the Minimum Bounding
Rectangles of the two geometries
<em class="replaceable"><code>g1</code></em> and
<em class="replaceable"><code>g2</code></em> overlap. The term
<span class="emphasis"><em>spatially overlaps</em></span> 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.
</p></li><li><p><a name="function_mbrtouches"></a>
<a class="indexterm" name="id2995607"></a>
<code class="literal">MBRTouches(<em class="replaceable"><code>g1</code></em>,<em class="replaceable"><code>g2</code></em>)</code>
</p><p>
Returns 1 or 0 to indicate whether the Minimum Bounding
Rectangles of the two geometries
<em class="replaceable"><code>g1</code></em> and
<em class="replaceable"><code>g2</code></em> touch. Two geometries
<span class="emphasis"><em>spatially touch</em></span> 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.
</p></li><li><p><a name="function_mbrwithin"></a>
<a class="indexterm" name="id2995673"></a>
<code class="literal">MBRWithin(<em class="replaceable"><code>g1</code></em>,<em class="replaceable"><code>g2</code></em>)</code>
</p><p>
Returns 1 or 0 to indicate whether the Minimum Bounding
Rectangle of <em class="replaceable"><code>g1</code></em> is within the
Minimum Bounding Rectangle of <em class="replaceable"><code>g2</code></em>.
This tests the opposite relationship as
<code class="literal">MBRContains()</code>.
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))');</code></strong>
mysql> <strong class="userinput"><code>SET @g2 = GeomFromText('Polygon((0 0,0 5,5 5,5 0,0 0))');</code></strong>
mysql> <strong class="userinput"><code>SELECT MBRWithin(@g1,@g2), MBRWithin(@g2,@g1);</code></strong>
+--------------------+--------------------+
| MBRWithin(@g1,@g2) | MBRWithin(@g2,@g1) |
+--------------------+--------------------+
| 1 | 0 |
+--------------------+--------------------+
</pre></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="functions-that-test-spatial-relationships-between-geometries"></a>17.5.6. Functions That Test Spatial Relationships Between Geometries</h3></div></div></div><p>
The OpenGIS specification defines the following functions. They
test the relationship between two geometry values
<code class="literal">g1</code> and <code class="literal">g2</code>.
</p><p>
The return values 1 and 0 indicate true and false, respectively.
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
Currently, MySQL does not implement these functions 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
<code class="literal">Distance()</code> and
<code class="literal">Related()</code>.
</p><p>
These functions may be implemented in future releases with
full support for spatial analysis, not just MBR-based support.
</p></div><div class="itemizedlist"><ul type="disc"><li><p><a name="function_contains"></a>
<a class="indexterm" name="id2995853"></a>
<code class="literal">Contains(<em class="replaceable"><code>g1</code></em>,<em class="replaceable"><code>g2</code></em>)</code>
</p><p>
Returns 1 or 0 to indicate whether
<em class="replaceable"><code>g1</code></em> completely contains
<em class="replaceable"><code>g2</code></em>. This tests the opposite
relationship as <code class="literal">Within()</code>.
</p></li><li><p><a name="function_crosses"></a>
<a class="indexterm" name="id2995918"></a>
<code class="literal">Crosses(<em class="replaceable"><code>g1</code></em>,<em class="replaceable"><code>g2</code></em>)</code>
</p><p>
Returns 1 if <em class="replaceable"><code>g1</code></em> spatially crosses
<em class="replaceable"><code>g2</code></em>. Returns
<code class="literal">NULL</code> if <code class="literal">g1</code> is a
<code class="literal">Polygon</code> or a
<code class="literal">MultiPolygon</code>, or if
<em class="replaceable"><code>g2</code></em> is a <code class="literal">Point</code>
or a <code class="literal">MultiPoint</code>. Otherwise, returns 0.
</p><p>
The term <span class="emphasis"><em>spatially crosses</em></span> denotes a
spatial relation between two given geometries that has the
following properties:
</p><div class="itemizedlist"><ul type="circle"><li><p>
The two geometries intersect
</p></li><li><p>
Their intersection results in a geometry that has a
dimension that is one less than the maximum dimension of
the two given geometries
</p></li><li><p>
Their intersection is not equal to either of the two
given geometries
</p></li></ul></div></li><li><p><a name="function_disjoint"></a>
<a class="indexterm" name="id2996054"></a>
<code class="literal">Disjoint(<em class="replaceable"><code>g1</code></em>,<em class="replaceable"><code>g2</code></em>)</code>
</p><p>
Returns 1 or 0 to indicate whether
<em class="replaceable"><code>g1</code></em> is spatially disjoint from
(does not intersect) <em class="replaceable"><code>g2</code></em>.
</p></li><li><p>
<a class="indexterm" name="id2996106"></a>
<code class="literal">Distance(<em class="replaceable"><code>g1</code></em>,<em class="replaceable"><code>g2</code></em>)</code>
</p><p>
Returns as a double-precision number the shortest distance
between any two points in the two geometries.
</p></li><li><p><a name="function_equals"></a>
<a class="indexterm" name="id2996153"></a>
<code class="literal">Equals(<em class="replaceable"><code>g1</code></em>,<em class="replaceable"><code>g2</code></em>)</code>
</p><p>
Returns 1 or 0 to indicate whether
<em class="replaceable"><code>g1</code></em> is spatially equal to
<em class="replaceable"><code>g2</code></em>.
</p></li><li><p><a name="function_intersects"></a>
<a class="indexterm" name="id2996211"></a>
<code class="literal">Intersects(<em class="replaceable"><code>g1</code></em>,<em class="replaceable"><code>g2</code></em>)</code>
</p><p>
Returns 1 or 0 to indicate whether
<em class="replaceable"><code>g1</code></em> spatially intersects
<em class="replaceable"><code>g2</code></em>.
</p></li><li><p><a name="function_overlaps"></a>
<a class="indexterm" name="id2996268"></a>
<code class="literal">Overlaps(<em class="replaceable"><code>g1</code></em>,<em class="replaceable"><code>g2</code></em>)</code>
</p><p>
Returns 1 or 0 to indicate whether
<em class="replaceable"><code>g1</code></em> spatially overlaps
<em class="replaceable"><code>g2</code></em>. The term <span class="emphasis"><em>spatially
overlaps</em></span> 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.
</p></li><li><p>
<a class="indexterm" name="id2996327"></a>
<code class="literal">Related(<em class="replaceable"><code>g1</code></em>,<em class="replaceable"><code>g2</code></em>,<em class="replaceable"><code>pattern_matrix</code></em>)</code>
</p><p>
Returns 1 or 0 to indicate whether the spatial relationship
specified by <em class="replaceable"><code>pattern_matrix</code></em>
exists between <em class="replaceable"><code>g1</code></em> and
<em class="replaceable"><code>g2</code></em>. Returns –1 if the
arguments are <code class="literal">NULL</code>. The pattern matrix is
a string. Its specification will be noted here if this
function is implemented.
</p></li><li><p><a name="function_touches"></a>
<a class="indexterm" name="id2996397"></a>
<code class="literal">Touches(<em class="replaceable"><code>g1</code></em>,<em class="replaceable"><code>g2</code></em>)</code>
</p><p>
Returns 1 or 0 to indicate whether
<em class="replaceable"><code>g1</code></em> spatially touches
<em class="replaceable"><code>g2</code></em>. Two geometries
<span class="emphasis"><em>spatially touch</em></span> 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.
</p></li><li><p><a name="function_within"></a>
<a class="indexterm" name="id2996462"></a>
<code class="literal">Within(<em class="replaceable"><code>g1</code></em>,<em class="replaceable"><code>g2</code></em>)</code>
</p><p>
Returns 1 or 0 to indicate whether
<em class="replaceable"><code>g1</code></em> is spatially within
<em class="replaceable"><code>g2</code></em>. This tests the opposite
relationship as <code class="literal">Contains()</code>.
</p></li></ul></div></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="optimizing-spatial-analysis"></a>17.6. Optimizing Spatial Analysis</h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="spatial-extensions.html#creating-spatial-indexes">17.6.1. Creating Spatial Indexes</a></span></dt><dt><span class="section"><a href="spatial-extensions.html#using-a-spatial-index">17.6.2. Using a Spatial Index</a></span></dt></dl></div><p>
Search operations in non-spatial databases can be optimized using
<code class="literal">SPATIAL</code> indexes. This is true for spatial
databases as well. With the help of a great variety of
multi-dimensional indexing methods that have previously been
designed, it is possible to optimize spatial searches. The most
typical of these are:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Point queries that search for all objects that contain a given
point
</p></li><li><p>
Region queries that search for all objects that overlap a
given region
</p></li></ul></div><p>
MySQL uses <span class="bold"><strong>R-Trees with quadratic
splitting</strong></span> for <code class="literal">SPATIAL</code> indexes on
spatial columns. A <code class="literal">SPATIAL</code> 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.
</p><p>
It is also possible to create normal indexes on spatial columns.
In a non-<code class="literal">SPATIAL</code> index, you must declare a
prefix for any spatial column except for <code class="literal">POINT</code>
columns.
</p><p>
<code class="literal">MyISAM</code> supports both <code class="literal">SPATIAL</code>
and non-<code class="literal">SPATIAL</code> indexes. Other storage engines
support non-<code class="literal">SPATIAL</code> indexes, as described in
<a href="sql-syntax.html#create-index" title="12.1.4. CREATE INDEX Syntax">Section 12.1.4, “<code class="literal">CREATE INDEX</code> Syntax”</a>.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="creating-spatial-indexes"></a>17.6.1. Creating Spatial Indexes</h3></div></div></div><p>
MySQL can create spatial indexes using syntax similar to that
for creating regular indexes, but extended with the
<code class="literal">SPATIAL</code> keyword. Currently, columns in
spatial indexes must be declared <code class="literal">NOT NULL</code>.
The following examples demonstrate how to create spatial
indexes:
</p><div class="itemizedlist"><ul type="disc"><li><p>
With <code class="literal">CREATE TABLE</code>:
</p><pre class="programlisting">CREATE TABLE geom (g GEOMETRY NOT NULL, SPATIAL INDEX(g));
</pre></li><li><p>
With <code class="literal">ALTER TABLE</code>:
</p><pre class="programlisting">ALTER TABLE geom ADD SPATIAL INDEX(g);
</pre></li><li><p>
With <code class="literal">CREATE INDEX</code>:
</p><pre class="programlisting">CREATE SPATIAL INDEX sp_index ON geom (g);
</pre></li></ul></div><p>
For <code class="literal">MyISAM</code> tables, <code class="literal">SPATIAL
INDEX</code> creates an R-tree index. For storage engines
that support non-spatial indexing of spatial columns, the engine
creates a B-tree index. A B-tree index on spatial values will be
useful for exact-value lookups, but not for range scans.
</p><p>
For more information on indexing spatial columns, see
<a href="sql-syntax.html#create-index" title="12.1.4. CREATE INDEX Syntax">Section 12.1.4, “<code class="literal">CREATE INDEX</code> Syntax”</a>.
</p><p>
To drop spatial indexes, use <code class="literal">ALTER TABLE</code> or
<code class="literal">DROP INDEX</code>:
</p><div class="itemizedlist"><ul type="disc"><li><p>
With <code class="literal">ALTER TABLE</code>:
</p><pre class="programlisting">ALTER TABLE geom DROP INDEX g;
</pre></li><li><p>
With <code class="literal">DROP INDEX</code>:
</p><pre class="programlisting">DROP INDEX sp_index ON geom;
</pre></li></ul></div><p>
Example: Suppose that a table <code class="literal">geom</code> contains
more than 32,000 geometries, which are stored in the column
<code class="literal">g</code> of type <code class="literal">GEOMETRY</code>. The
table also has an <code class="literal">AUTO_INCREMENT</code> column
<code class="literal">fid</code> for storing object ID values.
</p><pre class="programlisting">mysql> <strong class="userinput"><code>DESCRIBE geom;</code></strong>
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| fid | int(11) | | PRI | NULL | auto_increment |
| g | geometry | | | | |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> <strong class="userinput"><code>SELECT COUNT(*) FROM geom;</code></strong>
+----------+
| count(*) |
+----------+
| 32376 |
+----------+
1 row in set (0.00 sec)
</pre><p>
To add a spatial index on the column <code class="literal">g</code>, use
this statement:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>ALTER TABLE geom ADD SPATIAL INDEX(g);</code></strong>
Query OK, 32376 rows affected (4.05 sec)
Records: 32376 Duplicates: 0 Warnings: 0
</pre></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="using-a-spatial-index"></a>17.6.2. Using a Spatial Index</h3></div></div></div><p>
The optimizer investigates whether available spatial indexes can
be involved in the search for queries that use a function such
as <code class="literal">MBRContains()</code> or
<code class="literal">MBRWithin()</code> in the <code class="literal">WHERE</code>
clause. The following query finds all objects that are in the
given rectangle:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET @poly =</code></strong>
-> <strong class="userinput"><code>'Polygon((30000 15000,31000 15000,31000 16000,30000 16000,30000 15000))';</code></strong>
mysql> <strong class="userinput"><code>SELECT fid,AsText(g) FROM geom WHERE</code></strong>
-> <strong class="userinput"><code>MBRContains(GeomFromText(@poly),g);</code></strong>
+-----+---------------------------------------------------------------+
| fid | AsText(g) |
+-----+---------------------------------------------------------------+
| 21 | LINESTRING(30350.4 15828.8,30350.6 15845,30333.8 15845,30 ... |
| 22 | LINESTRING(30350.6 15871.4,30350.6 15887.8,30334 15887.8, ... |
| 23 | LINESTRING(30350.6 15914.2,30350.6 15930.4,30334 15930.4, ... |
| 24 | LINESTRING(30290.2 15823,30290.2 15839.4,30273.4 15839.4, ... |
| 25 | LINESTRING(30291.4 15866.2,30291.6 15882.4,30274.8 15882. ... |
| 26 | LINESTRING(30291.6 15918.2,30291.6 15934.4,30275 15934.4, ... |
| 249 | LINESTRING(30337.8 15938.6,30337.8 15946.8,30320.4 15946. ... |
| 1 | LINESTRING(30250.4 15129.2,30248.8 15138.4,30238.2 15136. ... |
| 2 | LINESTRING(30220.2 15122.8,30217.2 15137.8,30207.6 15136, ... |
| 3 | LINESTRING(30179 15114.4,30176.6 15129.4,30167 15128,3016 ... |
| 4 | LINESTRING(30155.2 15121.4,30140.4 15118.6,30142 15109,30 ... |
| 5 | LINESTRING(30192.4 15085,30177.6 15082.2,30179.2 15072.4, ... |
| 6 | LINESTRING(30244 15087,30229 15086.2,30229.4 15076.4,3024 ... |
| 7 | LINESTRING(30200.6 15059.4,30185.6 15058.6,30186 15048.8, ... |
| 10 | LINESTRING(30179.6 15017.8,30181 15002.8,30190.8 15003.6, ... |
| 11 | LINESTRING(30154.2 15000.4,30168.6 15004.8,30166 15014.2, ... |
| 13 | LINESTRING(30105 15065.8,30108.4 15050.8,30118 15053,3011 ... |
| 154 | LINESTRING(30276.2 15143.8,30261.4 15141,30263 15131.4,30 ... |
| 155 | LINESTRING(30269.8 15084,30269.4 15093.4,30258.6 15093,30 ... |
| 157 | LINESTRING(30128.2 15011,30113.2 15010.2,30113.6 15000.4, ... |
+-----+---------------------------------------------------------------+
20 rows in set (0.00 sec)
</pre><p>
Use <code class="literal">EXPLAIN</code> to check the way this query is
executed:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET @poly =</code></strong>
-> <strong class="userinput"><code>'Polygon((30000 15000,31000 15000,31000 16000,30000 16000,30000 15000))';</code></strong>
mysql> <strong class="userinput"><code>EXPLAIN SELECT fid,AsText(g) FROM geom WHERE</code></strong>
-> <strong class="userinput"><code>MBRContains(GeomFromText(@poly),g)\G</code></strong>
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: geom
type: range
possible_keys: g
key: g
key_len: 32
ref: NULL
rows: 50
Extra: Using where
1 row in set (0.00 sec)
</pre><p>
Check what would happen without a spatial index:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET @poly =</code></strong>
-> <strong class="userinput"><code>'Polygon((30000 15000,31000 15000,31000 16000,30000 16000,30000 15000))';</code></strong>
mysql> <strong class="userinput"><code>EXPLAIN SELECT fid,AsText(g) FROM g IGNORE INDEX (g) WHERE</code></strong>
-> <strong class="userinput"><code>MBRContains(GeomFromText(@poly),g)\G</code></strong>
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: geom
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 32376
Extra: Using where
1 row in set (0.00 sec)
</pre><p>
Executing the <code class="literal">SELECT</code> statement without the
spatial index yields the same result but causes the execution
time to rise from 0.00 seconds to 0.46 seconds:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SET @poly =</code></strong>
-> <strong class="userinput"><code>'Polygon((30000 15000,31000 15000,31000 16000,30000 16000,30000 15000))';</code></strong>
mysql> <strong class="userinput"><code>SELECT fid,AsText(g) FROM geom IGNORE INDEX (g) WHERE</code></strong>
-> <strong class="userinput"><code>MBRContains(GeomFromText(@poly),g);</code></strong>
+-----+---------------------------------------------------------------+
| fid | AsText(g) |
+-----+---------------------------------------------------------------+
| 1 | LINESTRING(30250.4 15129.2,30248.8 15138.4,30238.2 15136. ... |
| 2 | LINESTRING(30220.2 15122.8,30217.2 15137.8,30207.6 15136, ... |
| 3 | LINESTRING(30179 15114.4,30176.6 15129.4,30167 15128,3016 ... |
| 4 | LINESTRING(30155.2 15121.4,30140.4 15118.6,30142 15109,30 ... |
| 5 | LINESTRING(30192.4 15085,30177.6 15082.2,30179.2 15072.4, ... |
| 6 | LINESTRING(30244 15087,30229 15086.2,30229.4 15076.4,3024 ... |
| 7 | LINESTRING(30200.6 15059.4,30185.6 15058.6,30186 15048.8, ... |
| 10 | LINESTRING(30179.6 15017.8,30181 15002.8,30190.8 15003.6, ... |
| 11 | LINESTRING(30154.2 15000.4,30168.6 15004.8,30166 15014.2, ... |
| 13 | LINESTRING(30105 15065.8,30108.4 15050.8,30118 15053,3011 ... |
| 21 | LINESTRING(30350.4 15828.8,30350.6 15845,30333.8 15845,30 ... |
| 22 | LINESTRING(30350.6 15871.4,30350.6 15887.8,30334 15887.8, ... |
| 23 | LINESTRING(30350.6 15914.2,30350.6 15930.4,30334 15930.4, ... |
| 24 | LINESTRING(30290.2 15823,30290.2 15839.4,30273.4 15839.4, ... |
| 25 | LINESTRING(30291.4 15866.2,30291.6 15882.4,30274.8 15882. ... |
| 26 | LINESTRING(30291.6 15918.2,30291.6 15934.4,30275 15934.4, ... |
| 154 | LINESTRING(30276.2 15143.8,30261.4 15141,30263 15131.4,30 ... |
| 155 | LINESTRING(30269.8 15084,30269.4 15093.4,30258.6 15093,30 ... |
| 157 | LINESTRING(30128.2 15011,30113.2 15010.2,30113.6 15000.4, ... |
| 249 | LINESTRING(30337.8 15938.6,30337.8 15946.8,30320.4 15946. ... |
+-----+---------------------------------------------------------------+
20 rows in set (0.46 sec)
</pre><p>
In future releases, spatial indexes may also be used for
optimizing other functions. See
<a href="spatial-extensions.html#functions-for-testing-spatial-relations-between-geometric-objects" title="17.5.4. Functions for Testing Spatial Relations Between Geometric Objects">Section 17.5.4, “Functions for Testing Spatial Relations Between Geometric Objects”</a>.
</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="mysql-gis-conformance-and-compatibility"></a>17.7. MySQL Conformance and Compatibility</h2></div></div></div><p>
MySQL does not yet implement the following GIS features:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Additional Metadata Views
</p><p>
OpenGIS specifications propose several additional metadata
views. For example, a system view named
<code class="literal">GEOMETRY_COLUMNS</code> contains a description of
geometry columns, one row for each geometry column in the
database.
</p></li><li><p>
The OpenGIS function <code class="literal">Length()</code> on
<code class="literal">LineString</code> and
<code class="literal">MultiLineString</code> currently should be called
in MySQL as <code class="literal">GLength()</code>
</p><p>
The problem is that there is an existing SQL function
<code class="literal">Length()</code> that 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.
</p></li></ul></div></div></div><div class="navfooter"><hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="mysql-cluster.html">Prev</a> </td><td width="20%" align="center"> </td><td width="40%" align="right"> <a accesskey="n" href="stored-procedures.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 16. MySQL Cluster </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> Chapter 18. Stored Procedures and Functions</td></tr></table></div></body></html>
distrib
>
Mandriva
>
2008.1
>
x86_64
>
media
>
main-testing
>
by-pkgid
>
b1e2421f2416edfc24c5845fbc1c5a2e
>
files
>
111
