Chapter聽17.聽Spatial Extensions - MySQL 5.0参考手册英文版

Geometry (non-instantiable)

Point represents zero-dimensional objects.

Curve represents one-dimensional objects, and has subclass LineString, with sub-subclasses Line and LinearRing.

Surface is designed for two-dimensional objects and has subclass Polygon.

GeometryCollection has specialized zero-, one-, and two-dimensional collection classes named MultiPoint, MultiLineString, and MultiPolygon for modeling geometries corresponding to collections of Points, LineStrings, and Polygons, respectively. MultiCurve and MultiSurface are introduced as abstract superclasses that generalize the collection interfaces to handle Curves and Surfaces.

Its type. Each geometry belongs to one of the instantiable classes in the hierarchy.

Its SRID, or Spatial Reference Identifier. This value identifies the geometry's associated Spatial Reference System that describes the coordinate space in which the geometry object is defined.

In MySQL, the SRID value is just an integer associated with the geometry value. All calculations are done assuming Euclidean (planar) geometry.

Its coordinates in its Spatial Reference System, represented as double-precision (eight-byte) numbers. All non-empty geometries include at least one pair of (X,Y) coordinates. Empty geometries contain no coordinates.

Coordinates are related to the SRID. For example, in different coordinate systems, the distance between two objects may differ even when objects have the same coordinates, because the distance on the planar coordinate system and the distance on the geocentric system (coordinates on the Earth's surface) are different things.

Its interior, boundary, and exterior.

Every geometry occupies some position in space. The exterior of a geometry is all space not occupied by the geometry. The interior is the space occupied by the geometry. The boundary is the interface between the geometry's interior and exterior.

Its MBR (Minimum Bounding Rectangle), or Envelope. This is the bounding geometry, formed by the minimum and maximum (X,Y) coordinates:

((MINX MINY, MAXX MINY, MAXX MAXY, MINX MAXY, MINX MINY))

Whether the value is simple or non-simple. Geometry values of types (LineString, MultiPoint, MultiLineString) are either simple or non-simple. Each type determines its own assertions for being simple or non-simple.

Whether the value is closed or not closed. Geometry values of types (LineString, MultiString) are either closed or not closed. Each type determines its own assertions for being closed or not closed.

Whether the value is empty or non-empty A geometry is empty if it does not have any points. Exterior, interior, and boundary of an empty geometry are not defined (that is, they are represented by a NULL value). An empty geometry is defined to be always simple and has an area of 0.

Its dimension. A geometry can have a dimension of 鈥�1, 0, 1, or 2:

Point objects have a dimension of zero. LineString objects have a dimension of 1. Polygon objects have a dimension of 2. The dimensions of MultiPoint, MultiLineString, and MultiPolygon objects are the same as the dimensions of the elements they consist of.

Imagine a large-scale map of the world with many cities. A Point object could represent each city.

On a city map, a Point object could represent a bus stop.

X-coordinate value.

Y-coordinate value.

Point is defined as a zero-dimensional geometry.

The boundary of a Point is the empty set.

A Curve has the coordinates of its points.

A Curve is defined as a one-dimensional geometry.

A Curve is simple if it does not pass through the same point twice.

A Curve is closed if its start point is equal to its endpoint.

The boundary of a closed Curve is empty.

The boundary of a non-closed Curve consists of its two endpoints.

A Curve that is simple and closed is a LinearRing.

On a world map, LineString objects could represent rivers.

In a city map, LineString objects could represent streets.

A LineString has coordinates of segments, defined by each consecutive pair of points.

A LineString is a Line if it consists of exactly two points.

A LineString is a LinearRing if it is both closed and simple.

A Surface is defined as a two-dimensional geometry.

The OpenGIS specification defines a simple Surface as a geometry that consists of a single 鈥�patch鈥� that is associated with a single exterior boundary and zero or more interior boundaries.

The boundary of a simple Surface is the set of closed curves corresponding to its exterior and interior boundaries.

On a region map, Polygon objects could represent forests, districts, and so on.

The boundary of a Polygon consists of a set of LinearRing objects (that is, LineString objects that are both simple and closed) that make up its exterior and interior boundaries.

A Polygon has no rings that cross. The rings in the boundary of a Polygon may intersect at a Point, but only as a tangent.

A Polygon has no lines, spikes, or punctures.

A Polygon has an interior that is a connected point set.

A Polygon may have holes. The exterior of a Polygon with holes is not connected. Each hole defines a connected component of the exterior.

Element type (for example, a MultiPoint may contain only Point elements)

Dimension

Constraints on the degree of spatial overlap between elements

On a world map, a MultiPoint could represent a chain of small islands.

On a city map, a MultiPoint could represent the outlets for a ticket office.

A MultiPoint is a zero-dimensional geometry.

A MultiPoint is simple if no two of its Point values are equal (have identical coordinate values).

The boundary of a MultiPoint is the empty set.

A MultiCurve is a one-dimensional geometry.

A MultiCurve is simple if and only if all of its elements are simple; the only intersections between any two elements occur at points that are on the boundaries of both elements.

A MultiCurve boundary is obtained by applying the 鈥�mod 2 union rule鈥� (also known as the 鈥�odd-even rule鈥�): A point is in the boundary of a MultiCurve if it is in the boundaries of an odd number of MultiCurve elements.

A MultiCurve is closed if all of its elements are closed.

The boundary of a closed MultiCurve is always empty.

On a region map, a MultiLineString could represent a river system or a highway system.

Two MultiSurface surfaces have no interiors that intersect.

Two MultiSurface elements have boundaries that intersect at most at a finite number of points.

On a region map, a MultiPolygon could represent a system of lakes.

A MultiPolygon has no two Polygon elements with interiors that intersect.

A MultiPolygon has no two Polygon elements that cross (crossing is also forbidden by the previous assertion), or that touch at an infinite number of points.

A MultiPolygon may not have cut lines, spikes, or punctures. A MultiPolygon is a regular, closed point set.

A MultiPolygon that has more than one Polygon has an interior that is not connected. The number of connected components of the interior of a MultiPolygon is equal to the number of Polygon values in the MultiPolygon.

A MultiPolygon is a two-dimensional geometry.

A MultiPolygon boundary is a set of closed curves (LineString values) corresponding to the boundaries of its Polygon elements.

Each Curve in the boundary of the MultiPolygon is in the boundary of exactly one Polygon element.

Every Curve in the boundary of an Polygon element is in the boundary of the MultiPolygon.

A Point:

POINT(15 20)

Note that point coordinates are specified with no separating comma.

A LineString with four points:

LINESTRING(0 0, 10 10, 20 25, 50 60)

Note that point coordinate pairs are separated by commas.

A Polygon with one exterior ring and one interior ring:

POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))

