Skip navigation links
11.13.4.3 Creating Spatial Columns »
Section Navigation      [Toggle]

11.13.4.2. Creating Spatial Values

[+/-]

11.13.4.2.1. Creating Geometry Values Using WKT Functions
11.13.4.2.2. Creating Geometry Values Using WKB Functions
11.13.4.2.3. Creating Geometry Values Using MySQL-Specific Functions

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.

11.13.4.2.1. Creating Geometry Values Using WKT Functions

MySQL provides a number of functions that take as arguments 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.

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.

11.13.4.2.2. Creating Geometry Values Using WKB Functions

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

As of MySQL 5.1.35, these functions also accept geometry objects for compatibility with the changes made in MySQL 5.1.35 to the return value of the functions in Section 11.13.4.2.3, “Creating Geometry Values Using MySQL-Specific Functions”. Thus, those functions may continue to be used to provide the first argument to the functions in this section.

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.

11.13.4.2.3. Creating Geometry Values Using MySQL-Specific Functions

MySQL provides a set of useful nonstandard functions for creating geometry values. The functions described in this section are MySQL extensions to the OpenGIS specification.

As of MySQL 5.1.35, these functions produce geometry objects from either WKB values or geometry objects as arguments. If any argument is not a proper WKB or geometry representation of the proper object type, the return value is NULL.

Before MySQL 5.1.35, these functions produce BLOB values containing WKB representations of geometry values with no SRID from WKB arguments. The WKB value returned from these functions can be converted to geometry arguments by using them as the first argument to functions in the GeomFromWKB() function family.

For example, as of MySQL 5.1.35, you can insert the geometry return value from Point() directly into a Point column: 

INSERT INTO t1 (pt_col) VALUES(Point(1,2));

Prior to MySQL 5.1.35, convert the WKB return value to a Point before inserting it: 

INSERT INTO t1 (pt_col) VALUES(GeomFromWKB(Point(1,2)));

  • GeometryCollection(g1,g2,...)

    Constructs a GeometryCollection.

  • LineString(pt1,pt2,...)

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

  • MultiLineString(ls1,ls2,...)

    Constructs a MultiLineString value using LineString or WKB LineString arguments.

  • MultiPoint(pt1,pt2,...)

    Constructs a MultiPoint value using Point or WKB Point arguments.

  • MultiPolygon(poly1,poly2,...)

    Constructs a MultiPolygon value from a set of Polygon or WKB Polygon arguments.

  • Point(x,y)

    Constructs a Point using its coordinates.

  • Polygon(ls1,ls2,...)

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