OpenGIS support in MySQL ------------------------------------------------------------------------ Note: Blue colored lines among the text is features not implemented yet. They are: * Spatial Reference Systems and their IDs (SRIDs) related things: o Functions like Length() and Area() assume planar coordinate system. o All objects are currently considered to be in the same planar coordinate system. * Function Length() on LineString and MultiLineString currently should be called as GLength(). * No binary constructors like GeomFromWKB(). We also have to add "PostGIS compatibility" sections. 1 Introduction MySQL implements a subset of *SQL2 with Geometry Types* environment proposed by OpenGIS consortium's *Simple Features Specification For SQL*. In this environment a geometry-valued column is implemented as a column whose SQL type is drawn from the set of Geometry Types. SQL server supports both textual and binary access to geometry. 2 OpenGIS Geometry Model in MySQL MySQL supports the Open GIS Geometry Model based hierarcy of spatial objects classes, which consists of: * Geometry o *Point* o Curve + *LineString* o Surface + *Polygon* o *GeometryCollection* + *MultiPoint* + MultiCurve # *MultiLineString* + MultiSurface # *MultiPolygon* The base *Geometry* class has subclasses for Point, Curve, Surface and GeometryCollection. Geometry, Curve, Surface, MultiCurve and MultiSurface are defined to be non-instantiable classes, it is not possible to create an object of these classes. Point, LineString, Polygon, GeometryCollection, MultiPoint, MultiLineString, MultiPolygon are instantiable classes (bolded on the hierarcy tree). MySQL provides a number of functions to construct instances of these classes. TODO: Each spatial object is associated with a Spatial Reference System, which describes the coordinate space in which the geometric object is defined. 2.1 Geometry Geometry is the root class of the hierarchy. Geometry is an abstract (non-instantiable) class. The instantiable subclasses of Geometry defined in this specification are restricted to 0, 1 and two-dimensional geometric objects that exist in two-dimensional coordinate space. All instantiable geometry classes are defined so that valid instances of a geometry class are topologically closed (i.e. all defined geometries include their boundary). 2.2 Point A *Point* is a 0-dimensional geometry and represents a single location in coordinate space. A Point in the case of 2D has a x-coordinate value and a y-coordinate value. In the case of more dimensions, a Point has a coordinate value for each dimension. The boundary of a Point is the empty set. 2.3 Curve A *Curve* is a one-dimensional geometric object usually stored as a sequence of points, with the subclass of Curve specifying the form of the interpolation between points. MySQL implementation defines only one subclass of Curve, LineString, which uses linear interpolation between points. 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 end point. The boundary of a closed Curve is empty. A Curve that is simple and closed is a Ring. The boundary of a non-closed Curve consists of its two end points. A Curve is defined as topologically closed. 2.4 LineString, Line, LinearRing A LineString is a Curve with linear interpolation between points. Each consecutive pair of points defines a line segment. A Line is a LineString with exactly 2 points. A LinearRing is a LineString that is both closed and simple. 2.5 Surface A *Surface* is a two-dimensional geometric object. The OpenGIS Abstract Specification defines a simple Surface as consisting of a single 'patch' that is associated with one 'exterior boundary' and 0 or more 'interior' boundaries. Simple surfaces in three-dimensional space are isomorphic to planar surfaces. Polyhedral surfaces are formed by 'stitching' together simple surfaces along their boundaries, polyhedral surfaces in three-dimensional space may not be planar as a whole. The boundary of a simple Surface is the set of closed curves corresponding to its exterior and interior boundaries. The only instantiable subclass of Surface defined in this specification, Polygon, is a simple Surface that is planar. 2.6 Polygon A Polygon is a planar Surface, defined by 1 exterior boundary and 0 or more interior boundaries. Each interior boundary defines a hole in the Polygon. The assertions for polygons (the rules that define valid polygons) are: * Polygons are topologically closed. * The boundary of a Polygon consists of a set of LinearRings (i.e. LineStrings which are both simple and closed) that make up its exterior and interior boundaries. * No two rings in the boundary cross, the rings in the boundary of a Polygon may intersect at a Point but only as a tangent. * A Polygon may not have cut lines, spikes or punctures. * The Interior of every Polygon is a connected point set. * The Exterior of a Polygon with 1 or more holes is not connected. Each hole defines a connected component of the Exterior. In the above assertions, Interior, Closure and Exterior have the standard topological definitions. The combination of 1 and 3 make a Polygon a Regular Closed point set. Polygons are simple geometries. 2.6 GeometryCollection A *GeometryCollection* is a geometry that is a collection of 1 or more geometries of any class. All the elements in a GeometryCollection must be in the same Spatial Reference (i.e. in the same coordinate system). GeometryCollection places no other constraints on its elements. However subclasses of GeometryCollection described below may restrict membership based on dimension and may also place other constraints on the degree of spatial overlap between elements. 2.7 MultiPoint A *MultiPoint* is a 0 dimensional geometric collection. The elements of a MultiPoint are restricted to Points. The points are not connected or ordered. A MultiPoint is simple if no two Points in the MultiPoint are equal (have identical coordinate values). The boundary of a MultiPoint is the empty set. 2.8 MultiCurve A MultiCurve is a one-dimensional geometry collection whose elements are Curves. MultiCurve is a non-instantiable class, it defines a set of methods for its subclasses and is included for reasons of extensibility. 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. The boundary of a MultiCurve is obtained by applying the "mod 2 union rule": A point is in the boundary of a MultiCurve if it is in the boundaries of an odd number of elements of the MultiCurve. A MultiCurve is closed if all of its elements are closed. The boundary of a closed MultiCurve is always empty. A MultiCurve is defined as topologically closed. 2.9 MultiLineString A *MultiLineString* is a MultiCurve whose elements are LineStrings. 2.10 MultiSurface A MultiSurface is a two-dimensional geometric collection whose elements are surfaces. The interiors of any two surfaces in a MultiSurface may not intersect. The boundaries of any two elements in a MultiSurface may intersect at most at a finite number of points. MultiSurface is a non-instantiable class in this specification, it defines a set of methods for its subclasses and is included for reasons of extensibility. The instantiable subclass of MultiSurface is MultiPolygon, corresponding to a collection of Polygons. 2.11 MultiPolygon A MultiPolygon is a MultiSurface whose elements are Polygons. The assertions for MultiPolygons are : * The interiors of 2 Polygons that are elements of a MultiPolygon may not intersect. * The Boundaries of any 2 Polygons that are elements of a MultiPolygon may not cross and may touch at only a finite number of points. (Note that crossing is prevented by assertion 1 above). * A MultiPolygon is defined as topologically closed. * A MultiPolygon may not have cut lines, spikes or punctures, a MultiPolygon is a Regular, Closed point set. * The interior of a MultiPolygon with more than 1 Polygon is not connected, the number of connected components of the interior of a MultiPolygon is equal to the number of Polygons in the MultiPolygon. The boundary of a MultiPolygon is a set of closed curves (LineStrings) corresponding to the boundaries of its element Polygons. Each Curve in the boundary of the MultiPolygon is in the boundary of exactly 1 element Polygon, and every Curve in the boundary of an element Polygon is in the boundary of the MultiPolygon. 3 Exchange of spatial data MySQL provides binary and textual mechanismes to exchange spatial data. Exchange is provided via so called Well Known Binary (WKB) and Well Known Textual (WKT) representations of spatial data proposed by OpenGIS specifications. 3.1 Well-known Text representation (WKT) The Well-known Text (WKT) representation of Geometry is designed to exchange geometry data in textual format. WKT is defined below in Bechus-Naur forms: * the notation {}* denotes 0 or more repetitions of the tokens within the braces; * the braces do not appear in the output token list. The text representation of the implemented instantiable geometric types conforms to this grammar: := | | | | | | := POINT := LINESTRING := POLYGON := MULTIPOINT := MULTILINESTRING := MULTIPOLYGON := GEOMETRYCOLLECTION := EMPTY | ( ) := := double precision literal := double precision literal := EMPTY | ( {, }* ) := EMPTY | ( {, < LineString Text > }*) := EMPTY | ( {, }* ) := EMPTY | ( {, < LineString Text > }* ) := EMPTY | ( < Polygon Text > {, < Polygon Text > }* ) := EMPTY | ( {, }* ) WKT examples Examples of textual representations of Geometry objects are shown below: * |POINT(10 10)| - a Point * |LINESTRING( 10 10, 20 20, 30 40)| - a LineString with three points * |POLYGON((10 10, 10 20, 20 20,20 15, 10 10))| - a Polygon with one exterior ring and 0 interior rings * |MULTIPOINT(10 10, 20 20)| - a MultiPoint with two Points * |MULTILINESTRING((10 10, 20 20), (15 15, 30 15))| - a MultiLineString with two LineStrings * |MULTIPOLYGON(((10 10, 10 20, 20 20, 20 15, 10 10)), ((60 60, 70 70, 80 60, 60 60 ) ))| - a MultiPolygon with two Polygons * |GEOMETRYCOLLECTION( POINT (10 10),POINT (30 30), LINESTRING (15 15, 20 20))| - a GeometryCollection consisting of two Points and one LineString 3.2 Well-known Binary representation (WKB) Well Known Binary Representations is proposed by OpenGIS specifications to exchange geometry data in binary format. This is WKB description: // Basic Type definitions // byte : 1 byte // uint32 : 32 bit unsigned integer (4 bytes) // double : double precision number (8 bytes) // Building Blocks : Point, LinearRing Point { double [numDimentions]; }; LinearRing { uint32 numPoints; Point points[numPoints]; } enum wkbGeometryType { wkbPoint = 1, wkbLineString = 2, wkbPolygon = 3, wkbMultiPoint = 4, wkbMultiLineString = 5, wkbMultiPolygon = 6, wkbGeometryCollection = 7 }; enum wkbByteOrder { wkbXDR = 0, // Big Endian wkbNDR = 1 // Little Endian }; WKBPoint { byte byteOrder; uint32 wkbType; // 1 Point point; } WKBLineString { byte byteOrder; uint32 wkbType; // 2 uint32 numPoints; Point points[numPoints]; } WKBPolygon { byte byteOrder; uint32 wkbType; // 3 uint32 numRings; LinearRing rings[numRings]; } WKBMultiPoint { byte byteOrder; uint32 wkbType; // 4 uint32 num_wkbPoints; WKBPoint WKBPoints[num_wkbPoints]; } WKBMultiLineString { byte byteOrder; uint32 wkbType; // 5 uint32 num_wkbLineStrings; WKBLineString WKBLineStrings[num_wkbLineStrings]; } wkbMultiPolygon { byte byteOrder; uint32 wkbType; // 6 uint32 num_wkbPolygons; WKBPolygon wkbPolygons[num_wkbPolygons]; } WKBGeometry { union { WKBPoint point; WKBLineString linestring; WKBPolygon polygon; WKBGeometryCollection collection; WKBMultiPoint mpoint; WKBMultiLineString mlinestring; WKBMultiPolygon mpolygon; } }; WKBGeometryCollection { byte byte_order; uint32 wkbType; // 7 uint32 num_wkbGeometries; WKBGeometry wkbGeometries[num_wkbGeometries]; } 3.3 MySQL data types for spatial objects MySQL implementation of OpenGIS provides the *GEOMETRY* data type to be used in CREATE TABLE statements. For example, this statement creates a table *geom* with spatial field *g*: CREATE TABLE geom ( g Geometry; ); A field of *GEOMETRY* type can store a spatial objects of any OpenGIS geometry class described above. 3.4 Internal spatial data representation Internally (in *.MYD* files) spatial objects are stored in *WKB*, combined with object's *SRID* (a numeric ID of Spatial Reference System object associated with). During spatial analysis, for example, calculating the fact that one object crosses another one, only those with the same *SRID* are accepted. *SRID* may affect a way in which various spatial characteristics are calculated. For example, in different coordinate systems distance between two objects may differ even objects have the same coordinates, like distance on plane coordinate system and distance on geocentric (coordinates on Earth surface) systems are different things. There is a plan to provide a number of commonly used coordinate systems in MySQL OpenGIS implementation. 3.5 INSERTing spatial objects Spatial data can be INSERTed using a spatial constructor. The term *spatial constructor* is used in this manual to refer to any function which can construct a value of GEOMETRY type, i.e. an internal MySQL representation of spatial data. 3.5.1 Textual spatial constructors Textual spatial constructors take a gemometry description in WKT and built GEOMETRY value. * |*GeomFromText(geometryTaggedText String [, SRID Integer]):Geometry *| - constructs a Geometry value from its well-known textual representation. |*GeomFromText()*| function accepts a WKT of any Geometry class as it's first argument. For construction of Geometry values restricted to a particular subclass, an implementation also provides a class-specific construction function for each instantiable subtype as described in the list below: * |*PointFromText(pointTaggedText String [,SRID Integer]):Point *| - constructs a Point * |*LineFromText(lineStringTaggedText String [,SRID Integer]):LineString *| - constructs a LineString * |*PolyFromText(polygonTaggedText String [,SRID Integer]):Polygon *|- constructs a Polygon * |*MPointFromText(multiPointTaggedText String [,SRID Integer]):MultiPoint *| - constructs a MultiPoint * |*MLineFromText(multiLineStringTaggedText String [,SRID Integer]):MultiLineString *| - constructs a MultiLineString * |*MPolyFromText(multiPolygonTaggedText String [,SRID Integer]):MultiPolygon *| - constructs a MultiPolygon * |*GeomCollFromText(geometryCollectionTaggedText String [,SRID Integer]):GeomCollection *| - constructs a GeometryCollection Usage examples: INSERT INTO geom VALUES (GeomFromText('POINT(1 1)')) INSERT INTO geom VALUES (GeomFromText('LINESTRING(0 0,1 1,2 2)')) INSERT INTO geom VALUES (GeomFromText('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('GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(0 0,1 1,2 2,3 3,4 4))')) The second argument of spatial constructirs, described above, is currently ignored, It will be used to specify SRID in the future. Nowdays, it is added for reasons of compatibility with OpenGIS specifications and PostGIS implementation. As an optional feature, an implementation may also support building of Polygon or MultiPolygon values given an arbitrary collection of possibly intersecting rings or closed LineString values. Implementations that support this feature should include the following functions: * |*BdPolyFromText(multiLineStringTaggedText String, SRID Integer):Polygon *| - constructs a Polygon given an arbitrary collection of closed linestrings as a MultiLineString text representation. * |*BdMPolyFromText(multiLineStringTaggedText String, SRID Integer):MultiPolygon *| - constructs a MultiPolygon given an arbitrary collection of closed linestrings as a MultiLineString text representation. 3.5.2 Binary spatial constructors * |*GeomFromWKB(WKBGeometry Binary, SRID Integer):Geometry *| - constructs a Geometry value given its well-known binary representation. |*GeomFromWKB()*| function can accept in it's first argument a WKB of Geometry of any class. For construction of Geometry values restricted to a particular subclass, an implementation also provides a class-specific construction function for each instantiable subclass as described in the list below: * |*PointFromWKB(WKBPoint Binary, SRID Integer):Point - *|constructs a Point * |* LineFromWKB(WKBLineString Binary, SRID Integer):LineString *| - constructs a LineString * |* PolyFromWKB(WKBPolygon Binary, SRID Integer):Polygon *| - constructs a Polygon * |* MPointFromWKB(WKBMultiPoint Binary, SRID Integer):MultiPoint *| - constructs a MultiPoint * |* MLineFromWKB(WKBMultiLineString Binary, SRID Integer):MultiLineString *| - constructs a MultiLineString * |* MPolyFromWKB(WKBMultiPolygon Binary, SRID Integer): MultiPolygon *| - constructs a MultiPolygon * |* GeomCollFromWKB(WKBGeometryCollection Binary, SRID Integer): GeomCollection *| - constructs a GeometryCollection As an optional feature, an implementation may also support the uilding' of Polygon or MultiPolygon values given an arbitrary collection of possibly intersecting rings or closed LineString values. Implementations that support this feature should include the following functions: * |* BdPolyFromWKB (WKBMultiLineString Binary,SRID Integer): Polygon *| - constructs a Polygon given an arbitrary collection of closed linestrings as a MultiLineString binary representation. * |*BdMPolyFromWKB(WKBMultiLineString Binary, SRID Integer):MultiPolygon *| - constructs a MultiPolygon given an arbitrary collection of closed linestrings as a MultiLineString binary representation. Inserting in *WKB* assumes that |GeomFromWKB()| function argument contains a buffer with a correctly formed spatial object in WKB. In ODBC applications it can be done using binding of argument. One also can insert object in *WKB* using |mysql_escape_string()| in |libmysqlclient| applications. For example: INSERT INTO geom VALUES (GeomFromWKB(buf,SRID)); where |buf| is a binary buffer with a spatial object in *WKB* representation. 3.5 SELECTing spatial objects Spatial objects are selected either in *WKT* or *WKB* representation by use of AsText() and AsBinary() functions correspondently. mysql> select AsText(g) as g from geom; +-------------------------+ | g | +-------------------------+ | POINT(1 1) | | LINESTRING(0 0,1 1,2 2) | +-------------------------+ 2 rows in set (0.00 sec) mysql> The query: SELECT AsBinary(g) FROM geom returns a BLOB which contains *WKB* representation of object. 4 Functions for spatial analysis 4.1 Basic functions on Geometry * |*AsText(g:Geometry):String*| - Exports this Geometry to a specific well-known text representation of Geometry. * |*AsBinary(g:Geometry):Binary*| - Exports this Geometry to a specific well-known binary representation of Geometry. * |*GeometryType(g:Geometry):String*| - Returns the name of the instantiable subtype of Geometry of which this Geometry instance is a member. The name of the instantiable subtype of Geometry is returned as a string. * |*Dimension(g:Geometry):Integer*| - The inherent dimension of this Geometry object, which must be less than or equal to the coordinate dimension. This specification is restricted to geometries in two-dimensional coordinate space. * |*IsEmpty(g:Geometry):Integer*| - Returns 1 (TRUE) if this Geometry is the empty geometry . If true, then this Geometry represents the empty point set, , for the coordinate space. * |*IsSimple(g:Geometry):Integer *| - Returns 1 (TRUE) if this Geometry has no anomalous geometric points, such as self intersection or self tangency. The description of each instantiable geometric class includes the specific conditions that cause an instance of that class to be classified as not simple. * |*SRID(g:Geometry):Integer*| - Returns the Spatial Reference System ID for this Geometry. * |*Distance(g1:Geometry,g2:Geometry):Double*| - the shortest distance between any two points in the two geometries. 4.2 Functions for specific geometry type GeometryCollection functions * *NumGeometries(g:GeometryCollection ):Integer * -Returns the number of geometries in this GeometryCollection. * *GeometryN(g:GeometryCollection,N:integer):Geometry * -Returns the Nth geometry in this GeometryCollection. Point functions * *X(p:Point):Double* -The x-coordinate value for this Point. * *Y(p:Point):Double* -The y-coordinate value for this Point. LineString functions * *StartPoint(l:LineString):Point* The start point of this LineString. * *EndPoint(l:LineString):Point* The end point of this LineString. * *PointN(l:LineString,N:Integer):Point* Returns the specified point N in this Linestring. * *Length(l:LineString):Double* The length of this LineString in its associated spatial reference. * *IsRing(l:LineString):Integer* Returns 1 (TRUE) if this LineString is closed (StartPoint ( ) = EndPoint ( )) and this LineString is simple (does not pass through the same point more than once). * *IsClosed(l:LineString):Integer* Returns 1 (TRUE) if this LineString is closed (StartPoint ( ) = EndPoint ( )). * *NumPoints(l:LineString):Integer* The number of points in this LineString. MultiLineString functions * *Length(m:MultiLineString):Double* The Length of this MultiLineString which is equal to the sum of the lengths of the elements. * *IsClosed(m:MultiLineString):Integer* Returns 1 (TRUE) if this MultiLineString is closed (StartPoint() = EndPoint() for each LineString in this MultiLineString) Polygon functions * *Area(p:Polygon):Double* The area of this Polygon, as measured in the spatial reference system of this Polygon. * *Centroid(p:Polygon):Point* The mathematical centroid for this Polygon as a Point. The result is not guaranteed to be on this Polygon. * *PointOnSurface(p:Polygon):Point* A point guaranteed to be on this Polygon. * *NumInteriorRing(p:Polygon):Integer* Returns the number of interior rings in this Polygon. * *ExteriorRing(p:Polygon):LineString* Returns the exterior ring of this Polygon as a LineString. * *InteriorRingN(p:Polygon,N:Integer):LineString* Returns the Nth interior ring for this Polygon as a LineString. MultiPolygon functions * *Area(m:MultuSurface):Double* The area of this MultiPolygon, as measured in the spatial reference system of this MultiPolygon. * *Centroid(m:MultyPolygon):Point* The mathematical centroid for this MultiPolygon as a Point. The result is not guaranteed to be on this MultiPolygon. * *PointOnSurface(m:MultuPolygon):Point* A Point guaranteed to be on this MultiPolygon. Notes: /functions for specific geometry type retrun NULL if passed object type is incorrect. For example Area() returns NULL if object type is neither Polygon nor MultiPolygon/ 4.3 Spatial operations (compound spatial constructors) * |*Envelope(g:Geometry):Geometry*|The minimum bounding box for this Geometry, returned as a Geometry. The polygon is defined by the corner points of the bounding box |POLYGON((MINX,MINY),(MAXX,MINY),(MAXX,MAXY),(MINX,MAXY),(MINX,MINY))|. * |*Boundary(g:Geometry):Geometry*| - returns the closure of the combinatorial boundary of this Geometry. * |*Intersection(g1,g2:Geometry):Geometry*| - a geometry that represents the point set intersection of g1 with g2. * |*Union(g1,g2:Geometry):Geometry*| - a geometry that represents the point set union of g1 with g2. * |*Difference(g1,g2:Geometry):Geometry*| - a geometry that represents the point set difference of g1 with g2. * |*SymDifference(g1,g2:Geometry):Geometry*| - a geometry that represents the point set symmetric difference of g1 with g2. * |*Buffer(g:Geometry,distance:Double):Geometry*| - a geometry that represents all points whose distance from g is less than or equal to distance. * |*ConvexHull(g:Geometry):Geometry*| - a geometry that represents the convex hull of g. 4.4 Functions for testing Spatial Relations between geometric objects * |*Equals(g1,g2)*| - Returns 1 if g1 is spatially equal to g2. * |*Disjoint(g1,g2)*| - Returns 1 if g1 is spatially disjoint from g2. * |*Intersects(g1,g2)*| - Returns 1 if g1 spatially intersects g2. * |*Touches(g1,g2)*| - Returns 1 if g1 spatially touches g2. * |*Crosses(g1,g2)*| - Returns 1 if g1 spatially crosses g2. * |*Within(g1,g2)*| - Returns 1 if g1 is spatially within g2. * |*Contains(g1,g2)*| - Returns 1 if g1 spatially contains g2. * |*Overlaps(g1,g2)*| - Returns 1 if g1 spatially overlaps g2. 5 Optimizing spatial analysis 5.1 MBR MBR is a minimal bounding rectangle (box) for spatial object. It can be represented as a set of min and max values of each dimension. For example: (Xmin,Xmax,Ymin,Ymax) 5.2 Using SPATIAL indexes To optimize spatial object relationships analysis it is possible to create a spatial index on geometry field using R-tree algorythm. R-tree based spatial indexes store MBRs of spatial objects as a key values. CREATE SPATIAL INDEX gind ON geom (g); Or together with table definition: CREATE TABLE geom ( g GEOMETRY, SPATIAL INDEX(g) ); Optimizer attaches R-tree based SPATIAL index when a query with spatial objects relationship functions is executed in WHERE clause. For example: SELECT geom.name FROM geom WHERE Within(geom.g,GeomFromText('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))',SRID)); 8 OpenGIS extensions implemented in MySQL MySQL provides it's own constructors to build geometry objects: * |*Point(double,double,SRID)*| - constructs a geometry of Point class using it's coordinates and SRID. * |*MultiPoint(Point,Point,...,Point)*| - constructs a MultiPoint using Points. When any argument is not a geometry of Point class the return value is NULL. * |*LineString(Point,Point,...,Point)*| - constructs a LineString from a number of Points. When any argument is not a geometry of Point class the return value is NULL. When the number of Points is less than two the return value is NULL. * |*MultiLineString(LineString,LineString,...,LineString)*| - constructs a MultiLineString using using LineStrings. When any argument is not a geometry of LineStringClass return value is NULL. * |*Polygon(LineString,LineString,...,LineString)*| - constructs a Polygon from a number of LineStrings. When any argument is not a LinearRing (i.e. not closed and simple geometry of class LineString) the return value is NULL. * |*MultiPolygon(Polygon,Polygon,...,Polygon)*| - constructs a MultiPolygon from a set of Polygons. When any argument is not a Polygon, the rerurn value is NULL. * |*GeometryCollection(Geometry,Geometry,..,Geometry)*| - constucts a GeometryCollection. When any argument is not a valid geometry object of any instantiable class, the return value is NULL. The above functions (except Point()) return NULL if arguments are not in the same spatial reference system (i.e. have different SRIDs). Examples: INSERT INTO geom SELECT Point(x,y,SRID) FROM coords; SELECT AsText(g) FROM geom WHERE Contains(Polygon(LineString(Point(0,0),Point(0,1),Point(1,1),Point(1,0),Point(0,0)),SRID),geom.g); 9 Things that differ in MySQL implemention and OpenGIS specifications 9.1 Single GEOMETRY type Besides a GEOMETRY type, OpenGIS consortium specifications suggest the implementation of several spatial field types correspondent to every instansiable object subclass. For example a *Point* type is proposed to restrict data stored in a field of this type to only Point OpenGIS subclass. MySQL provides an implementation of single GEOMETRY type which doesn't restrict objects to certain OpenGIS subclass. 9.2 No additional Metadata Views OpenGIS specifications propose several additional metadata views. For example, a system view named GEOMETRY_COLUMNS contains a description of geometry columns, one row for each geometry column in the database. 9.3 No functions to add/drop spatial columns OpenGIS assumes that columns can be added/dropped using AddGeometryColumn() and DropGeometryColumn() functions correspondently. In MySQL implementation one should use ALTER TABLE instead.