16.4. Creating a Spatially Enabled MySQL Database

MySQL 5.0

previous page next page

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:

can store geometry values of any type. The other single-value types (, , and ) restrict their values to a particular geometry type.

The other data types hold collections of values:

can store a collection of objects of any type. The other collection types (, , , and ) 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.

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.

  • [,]), [,])

    Constructs a value using its WKT representation and SRID.

  • [,]), [,])

    Constructs a geometry value of any type using its WKT representation and SRID.

  • [,]), [,])

    Constructs a value using its WKT representation and SRID.

  • [,]), [,])

    Constructs a value using its WKT representation and SRID.

  • [,]), [,])

    Constructs a value using its WKT representation and SRID.

  • [,]), [,])

    Constructs a value using its WKT representation and SRID.

  • [,])

    Constructs a value using its WKT representation and SRID.

  • [,]), [,])

    Constructs a value using its WKT representation and SRID.

The OpenGIS specification also defines the following optional functions, which MySQL does not implement. These functions construct or values based on the WKT representation of a collection of rings or closed values. These values may intersect.

  • ,)

    Constructs a value from a value in WKT format containing an arbitrary collection of closed values.

  • ,)

    Constructs a value from a value in WKT format containing an arbitrary collection of closed values.

16.4.2.2. Creating Geometry Values Using WKB Functions

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

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.

  • [,]), [,])

    Constructs a value using its WKB representation and SRID.

  • [,]), [,])

    Constructs a geometry value of any type using its WKB representation and SRID.

  • [,]), [,])

    Constructs a value using its WKB representation and SRID.

  • [,]), [,])

    Constructs a value using its WKB representation and SRID.

  • [,]), [,])

    Constructs a value using its WKB representation and SRID.

  • [,]), [,])

    Constructs a value using its WKB representation and SRID.

  • [,])

    Constructs a value using its WKB representation and SRID.

  • [,]), [,])

    Constructs a value using its WKB representation and SRID.

The OpenGIS specification also describes optional functions for constructing or values based on the WKB representation of a collection of rings or closed values. These values may intersect. MySQL does not implement these functions:

  • ,)

    Constructs a value from a value in WKB format containing an arbitrary collection of closed values.

  • ,)

    Constructs a value from a value in WKB format containing an arbitrary collection of closed 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 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 function family.

  • ,,...)

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

  • ,,...)

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

  • ,,...)

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

  • ,,...)

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

  • ,,...)

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

  • ,)

    Constructs a WKB using its coordinates.

  • ,,...)

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

16.4.3. Creating Spatial Columns

MySQL provides a standard way of creating spatial columns for geometry types, for example, with or . Currently, spatial columns are supported for , , , , and tables. (Support for storage engines other than was added in MySQL 5.0.16.) See also the annotations about spatial indexes under Section 16.6.1, “Creating Spatial Indexes”.

  • Use the statement to create a table with a spatial column: 

    CREATE TABLE geom (g GEOMETRY);

  • Use the 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 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 : 

    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 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 value with hex literal syntax: 

    mysql> 
    ->

  • An ODBC application can send a WKB representation, binding it to a placeholder using an argument of 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 and include the result in a query string that is sent to the server. See Section 22.2.3.52, “”.

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 function converts a geometry from internal format into a WKT string. 

    SELECT AsText(g) FROM geom;

  • Fetching spatial data in WKB format:

    The function converts a geometry from internal format into a containing the WKB value. 

    SELECT AsBinary(g) FROM geom;
previous page start next page