Creating a Spatially Enabled MySQL Database
Prev Chapter 19. Spatial Extensions in MySQL Next

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.

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 and 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 and MULTILINESTRING and MULTIPOLYGON and GEOMETRYCOLLECTION, restrict collection members to those having a particular geometry type.

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.

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 describes optional functions for constructing Polygon or MultiPolygon values based on the WKT representation of a collection of rings or closed LineString values. These values may intersect. MySQL does not implement these functions:

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.

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.

GeomFromWKT() 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(wkt[,srid]),

Constructs a GEOMETRYCOLLECTION value using its WKB representation and SRID.

GeomFromWKB(wkb[,srid]), GeometryFromWKB(wkt[,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.

Creating Geometry Values Using MySQL-Specific Functions

Note: MySQL does not implement the functions listed in this section.

MySQL provides a set of useful functions for creating geometry WKB representations. The functions described in this section are MySQL extensions to the OpenGIS specifications. The results of these functions are 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 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 rerurn 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.

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 only for MyISAM tables.

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

    mysql> CREATE TABLE geom (g GEOMETRY);
Query OK, 0 rows affected (0.02 sec)

  • Use the ALTER TABLE statement to add or drop a spatial column to or from an existing table:

    mysql> ALTER TABLE geom ADD pt POINT;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> ALTER TABLE geom DROP pt;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

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.

You can 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));

Or you can 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 mysql_real_escape_string().

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.

mysql> SELECT AsText(g) FROM geom;
+-------------------------+
| AsText(p1)              |
+-------------------------+
| POINT(1 1)              |
| LINESTRING(0 0,1 1,2 2) |
+-------------------------+

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;
Prev Up Next
Supported Spatial Data Formats Home Analyzing Spatial Information