A MultiPoint with three Point values:

MULTIPOINT(0 0, 20 20, 60 60)

A MultiLineString with two LineString values:

MULTILINESTRING((10 10, 20 20), (15 15, 30 15))

A MultiPolygon with two Polygon values:

MULTIPOLYGON(((0 0,10 0,10 10,0 10,0 0)),((5 5,7 5,7 7,5 7, 5 5)))

A GeometryCollection consisting of two Point values and one LineString:

GEOMETRYCOLLECTION(POINT(10 10), POINT(30 30), LINESTRING(15 15, 20 20))

The byte order may be either 0 or 1 to indicate little-endian or big-endian storage. The little-endian and big-endian byte orders are also known as Network Data Representation (NDR) and External Data Representation (XDR), respectively.

The WKB type is a code that indicates the geometry type. Values from 1 through 7 indicate Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon, and GeometryCollection.

A Point value has X and Y coordinates, each represented as a double-precision value.

GEOMETRY

POINT

LINESTRING

POLYGON

MULTIPOINT

MULTILINESTRING

MULTIPOLYGON

GEOMETRYCOLLECTION

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;

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

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聽24.2.3.53, 鈥�mysql_real_escape_string()鈥�.

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;

AsBinary(g)

Converts a value in internal geometry format to its WKB representation and returns the binary result.

SELECT AsBinary(g) FROM geom;

AsText(g)

Converts a value in internal geometry format to its WKT representation and returns the string result.

mysql> SET @g = 'LineString(1 1,2 2,3 3)';
mysql> SELECT AsText(GeomFromText(@g));
+--------------------------+
| AsText(GeomFromText(@g)) |
+--------------------------+
| LINESTRING(1 1,2 2,3 3)  |
+--------------------------+

GeomFromText(wkt[,srid])

Converts a string value from its WKT representation into internal geometry format and returns the result. A number of type-specific functions are also supported, such as PointFromText() and LineFromText(). See Section聽17.4.2.1, 鈥淐reating Geometry Values Using WKT Functions鈥�.

GeomFromWKB(wkb[,srid])

Converts a binary value from its WKB representation into internal geometry format and returns the result. A number of type-specific functions are also supported, such as PointFromWKB() and LineFromWKB(). See Section聽17.4.2.2, 鈥淐reating Geometry Values Using WKB Functions鈥�.

