| 12.17. Spatial Extensions MySQL supports spatial extensions to enable the generation, storage, and analysis of geographic features. These features are available for MyISAM, InnoDB, NDB, and ARCHIVE tables. For spatial columns, MyISAM supports both SPATIAL and non-SPATIAL indexes. Other storage engines support non-SPATIAL indexes, as described in Section 13.1.13, "CREATE INDEX Syntax". This chapter covers the following topics: The basis of these spatial extensions in the OpenGIS geometry model Data formats for representing spatial data How to use spatial data in MySQL Use of indexing for spatial data MySQL differences from the OpenGIS specification
Additional Resources The Open Geospatial Consortium publishes the OpenGIS� Simple Features Specifications For SQL, a document that proposes several conceptual ways for extending an SQL RDBMS to support spatial data. This specification is available from the OGC Web site at http://www.opengis.org/docs/99-049.pdf. If you have questions or concerns about the use of the spatial extensions to MySQL, you can discuss them in the GIS forum: http://forums.mysql.com/list.php?23.
12.17.1. Introduction to MySQL Spatial Support 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 http://www.opengis.org/. In 1997, the Open Geospatial Consortium published the OpenGIS� Simple Features Specifications For SQL, a document that proposes several conceptual ways for extending an SQL RDBMS to support spatial data. This specification is available from the OGC Web site at http://www.opengis.org/docs/99-049.pdf. It contains additional information relevant to this chapter. MySQL implements a subset of the SQL with Geometry Types environment proposed by OGC. This term refers to an SQL environment that has been extended with a set of geometry types. A geometry-valued SQL column is implemented as a column that has a geometry type. The specification describe a set of SQL geometry types, as well as functions on those types to create and analyze geometry values. A geographic feature is anything in the world that has a location. A feature can be: An entity. For example, a mountain, a pond, a city. A space. For example, town district, the tropics. A definable location. For example, a crossroad, as a particular place where two streets intersect.
Some documents use the term geospatial feature to refer to geographic features. Geometry is another word that denotes a geographic feature. Originally the word geometry meant measurement of the earth. Another meaning comes from cartography, referring to the geometric features that cartographers use to map the world. This chapter uses all of these terms synonymously: geographic feature, geospatial feature, feature, or geometry. Here, the term most commonly used is geometry, defined as a point or an aggregate of points representing anything in the world that has a location. 12.17.2. The OpenGIS Geometry Model The set of geometry types proposed by OGC's SQL with Geometry Types environment is based on the OpenGIS Geometry Model. In this model, each geometric object has the following general properties: It is associated with a Spatial Reference System, which describes the coordinate space in which the object is defined. It belongs to some geometry class.
12.17.2.1. The Geometry Class Hierarchy The geometry classes define a hierarchy as follows: It is not possible to create objects in noninstantiable classes. It is possible to create objects in instantiable classes. All classes have properties, and instantiable classes may also have assertions (rules that define valid class instances). Geometry is the base class. It is an abstract class. The instantiable subclasses of Geometry are restricted to zero-, one-, and two-dimensional geometric objects that exist in two-dimensional coordinate space. All instantiable geometry classes are defined so that valid instances of a geometry class are topologically closed (that is, all defined geometries include their boundary). The base Geometry class has subclasses for Point, Curve, Surface, and GeometryCollection: Point represents zero-dimensional objects. Curve represents one-dimensional objects, and has subclass LineString, with sub-subclasses Line and LinearRing. Surface is designed for two-dimensional objects and has subclass Polygon. GeometryCollection has specialized zero-, one-, and two-dimensional collection classes named MultiPoint, MultiLineString, and MultiPolygon for modeling geometries corresponding to collections of Points, LineStrings, and Polygons, respectively. MultiCurve and MultiSurface are introduced as abstract superclasses that generalize the collection interfaces to handle Curves and Surfaces.
Geometry, Curve, Surface, MultiCurve, and MultiSurface are defined as noninstantiable classes. They define a common set of methods for their subclasses and are included for extensibility. Point, LineString, Polygon, GeometryCollection, MultiPoint, MultiLineString, and MultiPolygon are instantiable classes. 12.17.2.2. Class Geometry Geometry is the root class of the hierarchy. It is a noninstantiable class but has a number of properties that are common to all geometry values created from any of the Geometry subclasses. These properties are described in the following list. Particular subclasses have their own specific properties, described later. Geometry Properties A geometry value has the following properties: Its type. Each geometry belongs to one of the instantiable classes in the hierarchy. Its SRID, 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. In MySQL, the SRID value is just an integer associated with the geometry value. All calculations are done assuming Euclidean (planar) geometry. Its coordinates in its Spatial Reference System, represented as double-precision (8-byte) numbers. All nonempty geometries include at least one pair of (X,Y) coordinates. Empty geometries contain no coordinates. Coordinates are related to the SRID. For example, in different coordinate systems, the distance between two objects may differ even when objects have the same coordinates, because the distance on the planar coordinate system and the distance on the geocentric system (coordinates on the Earth's surface) are different things. Its interior, boundary, and exterior. 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. Its MBR (Minimum Bounding Rectangle), or Envelope. This is the bounding geometry, formed by the minimum and maximum (X,Y) coordinates: ((MINX MINY, MAXX MINY, MAXX MAXY, MINX MAXY, MINX MINY)) Whether the value is simple or nonsimple. Geometry values of types (LineString, MultiPoint, MultiLineString) are either simple or nonsimple. Each type determines its own assertions for being simple or nonsimple. Whether the value is closed or not closed. Geometry values of types (LineString, MultiString) are either closed or not closed. Each type determines its own assertions for being closed or not closed. Whether the value is empty or nonempty 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 NULL value). An empty geometry is defined to be always simple and has an area of 0. Its dimension. A geometry can have a dimension of �1, 0, 1, or 2: �1 for an empty geometry. 0 for a geometry with no length and no area. 1 for a geometry with nonzero length and zero area. 2 for a geometry with nonzero area.
Point objects have a dimension of zero. LineString objects have a dimension of 1. Polygon objects have a dimension of 2. The dimensions of MultiPoint, MultiLineString, and MultiPolygon objects are the same as the dimensions of the elements they consist of.
A Point is a geometry that represents a single location in coordinate space. Point Examples Imagine a large-scale map of the world with many cities. A Point object could represent each city. On a city map, a Point object could represent a bus stop.
Point Properties A Curve is a one-dimensional geometry, usually represented by a sequence of points. Particular subclasses of Curve define the type of interpolation between points. Curve is a noninstantiable class. Curve Properties A Curve has the coordinates of its points. A Curve is defined as a one-dimensional geometry. A Curve is simple if it does not pass through the same point twice. A Curve is closed if its start point is equal to its endpoint. The boundary of a closed Curve is empty. The boundary of a nonclosed Curve consists of its two endpoints. A Curve that is simple and closed is a LinearRing.
12.17.2.5. Class LineString A LineString is a Curve with linear interpolation between points. LineString Examples On a world map, LineString objects could represent rivers. In a city map, LineString objects could represent streets.
LineString Properties A LineString has coordinates of segments, defined by each consecutive pair of points. A LineString is a Line if it consists of exactly two points. A LineString is a LinearRing if it is both closed and simple.
A Surface is a two-dimensional geometry. It is a noninstantiable class. Its only instantiable subclass is Polygon. Surface Properties A Surface is defined as a two-dimensional geometry. The OpenGIS specification defines a simple Surface as a geometry that consists of a single "patch" that is associated with a single exterior boundary and zero or more interior boundaries. The boundary of a simple Surface is the set of closed curves corresponding to its exterior and interior boundaries.
A Polygon is a planar Surface representing a multisided geometry. It is defined by a single exterior boundary and zero or more interior boundaries, where each interior boundary defines a hole in the Polygon. Polygon Examples Polygon Assertions The boundary of a Polygon consists of a set of LinearRing objects (that is, LineString objects that are both simple and closed) that make up its exterior and interior boundaries. A Polygon has no rings that cross. The rings in the boundary of a Polygon may intersect at a Point, but only as a tangent. A Polygon has no lines, spikes, or punctures. A Polygon has an interior that is a connected point set. A Polygon may have holes. The exterior of a Polygon with holes is not connected. Each hole defines a connected component of the exterior.
The preceding assertions make a Polygon a simple geometry. 12.17.2.8. Class GeometryCollection A GeometryCollection is a geometry that is a collection of one or more geometries of any class. All the elements in a GeometryCollection must be in the same Spatial Reference System (that is, in the same coordinate system). There are no other constraints on the elements of a GeometryCollection, although the subclasses of GeometryCollection described in the following sections may restrict membership. Restrictions may be based on: Element type (for example, a MultiPoint may contain only Point elements) Dimension Constraints on the degree of spatial overlap between elements
12.17.2.9. Class MultiPoint A MultiPoint is a geometry collection composed of Point elements. The points are not connected or ordered in any way. MultiPoint Examples On a world map, a MultiPoint could represent a chain of small islands. On a city map, a MultiPoint could represent the outlets for a ticket office.
MultiPoint Properties A MultiPoint is a zero-dimensional geometry. A MultiPoint is simple if no two of its Point values are equal (have identical coordinate values). The boundary of a MultiPoint is the empty set.
12.17.2.10. Class MultiCurve A MultiCurve is a geometry collection composed of Curve elements. MultiCurve is a noninstantiable class. MultiCurve Properties A MultiCurve is a one-dimensional geometry. A MultiCurve is simple if and only if all of its elements are simple; the only intersections between any two elements occur at points that are on the boundaries of both elements. A MultiCurve boundary is obtained by applying the "mod 2 union rule" (also known as the "odd-even rule"): A point is in the boundary of a MultiCurve if it is in the boundaries of an odd number of MultiCurve elements. A MultiCurve is closed if all of its elements are closed. The boundary of a closed MultiCurve is always empty.
12.17.2.11. Class MultiLineString A MultiLineString is a MultiCurve geometry collection composed of LineString elements. MultiLineString Examples 12.17.2.12. Class MultiSurface A MultiSurface is a geometry collection composed of surface elements. MultiSurface is a noninstantiable class. Its only instantiable subclass is MultiPolygon. MultiSurface Assertions 12.17.2.13. Class MultiPolygon A MultiPolygon is a MultiSurface object composed of Polygon elements. MultiPolygon Examples MultiPolygon Assertions A MultiPolygon has no two Polygon elements with interiors that intersect. A MultiPolygon has no two Polygon elements that cross (crossing is also forbidden by the previous assertion), or that touch at an infinite number of points. A MultiPolygon may not have cut lines, spikes, or punctures. A MultiPolygon is a regular, closed point set. A MultiPolygon that has more than one Polygon has an interior that is not connected. The number of connected components of the interior of a MultiPolygon is equal to the number of Polygon values in the MultiPolygon.
MultiPolygon Properties A MultiPolygon is a two-dimensional geometry. A MultiPolygon boundary is a set of closed curves (LineString values) corresponding to the boundaries of its Polygon elements. Each Curve in the boundary of the MultiPolygon is in the boundary of exactly one Polygon element. Every Curve in the boundary of an Polygon element is in the boundary of the MultiPolygon.
12.17.3. Supported Spatial Data Formats This section describes the standard spatial data formats that are used to represent geometry objects in queries. They are: Internally, MySQL stores geometry values in a format that is not identical to either WKT or WKB format. 12.17.3.1. Well-Known Text (WKT) Format The Well-Known Text (WKT) representation of Geometry is designed to exchange geometry data in ASCII form. For a Backus-Naur grammar that specifies the formal production rules for writing WKT values, see the OpenGIS specification document referenced in Section 12.17, "Spatial Extensions". Examples of WKT representations of geometry objects: A Point: POINT(15 20) Note that point coordinates are specified with no separating comma. This differs from the syntax for the SQL POINT() function, which requires a comma between the coordinates. Take care to use the syntax appropriate to the context of a given spatial operation. For example, the following statements both extract the X-coordinate from a Point object. The first produces the object directly using the POINT() function. The second uses a WKT representation converted to a Point with GeomFromText(). mysql> SELECT X(POINT(15, 20));+------------------+| X(POINT(15, 20)) |+------------------+| 15 |+------------------+mysql> SELECT X(GeomFromText('POINT(15 20)'));+---------------------------------+| X(GeomFromText('POINT(15 20)')) |+---------------------------------+| 15 |+---------------------------------+ A LineString with four points: LINESTRING(0 0, 10 10, 20 25, 50 60) Note that point coordinate pairs are separated by commas. A Polygon with one exterior ring and one interior ring: POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5)) A MultiPoint with three Point values: MULTIPOINT(0 0, 20 20, 60 60) A MultiLineString with two LineString values: MULTILINESTRING((10 10, 20 20), (15 15, 30 15)) A MultiPolygon with two Polygon values: MULTIPOLYGON(((0 0,10 0,10 10,0 10,0 0)),((5 5,7 5,7 7,5 7, 5 5))) A GeometryCollection consisting of two Point values and one LineString: GEOMETRYCOLLECTION(POINT(10 10), POINT(30 30), LINESTRING(15 15, 20 20))
12.17.3.2. Well-Known Binary (WKB) Format The Well-Known Binary (WKB) representation for geometric values is defined by the OpenGIS specification. It is also defined in the ISO SQL/MM Part 3: Spatial standard. WKB is used to exchange geometry data as binary streams represented by BLOB values containing geometric WKB information. WKB uses 1-byte unsigned integers, 4-byte unsigned integers, and 8-byte double-precision numbers (IEEE 754 format). A byte is eight bits. For example, a WKB value that corresponds to POINT(1 1) consists of this sequence of 21 bytes (each represented here by two hex digits): 0101000000000000000000F03F000000000000F03F The sequence may be broken down into these components: Byte order : 01WKB type : 01000000X : 000000000000F03FY : 000000000000F03F Component representation is as follows: 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. The WKB type is a code that indicates the geometry type. Values from 1 through 7 indicate Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon, and GeometryCollection. A Point value has X and Y coordinates, each represented as a double-precision value.
WKB values for more complex geometry values are represented by more complex data structures, as detailed in the OpenGIS specification. 12.17.4. Creating a Spatially Enabled MySQL Database 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. 12.17.4.1. MySQL Spatial Data Types MySQL has data types that correspond to OpenGIS classes. Some of these types hold single geometry values: GEOMETRY POINT LINESTRING POLYGON
GEOMETRY can store geometry values of any type. The other single-value types (POINT, LINESTRING, and POLYGON) restrict their values to a particular geometry type. The other data types hold collections of values: MULTIPOINT MULTILINESTRING MULTIPOLYGON GEOMETRYCOLLECTION
GEOMETRYCOLLECTION can store a collection of objects of any type. The other collection types (MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, and GEOMETRYCOLLECTION) restrict collection members to those having a particular geometry type. 12.17.4.2. Creating Spatial Values 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. 12.17.4.2.1. Creating Geometry Values Using WKT Functions MySQL provides a number of functions that take as arguments a Well-Known Text representation and, optionally, a spatial reference system identifier (SRID). They return the corresponding geometry. GeomFromText() accepts a WKT of any geometry type as its first argument. An implementation also provides type-specific construction functions for construction of geometry values of each geometry type. GeomCollFromText(wkt[,srid]), GeometryCollectionFromText(wkt[,srid]) Constructs a GEOMETRYCOLLECTION value using its WKT representation and SRID. GeomFromText(wkt[,srid]), GeometryFromText(wkt[,srid]) Constructs a geometry value of any type using its WKT representation and SRID. LineFromText(wkt[,srid]), LineStringFromText(wkt[,srid]) Constructs a LINESTRING value using its WKT representation and SRID. MLineFromText(wkt[,srid]), MultiLineStringFromText(wkt[,srid]) Constructs a MULTILINESTRING value using its WKT representation and SRID. MPointFromText(wkt[,srid]), MultiPointFromText(wkt[,srid]) Constructs a MULTIPOINT value using its WKT representation and SRID. MPolyFromText(wkt[,srid]), MultiPolygonFromText(wkt[,srid]) Constructs a MULTIPOLYGON value using its WKT representation and SRID. PointFromText(wkt[,srid]) Constructs a POINT value using its WKT representation and SRID. PolyFromText(wkt[,srid]), PolygonFromText(wkt[,srid]) Constructs a POLYGON value using its WKT representation and SRID.
The OpenGIS specification also defines the following optional functions, which MySQL does not implement. These functions construct Polygon or MultiPolygon values based on the WKT representation of a collection of rings or closed LineString values. These values may intersect. BdMPolyFromText(wkt,srid) Constructs a MultiPolygon value from a MultiLineString value in WKT format containing an arbitrary collection of closed LineString values. BdPolyFromText(wkt,srid) Constructs a Polygon value from a MultiLineString value in WKT format containing an arbitrary collection of closed LineString values.
12.17.4.2.2. Creating Geometry Values Using WKB Functions MySQL provides a number of functions that take as arguments a BLOB containing a Well-Known Binary representation and, optionally, a spatial reference system identifier (SRID). They return the corresponding geometry. These functions also accept geometry objects for compatibility with the return value of the functions in Section 12.17.4.2.3, "Creating Geometry Values Using MySQL-Specific Functions". Thus, those functions may be used to provide the first argument to the functions in this section. GeomCollFromWKB(wkb[,srid]), GeometryCollectionFromWKB(wkb[,srid]) Constructs a GEOMETRYCOLLECTION value using its WKB representation and SRID. GeomFromWKB(wkb[,srid]), GeometryFromWKB(wkb[,srid]) Constructs a geometry value of any type using its WKB representation and SRID. LineFromWKB(wkb[,srid]), LineStringFromWKB(wkb[,srid]) Constructs a LINESTRING value using its WKB representation and SRID. MLineFromWKB(wkb[,srid]), MultiLineStringFromWKB(wkb[,srid]) Constructs a MULTILINESTRING value using its WKB representation and SRID. MPointFromWKB(wkb[,srid]), MultiPointFromWKB(wkb[,srid]) Constructs a MULTIPOINT value using its WKB representation and SRID. MPolyFromWKB(wkb[,srid]), MultiPolygonFromWKB(wkb[,srid]) Constructs a MULTIPOLYGON value using its WKB representation and SRID. PointFromWKB(wkb[,srid]) Constructs a POINT value using its WKB representation and SRID. PolyFromWKB(wkb[,srid]), PolygonFromWKB(wkb[,srid]) Constructs a POLYGON value using its WKB representation and SRID.
The OpenGIS specification also describes optional functions for constructing Polygon or MultiPolygon values based on the WKB representation of a collection of rings or closed LineString values. These values may intersect. MySQL does not implement these functions: BdMPolyFromWKB(wkb,srid) Constructs a MultiPolygon value from a MultiLineString value in WKB format containing an arbitrary collection of closed LineString values. BdPolyFromWKB(wkb,srid) Constructs a Polygon value from a MultiLineString value in WKB format containing an arbitrary collection of closed LineString values.
12.17.4.2.3. Creating Geometry Values Using MySQL-Specific Functions MySQL provides a set of useful nonstandard functions for creating geometry values. The functions described in this section are MySQL extensions to the OpenGIS specification. These functions produce geometry objects from either WKB values or geometry objects as arguments. If any argument is not a proper WKB or geometry representation of the proper object type, the return value is NULL. For example, you can insert the geometry return value from Point() directly into a Point column: INSERT INTO t1 (pt_col) VALUES(Point(1,2)); GeometryCollection(g1,g2,...) Constructs a GeometryCollection. LineString(pt1,pt2,...) Constructs a LineString value from a number of Point or WKB Point arguments. If the number of arguments is less than two, the return value is NULL. MultiLineString(ls1,ls2,...) Constructs a MultiLineString value using LineString or WKB LineString arguments. MultiPoint(pt1,pt2,...) Constructs a MultiPoint value using Point or WKB Point arguments. MultiPolygon(poly1,poly2,...) Constructs a MultiPolygon value from a set of Polygon or WKB Polygon arguments. Point(x,y) Constructs a Point using its coordinates. Polygon(ls1,ls2,...) Constructs a Polygon value from a number of LineString or WKB LineString arguments. If any argument does not represent a LinearRing (that is, not a closed and simple LineString), the return value is NULL.
12.17.4.3. Creating Spatial Columns MySQL provides a standard way of creating spatial columns for geometry types, for example, with CREATE TABLE or ALTER TABLE. Currently, spatial columns are supported for MyISAM, InnoDB, NDB, and ARCHIVE tables. See also the annotations about spatial indexes under Section 12.17.6.1, "Creating Spatial Indexes". Use the CREATE TABLE statement to create a table with a spatial column: CREATE TABLE geom (g GEOMETRY); Use the ALTER TABLE statement to add or drop a spatial column to or from an existing table: ALTER TABLE geom ADD pt POINT;ALTER TABLE geom DROP pt;
12.17.4.4. Populating Spatial Columns After you have created spatial columns, you can populate them with spatial data. Values should be stored in internal geometry format, but you can convert them to that format from either Well-Known Text (WKT) or Well-Known Binary (WKB) format. The following examples demonstrate how to insert geometry values into a table by converting WKT values into internal geometry format: Perform the conversion directly in the INSERT statement: INSERT INTO geom VALUES (GeomFromText('POINT(1 1)'));SET @g = 'POINT(1 1)';INSERT INTO geom VALUES (GeomFromText(@g)); Perform the conversion prior to the INSERT: SET @g = GeomFromText('POINT(1 1)');INSERT INTO geom VALUES (@g);
The following examples insert more complex geometries into the table: SET @g = 'LINESTRING(0 0,1 1,2 2)';INSERT INTO geom VALUES (GeomFromText(@g));SET @g = 'POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))';INSERT INTO geom VALUES (GeomFromText(@g));SET @g ='GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(0 0,1 1,2 2,3 3,4 4))';INSERT INTO geom VALUES (GeomFromText(@g)); The preceding examples all use GeomFromText() to create geometry values. You can also use type-specific functions: SET @g = 'POINT(1 1)';INSERT INTO geom VALUES (PointFromText(@g));SET @g = 'LINESTRING(0 0,1 1,2 2)';INSERT INTO geom VALUES (LineStringFromText(@g));SET @g = 'POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))';INSERT INTO geom VALUES (PolygonFromText(@g));SET @g ='GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(0 0,1 1,2 2,3 3,4 4))';INSERT INTO geom VALUES (GeomCollFromText(@g)); Note that if a client application program wants to use WKB representations of geometry values, it is responsible for sending correctly formed WKB in queries to the server. However, there are several ways of satisfying this requirement. For example: Inserting a POINT(1 1) value with hex literal syntax: mysql> INSERT INTO geom VALUES -> (GeomFromWKB(0x0101000000000000000000F03F000000000000F03F)); An ODBC application can send a WKB representation, binding it to a placeholder using an argument of BLOB type: INSERT INTO geom VALUES (GeomFromWKB(?)) Other programming interfaces may support a similar placeholder mechanism. In a C program, you can escape a binary value using mysql_real_escape_string() and include the result in a query string that is sent to the server. See Section 22.8.3.53, "mysql_real_escape_string()".
12.17.4.5. Fetching Spatial Data Geometry values stored in a table can be fetched in internal format. You can also convert them into WKT or WKB format. Fetching spatial data in internal format: Fetching geometry values using internal format can be useful in table-to-table transfers: CREATE TABLE geom2 (g GEOMETRY) SELECT g FROM geom; Fetching spatial data in WKT format: The AsText() function converts a geometry from internal format into a WKT string. SELECT AsText(g) FROM geom; Fetching spatial data in WKB format: The AsBinary() function converts a geometry from internal format into a BLOB containing the WKB value. SELECT AsBinary(g) FROM geom;
12.17.5. Spatial Analysis Functions 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: Functions that convert geometries between various formats Functions that provide access to qualitative or quantitative properties of a geometry Functions that describe relations between two geometries Functions that create new geometries from existing ones
Spatial analysis functions can be used in many contexts, such as: Any interactive SQL program, such as mysql. Application programs written in any language that supports a MySQL client API
12.17.5.1. Geometry Format Conversion Functions MySQL supports the following functions for converting geometry values between internal format and either WKT or WKB format: AsBinary(g), AsWKB(g) Converts a value in internal geometry format to its WKB representation and returns the binary result. SELECT AsBinary(g) FROM geom; AsText(g), AsWKT(g) Converts a value in internal geometry format to its WKT representation and returns the string result. mysql> SET @g = 'LineString(1 1,2 2,3 3)';mysql> SELECT AsText(GeomFromText(@g));+--------------------------+| AsText(GeomFromText(@g)) |+--------------------------+| LINESTRING(1 1,2 2,3 3) |+--------------------------+ GeomFromText(wkt[,srid]) 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 PointFromText() and LineFromText(). See Section 12.17.4.2.1, "Creating Geometry Values Using WKT Functions". GeomFromWKB(wkb[,srid]) 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 PointFromWKB() and LineFromWKB(). See Section 12.17.4.2.2, "Creating Geometry Values Using WKB Functions".
12.17.5.2. Geometry Property Functions Each function that belongs to this group takes a geometry value as its argument and returns some quantitative or qualitative property of the geometry. Some functions restrict their argument type. Such functions return NULL if the argument is of an incorrect geometry type. For example, Area() returns NULL if the object type is neither Polygon nor MultiPolygon. 12.17.5.2.1. General Geometry Functions The functions listed in this section do not restrict their argument and accept a geometry value of any type. Dimension(g) Returns the inherent dimension of the geometry value g. The result can be �1, 0, 1, or 2. The meaning of these values is given in Section 12.17.2.2, "Class Geometry". mysql> SELECT Dimension(GeomFromText('LineString(1 1,2 2)'));+------------------------------------------------+| Dimension(GeomFromText('LineString(1 1,2 2)')) |+------------------------------------------------+| 1 |+------------------------------------------------+ Envelope(g) Returns the Minimum Bounding Rectangle (MBR) for the geometry value g. The result is returned as a Polygon value. The polygon is defined by the corner points of the bounding box: POLYGON((MINX MINY, MAXX MINY, MAXX MAXY, MINX MAXY, MINX MINY)) mysql> SELECT AsText(Envelope(GeomFromText('LineString(1 1,2 2)')));+-------------------------------------------------------+| AsText(Envelope(GeomFromText('LineString(1 1,2 2)'))) |+-------------------------------------------------------+| POLYGON((1 1,2 1,2 2,1 2,1 1)) |+-------------------------------------------------------+ GeometryType(g) Returns as a binary string the name of the geometry type of which the geometry instance g is a member. The name corresponds to one of the instantiable Geometry subclasses. mysql> SELECT GeometryType(GeomFromText('POINT(1 1)'));+------------------------------------------+| GeometryType(GeomFromText('POINT(1 1)')) |+------------------------------------------+| POINT |+------------------------------------------+ SRID(g) Returns an integer indicating the Spatial Reference System ID for the geometry value g. In MySQL, the SRID value is just an integer associated with the geometry value. All calculations are done assuming Euclidean (planar) geometry. mysql> SELECT SRID(GeomFromText('LineString(1 1,2 2)',101));+-----------------------------------------------+| SRID(GeomFromText('LineString(1 1,2 2)',101)) |+-----------------------------------------------+| 101 |+-----------------------------------------------+
The OpenGIS specification also defines the following functions, which MySQL does not implement: Boundary(g) Returns a geometry that is the closure of the combinatorial boundary of the geometry value g. IsEmpty(g) This function is a placeholder that returns 0 for any valid geometry value, 1 for any invalid geometry value or NULL. MySQL does not support GIS EMPTY values such as POINT EMPTY. IsSimple(g) In MySQL 5.5, this function is a placeholder that always returns 0. 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 Section 12.17.2.1, "The Geometry Class Hierarchy".)
12.17.5.2.2. Point Functions A Point consists of X and Y coordinates, which may be obtained using the following functions: X(p) Returns the X-coordinate value for the Point object p as a double-precision number. mysql> SELECT X(POINT(56.7, 53.34));+-----------------------+| X(POINT(56.7, 53.34)) |+-----------------------+| 56.7 |+-----------------------+ Y(p) Returns the Y-coordinate value for the Point object p as a double-precision number. mysql> SELECT Y(POINT(56.7, 53.34));+-----------------------+| Y(POINT(56.7, 53.34)) |+-----------------------+| 53.34 |+-----------------------+
12.17.5.2.3. LineString Functions A LineString consists of Point values. You can extract particular points of a LineString, count the number of points that it contains, or obtain its length. EndPoint(ls) Returns the Point that is the endpoint of the LineString value ls. mysql> SET @ls = 'LineString(1 1,2 2,3 3)';mysql> SELECT AsText(EndPoint(GeomFromText(@ls)));+-------------------------------------+| AsText(EndPoint(GeomFromText(@ls))) |+-------------------------------------+| POINT(3 3) |+-------------------------------------+ GLength(ls) Returns as a double-precision number the length of the LineString value ls in its associated spatial reference. mysql> SET @ls = 'LineString(1 1,2 2,3 3)';mysql> SELECT GLength(GeomFromText(@ls));+----------------------------+| GLength(GeomFromText(@ls)) |+----------------------------+| 2.8284271247462 |+----------------------------+ GLength() is a nonstandard name. It corresponds to the OpenGIS Length() function. NumPoints(ls) Returns the number of Point objects in the LineString value ls. mysql> SET @ls = 'LineString(1 1,2 2,3 3)';mysql> SELECT NumPoints(GeomFromText(@ls));+------------------------------+| NumPoints(GeomFromText(@ls)) |+------------------------------+| 3 |+------------------------------+ PointN(ls,N) Returns the N-th Point in the Linestring value ls. Points are numbered beginning with 1. mysql> SET @ls = 'LineString(1 1,2 2,3 3)';mysql> SELECT AsText(PointN(GeomFromText(@ls),2));+-------------------------------------+| AsText(PointN(GeomFromText(@ls),2)) |+-------------------------------------+| POINT(2 2) |+-------------------------------------+ StartPoint(ls) Returns the Point that is the start point of the LineString value ls. mysql> SET @ls = 'LineString(1 1,2 2,3 3)';mysql> SELECT AsText(StartPoint(GeomFromText(@ls)));+---------------------------------------+| AsText(StartPoint(GeomFromText(@ls))) |+---------------------------------------+| POINT(1 1) |+---------------------------------------+
The OpenGIS specification also defines the following function, which MySQL does not implement: IsRing(ls) Returns 1 if the LineString value ls is closed (that is, its StartPoint() and EndPoint() values are the same) and is simple (does not pass through the same point more than once). Returns 0 if ls is not a ring, and �1 if it is NULL.
12.17.5.2.4. MultiLineString Functions These functions return properties of MultiLineString values. GLength(mls) Returns as a double-precision number the length of the MultiLineString value mls. The length of mls is equal to the sum of the lengths of its elements. mysql> SET @mls = 'MultiLineString((1 1,2 2,3 3),(4 4,5 5))';mysql> SELECT GLength(GeomFromText(@mls));+-----------------------------+| GLength(GeomFromText(@mls)) |+-----------------------------+| 4.2426406871193 |+-----------------------------+ GLength() is a nonstandard name. It corresponds to the OpenGIS Length() function. IsClosed(mls) Returns 1 if the MultiLineString value mls is closed (that is, the StartPoint() and EndPoint() values are the same for each LineString in mls). Returns 0 if mls is not closed, and �1 if it is NULL. mysql> SET @mls = 'MultiLineString((1 1,2 2,3 3),(4 4,5 5))';mysql> SELECT IsClosed(GeomFromText(@mls));+------------------------------+| IsClosed(GeomFromText(@mls)) |+------------------------------+| 0 |+------------------------------+
12.17.5.2.5. Polygon Functions These functions return properties of Polygon values. Area(poly) Returns as a double-precision number the area of the Polygon value poly, as measured in its spatial reference system. mysql> SET @poly = 'Polygon((0 0,0 3,3 0,0 0),(1 1,1 2,2 1,1 1))';mysql> SELECT Area(GeomFromText(@poly));+---------------------------+| Area(GeomFromText(@poly)) |+---------------------------+| 4 |+---------------------------+ ExteriorRing(poly) Returns the exterior ring of the Polygon value poly as a LineString. mysql> SET @poly = -> 'Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))';mysql> SELECT AsText(ExteriorRing(GeomFromText(@poly)));+-------------------------------------------+| AsText(ExteriorRing(GeomFromText(@poly))) |+-------------------------------------------+| LINESTRING(0 0,0 3,3 3,3 0,0 0) |+-------------------------------------------+ InteriorRingN(poly,N) Returns the N-th interior ring for the Polygon value poly as a LineString. Rings are numbered beginning with 1. mysql> SET @poly = -> 'Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))';mysql> SELECT AsText(InteriorRingN(GeomFromText(@poly),1));+----------------------------------------------+| AsText(InteriorRingN(GeomFromText(@poly),1)) |+----------------------------------------------+| LINESTRING(1 1,1 2,2 2,2 1,1 1) |+----------------------------------------------+ NumInteriorRings(poly) Returns the number of interior rings in the Polygon value poly. mysql> SET @poly = -> 'Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))';mysql> SELECT NumInteriorRings(GeomFromText(@poly));+---------------------------------------+| NumInteriorRings(GeomFromText(@poly)) |+---------------------------------------+| 1 |+---------------------------------------+
12.17.5.2.6. MultiPolygon Functions These functions return properties of MultiPolygon values. Area(mpoly) Returns as a double-precision number the area of the MultiPolygon value mpoly, as measured in its spatial reference system. mysql> SET @mpoly = -> 'MultiPolygon(((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1)))';mysql> SELECT Area(GeomFromText(@mpoly));+----------------------------+| Area(GeomFromText(@mpoly)) |+----------------------------+| 8 |+----------------------------+ Centroid(mpoly) Returns the mathematical centroid for the MultiPolygon value mpoly as a Point. The result is not guaranteed to be on the MultiPolygon. mysql> SET @poly = -> GeomFromText('POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7,5 5))');mysql> SELECT GeometryType(@poly),AsText(Centroid(@poly));+---------------------+--------------------------------------------+| GeometryType(@poly) | AsText(Centroid(@poly)) |+---------------------+--------------------------------------------+| POLYGON | POINT(4.958333333333333 4.958333333333333) |+---------------------+--------------------------------------------+
The OpenGIS specification also defines the following function, which MySQL does not implement: 12.17.5.2.7. GeometryCollection Functions These functions return properties of GeometryCollection values. GeometryN(gc,N) Returns the N-th geometry in the GeometryCollection value gc. Geometries are numbered beginning with 1. mysql> SET @gc = 'GeometryCollection(Point(1 1),LineString(2 2, 3 3))';mysql> SELECT AsText(GeometryN(GeomFromText(@gc),1));+----------------------------------------+| AsText(GeometryN(GeomFromText(@gc),1)) |+----------------------------------------+| POINT(1 1) |+----------------------------------------+ NumGeometries(gc) Returns the number of geometries in the GeometryCollection value gc. mysql> SET @gc = 'GeometryCollection(Point(1 1),LineString(2 2, 3 3))';mysql> SELECT NumGeometries(GeomFromText(@gc));+----------------------------------+| NumGeometries(GeomFromText(@gc)) |+----------------------------------+| 2 |+----------------------------------+
12.17.5.3. Functions That Create New Geometries from Existing Ones The following sections describe functions that take geometry values as arguments and return new geometry values. 12.17.5.3.1. Geometry Functions That Produce New Geometries Section 12.17.5.2, "Geometry Property Functions", discusses several functions that construct new geometries from existing ones. See that section for descriptions of these functions: 12.17.5.3.2. Spatial Operators OpenGIS proposes a number of other functions that can produce geometries. They are designed to implement spatial operators. These functions are not implemented in MySQL. Buffer(g,d) Returns a geometry that represents all points whose distance from the geometry value g is less than or equal to a distance of d. ConvexHull(g) Returns a geometry that represents the convex hull of the geometry value g. Difference(g1,g2) Returns a geometry that represents the point set difference of the geometry value g1 with g2. Intersection(g1,g2) Returns a geometry that represents the point set intersection of the geometry values g1 with g2. SymDifference(g1,g2) Returns a geometry that represents the point set symmetric difference of the geometry value g1 with g2. Union(g1,g2) Returns a geometry that represents the point set union of the geometry values g1 and g2.
12.17.5.4. Functions for Testing Spatial Relations Between Geometric Objects The functions described in these sections take two geometries as input parameters and return a qualitative or quantitative relation between them. 12.17.5.4.1. Relations on Geometry Minimal Bounding Rectangles (MBRs) MySQL provides several functions that test relations between minimal bounding rectangles of two geometries g1 and g2. The return values 1 and 0 indicate true and false, respectively. MBRContains(g1,g2) Returns 1 or 0 to indicate whether the Minimum Bounding Rectangle of g1 contains the Minimum Bounding Rectangle of g2. This tests the opposite relationship as MBRWithin(). mysql> SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))');mysql> SET @g2 = GeomFromText('Point(1 1)');mysql> SELECT MBRContains(@g1,@g2), MBRContains(@g2,@g1);----------------------+----------------------+| MBRContains(@g1,@g2) | MBRContains(@g2,@g1) |+----------------------+----------------------+| 1 | 0 |+----------------------+----------------------+ MBRDisjoint(g1,g2) Returns 1 or 0 to indicate whether the Minimum Bounding Rectangles of the two geometries g1 and g2 are disjoint (do not intersect). MBREqual(g1,g2) Returns 1 or 0 to indicate whether the Minimum Bounding Rectangles of the two geometries g1 and g2 are the same. MBRIntersects(g1,g2) Returns 1 or 0 to indicate whether the Minimum Bounding Rectangles of the two geometries g1 and g2 intersect. MBROverlaps(g1,g2) Returns 1 or 0 to indicate whether the Minimum Bounding Rectangles of the two geometries g1 and g2 overlap. The term spatially overlaps is used if two geometries intersect and their intersection results in a geometry of the same dimension but not equal to either of the given geometries. MBRTouches(g1,g2) Returns 1 or 0 to indicate whether the Minimum Bounding Rectangles of the two geometries g1 and g2 touch. Two geometries spatially touch if the interiors of the geometries do not intersect, but the boundary of one of the geometries intersects either the boundary or the interior of the other. MBRWithin(g1,g2) Returns 1 or 0 to indicate whether the Minimum Bounding Rectangle of g1 is within the Minimum Bounding Rectangle of g2. This tests the opposite relationship as MBRContains(). mysql> SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))');mysql> SET @g2 = GeomFromText('Polygon((0 0,0 5,5 5,5 0,0 0))');mysql> SELECT MBRWithin(@g1,@g2), MBRWithin(@g2,@g1);+--------------------+--------------------+| MBRWithin(@g1,@g2) | MBRWithin(@g2,@g1) |+--------------------+--------------------+| 1 | 0 |+--------------------+--------------------+
12.17.5.4.2. Functions That Test Spatial Relationships Between Geometries The OpenGIS specification defines the following functions. They test the relationship between two geometry values g1 and g2. The return values 1 and 0 indicate true and false, respectively. Note 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. Contains(g1,g2) Returns 1 or 0 to indicate whether g1 completely contains g2. This tests the opposite relationship as Within(). Crosses(g1,g2) Returns 1 if g1 spatially crosses g2. Returns NULL if g1 is a Polygon or a MultiPolygon, or if g2 is a Point or a MultiPoint. Otherwise, returns 0. The term spatially crosses denotes a spatial relation between two given geometries that has the following properties: The two geometries intersect Their intersection results in a geometry that has a dimension that is one less than the maximum dimension of the two given geometries Their intersection is not equal to either of the two given geometries
Disjoint(g1,g2) Returns 1 or 0 to indicate whether g1 is spatially disjoint from (does not intersect) g2. Equals(g1,g2) Returns 1 or 0 to indicate whether g1 is spatially equal to g2. Intersects(g1,g2) Returns 1 or 0 to indicate whether g1 spatially intersects g2. Overlaps(g1,g2) Returns 1 or 0 to indicate whether g1 spatially overlaps g2. The term spatially overlaps is used if two geometries intersect and their intersection results in a geometry of the same dimension but not equal to either of the given geometries. Touches(g1,g2) Returns 1 or 0 to indicate whether g1 spatially touches g2. Two geometries spatially touch if the interiors of the geometries do not intersect, but the boundary of one of the geometries intersects either the boundary or the interior of the other. Within(g1,g2) Returns 1 or 0 to indicate whether g1 is spatially within g2. This tests the opposite relationship as Contains().
12.17.6. Optimizing Spatial Analysis For MyISAM tables, Search operations in nonspatial databases can be optimized using SPATIAL 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: MySQL uses R-Trees with quadratic splitting for SPATIAL indexes on spatial columns. A SPATIAL index is built using the MBR of a geometry. For most geometries, the MBR is a minimum rectangle that surrounds the geometries. For a horizontal or a vertical linestring, the MBR is a rectangle degenerated into the linestring. For a point, the MBR is a rectangle degenerated into the point. It is also possible to create normal indexes on spatial columns. In a non-SPATIAL index, you must declare a prefix for any spatial column except for POINT columns. MyISAM supports both SPATIAL and non-SPATIAL indexes. Other storage engines support non-SPATIAL indexes, as described in Section 13.1.13, "CREATE INDEX Syntax". 12.17.6.1. Creating Spatial Indexes For MyISAM tables, MySQL can create spatial indexes using syntax similar to that for creating regular indexes, but extended with the SPATIAL keyword. Currently, columns in spatial indexes must be declared NOT NULL. The following examples demonstrate how to create spatial indexes: With CREATE TABLE: CREATE TABLE geom (g GEOMETRY NOT NULL, SPATIAL INDEX(g)) ENGINE=MyISAM; With ALTER TABLE: ALTER TABLE geom ADD SPATIAL INDEX(g); With CREATE INDEX: CREATE SPATIAL INDEX sp_index ON geom (g);
For MyISAM tables, SPATIAL INDEX creates an R-tree index. For storage engines that support nonspatial 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. For more information on indexing spatial columns, see Section 13.1.13, "CREATE INDEX Syntax". To drop spatial indexes, use ALTER TABLE or DROP INDEX: Example: Suppose that a table geom contains more than 32,000 geometries, which are stored in the column g of type GEOMETRY. The table also has an AUTO_INCREMENT column fid for storing object ID values. mysql> DESCRIBE geom;+-------+----------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------+----------+------+-----+---------+----------------+| fid | int(11) | | PRI | NULL | auto_increment || g | geometry | | | | |+-------+----------+------+-----+---------+----------------+2 rows in set (0.00 sec)mysql> SELECT COUNT(*) FROM geom;+----------+| count(*) |+----------+| 32376 |+----------+1 row in set (0.00 sec) To add a spatial index on the column g, use this statement: mysql> ALTER TABLE geom ADD SPATIAL INDEX(g);Query OK, 32376 rows affected (4.05 sec)Records: 32376 Duplicates: 0 Warnings: 0 12.17.6.2. Using a Spatial Index The optimizer investigates whether available spatial indexes can be involved in the search for queries that use a function such as MBRContains() or MBRWithin() in the WHERE clause. The following query finds all objects that are in the given rectangle: mysql> SET @poly = -> 'Polygon((30000 15000, 31000 15000, 31000 16000, 30000 16000, 30000 15000))';mysql> SELECT fid,AsText(g) FROM geom WHERE -> MBRContains(GeomFromText(@poly),g);+-----+---------------------------------------------------------------+| 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) Use EXPLAIN to check the way this query is executed: mysql> SET @poly = -> 'Polygon((30000 15000, 31000 15000, 31000 16000, 30000 16000, 30000 15000))';mysql> EXPLAIN SELECT fid,AsText(g) FROM geom WHERE -> MBRContains(GeomFromText(@poly),g)\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: geom type: rangepossible_keys: g key: g key_len: 32 ref: NULL rows: 50 Extra: Using where1 row in set (0.00 sec) Check what would happen without a spatial index: mysql> SET @poly = -> 'Polygon((30000 15000, 31000 15000, 31000 16000, 30000 16000, 30000 15000))';mysql> EXPLAIN SELECT fid,AsText(g) FROM g IGNORE INDEX (g) WHERE -> MBRContains(GeomFromText(@poly),g)\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: geom type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 32376 Extra: Using where1 row in set (0.00 sec) Executing the SELECT statement without the spatial index yields the same result but causes the execution time to rise from 0.00 seconds to 0.46 seconds: mysql> SET @poly = -> 'Polygon((30000 15000, 31000 15000, 31000 16000, 30000 16000, 30000 15000))';mysql> SELECT fid,AsText(g) FROM geom IGNORE INDEX (g) WHERE -> MBRContains(GeomFromText(@poly),g);+-----+---------------------------------------------------------------+| 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) 12.17.7. MySQL Conformance and Compatibility MySQL does not yet implement the following GIS features: Additional Metadata Views OpenGIS specifications propose several additional metadata views. For example, a system view named GEOMETRY_COLUMNS contains a description of geometry columns, one row for each geometry column in the database. The OpenGIS function Length() on LineString and MultiLineString currently should be called in MySQL as GLength() The problem is that there is an existing SQL function Length() 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.
MySQL 5.5 provides support for precision math: numeric value handling that results in extremely accurate results and a high degree control over invalid values. Precision math is based on these two features: These features have several implications for numeric operations and provide a high degree of compliance with standard SQL: Precise calculations: For exact-value numbers, calculations do not introduce floating-point errors. Instead, exact precision is used. For example, MySQL treats a number such as .0001 as an exact value rather than as an approximation, and summing it 10,000 times produces a result of exactly 1, not a value that is merely "close" to 1. Well-defined rounding behavior: For exact-value numbers, the result of ROUND() depends on its argument, not on environmental factors such as how the underlying C library works. Platform independence: Operations on exact numeric values are the same across different platforms such as Windows and Unix. Control over handling of invalid values: Overflow and division by zero are detectable and can be treated as errors. For example, you can treat a value that is too large for a column as an error rather than having the value truncated to lie within the range of the column's data type. Similarly, you can treat division by zero as an error rather than as an operation that produces a result of NULL. The choice of which approach to take is determined by the setting of the server SQL mode.
The following discussion covers several aspects of how precision math works, including possible incompatibilities with older applications. At the end, some examples are given that demonstrate how MySQL 5.5 handles numeric operations precisely. For information about controlling the SQL mode, see Section 5.1.7, "Server SQL Modes". 12.18.1. Types of Numeric Values The scope of precision math for exact-value operations includes the exact-value data types (DECIMAL and integer types) and exact-value numeric literals. Approximate-value data types and numeric literals are handled as floating-point numbers. Exact-value numeric literals have an integer part or fractional part, or both. They may be signed. Examples: 1, .2, 3.4, -5, -6.78, +9.10. Approximate-value numeric literals are represented in scientific notation with a mantissa and exponent. Either or both parts may be signed. Examples: 1.2E3, 1.2E-3, -1.2E3, -1.2E-3. Two numbers that look similar may be treated differently. For example, 2.34 is an exact-value (fixed-point) number, whereas 2.34E0 is an approximate-value (floating-point) number. The DECIMAL data type is a fixed-point type and calculations are exact. In MySQL, the DECIMAL type has several synonyms: NUMERIC, DEC, FIXED. The integer types also are exact-value types. The FLOAT and DOUBLE data types are floating-point types and calculations are approximate. In MySQL, types that are synonymous with FLOAT or DOUBLE are DOUBLE PRECISION and REAL. 12.18.2. DECIMAL Data Type Changes This section discusses the characteristics of the DECIMAL data type (and its synonyms) in MySQL 5.5, with particular regard to the following topics: Possible incompatibilities with applications that are written for older versions of MySQL (prior to 5.0.3) are noted throughout this section. The declaration syntax for a DECIMAL column is DECIMAL(M,D). The ranges of values for the arguments in MySQL 5.5 are as follows: M is the maximum number of digits (the precision). It has a range of 1 to 65. (Older versions of MySQL permitted a range of 1 to 254.) D is the number of digits to the right of the decimal point (the scale). It has a range of 0 to 30 and must be no larger than M.
The maximum value of 65 for M means that calculations on DECIMAL values are accurate up to 65 digits. This limit of 65 digits of precision also applies to exact-value numeric literals, so the maximum range of such literals differs from before. (In older versions of MySQL, decimal values could have up to 254 digits. However, calculations were done using floating-point and thus were approximate, not exact.) Values for DECIMAL columns in MySQL 5.5 are stored using a binary format that packs nine decimal digits into 4 bytes. The storage requirements for the integer and fractional parts of each value are determined separately. Each multiple of nine digits requires 4 bytes, and any remaining digits left over require some fraction of 4 bytes. The storage required for remaining digits is given by the following table. For example, a DECIMAL(18,9) column has nine digits on either side of the decimal point, so the integer part and the fractional part each require 4 bytes. A DECIMAL(20,6) column has fourteen integer digits and six fractional digits. The integer digits require four bytes for nine of the digits and 3 bytes for the remaining five digits. The six fractional digits require 3 bytes. Unlike some older versions of MySQL, DECIMAL columns in MySQL 5.5 do not store a leading + character or - character or leading 0 digits. If you insert +0003.1 into a DECIMAL(5,1) column, it is stored as 3.1. For negative numbers, a literal - character is not stored. Applications that rely on the older behavior must be modified to account for this change. DECIMAL columns in MySQL 5.5 do not permit values larger than the range implied by the column definition. For example, a DECIMAL(3,0) column supports a range of -999 to 999. A DECIMAL(M,D) column permits at most M - D digits to the left of the decimal point. This is not compatible with applications relying on older versions of MySQL that permitted storing an extra digit in lieu of a + sign. The SQL standard requires that the precision of NUMERIC(M,D) be exactly M digits. For DECIMAL(M,D), the standard requires a precision of at least M digits but permits more. In MySQL, DECIMAL(M,D) and NUMERIC(M,D) are the same, and both have a precision of exactly M digits. For more detailed information about porting applications that rely on the old treatment of the DECIMAL data type, see the MySQL 5.0 Reference Manual. 12.18.3. Expression Handling With precision math, exact-value numbers are used as given whenever possible. For example, numbers in comparisons are used exactly as given without a change in value. In strict SQL mode, for INSERT into a column with an exact data type (DECIMAL or integer), a number is inserted with its exact value if it is within the column range. When retrieved, the value should be the same as what was inserted. (Without strict mode, truncation for INSERT is permissible.) Handling of a numeric expression depends on what kind of values the expression contains: If any approximate values are present, the expression is approximate and is evaluated using floating-point arithmetic. If no approximate values are present, the expression contains only exact values. If any exact value contains a fractional part (a value following the decimal point), the expression is evaluated using DECIMAL exact arithmetic and has a precision of 65 digits. The term "exact" is subject to the limits of what can be represented in binary. For example, 1.0/3.0 can be approximated in decimal notation as .333..., but not written as an exact number, so (1.0/3.0)*3.0 does not evaluate to exactly 1.0. Otherwise, the expression contains only integer values. The expression is exact and is evaluated using integer arithmetic and has a precision the same as BIGINT (64 bits).
If a numeric expression contains any strings, they are converted to double-precision floating-point values and the expression is approximate. Inserts into numeric columns are affected by the SQL mode, which is controlled by the sql_mode system variable. (See Section 5.1.7, "Server SQL Modes".) The following discussion mentions strict mode (selected by the STRICT_ALL_TABLES or STRICT_TRANS_TABLES mode values) and ERROR_FOR_DIVISION_BY_ZERO. To turn on all restrictions, you can simply use TRADITIONAL mode, which includes both strict mode values and ERROR_FOR_DIVISION_BY_ZERO: mysql> SET sql_mode='TRADITIONAL'; If a number is inserted into an exact type column (DECIMAL or integer), it is inserted with its exact value if it is within the column range. If the value has too many digits in the fractional part, rounding occurs and a warning is generated. Rounding is done as described in Section 12.18.4, "Rounding Behavior". If the value has too many digits in the integer part, it is too large and is handled as follows: If strict mode is not enabled, the value is truncated to the nearest legal value and a warning is generated. If strict mode is enabled, an overflow error occurs.
Underflow is not detected, so underflow handling is undefined. By default, division by zero produces a result of NULL and no warning. With the ERROR_FOR_DIVISION_BY_ZERO SQL mode enabled, MySQL handles division by zero differently: If strict mode is not enabled, a warning occurs. If strict mode is enabled, inserts and updates involving division by zero are prohibited, and an error occurs.
In other words, inserts and updates involving expressions that perform division by zero can be treated as errors, but this requires ERROR_FOR_DIVISION_BY_ZERO in addition to strict mode. Suppose that we have this statement: INSERT INTO t SET i = 1/0; This is what happens for combinations of strict and ERROR_FOR_DIVISION_BY_ZERO modes. For inserts of strings into numeric columns, conversion from string to number is handled as follows if the string has nonnumeric contents: A string that does not begin with a number cannot be used as a number and produces an error in strict mode, or a warning otherwise. This includes the empty string. A string that begins with a number can be converted, but the trailing nonnumeric portion is truncated. If the truncated portion contains anything other than spaces, this produces an error in strict mode, or a warning otherwise.
12.18.4. Rounding Behavior This section discusses precision math rounding for the ROUND() function and for inserts into columns with exact-value types (DECIMAL and integer). The ROUND() function rounds differently depending on whether its argument is exact or approximate: For exact-value numbers, ROUND() uses the "round half up" rule: A value with a fractional part of .5 or greater is rounded up to the next integer if positive or down to the next integer if negative. (In other words, it is rounded away from zero.) A value with a fractional part less than .5 is rounded down to the next integer if positive or up to the next integer if negative. For approximate-value numbers, the result depends on the C library. On many systems, this means that ROUND() uses the "round to nearest even" rule: A value with any fractional part is rounded to the nearest even integer.
The following example shows how rounding differs for exact and approximate values: mysql> SELECT ROUND(2.5), ROUND(25E-1);+------------+--------------+| ROUND(2.5) | ROUND(25E-1) |+------------+--------------+| 3 | 2 |+------------+--------------+ For inserts into a DECIMAL or integer column, the target is an exact data type, so rounding uses "round half away from zero," regardless of whether the value to be inserted is exact or approximate: mysql> CREATE TABLE t (d DECIMAL(10,0));Query OK, 0 rows affected (0.00 sec)mysql> INSERT INTO t VALUES(2.5),(2.5E0);Query OK, 2 rows affected, 2 warnings (0.00 sec)Records: 2 Duplicates: 0 Warnings: 2mysql> SELECT d FROM t;+------+| d |+------+| 3 || 3 |+------+ 12.18.5. Precision Math Examples This section provides some examples that show precision math query results in MySQL 5.5. These examples demonstrate the principles described in Section 12.18.3, "Expression Handling", and Section 12.18.4, "Rounding Behavior". Example 1. Numbers are used with their exact value as given when possible: mysql> SELECT (.1 + .2) = .3;+----------------+| (.1 + .2) = .3 |+----------------+| 1 |+----------------+ For floating-point values, results are inexact: mysql> SELECT (.1E0 + .2E0) = .3E0;+----------------------+| (.1E0 + .2E0) = .3E0 |+----------------------+| 0 |+----------------------+ Another way to see the difference in exact and approximate value handling is to add a small number to a sum many times. Consider the following stored procedure, which adds .0001 to a variable 1,000 times. CREATE PROCEDURE p ()BEGIN DECLARE i INT DEFAULT 0; DECLARE d DECIMAL(10,4) DEFAULT 0; DECLARE f FLOAT DEFAULT 0; WHILE i < 10000 DO SET d = d + .0001; SET f = f + .0001E0; SET i = i + 1; END WHILE; SELECT d, f;END; The sum for both d and f logically should be 1, but that is true only for the decimal calculation. The floating-point calculation introduces small errors: +--------+------------------+| d | f |+--------+------------------+| 1.0000 | 0.99999999999991 |+--------+------------------+ Example 2. Multiplication is performed with the scale required by standard SQL. That is, for two numbers X1 and X2 that have scale S1 and S2, the scale of the result is S1 + S2: mysql> SELECT .01 * .01;+-----------+| .01 * .01 |+-----------+| 0.0001 |+-----------+ Example 3. Rounding behavior for exact-value numbers is well-defined: Rounding behavior (for example, with the ROUND() function) is independent of the implementation of the underlying C library, which means that results are consistent from platform to platform. Rounding for exact-value columns (DECIMAL and integer) and exact-valued numbers uses the "round half away from zero" rule. Values with a fractional part of .5 or greater are rounded away from zero to the nearest integer, as shown here: mysql> SELECT ROUND(2.5), ROUND(-2.5);+------------+-------------+| ROUND(2.5) | ROUND(-2.5) |+------------+-------------+| 3 | -3 |+------------+-------------+ Rounding for floating-point values uses the C library, which on many systems uses the "round to nearest even" rule. Values with any fractional part on such systems are rounded to the nearest even integer: mysql> SELECT ROUND(2.5E0), ROUND(-2.5E0);+--------------+---------------+| ROUND(2.5E0) | ROUND(-2.5E0) |+--------------+---------------+| 2 | -2 |+--------------+---------------+
Example 4. In strict mode, inserting a value that is out of range for a column causes an error, rather than truncation to a legal value. When MySQL is not running in strict mode, truncation to a legal value occurs: mysql> SET sql_mode='';Query OK, 0 rows affected (0.00 sec)mysql> CREATE TABLE t (i TINYINT);Query OK, 0 rows affected (0.01 sec)mysql> INSERT INTO t SET i = 128;Query OK, 1 row affected, 1 warning (0.00 sec)mysql> SELECT i FROM t;+------+| i |+------+| 127 |+------+1 row in set (0.00 sec) However, an error occurs if strict mode is in effect: mysql> SET sql_mode='STRICT_ALL_TABLES';Query OK, 0 rows affected (0.00 sec)mysql> CREATE TABLE t (i TINYINT);Query OK, 0 rows affected (0.00 sec)mysql> INSERT INTO t SET i = 128;ERROR 1264 (22003): Out of range value adjusted for column 'i' at row 1mysql> SELECT i FROM t;Empty set (0.00 sec) Example 5: In strict mode and with ERROR_FOR_DIVISION_BY_ZERO set, division by zero causes an error, not a result of NULL. In nonstrict mode, division by zero has a result of NULL: mysql> SET sql_mode='';Query OK, 0 rows affected (0.01 sec)mysql> CREATE TABLE t (i TINYINT);Query OK, 0 rows affected (0.00 sec)mysql> INSERT INTO t SET i = 1 / 0;Query OK, 1 row affected (0.00 sec)mysql> SELECT i FROM t;+------+| i |+------+| NULL |+------+1 row in set (0.03 sec) However, division by zero is an error if the proper SQL modes are in effect: mysql> SET sql_mode='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO';Query OK, 0 rows affected (0.00 sec)mysql> CREATE TABLE t (i TINYINT);Query OK, 0 rows affected (0.00 sec)mysql> INSERT INTO t SET i = 1 / 0;ERROR 1365 (22012): Division by 0mysql> SELECT i FROM t;Empty set (0.01 sec) Example 6. Exact-value literals are evaluated as exact values. Prior to MySQL 5.0.3, exact-value and approximate-value literals both are evaluated as double-precision floating-point values: mysql> SELECT VERSION();+------------+| VERSION() |+------------+| 4.1.18-log |+------------+1 row in set (0.01 sec)mysql> CREATE TABLE t SELECT 2.5 AS a, 25E-1 AS b;Query OK, 1 row affected (0.07 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> DESCRIBE t;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| a | double(3,1) | | | 0.0 | || b | double | | | 0 | |+-------+-------------+------+-----+---------+-------+2 rows in set (0.04 sec) As of MySQL 5.0.3, the approximate-value literal is evaluated using floating point, but the exact-value literal is handled as DECIMAL: mysql> SELECT VERSION();+-----------------+| VERSION() |+-----------------+| 5.1.6-alpha-log |+-----------------+1 row in set (0.11 sec)mysql> CREATE TABLE t SELECT 2.5 AS a, 25E-1 AS b;Query OK, 1 row affected (0.01 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> DESCRIBE t;+-------+-----------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-----------------------+------+-----+---------+-------+| a | decimal(2,1) unsigned | NO | | 0.0 | || b | double | NO | | 0 | |+-------+-----------------------+------+-----+---------+-------+2 rows in set (0.01 sec) Example 7. If the argument to an aggregate function is an exact numeric type, the result is also an exact numeric type, with a scale at least that of the argument. Consider these statements: mysql> CREATE TABLE t (i INT, d DECIMAL, f FLOAT);mysql> INSERT INTO t VALUES(1,1,1);mysql> CREATE TABLE y SELECT AVG(i), AVG(d), AVG(f) FROM t; Before MySQL 5.0.3, the result is a double no matter the argument type: mysql> DESCRIBE y;+--------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+--------------+------+-----+---------+-------+| AVG(i) | double(17,4) | YES | | NULL | || AVG(d) | double(17,4) | YES | | NULL | || AVG(f) | double | YES | | NULL | |+--------+--------------+------+-----+---------+-------+ As of MySQL 5.0.3, the result is a double only for the floating-point argument. For exact type arguments, the result is also an exact type: mysql> DESCRIBE y;+--------+---------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+---------------+------+-----+---------+-------+| AVG(i) | decimal(14,4) | YES | | NULL | || AVG(d) | decimal(14,4) | YES | | NULL | || AVG(f) | double | YES | | NULL | |+--------+---------------+------+-----+---------+-------+ The result is a double only for the floating-point argument. For exact type arguments, the result is also an exact type. Copyright © 1997, 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices |
| |
