![]() |
|
[ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] | [Top] | [Contents] | [Index] | [ ? ] |
After populating spatial columns with values, you are ready to query and analyse them. MySQL provides a set of functions to perform various operations on spatial data. These functions can be grouped into four major categories according to the type of operation they perform:
Spatial analysis functions can be used in many contexts, such as:
mysql
or MySQLCC
MySQL supports the following functions for converting geometry values between internal format and either WKT or WKB format:
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
Creating 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
Creating Geometry Values Using WKB Functions.
AsText(g)
Converts a value in internal geometry format to its WKT representation and returns the resulting string.
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) | +--------------------------+ |
AsBinary(g)
Converts a value in internal geometry format to its WKB representation and returns the resulting binary value.
Geometry
Property Analysis Functions Each function that belongs to this group takes a geometry value as its
argument and returns some quantitive or qualitive property of the
geometry. Some functions restrict their argument type. Such functions
return NULL
if the argument is of an incorrect geometry
type. For example, Area()
returns NULL
if the object
type is neither Polygon
nor MultiPolygon
.
The functions listed in this ssection do not restrict their argument and accept a geometry value of any type.
GeometryType(g)
Returns as a string the name of the geometry type of which
the geometry instance g
is a member.
The name will correspond to one of the instantiable Geometry
subclasses.
mysql> SELECT GeometryType(GeomFromText('POINT(1 1)')); +------------------------------------------+ | GeometryType(GeomFromText('POINT(1 1)')) | +------------------------------------------+ | POINT | +------------------------------------------+ |
Dimension(g)
Returns the inherent dimension of the geometry value g
. The result
can be -1, 0, 1, or 2. (The meaning of these values is given in
Class Geometry
.)
mysql> SELECT Dimension(GeomFromText('LineString(1 1,2 2)')); +------------------------------------------------+ | Dimension(GeomFromText('LineString(1 1,2 2)')) | +------------------------------------------------+ | 1 | +------------------------------------------------+ |
SRID(g)
Returns an integer indicating the Spatial Reference System ID for the geometry
value g
.
mysql> SELECT SRID(GeomFromText('LineString(1 1,2 2)',101)); +-----------------------------------------------+ | SRID(GeomFromText('LineString(1 1,2 2)',101)) | +-----------------------------------------------+ | 101 | +-----------------------------------------------+ |
Envelope(g)
Returns the Minimum Bounding Rectangle (MBR) for the geometry value g
.
The result is returned as a polygon value.
mysql> SELECT AsText(Envelope(GeomFromText('LineString(1 1,2 2)'))); +-------------------------------------------------------+ | AsText(Envelope(GeomFromText('LineString(1 1,2 2)'))) | +-------------------------------------------------------+ | POLYGON((1 1,2 1,2 2,1 2,1 1)) | +-------------------------------------------------------+ |
The polygon is defined by the corner points of the bounding box:
POLYGON((MINX MINY, MAXX MINY, MAXX MAXY, MINX MAXY, MINX MINY)) |
The OpenGIS specification also defines the following functions, which MySQL does not yet implement:
Boundary(g)
Returns a geometry that is the closure of the combinatorial boundary of the
geometry value g
.
IsEmpty(g)
Returns 1 if the geomtry value g
is the empty geometry, 0 if it is not
empty, and -1 if the argument is NULL
.
If the geometry is empty, it represents the empty point set.
IsSimple(g)
Returns 1 if the geometry value g
has no anomalous geometric points,
such as self intersection or self tangency. IsSimple()
returns 0 if the
argument is not simple, and -1 if it is NULL
.
The description of each instantiable geometric class given earlier in the chapter includes the specific conditions that cause an instance of that class to be classified as not simple.
Point
Property Analysis Functions A Point
consists of its X and Y coordinates, which may be obtained
using the following functions:
X(p)
Returns the X-coordinate value for the point p
as a double-precision
number.
mysql> SELECT X(GeomFromText('Point(56.7 53.34)')); +--------------------------------------+ | X(GeomFromText('Point(56.7 53.34)')) | +--------------------------------------+ | 56.7 | +--------------------------------------+ |
Y(p)
Returns the Y-coordinate value for the point p
as a double-precision
number.
mysql> SELECT Y(GeomFromText('Point(56.7 53.34)')); +--------------------------------------+ | Y(GeomFromText('Point(56.7 53.34)')) | +--------------------------------------+ | 53.34 | +--------------------------------------+ |
LineString
Property Analysis Functions A LineString
consists of Point
values. You can extract
particular points of a LineString
, count the number of points that it
contains, or obtain its length.
EndPoint(ls)
Returns the Point
that is the end point of the LineString
value
ls
.
mysql> SELECT AsText(EndPoint(GeomFromText('LineString(1 1,2 2,3 3)'))); +------------------------------------------------------------+ | AsText(EndPoint(GeomFromText('LineString(1 1,2 2,3 3)'))) | +------------------------------------------------------------+ | POINT(3 3) | +------------------------------------------------------------+ |
GLength(ls)
Returns as a double-precision number the length of the LineString
value ls
in its associated spatial reference.
mysql> SELECT GLength(GeomFromText('LineString(1 1,2 2,3 3)')); +--------------------------------------------------+ | GLength(GeomFromText('LineString(1 1,2 2,3 3)')) | +--------------------------------------------------+ | 2.8284271247462 | +--------------------------------------------------+ |
IsClosed(ls)
Returns 1 if the LineString
value ls
is closed
(that is, it sStartPoint()
and EndPoint()
values are the same).
Returns 0 if ls
is not closed, and -1 if it is NULL
.
mysql> SELECT IsClosed(GeomFromText('LineString(1 1,2 2,3 3)')); +---------------------------------------------------+ | IsClosed(GeomFromText('LineString(1 1,2 2,3 3)')) | +---------------------------------------------------+ | 0 | +---------------------------------------------------+ |
NumPoints(ls)
Returns the number of points in the LineString
value ls
.
mysql> SELECT NumPoints(GeomFromText('LineString(1 1,2 2,3 3)')); +----------------------------------------------------+ | NumPoints(GeomFromText('LineString(1 1,2 2,3 3)')) | +----------------------------------------------------+ | 3 | +----------------------------------------------------+ |
PointN(ls,n)
Returns the n
-th point in the Linestring
value ls
.
Point numbers begin at 1.
mysql> SELECT AsText(PointN(GeomFromText('LineString(1 1,2 2,3 3)'),2)); +-----------------------------------------------------------+ | AsText(PointN(GeomFromText('LineString(1 1,2 2,3 3)'),2)) | +-----------------------------------------------------------+ | POINT(2 2) | +-----------------------------------------------------------+ |
StartPoint(ls)
Returns the Point
that is the start point of the LineString
value
ls
.
mysql> SELECT AsText(StartPoint(GeomFromText('LineString(1 1,2 2,3 3)'))); +-------------------------------------------------------------+ | AsText(StartPoint(GeomFromText('LineString(1 1,2 2,3 3)'))) | +-------------------------------------------------------------+ | POINT(1 1) | +-------------------------------------------------------------+ |
The OpenGIS specification also defines the following function, which MySQL does not yet implement:
IsRing(ls)
Returns 1 if the LineString
value ls
is closed
(thatis, its StartPoint()
and EndPoint()
values are the same)
and is simple (does not pass through the same point more than once).
Returns 0 if ls
is not a ring, and -1 if it is NULL
.
MultiLineString
Property Analysis Functions GLength(mls)
Returns as a double-precision number
the length of the MultiLineString
value mls
. The length of
mls
is equal to the sum of the lengths of its elements.
mysql> SELECT GLength(GeomFromText('MultiLineString((1 1,2 2,3 3),(4 4,5 5))')); +-------------------------------------------------------------------+ | GLength(GeomFromText('MultiLineString((1 1,2 2,3 3),(4 4,5 5))')) | +-------------------------------------------------------------------+ | 4.2426406871193 | +-------------------------------------------------------------------+ |
IsClosed(mls)
Returns 1 if the MultiLineString
value mls
is closed
(that is, the StartPoint()
and EndPoint()
values are the same
for each LineString
in mls
).
Returns 0 if mls
is not closed, and -1 if it is NULL
.
mysql> SELECT IsClosed(GeomFromText('MultiLineString((1 1,2 2,3 3),(4 4,5 5))')); +--------------------------------------------------------------------+ | IsClosed(GeomFromText('MultiLineString((1 1,2 2,3 3),(4 4,5 5))')) | +--------------------------------------------------------------------+ | 0 | +--------------------------------------------------------------------+ |
Polygon
Property Analysis Functions Area(poly)
Returns as a double-precision number the area of the Polygon
value
poly
, as measured in its spatial reference system.
mysql> SELECT Area(GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))')); +----------------------------------------------------------------------------+ | Area(GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))')) | +----------------------------------------------------------------------------+ | 8 | +----------------------------------------------------------------------------+ |
NumInteriorRings(poly)
Returns the number of interior rings in the Polygon
value poly
.
mysql> SELECT NumInteriorRings(GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))')); +----------------------------------------------------------------------------------------+ | NumInteriorRings(GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))')) | +----------------------------------------------------------------------------------------+ | 1 | +----------------------------------------------------------------------------------------+ |
ExteriorRing(poly)
Returns the exterior ring of the Polygon
value poly
as a LineString
.
mysql> SELECT AsText(ExteriorRing(GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))'))); +--------------------------------------------------------------------------------------------+ | AsText(ExteriorRing(GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))'))) | +--------------------------------------------------------------------------------------------+ | LINESTRING(0 0,0 3,3 3,3 0,0 0) | +--------------------------------------------------------------------------------------------+ |
InteriorRingN(poly,n)
Returns the n
-th interior ring for the Polygon
value
poly
as a LineString
.
Ring numbers begin at 1.
mysql> SELECT AsText(InteriorRingN(GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))'),1)); +-----------------------------------------------------------------------------------------------+ | AsText(InteriorRingN(GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))'),1)) | +-----------------------------------------------------------------------------------------------+ | LINESTRING(1 1,1 2,2 2,2 1,1 1) | +-----------------------------------------------------------------------------------------------+ |
The OpenGIS specification also defines the following functions, which MySQL does not yet implement:
Centroid(poly)
Returns the mathematical centroid for the Polygon
value poly
as a Point
. The result is not guaranteed to be on the polygon.
PointOnSurface(poly)
Returns a Point
value that is guaranteed to be on the Polygon
value poly
.
MultiPolygon
Property Analysis Functions Area(mpoly)
Returns as a double-precision number the area of the MultiPolygon
value mpoly
, as measured in its spatial reference system.
mysql> SELECT Area(GeomFromText('MultiPolygon(((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1)))')); +-----------------------------------------------------------------------------------+ | Area(GeomFromText('MultiPolygon(((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1)))')) | +-----------------------------------------------------------------------------------+ | 8 | +-----------------------------------------------------------------------------------+ |
The OpenGIS specification also defines the following functions, which MySQL does not yet implement:
Centroid(mpoly)
Returns the mathematical centroid for the MultiPolygon
value
mpoly
as a Point
. The result is not guaranteed to be on
the MultiPolygon
.
PointOnSurface(mpoly)
Returns a Point
value that is guaranteed to be on the
MultiPolygon
value mpoly
.
GeometryCollection
Property Analysis Functions NumGeometries(gc)
Returns the number of geometries in the GeometryCollection
value
gc
.
mysql> SELECT NumGeometries(GeomFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))')); +------------------------------------------------------------------------------------+ | NumGeometries(GeomFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))')) | +------------------------------------------------------------------------------------+ | 2 | +------------------------------------------------------------------------------------+ |
GeometryN(gc,n)
Returns the n
-th geometry in the GeometryCollection
value
gc
. Geometry numbers begin at 1.
mysql> SELECT AsText(GeometryN(GeomFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))'),1)); +------------------------------------------------------------------------------------------+ | AsText(GeometryN(GeomFromText('GeometryCollection(Point(1 1),LineString(2 2, 3 3))'),1)) | +------------------------------------------------------------------------------------------+ | POINT(1 1) | +------------------------------------------------------------------------------------------+ |
11.5.3.1 Geometry Functions That Produce New Geometries | ||
11.5.3.2 Spatial Operators |
In the section Geometry
Property Analysis Functions,
we've already discussed some functions that can construct new geometries
from the existing ones:
Envelope(g)
StartPoint(ls)
EndPoint(ls)
PointN(ls,n)
ExteriorRing(poly)
InteriorRingN(poly,n)
GeometryN(gc,n)
OpenGIS proposes a number of other functions that can produce geometries. They are designed to implement Spatial Operators.
These functions are not yet implemented in MySQL. They should appear in future releases.
Intersection(g1,g2)
Returns a geometry that represents the point set intersection of the geometry
values g1
with g2
.
Union(g1,g2)
Returns a geometry that represents the point set union of the geometry values
g1
and g2
.
Difference(g1,g2)
Returns a geometry that represents the point set difference of the geometry
value g1
with g2
.
SymDifference(g1,g2)
Returns a geometry that represents the point set symmetric difference of the
geometry value g1
with g2
.
Buffer(g,d)
Returns a geometry that represents all points whose distance from the geometry
value g
is less than or equal to a distance of d
.
ConvexHull(g)
Returns a geometry that represents the convex hull of the geometry value
g
.
The functions described in these sections take two geometries as input parameters and return a qualitive or quantitive relation between them.
MySQL provides some functions that can test relations
between mininal bounding rectangles of two geometries g1
and g2
.
They include:
MBRContains(g1,g2)
Returns 1 or 0 to indicate whether or not the Minimum Bounding Rectangle of
g1
contains the Minimum Bounding Rectangle of g2
.
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 | +----------------------+----------------------+ |
MBRWithin(g1,g2)
Returns 1 or 0 to indicate whether or not the Minimum Bounding Rectangle
of g1
is within the Minimum Bounding Rectangle of g2
.
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 | +--------------------+--------------------+ |
MBRDisjoint(g1,g2)
Returns 1 or 0 to indicate whether or not the Minimum Bounding Rectangles of
the two geometries g1
and g2
are disjoint (do not intersect).
MBREquals(g1,g2)
Returns 1 or 0 to indicate whether or not the Minimum Bounding Rectangles of
the two geometries g1
and g2
are the same.
MBRIntersects(g1,g2)
Returns 1 or 0 to indicate whether or not the Minimum Bounding Rectangles of
the two geometries g1
and g2
intersect.
MBROverlaps(g1,g2)
Returns 1 or 0 to indicate whether or not the Minimum Bounding Rectangles of
the two geometries g1
and g2
overlap.
MBRTouches(g1,g2)
Returns 1 or 0 to indicate whether or not the Minimum Bounding Rectangles of
the two geometries g1
and g2
touch.
The OpenGIS specification defines the following functions, which MySQL does not yet implement. They should appear in future releases. When implemented, they will provide full support for spatial analysis, not just MBR-based support.
The functions operate on two geometry values g1
and g2
.
Contains(g1,g2)
Returns 1 or 0 to indicate whether or not g1
completely contains
g2
.
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 or not g1
is spatially disjoint
from (does not intersect) g2
.
Equals(g1,g2)
Returns 1 or 0 to indicate whether or not g1
is spatially equal to
g2
.
Intersects(g1,g2)
Returns 1 or 0 to indicate whether or not g1
spatially intersects
g2
.
Overlaps(g1,g2)
Returns 1 or 0 to indicate whether or not 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.
Touches(g1,g2)
Returns 1 or 0 to indicate whether or not 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 or not g1
is spatially within
g2
.
Distance(g1,g2)
Returns as a double-precision number the shortest distance between any two points in the two geometries.
Related(g1,g2,pattern_matrix)
Returns 1 or 0 to indicate whether or not 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 when this
function is implemented.
[ < ] | [ > ] | [ << ] | [ Up ] | [ >> ] |
Hosting by: Hurra Communications Ltd.
Generated: 2007-01-26 17:58:46