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

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.

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

Constructs a

`MultiPolygon`

value from a`MultiLineString`

value in WKT format containing an arbitrary collection of closed`LineString`

values.Constructs a

`Polygon`

value from a`MultiLineString`

value in WKT format containing an arbitrary collection of closed`LineString`

values.

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

Constructs a

`MultiPolygon`

value from a`MultiLineString`

value in WKB format containing an arbitrary collection of closed`LineString`

values.Constructs a

`Polygon`

value from a`MultiLineString`

value in WKB format containing an arbitrary collection of closed`LineString`

values.

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.

Constructs a WKB

`GeometryCollection`

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

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

.Constructs a WKB

`MultiLineString`

value using WKB`LineString`

arguments. If any argument is not a WKB`LineString`

, the return value is`NULL`

.Constructs a WKB

`MultiPoint`

value using WKB`Point`

arguments. If any argument is not a WKB`Point`

, the return value is`NULL`

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

.Constructs a WKB

`Point`

using its coordinates.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`

.

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;

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()`

”.

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;