![]() |
|
[ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top] | [Contents] | [Index] | [ ? ] |
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 | ||
11.4.2 Creating Spatial Values | ||
11.4.3 Creating Spatial Columns | ||
11.4.4 Populating Spatial Columns | ||
11.4.5 Fetching Spatial Data |
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
POINT
LINESTRING
POLYGON
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:
MULTIPOINT
MULTILINESTRING
MULTIPOLYGON
GEOMETRYCOLLECTION
GEOMETRYCOLLECTION
can store a collection of objects
of any type. The other collection types
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.
11.4.2.1 Creating Geometry Values Using WKT Functions | ||
11.4.2.2 Creating Geometry Values Using WKB Functions | ||
11.4.2.3 Creating Geometry Values 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)), 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.
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.
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
.
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.
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) |
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 |
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:
POINT(1 1)
value with hex literal syntax:
mysql> INSERT INTO geom VALUES -> (GeomFromWKB(0x0101000000000000000000F03F000000000000F03F)); |
BLOB
type:
INSERT INTO geom VALUES (GeomFromWKB(?)) |
Other programming interfaces may support a similar placeholder mechanism.
mysql_real_escape_string()
and include the result in a query string
that is sent to the server.
See section mysql_real_escape_string()
.
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 | ||
11.4.5.2 Fetching Spatial Data in WKT Format | ||
11.4.5.3 Fetching Spatial Data in WKB Format |
Fetching geometry values using internal format can be useful in table-to-table transfers:
CREATE TABLE geom2 (g GEOMETRY) SELECT g FROM geom; |
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) | +-------------------------+ |
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