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.
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'
* `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.
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.
Creating Geometry Values Using WKT Functions
............................................
`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.
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.
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.
Creating Geometry Values Using MySQL-Specific Functions
.......................................................
*Note*: MySQL does not yet implement the functions listed in this
section.
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'.
`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'.
Creating Spatial Columns
------------------------
* Use the `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)
* Use the `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
Populating Spatial Columns
--------------------------
After you have created spatial columns, you can populate them with
spatial data.
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:
* 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. *Note
`mysql_real_escape_string()': mysql_real_escape_string.
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.
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 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) |
+-------------------------+
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;
[Назад] [Содержание] [Вперед]
| Главная |