16.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.

16.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.

16.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.

16.4.2.1. Creating Geometry Values Using WKT Functions

MySQL provides a number of functions that take as input parameters a Well-Known Text representation and, optionally, a spatial reference system identifier (SRID). They return the corresponding geometry.

GeomFromText() accepts a WKT of any geometry type as its first argument. An implementation also provides type-specific construction functions for construction of geometry values of each geometry type.

  • GeomCollFromText(wkt[,srid]), GeometryCollectionFromText(wkt[,srid])

    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.

16.4.2.2. Creating Geometry Values Using WKB Functions

MySQL provides a number of functions that take as input parameters a BLOB containing a Well-Known Binary representation and, optionally, a spatial reference system identifier (SRID). They return the corresponding geometry.

GeomFromWKB() accepts a WKB of any geometry type as its first argument. An implementation also provides type-specific construction functions for construction of geometry values of each geometry type.

  • GeomCollFromWKB(wkb[,srid]), GeometryCollectionFromWKB(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.

16.4.2.3. Creating Geometry Values Using MySQL-Specific Functions

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 BLOB values containing WKB representations of geometry values with no SRID. The results of these functions can be substituted as the first argument for any function in the GeomFromWKB() function family.

  • GeometryCollection(g1,g2,...)

    Constructs a WKB GeometryCollection. If any argument is not a well-formed WKB representation of a geometry, the return value is NULL.

  • LineString(pt1,pt2,...)

    Constructs a WKB LineString value from a number of WKB Point arguments. If any argument is not a WKB Point, the return value is NULL. If the number of Point arguments is less than two, the return value is NULL.

  • MultiLineString(ls1,ls2,...)

    Constructs a WKB MultiLineString value using WKB LineString arguments. If any argument is not a WKB LineString, the return value is NULL.

  • MultiPoint(pt1,pt2,...)

    Constructs a WKB MultiPoint value using WKB Point arguments. If any argument is not a WKB Point, the return value is NULL.

  • MultiPolygon(poly1,poly2,...)

    Constructs a WKB MultiPolygon value from a set of WKB Polygon arguments. If any argument is not a WKB Polygon, the return value is NULL.

  • Point(x,y)

    Constructs a WKB Point using its coordinates.

  • Polygon(ls1,ls2,...)

    Constructs a WKB Polygon value from a number of WKB LineString arguments. If any argument does not represent the WKB of a LinearRing (that is, not a closed and simple LineString) the return value is NULL.

16.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, BDB, and ARCHIVE tables. (Support for storage engines other than MyISAM was added in MySQL 5.0.16.) See also the annotations about spatial indexes under Section 16.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;

16.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.2.3.52, “mysql_real_escape_string()”.

16.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;