[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

11.4 Creating a Spatially Enabled MySQL Database

This section describes the datatypes you can use for representing spatial data in MySQL, and the functions available for creating and retrieving spatial values.


11.4.1 MySQL Spatial Datatypes

MySQL provides a set of datatypes that correspond to classes in the class hierarchy of the OpenGIS Geometry Model. Some of these types hold single geometry values:

GEOMETRY is the most general of these single-value types; it can store geometry values of any type. The others restrict their values to a particular geometry type.

The other datatypes hold collections of values:

GEOMETRYCOLLECTION can store a collection of objects of any type. The other collection types restrict collection members to those having a particular geometry type.


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


11.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)), and 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.

GeomFromText(wkt[,srid])
GeometryFromText(wkt[,srid])

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

PointFromText(wkt[,srid])

Constructs a POINT value using its WKT representation and SRID.

LineFromText(wkt[,srid])
LineStringFromText(wkt[,srid])

Constructs a LINESTRING value using its WKT representation and SRID.

PolyFromText(wkt[,srid])
PolygonFromText(wkt[,srid])

Constructs a POLYGON value using its WKT representation and SRID.

MPointFromText(wkt[,srid])
MultiPointFromText(wkt[,srid])

Constructs a MULTIPOINT value using its WKT representation and SRID.

MLineFromText(wkt[,srid])
MultiLineStringFromText(wkt[,srid])

Constructs a MULTILINESTRING value using its WKT representation and SRID.

MPolyFromText(wkt[,srid])
MultiPolygonFromText(wkt[,srid])

Constructs a MULTIPOLYGON value using its WKT representation and SRID.

GeomCollFromText(wkt[,srid])
GeometryCollectionFromText(wkt[,srid])

Constructs a GEOMETRYCOLLECTION 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 yet implement these functions:

BdPolyFromText(wkt,srid)

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

BdMPolyFromText(wkt,srid)

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


11.4.2.2 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)), and 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.

GeomFromWKB(wkb[,srid])
GeometryFromWKB(wkt[,srid])

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

PointFromWKB(wkb[,srid])

Constructs a POINT value using its WKB representation and SRID.

LineFromWKB(wkb[,srid])
LineStringFromWKB(wkb[,srid])

Constructs a LINESTRING value using its WKB representation and SRID.

PolyFromWKB(wkb[,srid])
PolygonFromWKB(wkb[,srid])

Constructs a POLYGON value using its WKB representation and SRID.

MPointFromWKB(wkb[,srid])
MultiPointFromWKB(wkb[,srid])

Constructs a MULTIPOINT value using its WKB representation and SRID.

MLineFromWKB(wkb[,srid])
MultiLineStringFromWKB(wkb[,srid])

Constructs a MULTILINESTRING value using its WKB representation and SRID.

MPolyFromWKB(wkb[,srid])
MultiPolygonFromWKB(wkb[,srid])

Constructs a MULTIPOLYGON value using its WKB representation and SRID.

GeomCollFromWKB(wkb[,srid])
GeometryCollectionFromWKB(wkt[,srid])

Constructs a GEOMETRYCOLLECTION 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 yet implement these functions:

BdPolyFromWKB(wkb,srid)

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

BdMPolyFromWKB(wkb,srid)

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


11.4.2.3 Creating Geometry Values Using MySQL-Specific Functions

Note: MySQL does not yet 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.

Point(x,y)

Constructs a WKB Point using its coordinates.

MultiPoint(pt1,pt2,...)

Constructs a WKB MultiPoint value using WKB Point arguments. If any argument is not a WKBPoint, the return value is NULL.

LineString(pt1,pt2,...)

Constructs a WKB LineString valeu 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 WBK LineString arguments. If any argument is not a LineString, the return value is NULL.

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.

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.

GeometryCollection(g1,g2,...)

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


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


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

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 conversion can take place 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:


11.4.5 Fetching Spatial Data

Geometry values stored in a table can be fetched with conversion in internal format. You can also convert them into WKT or WKB format.


11.4.5.1 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;

11.4.5.2 Fetching Spatial Data in WKT Format

The AsText() function provides textual access to geometry values. It 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) |
+-------------------------+

11.4.5.3 Fetching Spatial Data in WKB Format

The AsBinary() function provides binary access to geometry values. It converts a geometry from internal format into a BLOB containing the WKB value.

 
SELECT AsBinary(g) FROM geom;

[ < ] [ > ]   [ << ] [ Up ] [ >> ]


Hosting by: Hurra Communications Ltd.
Generated: 2007-01-26 17:58:46