MBRContains(g1,g2)

Returns 1 or 0 to indicate whether the Minimum Bounding Rectangle of g1 contains the Minimum Bounding Rectangle of g2. This tests the opposite relationship as MBRWithin().

mysql> SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))');
mysql> SET @g2 = GeomFromText('Point(1 1)');
mysql> SELECT MBRContains(@g1,@g2), MBRContains(@g2,@g1);
----------------------+----------------------+
| MBRContains(@g1,@g2) | MBRContains(@g2,@g1) |
+----------------------+----------------------+
|                    1 |                    0 |
+----------------------+----------------------+

MBRDisjoint(g1,g2)

Returns 1 or 0 to indicate whether the Minimum Bounding Rectangles of the two geometries g1 and g2 are disjoint (do not intersect).

MBREqual(g1,g2)

Returns 1 or 0 to indicate whether the Minimum Bounding Rectangles of the two geometries g1 and g2 are the same.

MBRIntersects(g1,g2)

Returns 1 or 0 to indicate whether the Minimum Bounding Rectangles of the two geometries g1 and g2 intersect.

MBROverlaps(g1,g2)

Returns 1 or 0 to indicate whether the Minimum Bounding Rectangles of the two geometries g1 and g2 overlap. The term spatially overlaps is used if two geometries intersect and their intersection results in a geometry of the same dimension but not equal to either of the given geometries.

MBRTouches(g1,g2)

Returns 1 or 0 to indicate whether the Minimum Bounding Rectangles of the two geometries g1 and g2 touch. Two geometries spatially touch if the interiors of the geometries do not intersect, but the boundary of one of the geometries intersects either the boundary or the interior of the other.

MBRWithin(g1,g2)

Returns 1 or 0 to indicate whether the Minimum Bounding Rectangle of g1 is within the Minimum Bounding Rectangle of g2. This tests the opposite relationship as MBRWithin().

mysql> SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))');
mysql> SET @g2 = GeomFromText('Polygon((0 0,0 5,5 5,5 0,0 0))');
mysql> SELECT MBRWithin(@g1,@g2), MBRWithin(@g2,@g1);
+--------------------+--------------------+
| MBRWithin(@g1,@g2) | MBRWithin(@g2,@g1) |
+--------------------+--------------------+
|                  1 |                  0 |
+--------------------+--------------------+

Contains(g1,g2)

Returns 1 or 0 to indicate whether g1 completely contains g2. This tests the opposite relationship as Within().

Crosses(g1,g2)

Returns 1 if g1 spatially crosses g2. Returns NULL if g1 is a Polygon or a MultiPolygon, or if g2 is a Point or a MultiPoint. Otherwise, returns 0.

The term spatially crosses denotes a spatial relation between two given geometries that has the following properties:

Disjoint(g1,g2)

Returns 1 or 0 to indicate whether g1 is spatially disjoint from (does not intersect) g2.

Distance(g1,g2)

Returns as a double-precision number the shortest distance between any two points in the two geometries.

Equals(g1,g2)

Returns 1 or 0 to indicate whether g1 is spatially equal to g2.

Intersects(g1,g2)

Returns 1 or 0 to indicate whether g1 spatially intersects g2.

Overlaps(g1,g2)

Returns 1 or 0 to indicate whether g1 spatially overlaps g2. The term spatially overlaps is used if two geometries intersect and their intersection results in a geometry of the same dimension but not equal to either of the given geometries.

Related(g1,g2,pattern_matrix)

Returns 1 or 0 to indicate whether the spatial relationship specified by pattern_matrix exists between g1 and g2. Returns 鈥�1 if the arguments are NULL. The pattern matrix is a string. Its specification will be noted here if this function is implemented.

Touches(g1,g2)

Returns 1 or 0 to indicate whether g1 spatially touches g2. Two geometries spatially touch if the interiors of the geometries do not intersect, but the boundary of one of the geometries intersects either the boundary or the interior of the other.

Within(g1,g2)

Returns 1 or 0 to indicate whether g1 is spatially within g2. This tests the opposite relationship as Contains().

With CREATE TABLE:

CREATE TABLE geom (g GEOMETRY NOT NULL, SPATIAL INDEX(g));

With ALTER TABLE:

ALTER TABLE geom ADD SPATIAL INDEX(g);

With CREATE INDEX:

CREATE SPATIAL INDEX sp_index ON geom (g);

With ALTER TABLE:

ALTER TABLE geom DROP INDEX g;

With DROP INDEX:

DROP INDEX sp_index ON geom;