Source code editor What Is Ajax

↑

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.53, “`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;