More

Empty geometries in GEOMETRYCOLLECTION


I just noticed some strange behavior in PostGIS, with respect to WKT parsing.

A GeometryCollection containing 1 or more empty geometries

postgis_test=# SELECT ST_GeomFromEWKT('GEOMETRYCOLLECTION(POINT EMPTY, LINESTRING EMPTY)'); 010700000000000000

is the same an empty GeometryCollection

postgis_test=# SELECT ST_GeomFromEWKT('GEOMETRYCOLLECTION EMPTY'); 010700000000000000

This doesn't seem right. But here's the truly weird part. If you throw a non-empty geometry into the collection, the empty geometries are suddenly represented in the collection:

postgis_test=# SELECT ST_GeomFromEWKT('GEOMETRYCOLLECTION(POINT EMPTY, LINESTRING EMPTY, POINT EMPTY)'); 010700000000000000 postgis_test=# SELECT ST_GeomFromEWKT('GEOMETRYCOLLECTION(POINT EMPTY, LINESTRING EMPTY, POINT (1 2))'); 0107000000030000000104000000000000000102000000000000000101000000000000000000F03F0000000000000040

It seems strange that empty geometries are only recognized if the collection contains at least one non-empty.

Is this is a bug? Is this behavior documented somewhere?


It's not a bug, more like a strategic laziness. 1.X versions of PostGIS only supported GEOMETRYCOLLECTION EMPTY, not other forms of empty. For 2.X I (perhaps foolishly) embraced the full variety of nothings. The result is a not-entirely-complete support of varieties of nothing, made slightly ill-formed by the fact that support libraries like GEOS have their own concepts of what kinds of nothing are worth preserving, and that standards like WKB cannot even represent some forms of it (POINT EMPTY is not representable in WKB).

Anyhow, a bunch of nothing is still nothing. Do you you need to retain fidelity of your collections of nothing?

UPDATE

Looking at the PostGIS code, I'm pretty sure you're seeing an effect of the "is empty" function. Your input is in fact being parsed into an internal representation that reflects the input, a collection of empty things. But on output to WKB, the first test is "is this thing empty? if so emit an empty representation". So then we get this situation: is a geometry collection of empty things itself empty? Philosophy 101. In terms of most things we might do with it (calculate area or length, intersect it with things, really any operation at all) a collection of empty is just the same as a single empty. Mucking with the definition of "is empty" has a lot of knock-on effects all over the code base, so we haven't touched it much.


Empty geometries in GEOMETRYCOLLECTION - Geographic Information Systems

A collection of geometry shapes. Included geometries may be Polyline s, Polygon s or others GeometryCollection . Regrouping related polygons in a single collection help to speed up the rendering. Polygons can be regrouped on a spatial basis (European polygons, African polygons, etc.) or on a value basis (50 meters isobath, 100 meters isobath, etc.).

A GeometryCollection is initially built with a coordinate system. An arbitrary amount of geometries can be added after construction using add(Geometry) or add(float[],int,int) . Geometries will be rendered in the order they were added. If polygons are broken in many pieces, then the assemble(. ) method may help to assemble them before rendering.

Note: this class has a natural ordering that is inconsistent with equals. The compareTo(java.lang.Object) method compares only the collection's value, while equals(java.lang.Object) compares also all coordinate points. The natural ordering for GeometryCollection is convenient for sorting collections in alphabetical order or isobaths in increasing order of altitude.

Version: $Id: GeometryCollection.java 17672 2006-01-19 00:25:55Z desruisseaux $ Author: Martin Desruisseaux See Also: Polyline , Polygon , Serialized Form Task: TODO: Add a 'getTree(boolean)' method returning a TreeNode. Would be usefull for debugging. Node contains GeometryCollection only if boolean argument is false, GeometryCollection and Polygons if true (not Polylines). Node.toString returns Geometry.getName().

Field Summary
Fields inherited from class org.geotools.renderer.geom.Geometry
DEFAULT_COORDINATE_SYSTEM
Constructor Summary
GeometryCollection ()
Construct an initially empty collection using the default coordinate system.
GeometryCollection (CoordinateSystem coordinateSystem)
Construct an initially empty collection.
GeometryCollection (GeometryCollection geometry)
Construct a collection with the same data as the specified collection.
Method Summary
void add (float[] array, int lower, int upper)
Adds points to this collection.
Geometry add (Geometry toAdd)
Add a geometry to this collection.
void add (java.awt.Shape shape)
Add geometries from the specified shape.
void assemble (ProgressListener progress)
Assemble all polylines with default setting.
void assemble (java.awt.Shape mapBounds, float[] toComplete, ProgressListener progress)
Assemble all polylines in order to create closed polygons for proper rendering.
Geometry clip (Clipper clipper)
Returns an geometry approximately equal to this geometry clipped to the specified bounds.
java.lang.Object clone ()
Return a copy of this geometry.
int compareTo (java.lang.Object object)
Compare this geometry with the specified object for order.
float compress (CompressionLevel level)
Compress all geometries in this collection.
boolean contains (double x, double y)
Indicates whether the specified ( x , y ) point is inside this geometry.
boolean contains (java.awt.geom.Point2D point)
Indicates whether the specified point is inside this geometry.
boolean contains (java.awt.geom.Rectangle2D rect)
Checks whether the specified rectangle is entirely contained within this geometry.
boolean contains (java.awt.Shape shape)
Checks whether the specified shape is entirely contained within this geometry.
boolean equals (java.lang.Object object)
Compares the specified object with this geometry for equality.
java.awt.geom.Rectangle2D getBounds2D ()
Return the bounding box of this geometry, including its possible borders.
CoordinateSystem getCoordinateSystem ()
Returns the geometry's coordinate system, or null if unknown.
java.util.Collection getGeometries ()
Returns the collection of Geometry objects.
java.util.Collection getGeometries (java.awt.geom.Point2D point)
Returns the collection of geometries containing the specified point.
java.util.Collection getGeometriesContaining (java.awt.Shape shape)
Returns the collection of geometries containing the specified shape.
java.util.Collection getGeometriesIntersecting (java.awt.Shape shape)
Returns the collection of geometries intersecting the specified shape.
java.lang.String getName (java.util.Locale locale)
Returns the localized name for this geometry, or null if none.
java.awt.geom.PathIterator getPathIterator (java.awt.geom.AffineTransform transform)
Returns a path iterator for this geometry.
int getPointCount ()
Returns the number of points in this geometry.
java.lang.String getPolygonName (java.awt.geom.Point2D point, java.util.Locale locale)
Returns the name of the smallest polygon at the given location.
float getRenderingResolution ()
Returns the rendering resolution.
Statistics getResolution ()
Returns the geometry's resolution.
float getValue ()
Returns the value for this collection, or NaN if none.
int hashCode ()
Returns a hash value for this geometry.
boolean intersects (java.awt.geom.Rectangle2D rect)
Tests whether the specified rectangle intersects the interior of this geometry.
boolean intersects (java.awt.Shape shape)
Tests whether the specified shape intersects the interior of this geometry.
boolean isEmpty ()
Determines whetever the collection is empty.
protected void readObject (java.io.ObjectInputStream in)
Invoked during deserialization.
boolean remove (Geometry toRemove)
Removes a geometry from this collection.
void removeAll ()
Remove all geometries from this geometry.
void setCoordinateSystem (CoordinateSystem coordinateSystem)
Set the geometry's coordinate system.
void setRenderingResolution (float resolution)
Hints this geometry that the specified resolution is sufficient for rendering.
void setResolution (double resolution)
Set the geometry's resolution.
void setValue (java.lang.Comparable value)
Set the value for this geometry.
void setValue (float value)
Set the value for this geometry.
protected void writeObject (java.io.ObjectOutputStream out)
Invoked during serialization.
Methods inherited from class org.geotools.renderer.geom.Geometry
contains, getBounds, getID, getPathIterator, getStyle, getUserObject, intersects, setID, setStyle, setUserObject, toString
Methods inherited from class java.lang.Object
finalize, getClass, notify, notifyAll, wait, wait, wait

GeometryCollection

GeometryCollection

GeometryCollection

GetName

Overrides: getName in class Geometry Parameters: locale - The desired locale. If no name is available for this locale, a default locale will be used. Returns: The geometry's name, localized if possible. Task: TODO: We should find a way to avoid the creation of Format object at each invocation.

GetValue

SetValue

SetValue

Parameters: value - The value of value for this geometry.

GetCoordinateSystem

SetCoordinateSystem

Specified by: setCoordinateSystem in class Geometry Parameters: coordinateSystem - The new coordinate system. A null value resets the coordinate system given at construction time. Throws: org.opengis.referencing.operation.TransformException - If a transformation failed. In case of failure, the state of this object will remain unchanged (as if this method has never been invoked). UnmodifiableGeometryException - if modifying this geometry would corrupt a container. To avoid this exception, clone this geometry before to modify it.

Parameters: array - Coordinate array (may contain NaNs). These data will be copied. Consequently, any modification on data will have no impact on the geometries created by this method. lower - Index of the first x ordinate to add to the polyline. upper - Index after of the last y ordinate to add to the polyline. Throws: UnmodifiableGeometryException - if modifying this geometry would corrupt a container. To avoid this exception, clone this geometry before to modify it.

Parameters: shape - The shape to add. Throws: java.lang.IllegalArgumentException - if the specified shape can't be added. This error may occur if shape is an instance of Geometry and uses an incompatible coordinate system. UnmodifiableGeometryException - if modifying this geometry would corrupt a container. To avoid this exception, clone this geometry before to modify it.

Parameters: toAdd - Geometry to add. Throws: org.opengis.referencing.operation.TransformException - if the specified geometry can't be transformed in this collection coordinate system. UnmodifiableGeometryException - if modifying this geometry would corrupt a container. To avoid this exception, clone this geometry before to modify it.

Remove

Parameters: toRemove - The geometry to remove. Returns: true if the geometry has been removed. Throws: UnmodifiableGeometryException - if modifying this geometry would corrupt a container. To avoid this exception, clone this geometry before to modify it.

RemoveAll

Throws: UnmodifiableGeometryException - if modifying this geometry would corrupt a container. To avoid this exception, clone this geometry before to modify it.

Assemble

Running this method once for a given collection of geometries before renderering helps to repair them. The algorithm is:

  1. A list of all possible pairs of polylines is built.
  2. For any pair of polylines, the shortest distance between their extremities is computed. All combinations between the beginning and the end of a polyline with the beginning or end of the other polyline are taken into account.
  3. The pair with the shortest distance are identified. When the shortest distance from one polyline's extremity is the other extremity of the same polyline, then the polyline is identified as a closed polygon (e.g. an island or a lake). Otherwise, the closest polylines are merged together.
  4. The loop is reexecuted from step 1 until no more polylines have been merged.

Parameters: mapBounds - The bounded shape of the map, or null for assuming a rectangular map inferred from this geometry. This is the bounding shape of the software that created the polylines, not an arbitrary clip that the application would like. toComplete - value of collections to complete with map border, or null if none. progress - An optional progress listener ( null in none). This is an optional but recommanded argument, since the computation may be very long. Throws: org.opengis.referencing.operation.TransformException - if a transformation was required and failed. UnmodifiableGeometryException - if modifying this geometry would corrupt a container. To avoid this exception, clone this geometry before to modify it.

Assemble

Parameters: progress - An optional progress listener ( null in none). This is an optional but recommanded argument, since the computation may be very long. Throws: org.opengis.referencing.operation.TransformException - if a transformation was required and failed. UnmodifiableGeometryException - if modifying this geometry would corrupt a container. To avoid this exception, clone this geometry before to modify it.

Overrides: clip in class Geometry Parameters: clipper - The clipping area. Returns: null if this geometry doesn't intersect the clip, this if no clip has been performed, or a new clipped geometry otherwise.

GetGeometries

Returns: A collection of Geometry objects.

GetGeometries

Parameters: point - The coordinates to look at in this geometry's coordinate system. Returns: The collection of geometries under the specified point.

GetGeometriesContaining

Parameters: shape - A shape with coordinates expressed according to getCoordinateSystem() . Returns: The collection of geometries containing the specified shape.

GetGeometriesIntersecting

Parameters: shape - A shape with coordinates expressed according to getCoordinateSystem() . Returns: The collection of geometries intersecting the specified shape.

GetPolygonName

Parameters: point - The coordinates to look at in this geometry's coordinate system. locale - The desired locale for the geometry name. Returns: The geometry name at the given location, or null if there is none.

IsEmpty

GetPointCount

GetBounds2D

Specified by: getBounds2D in interface java.awt.Shape Specified by: getBounds2D in class Geometry Returns: A bounding box of this geometry. Changes to this rectangle will not affect the cache.

Contains

Specified by: contains in interface java.awt.Shape Overrides: contains in class Geometry Parameters: x - the specified x coordinates in this geometry coordinate system. y - the specified y coordinates in this geometry coordinate system. Returns: true if the specified coordinates are inside the geometry boundary false otherwise.

Contains

Specified by: contains in interface java.awt.Shape Specified by: contains in class Geometry Parameters: point - the specified point in this geometry coordinate system. Returns: true if the specified point is inside the geometry boundary false otherwise.

Contains

Specified by: contains in interface java.awt.Shape Overrides: contains in class Geometry

Contains

Intersects

Specified by: intersects in interface java.awt.Shape Overrides: intersects in class Geometry

Intersects

Compress

Specified by: compress in class Geometry Parameters: level - The compression level (or algorithm) to use. See the CompressionLevel javadoc for an explanation of available algorithms. Returns: A estimation of the compression rate. For example a value of 0.2 means that the new polygon uses approximately 20% less memory. Throws: org.opengis.referencing.operation.TransformException - If an error has occurred during a cartographic projection. UnmodifiableGeometryException - if modifying this geometry would corrupt a container. To avoid this exception, clone this geometry before to modify it.

GetResolution

Specified by: getResolution in class Geometry Returns: Statistics about the resolution, or null if this geometry doesn't contains any point. If non-null, the statistics object contains minimum, maximum, mean, root mean square and standard deviation always in linear units.

SetResolution

Specified by: setResolution in class Geometry Parameters: resolution - Desired resolution, in the same units as getResolution() . Throws: org.opengis.referencing.operation.TransformException - If some coordinate transformations were needed and failed. There is no guarantee on contour's state in case of failure. UnmodifiableGeometryException - if modifying this geometry would corrupt a container. To avoid this exception, clone this geometry before to modify it.

GetRenderingResolution

Overrides: getRenderingResolution in class Geometry Returns: The rendering resolution in units of this geometry's coordinate system (linear or angular units), or 0 if the finest available resolution should be used.

SetRenderingResolution

Overrides: setRenderingResolution in class Geometry Parameters: resolution - The resolution to use at rendering time, in units of this geometry's coordinate system (linear or angular units).


Scalar Functions

Returns the area of the given geometry geom if it is a POLYGON or MULTIPOLYGON using the specified solution type. Returns 0 if the input geometry type is (MULTI)POINT or (MULTI)LINESTRING. Solution types available:

  • 0 (default) - 2D Euclidean area
  • 1 - curved surface area on a sphere in square meters
  • 2 - curved surface area on a spheroid in square meters

Returns a geometry that represents all points whose distance from the given geometry geom is less than or equal to the given distance radius. The radius units can be specified by the solution type (default is in degrees) and the radius is created in the provided style. The style options are specified as a list of blank-separated key-value pairs, e.g., 'quad_segs=8 endcap=round'. If an empty style list ('') is provided, the default settings will be used. The style parameter must be specified to provide a solution type.

  • quad_segs -- the number of segments used to approximate a quarter circle (default is 8)
  • endcap -- the endcap style of the buffer (default is round) options are round, flat (or butt), and square
  • join -- the join style of the buffer (default is round) options are round, mitre (or miter), and bevel
  • mitre_limit -- the mitre ratio limit expressed as a floating point number (miter_limit is also acceptable)
  • 0 (default) - 2D Euclidean radius distance in degrees
  • 1 - curved surface radius distance on a sphere in meters
  • 2 - curved surface radius distance on a spheroid in meters

To create a 5-meter buffer around geom using the default styles: ST_BUFFER(geom, 5, '', 1). To create a 5-foot (converting feet to meters) buffer around geom using the following styles: ST_BUFFER(geom, 5*0.3048,'quad_segs=4 endcap=flat', 1)

Calculates the 2-D POINT in geom1 that is closest to geom2 using the specified solution type. If geom1 or geom2 is empty, a null is returned. Solution types available:

  • 0 (default) - Euclidean calculates the closest point using 2-D Euclidean distance
  • 1 - Haversine calculates the closest point using sphere distance in meters
  • 2 - Vincenty returns minimum spheroid distance in meters, more accurate than Haversine but slower performance

Returns only the specified type from the given geometry collection. Type is a number that maps to the following:

Calculates the minimum distance between the given geometries, geom1 and geom2, using the specified solution type. Solution types available:

  • 0 (default) - Euclidean returns 2-D Euclidean distance
  • 1 - Haversine returns minimum sphere distance in meters
  • 2 - Vincenty returns minimum spheroid distance in meters, more accurate than Haversine but slower performance

Note: If geom1 and geom2 intersect (verify using ST_INTERSECTS), the distance will always be 0.

Calculates the minimum distance between the given points, x1, y1 and x2, y2, using the specified solution type. Solution types available:

  • 0 (default) - Euclidean returns 2-D Euclidean distance
  • 1 - Haversine returns minimum sphere distance in meters
  • 2 - Vincenty returns minimum spheroid distance in meters, more accurate than Haversine but slower performance

Returns 1 (true) if the maximum distance between geometries geom1 and geom2 is less than or equal to the specified distance of each other using the specified solution type. If geom1 or geom2 is null, 0 (false) is returned. Solution types available:

  • 0 (default) - Euclidean uses degrees to calculate distance
  • 1 - Sphere uses meters to calculate distance
  • 2 - Spheroid uses meters to calculate distance, more accurate than sphere but slower performance

Returns 1 (true) if the minimum distance between geometries geom1 and geom2 is within the specified distance of each other using the specified solution type. Solution types available:

  • 0 (default) - Euclidean uses degrees to calculate distance
  • 1 - Sphere uses meters to calculate distance
  • 2 - Spheroid uses meters to calculate distance, more accurate than sphere but slower performance

Returns an ellipse using the following values:

  • centerx -- the x coordinate or longitude used to center the ellipse
  • centery -- the y coordinate or latitude used to center the ellipse
  • height -- the height of the ellipse (in degrees)
  • width -- the width of the ellipse (in degrees)

Returns 1 (true) if geom1 is within the specified distance of the bounding box of geom2 using the specified solution type. Solution types available:

  • 0 (default) - Euclidean uses degrees to calculate distance
  • 1 - Sphere uses meters to calculate distance

Returns the 3-dimensional version (e.g., X, Y, and Z coordinates) of geom, a provided geometry or set of geometries (e.g., via GEOMETRYCOLLECTION or WKT column name), using z as the geometry's new z-value. The provided z-values can also be derived from a numeric column. If no z is provided, a 0 will be applied.

If a WKT column is provided for geom and a numeric column is provided for z, the z values will be matched to the provided geometries by row in the source table. If a singular geometry is provided for geom and a column is provided for z, three-dimensional versions of the provided geometry will be returned for each z value found in the provided z column. If columns are provided for both geom and z and nulls are present in either column, the row containing null values will be skipped in the results.

Returns a hash string representation of the given geometry geom with specified precision (the length of the geohash string). The longer the precision, the more precise the hash is. By default, precision is set to 20 the max for precision is 32. Returns null if geom is an empty geometry.

The value returned will not be a geohash of the exact geometry but a geohash of the centroid of the given geometry

Returns the type ID of from geom. Type and ID mappings:

  • POINT = 0
  • LINESTRING = 1
  • POLYGON = 3
  • MULTIPOINT = 4
  • MULTILINESTRING = 5
  • MULTIPOLYGON = 6
  • GEOMETRYCOLLECTION = 7

Creates a MULTIPOLYGON containing a grid of hexagons between given minimum and maximum points of a bounding box. The minimum point cannot be greater than or equal to the maximum point. The size (in meters) of the individual hexagons' sides is determined by cell_side. The cell_side cannot be greater than the width or height of the bounding box. The maximum number of cells that can be produced is determined by limit, a positive integer. Supported values for limit:

  • -1 - No limit to the number of cells generated (effectively limited by system memory)
  • 0 (default) - 100 million cells
  • <n> - Custom limit of n cells

If the custom limit request specifies more cells (based on the bounding box and the cell_side) than the system limit, a null is returned.

Returns the length of the geometry if it is a LINESTRING or MULTILINESTRING. Returns 0 if another type of geometry, e.g., POINT, MULTIPOINT, etc. GEOMETRYCOLLECTIONs are also supported but the aforementioned type limitation still applies the collection will be recursively searched for LINESTRINGs and MULTILINESTRINGs and the summation of all supported geometry types is returned (unsupported types are ignored). Solution types available:

  • 0 (default) - 2D Euclidean length
  • 1 - length on a sphere in meters
  • 2 - length on a spheroid in meters

Returns the LINESTRING that represents the longest line of points between the two geometries. If multiple longest lines are found, only the first line found is returned. If geom1 or geom2 is empty, null is returned. Solution types available:

  • 0 (default) - Euclidean uses degrees to calculate the longest line
  • 1 - Sphere uses meters to calculate the longest line
  • 2 - Spheroid uses meters to calculate the longest line, more accurate than sphere but slower performance

Creates a LINESTRING from geom if it is a MULTIPOINT. If geom is a POINT, there must be at least one other POINT to construct a LINESTRING. If geom is a LINESTRING, it must have at least two points. Returns null if geom is not a POINT, MULTIPOINT, or LINESTRING

This function can be rather costly in terms of performance

Creates a POINT at the given coordinate

This function can be rather costly in terms of performance

Creates a POLYGON from geom. Inputs must be closed LINESTRINGs

This function can be rather costly in terms of performance

Returns the maximum distance between the given geom1 and geom2 geometries using the specifed solution type. If geom1 or geom2 is empty, null is returned. Solution types available:

  • 0 (default) - returns maximum 2-D Euclidean distance
  • 1 - Sphere returns maximum distance in meters
  • 2 - Spheroid returns maximum distance in meters, more accurate than sphere but slower performance

Creates multiple buffers at specified distance around the given geom geometry. Multiple distances are specified as comma-separated values in an array, e.g., [10,20,30]. Valid values for outside are:

  • FULL -- indicates that buffers will overlap or cover the given geom geometry. This is the default.
  • OUTSIDE_ONLY -- indicates that buffers will be rings around the given geom geometry.

Returns the perimeter of the geometry if it is a POLYGON or MULTIPOLYGON. Returns 0 if another type of geometry, e.g., POINT, MULTIPOINT, LINESTRING, or MULTILINESTRING. GEOMETRYCOLLECTIONs are also supported but the aforementioned type limitation still applies the collection will be recursively searched for POLYGONs and MULTIPOLYGONs and the summation of all supported geometry types is returned (unsupported types are ignored). Solution types available:

  • 0 (default) - 2D Euclidean length
  • 1 - length on a sphere in meters
  • 2 - length on a spheroid in meters

Returns a POINT using the given geohash with a precision set by the integer precision. If precision is specified, the function will use as many characters in the hash equal to precision to create the geometry. If no precision is specified, the full length of the geohash is used.

The POINT returned represents the center of the bounding box of the geohash

Creates a MULTIPOLYGON containing a square-shaped grid of points between given minimum and maximum points of a bounding box. The minimum point cannot be greater than or equal to the maximum point. The distance between the points (in meters) is determined by cell_side. The cell_side cannot be greater than the width or height of the bounding box. The maximum number of cells that can be produced is determined by limit, a positive integer. Supported values for limit:

  • -1 - No limit to the number of cells generated (effectively limited by system memory)
  • 0 (default) - 100 million cells
  • <n> - Custom limit of n cells

If the custom limit request specifies more cells (based on the bounding box and the cell_side) than the system limit, a null is returned.

Returns the given geom but segmentized n number of times depending on how the max_segment_length distance (in units based on the solution type) divides up the original geometry. The new geom is guaranteed to have segments that are smaller than the given max_segment_length. Note that POINTs are not able to be segmentized. Collection geometries (GEOMETRYCOLLECTION, MULTILINESTRING, MULTIPOINT, etc.) can be segmentized, but only the individual parts will be segmentized, not the collection as a whole. Solution types available:

  • 0 - Euclidean uses degrees to calculate distance
  • 1 (default) - Sphere uses meters to calculate distance

Returns a simplified version of the given geom using an algorithm to reduce the number of points comprising a given geometry while attempting to best retain the original shape. The given tolerance determines how much to simplify the geometry. The higher the tolerance, the more simplified the returned geometry. Some holes might be removed and some invalid polygons (e.g., self-intersecting, etc.) might be present in the returned geometry. Only (MULTI)LINESTRINGs and (MULTI)POLYGONs can be simplified, including those found within GEOMETRYCOLLECTIONs any other geometry objects will be returned unsimplified.

The tolerance should be provided in the same units as the data. As a rule of thumb, a tolerance of 0.00001 would correspond to about one meter.

Returns a simplified version of the given geom using an algorithm to reduce the number of points comprising a given geometry while attempting to best retain the original shape. The given tolerance determines how much to simplify the geometry. The higher the tolerance, the more simplified the returned geometry. No holes will be removed and no invalid polygons (e.g., self-intersecting, etc.) will be present in the returned geometry. Only (MULTI)LINESTRINGs and (MULTI)POLYGONs can be simplified, including those found within GEOMETRYCOLLECTIONs any other geometry objects will be returned unsimplified.

The tolerance should be provided in the same units as the data. As a rule of thumb, a tolerance of 0.00001 would correspond to about one meter.

Creates a MULTIPOLYGON containing a grid of squares between given minimum and maximum points of a bounding box. The minimum point cannot be greater than or equal to the maximum point. The size (in meters) of the individual squares' sides is determined by cell_side. The cell_side cannot be greater than the width or height of the bounding box. The maximum number of cells that can be produced is determined by limit, a positive integer. Supported values for limit:

  • -1 - No limit to the number of cells generated (effectively limited by system memory)
  • 0 (default) - 100 million cells
  • <n> - Custom limit of n cells

If the custom limit request specifies more cells (based on the bounding box and the cell_side) than the system limit, a null is returned.

Creates a MULTIPOLYGON containing a grid of triangles between given minimum and maximum points of a bounding box. The minimum point cannot be greater than or equal to the maximum point. The size (in meters) of the individual triangles' sides is determined by cell_side. The cell_side cannot be greater than the width or height of the bounding box. The maximum number of cells that can be produced is determined by limit, a positive integer. Supported values for limit:

  • -1 - No limit to the number of cells generated (effectively limited by system memory)
  • 0 (default) - 100 million cells
  • <n> - Custom limit of n cells

If the custom limit request specifies more cells (based on the bounding box and the cell_side) than the system limit, a null is returned.


Empty geometries in GEOMETRYCOLLECTION - Geographic Information Systems

MySQL 4.1 introduces spatial extensions to allow the generation, storage, and analysis of geographic features. Currently, these features are available for MyISAM tables only.

This chapter covers the following topics:

  • The basis of these spatial extensions in the OpenGIS geometry model
  • Data formats for representing spatial data
  • How to use spatial data in MySQL
  • Use of indexing for spatial data
  • MySQL differences from the OpenGIS specification

MySQL implements spatial extensions following the specification of the Open GIS Consortium (OGC). This is an international consortium of more than 250 companies, agencies, and universities participating in the development of publicly available conceptual solutions that can be useful with all kinds of applications that manage spatial data. The OGC maintains a web site at http://www.opengis.org/.

In 1997, the Open GIS Consortium published the OpenGIS (R) Simple Features Specifications For SQL , a document that proposes several conceptual ways for extending an SQL RDBMS to support spatial data. This specification is available from the Open GIS web site at http://www.opengis.org/techno/implementation.htm. It contains additional information relevant to this chapter.

MySQL implements a subset of the SQL with Geometry Types environment proposed by OGC. This term refers to an SQL environment that has been extended with a set of geometry types. A geometry-valued SQL column is implemented as a column that has a geometry type. The specifications describe a set of SQL geometry types, as well as functions on those types to create and analyze geometry values.

A geographic feature is anything in the world that has a location. A feature can be:

  • An entity. For example, a mountain, a pond, a city.
  • A space. For example, a postcode area, the tropics.
  • A definable location. For example, a crossroad, as a particular place where two streets intersect.

You can also find documents that use term geospatial feature to refer to geographic features.

Geometry is another word that denotes a geographic feature. The original meaning of the word geometry denotes a branch of mathematics. Another meaning comes from cartography, referring to the geometric features that cartographers use to map the world.

This chapter uses all of these terms synonymously: geographic feature, geospatial feature, feature, or geometry. The term most commonly used here is geometry.

Let's define a geometry as a point or an aggregate of points representing anything in the world that has a location.

The set of geometry types proposed by OGC's SQL with Geometry Types environment is based on the OpenGIS Geometry Model. In this model, each geometric object has the following general properties:

  • It is associated with a Spatial Reference System, which describes the coordinate space in which the object is defined.
  • It belongs to some geometry class.

The geometry classes define a hierarchy as follows:

  • Geometry (non-instantiable)
    • Point (instantiable)
    • Curve (non-instantiable)
      • LineString (instantiable)
        • Line
        • LinearRing
        • Polygon (instantiable)
        • MultiPoint (instantiable)
        • MultiCurve (non-instantiable)
          • MultiLineString (instantiable)
          • MultiPolygon (instantiable)

          Some of these classes are abstract (non-instantiable). That is, it is not possible to create an object of these classes. Other classes are instantiable and objects may be created of them. Each class has properties and instantiable classes may have assertions (rules that define valid class instances).

          Geometry is the base class. It's an abstract class. The instantiable subclasses of Geometry are restricted to zero-, one-, 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 (that is, all defined geometries include their boundary).

          The base Geometry class has subclasses for Point , Curve , Surface and GeometryCollection :

          • 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 modelling 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 .

          Geometry , Curve , Surface , MultiCurve , and MultiSurface are defined as non-instantiable classes. They define a common set of methods for their subclasses and are included for the reason of extensibility.

          Point , LineString , Polygon , GeometryCollection , MultiPoint , MultiLineString , and MultiPolygon are instantiable classes.

          Geometry is the root class of the hierarchy. It is a non-instantiable class but has a number of properties that are common to all geometry values created from any of the Geometry subclasses. These properties are described in the following list. (Particular subclasses have their own specific properties, described later.)

          17.2.3 Geometry properties

          A geometry value has the following properties:

          • 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.
          • Its coordinates in its Spatial Reference System, represented as double-precision (8-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. All geometries occupy 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 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:
          • The quality of being simple or non-simple. Geometry values of some types ( LineString , MultiPoint , MultiLineString) are either simple or non-simple. Each type determines its own assertions for being simple or non-simple.
          • The quality of being closed or not closed. Geometry values of some types ( LineString , MultiString ) are either closed or not closed. Each type determines its own assertions for being closed or not closed.
          • The quality of being empty or not 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:
            • -1 stands for empty geometries.
            • 0 stands for geometries with no length and no area.
            • 1 stands for geometries with non-zero length and zero area.
            • 2 stands for geometries with non-zero area.

            A Point is a geometry that represents a single location in coordinate space.

            17.2.5 Point Examples

            • Imagine a large-scale map of the world with many cities. A point could represent each city.
            • On a city map, a Point could represent a bus stop.

            17.2.6 Point Properties

            • 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 is a one-dimensional geometry, usually represented by a sequence of points. Particular subclasses of Curve define the type of interpolation between points. Curve is a non-instantiable class.

            17.2.8 Curve Properties

            • The coordinates of its points.
            • Curve is defined as 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 end point.
            • The boundary of a closed Curve is empty.
            • The boundary of a non-closed Curve consists of its two end points.
            • A Curve that is simple and closed is a LinearRing .

            A LineString is a Curve with linear interpolation between points.

            17.2.10 LineString Examples

            • On a world map, LineString objects could represent rivers.
            • In a city map, LineString objects could represent streets.

            17.2.11 LineString Properties

            • Coordinates of LineString 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's both closed and simple.

            A Surface is a two-dimensional geometry. It is a non-instantiable class. Its only instantiable subclass is Polygon .

            17.2.13 Surface Properties

            • 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.

            A Polygon is a planar Surface representing a multisided geometry. It is defined by a single exterior boundary and zero or more interior boundaries, where each interior boundary defines a hole in the Polygon .

            17.2.15 Polygon Examples

            17.2.16 Polygon Assertions

            • 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.
            • 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 one or more holes is not connected. Each hole defines a connected component of the exterior.

            In the above assertions, polygons are simple geometries. These assertions make a Polygon a simple geometry.

            A GeometryCollection is a geometry that is a collection of one or more geometries of any class.

            All the elements in a GeometryCollection must be in the same Spatial Reference System (that is, in the same coordinate system). GeometryCollection places no other constraints on its elements, although the subclasses of GeometryCollection described in the following sections may restrict membership. Retrictions may be based on:

            • Element type (for example, a MultiPoint may contain only Point elements)
            • Dimension
            • Constraints on the degree of spatial overlap between elements

            A MultiPoint is a geometry collection composed of Point elements. The points are not connected or ordered in any way.

            17.2.19 MultiPoint Examples

            • 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.

            17.2.20 MultiPoint Properties

            • MultiPoint is defined as 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 geometry collection composed of Curve elements. MultiCurve is a non-instantiable class.

            17.2.22 MultiCurve Properties

            • MultiCurve is defined as 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.
            • The boundary of a MultiCurve 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.

            A MultiLineString is a MultiCurve geometry collection composed of LineString elements.

            17.2.24 MultiLineString Examples

            A MultiSurface is a geometry collection composed of surface elements. MultiSurface is a non-instantiable class. Its only instantiable subclass is MultiPolygon .

            17.2.26 MultiSurface Assertions

            • 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.

            A MultiPolygon is a MultiSurface object composed of Polygon elements.

            17.2.28 MultiPolygon Examples

            17.2.29 MultiPolygon Assertions

            • The interiors of two Polygon values that are elements of a MultiPolygon may not intersect.
            • The boundaries of any two Polygon values that are elements of a MultiPolygon may not cross and may touch at only a finite number of points. (Crossing is also forbidden by the preceding assertion.)
            • A MultiPolygon may not have cut lines, spikes or punctures. A MultiPolygon is a regular, closed point set.
            • The interior of a MultiPolygon composed of more than one Polygon 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 .

            17.2.30 MultiPolygon Properties

            • A MultiPolygon is defined as a two-dimensional geometry.
            • The boundary of a MultiPolygon 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 element Polygon .
            • Every Curve in the boundary of an element Polygon is in the boundary of the MultiPolygon .

            This section describes the standard spatial data formats that are used to represent geometry objects in queries. They are:

            Internally, MySQL stores geometry values in a format that is not identical to either WKT or WKB format.

            The Well-Known Text (WKT) representation of Geometry is designed to exchange geometry data in ASCII form.

            Examples of WKT representations of geometry objects are:

            • A Point : Note that point coordinates are specified with no separating comma.
            • A LineString with four points:
            • A Polygon with one exterior ring and one interior ring:
            • A MultiPoint with three Point values:
            • A MultiLineString with two LineString values:
            • A MultiPolygon with two Polygon values:
            • A GeometryCollection consisting of two Point values and one LineString :

            A Backus-Naur grammar that specifies the formal production rules for writing WKT values may be found in the OGC specification document referenced near the beginning of this chapter.

            The Well-Known Binary (WKB) representation for geometric values is defined by the OpenGIS specifications. It is also defined in the ISO ``SQL/MM Part 3: Spatial'' standard.

            WKB is used to exchange geometry data as binary streams represented by BLOB values containing geometric WKB information.

            WKB uses 1-byte unsigned integers, 4-byte unsigned integers, and 8-byte double-precision numbers (IEEE 754 format). A byte is 8 bits.

            For example, a WKB value that corresponds to POINT(1 1) consists of this sequence of 21 bytes (each represented here by two hex digits):

            The sequence may be broken down into these components:

            Component representation is as follows:

            • 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.

            WKB values for more complex geometry values are represented by more complex data structures, as detailed in the OpenGIS specification.

            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 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 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:

            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.

            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 value 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.

            • Use the CREATE TABLE statement to create a table with a spatial column:
            • Use the ALTER TABLE statement to add or drop a spatial column to or from an existing table:

            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:

            Or conversion can take place prior to the INSERT :

            The following examples insert more complex geometries into the table:

            The preceding examples all use GeomFromText() to create geometry values. You can also use type-specific functions:

            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:
            • An ODBC application can send a WKB representation, binding it to a placeholder using an argument of BLOB type: 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 19.1.3.44 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.

            Fetching geometry values using internal format can be useful in table-to-table transfers:

            The AsText() function provides textual access to geometry values. It converts a geometry from internal format into a WKT string.

            The AsBinary() function provides binary access to geometry values. It converts a geometry from internal format into a BLOB containing the WKB value.

            After populating spatial columns with values, you are ready to query and analyze 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:

            • Functions that convert geometries between various formats
            • Functions that provide access to qualitative or quantitative properties of a geometry
            • Functions that describe relations between two geometries
            • Functions that create new geometries from existing ones

            Spatial analysis functions can be used in many contexts, such as:

            • Any interactive SQL program, like mysql or MySQLCC
            • Application programs written in any language that supports a MySQL client API

            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 section 17.4.2.1 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 section 17.4.2.2 Creating Geometry Values Using WKB Functions. AsText(g) Converts a value in internal geometry format to its WKT representation and returns the resulting string. AsBinary(g) Converts a value in internal geometry format to its WKB representation and returns the resulting binary value.

            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. 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 section 17.2.2 Class Geometry .) SRID(g) Returns an integer indicating the Spatial Reference System ID for the geometry value g . Envelope(g) Returns the Minimum Bounding Rectangle (MBR) for the geometry value g . The result is returned as a polygon value. The polygon is defined by the corner points of the bounding box:

            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) Currently, this function is a placeholder and should not be used. When implemented, its behavior will be as described in the next paragraph. 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.

            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. Y(p) Returns the Y-coordinate value for the point p as a double-precision number.

            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 . GLength(ls) Returns as a double-precision number the length of the LineString value ls in its associated spatial reference. IsClosed(ls) Returns 1 if the LineString value ls is closed (that is, it s StartPoint() and EndPoint() values are the same). Returns 0 if ls is not closed, and -1 if it is NULL . NumPoints(ls) Returns the number of points in the LineString value ls . PointN(ls,n) Returns the n -th point in the Linestring value ls . Point numbers begin at 1. StartPoint(ls) Returns the Point that is the start point of the LineString value ls .

            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 .

            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 .

            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 .

            In the section section 17.5.2 Geometry 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 . 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 . 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 .

            • The two geometries intersect
            • Their intersection results in a geometry that has a dimension that is one less than the maximum dimension of the two given geometries
            • Their intersection is not equal to either of the two given geometries

            It is known that search operations in non-spatial databases can be optimized using indexes. This is true for spatial databases as well. With the help of a great variety of multi-dimensional indexing methods that have already been designed, it's possible to optimize spatial searches. The most typical of these are:

            • Point queries that search for all objects that contain a given point
            • Region queries that search for all objects that overlap a given region

            MySQL utilizes R-Trees with quadratic splitting to index spatial columns. A spatial index is built using the MBR of a geometry. For most geometries, the MBR is a minimum rectangle that surrounds the geometries. For a horizontal or a vertical linestring, the MBR is a rectangle degenerated into the linestring. For a point, the MBR is a rectangle degenerated into the point.

            MySQL can create spatial indexes using syntax similar to that for creating regular indexes, but extended with the SPATIAL keyword. Spatial columns that are indexed currently must be declared NOT NULL . The following examples demonstrate how to create spatial indexes.

            To drop spatial indexes, use ALTER TABLE or DROP INDEX :

            Example: Suppose that a table geom contains more than 32000 geometries, which are stored in the column g of type GEOMETRY . The table also has an AUTO_INCREMENT column fid for storing object ID values.

            To add a spatial index on the column g , use this statement:

            The optimizer investigates whether available spatial indexes can be involved in the search for queries that use a function such as MBRContains() or MBRWithin() in the WHERE clause. For example, let's say we want to find all objects that are in the given rectangle:

            Now let's check the way this query is executed, using EXPLAIN :

            Now let's check what would happen if we didn't have a spatial index:

            Let's execute the above query, ignoring the spatial key we have:

            When the index is not used, the execution time for this query rises from 0.00 seconds to 0.46 seconds.

            In future releases, spatial indexes will also be used for optimizing other functions. See section 17.5.4 Functions for Testing Spatial Relations Between Geometric Objects.


            Empty geometries in GEOMETRYCOLLECTION - Geographic Information Systems

            In release 4.1 MySQL introduces spatial extensions, which allow generating, storing and analysing of geographic features.

            A geographic feature is anything in the world that has a location.

            • An entity. For example, a mountain, a pond, a city.
            • A space. For example, a postcode area, the tropics.
            • A definable location. For example, a crossroad, as a particular place where two streets intersect.

            You can also find documents which use term geospatial feature to refer to geographic features.

            Geometry is another word that denotes a geographic feature. The original meaning of the word geometry denotes a branch of mathematics. Another meaning that comes from cartography, referring to the geometric features that cartographers use to map the world.

            We will mean the same thing using all these terms, a geographic feature , or a geospatial feature , or a feature , or a geometry , with geometry as the most used in this documentation.

            Let's define a geometry as a point or an aggregate of points representing anything in the world that has a location.

            MySQL implements spatial extensions following OpenGIS specifications.

            The OpenGIS Consortium (OGC), is an international consortium of more than 250 companies, agencies, universities participating in the development of publicly available conceptual solutions that can be useful with all kinds of applications that manage spatial data. See http://www.opengis.org/.

            In 1997, the OpenGIS Consortium published the OpenGIS (r) Simple Features Specifications For SQL , which proposes several conceptual ways for extending an SQL RDBMS to support spatial data. MySQL implements a subset of the SQL with Geometry Types environment proposed by OGC. This term refers to an SQL environment that has been extended with a set of geometry types. A geometry-valued SQL column is implemented as a column of a geometry type. The specifications describe a set of SQL geometry types, as well as functions on those types to create and analyse geometry values.

            The set of geometry types, proposed by OGC's SQL with Geometry Types environment, is based of OpenGIS Geometry Model . In this model, each geometric object:

            • is associated with a Spatial Reference System, which describes the coordinate space in which the object is defined.
            • belongs to some geometry class.
            • Geometry
              • Point
              • Curve
                • LineString
                  • Line
                  • LinearRing
                  • Polygon
                  • MultiPoint
                  • MultiCurve
                    • MultiLineString
                    • MultiPolygon

                    Geometry is the base class. It's an abstract (non-instantiable) class. The instantiable subclasses of Geometry are restricted to zero, one, 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).

                    The base Geometry class has subclasses for Point , Curve , Surface and GeometryCollection .

                    Curve stands for 1-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 specialised 0, 1 and two-dimensional collection classes named MultiPoint , MultiLineString and MultiPolygon for modelling geometries corresponding to collections of Points , LineStrings and Polygons respectively. MultiCurve and MultiSurface are introduced as abstract superclasses that generalise the collection interfaces to handle Curves and Surfaces .

                    Geometry , Curve , Surface , MultiCurve and MultiSurface are defined as non-instantiable classes, it is not possible to create an object of these classes. They define a common set of methods for its subclasses and included for the reason of extensibility.

                    Point, LineString, Polygon, GeometryCollection, MultiPoint, MultiLineString, MultiPolygon are instantiable classes (marked bold in the hierarchy tree).

                    Geometry is the root class of the hierarchy. Each geometry is described by a number of its properties. Particular subclasses of the root class Geometry have their own specific properties. Properties, which are common for all geometry subclasses, are described in the list below. Geometry is a non-instantiable class.

                    • The type that a geometry belongs to. Each geometry belongs to one of instantiable classes in the hierarchy.
                    • Its SRID , the identifier of a geometry's associated Spatial Reference System which describes the coordinate space in which the geometry object is defined.
                    • Geometry's coordinates in its Spatial Reference System, represented as double precision (8 byte) numbers. All non-empty geometries include at least one pair of (X,Y) coordinates. Empty geometries contain no coordinates.
                    • Its interior, boundary and exterior . All geometries occupy 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 geometry's interior and exterior.
                    • Its MBR , or Envelope, the geometry's Minimum Bounding Rectangle. This is the bounding geometry, formed by the minimum and maximum (X,Y) coordinates :
                    • The quality of being simple or non-simple . Geometry values of some types (LineString, MultyPoint, MultiLineString) are either simple of non-simple. Each type determines its own assertions for being simple or non-simple.
                    • The quality of being closed or not closed . Geometry values of some types (LineString, MultiString) are either closed or not closed. Each type determines its own assertions for being closed or not closed.
                    • The quality of being empty or not empty A geometry is empty if it does not have any points. Exterior, interior and boundary of an empty geometry are not defined, i.e., they are represented by a NULL value. An empty geometry is defined to be always simple. An empty geometry has an area of 0.
                    • Its dimension A geometry can have a dimension of -1, 0, 1 or 2.
                      • -1 stands for empty geometries.
                      • 0 stands for geometries with no length and no area.
                      • 1 stands for geometries with non-zero length and zero area.
                      • 2 stands for geometries with non-zero area.

                      A Point is a geometry that represents a single location in coordinate space.

                      9.2.5 Point examples

                      • Imagine a large-scale map of the world with a lot of cities. A point could represent each city.
                      • On a city map, a Point could represent a bus stop.

                      9.2.6 Point properties

                      • X-coordinate value.
                      • Y-coordinate value.
                      • The Boundary of a Point is an empty set.
                      • A Point is defined as zero-dimensional geometry.

                      A Curve is a one-dimensional geometry, usually represented by a sequence of points. Particular subclasses of Curve specify the form of the interpolation between points. Curve is a non-instantiable class.

                      9.2.8 Curve properties

                      • Coordinates of its points.
                      • Curve is defined as 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 end point.
                      • The boundary of a closed Curve is empty.
                      • The boundary of a non-closed Curve consists of its two end points.
                      • A Curve that is simple and closed is Ring.

                      A LineString is a Curve with linear interpolation between points.

                      9.2.10 LineString examples

                      • On a world map a LineStrings could represent rivers.
                      • In a city map a LineStrings could represent streets.

                      9.2.11 LineString properties

                      • Coordinates of LineString 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, of it's both closed and simple.

                      A Surface is a two-dimensional geometric object.

                      9.2.13 Surface properties

                      • A Surface is defined as a two-dimensional geometry.
                      • The OpenGIS specification defines a simple Surface as consisting of a single 'patch' that is associated with one '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.

                      The only instantiable subclass of Surface defined in OpenGIS specification, is Polygon.

                      A Polygon is a planar Surface representing a multisided geometry, defined by one exterior boundary and zero or more interior boundaries. Each interior boundary defines a hole in the Polygon.

                      9.2.15 Polygon examples

                      1. The boundary of a Polygon consists of a set of LinearRings (i.e. LineStrings that are both simple and closed) that make up its exterior and interior boundaries.
                      2. 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.
                      3. A Polygon may not have cut lines, spikes or punctures.
                      4. The Interior of every Polygon is a connected point set.
                      5. The Exterior of a Polygon with one or more holes is not connected. Each hole defines a connected component of the Exterior.

                      In the above assertions, polygons are simple geometries.

                      A GeometryCollection is a geometry that is a collection of one 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.

                      • Element types
                      • Dimension.
                      • Other constraints on the degree of spatial overlap between elements.

                      A MultiPoint is a collection whose elements are restricted to Points. The points are not connected or ordered in any way.

                      9.2.18 MultiPoint examples

                      9.2.19 MultiPoint properties

                      • MultiPoint is defined as a zero-dimensional geometry.
                      • A MultiPoint is simple if no two Points in the MultiPoint are equal (have identical coordinate values).
                      • The boundary of a MultiPoint is empty set.

                      A MultiCurve is a geometry collection whose elements are Curves. MultiCurve is a non-instantiable class.

                      9.2.21 MultiCurve properties

                      • 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 defined as a one-dimensional geometry.
                      • A MultiCurve is closed if all of its elements are closed.
                      • The boundary of a closed MultiCurve is always empty.

                      A MultiLineString is a MultiCurve whose elements are LineStrings.

                      9.2.23 MultiLineString examples

                      A MultiSurface is a geometric collection whose elements are surfaces. MultiSurface is a non-instantiable class.

                      9.2.25 MultiSurface assertions

                      1. The interiors of any two surfaces in a MultiSurface may not intersect.
                      2. The boundaries of any two elements in a MultiSurface may intersect at most at a finite number of points.

                      The only instantiable subclass of MultiSurface is MultiPolygon.

                      A MultiPolygon is a MultiSurface whose elements are Polygons.

                      9.2.27 MultiPolygon examples

                      9.2.28 The assertions for MultiPolygons are:

                      1. The interiors of two Polygons that are elements of a MultiPolygon may not intersect.
                      2. The Boundaries of any two Polygons that are elements of a MultiPolygon may not cross and may touch at only a finite number of points. (Note that crossing is already forbidden by the first assertion.)
                      3. A MultiPolygon may not have cut lines, spikes or punctures a MultiPolygon is a Regular, Closed point set.
                      4. The interior of a MultiPolygon with more than one 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.

                      9.2.29 MultiPolygon properties

                      • MultiPolygon is defined as two-dimensional geometry.
                      • 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 one element Polygon, and every Curve in the boundary of an element Polygon is in the boundary of the MultiPolygon.

                      This section describes the standard spatial data formats that are used to store geometry objects.

                      The Well-Known Text (WKT) representation of Geometry is designed to exchange geometry data in ASCII form.

                      Examples of WKT representations of geometry objects are:

                      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 zero 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 7, 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.

                      The text representation of the implemented instantiable geometric types conforms to this grammar:

                      • The notation <>* denotes zero or more repetitions of the tokens within the braces
                      • The braces do not appear in the output token list.

                      Well-Known Binary (WKB) representation is defined by the OpenGIS specifications. It's also defined in the ISO "SQL/MM Part 3: Spatial" standard.

                      WKB is used to exchange geometry data as binary streams represented by BLOB values containing geometic information, according to the structures described below.

                      WKB uses the following basic type definitions:

                      A WKB which corresponds to POINT(1,1) looks like this sequence of 21 bytes:

                      MySQL provides a hierarchy of datatypes, corresponding to the OpenGIS Geometry Model.

                      • GEOMETRY
                      • POINT
                      • LINESTRING
                      • POLYGON
                      • MULTIPOINT
                      • MULTILINESTRING
                      • MULTIPOLYGON
                      • GEOMETRYCOLLECTION

                      The GEOMETRY type can store geometries of any type, other types restrict their values to a partilcular geometry type. GEOMETRYCOLLECTION can store a collection of objects of any type, other collection types restrict the type of collection members to a particular geometry type.

                      MySQL provides a number of function which take a Well-Known Text representation and, optionally, a spatial reference system identifier as input parameters, and return the corresponding geometry.

                      GeomFromText() accepts a WKT of any geometry type as its first argument.

                      For construction of geometry values restricted to a particular type, an implementation also provides a type-specific construction function for each geometry type.

                      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 (Note: MySQL does not yet implement these):

                      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.

                      MySQL provides a set of functions which take a BLOB containing Well-Known Binary representation and, optionally, a spatial reference system identifier (SRID) as their input parameters, and return the corresponding geometry.

                      GeomFromWKB can accept a WKB of any geometry type as its first argument. For construction of geometry values restricted to a particular type, an implementation also provides a specific construction function for each type of geometry as described in the list above.

                      As an optional feature, an implementation may also support the 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 (Note: MySQL does not yet implement these):

                      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.

                      Note: the functions listed in this section are not yet implemented in the current version.

                      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 s containing geometry WKB representations. The results of these functions can be substituted as first argument for GeomFromWKB() function family.

                      Point(x,y) Constructs a WKBPoint using its coordinates. MultiPoint(WKBPoint,WKBPoint. WKBPoint) Constructs a WKBMultiPoint using WKBPoints. When any argument is not WKBPoint, the return value is NULL. LineString(WKBPoint,WKBPoint. WKBPoint) Constructs a WKBLineString from a number of WKBPoints. When any argument is not WKBPoint, the return value is NULL. When the number of WKBPoints is less than two the return value is NULL. MultiLineString(WKBLineString,WKBLineString. WKBLineString) Constructs a WKBMultiLineString using using WKBLineStrings. When any argument is not WKBLineString, the return value is NULL. Polygon(WKBLineString,WKBLineString. WKBLineString) Constructs a Polygon from a number of WKBLineStrings. When any argument is not representing WKB of a LinearRing (i.e. not closed and simple LineString) the return value is NULL. MultiPolygon(WKBPolygon,WKBPolygon. WKBPolygon) Constructs a WKBMultiPolygon from a set of WKBPolygons. When any argument is not a WKBPolygon, the rerurn value is NULL. GeometryCollection(WKBGeometry,WKBGeometry. WKBGeometry) Constucts a GeometryCollection. When 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.

                      CREATE TABLE Use the CREATE TABLE statement to create a table with a spatial column: ALTER TABLE Use the ALTER TABLE statement to add a spatial column to an existing table:

                      After you have created spatial columns, you can populate them with your spatial data.

                      To populate spatially enabled columns, MySQL supports two spatial formats (described previously), Well Known Text (WKT) and Well-Known Binary (WKB) representation.

                      Note, a client application program which wants to use WKB representation of geometry values, is responsible for sending correctly formed WKB in queries to server.

                      Inserting a Point(1,1) with binary literal syntax: An ODBC applications can send a WKB representation, binding it as an argument of BLOB type: Inserting Point(1,1) using the result of mysql_escape_string() in libmysqlclient applications.

                      Geometry values, previously stored in a table, can be fetched in either WKT or WKB representation.

                      The AsText() function provides textual access to geometry values by converting them into a WKT string.

                      The AsBinary() and AsWKB() functions provide binary access to geometry values by converting them into a BLOB containing WKB.

                      AsBinary() and AsWKB() return a BLOB with a geometry in its WKB representation.

                      • Any interactive SQL program, like mysql or MySQLCC .
                      • Application programs in any languages supporting a MySQL client API.
                      • Functions that convert geometries between various formats.
                      • Functions that describe qualitative or quantitative properties of a geometry.
                      • Functions that describe relations between two geometries.
                      • Functions that create new geometries from existing ones.

                      As discussed (see section 9.4.1 MySQL Spatial Data Types, see section 9.4.4 Populating Spatial Columns), MySQL understands Well-Known Text (WKT) and Well-Known Binary (WKB) geometry representations through support of these functions:

                      GeomFromWKT(string wkt [,integer srid]): geometry Converts WKT representation into internal geometry format. A number of type-specific functions are also supported. GeomFromWKB(binary wkb [,integer srid]): geometry Converts WKB representation into internal geometry format A number of type-specific functions are also supported. AsWKT(geometry g): string Converts internal geometry format into WKT representation. AsWKB(geometry g): binary Converts internal geometry format into WKB representation.

                      Functions that belong to this group take a geometry value as their argument and return some quantitive or qualitive property of this geometry. Some functions restrict their argument type.

                      These functions don't restrict their argument and accept a geometry of any type.

                      GeometryType(geometry g):string Returns as string the name of the geometry subtype of which this geometry instance is a member. Dimension(geometry g):integer The inherent dimension of this Geometry object, which can be -1, 0, 1 or 2. SRID(geometry g):integer Returns the Spatial Reference System ID for this geometry. Envelope(geometry g):geometry The Minimum Bounding Rectangle (MBR) for this geometry, returned as a polygon. The polygon is defined by the corner points of the bounding box:

                      Note: MySQL does not yet implement the following functions:

                      Boundary(g:Geometry):Geometry Returns the closure of the combinatorial boundary of this Geometry. IsEmpty(geometry g):Integer Returns 1 (TRUE) if this Geometry is the empty geometry. If true, then this Geometry represents the empty point set. IsSimple(geometry g):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.

                      Note: MySQL does not yet implement the following functions:

                      IsRing(LineString l):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(LineString l):Integer Returns 1 (TRUE) if this LineString is closed (StartPoint() == EndPoint()).

                      Note: MySQL does not yet implement the following functions:

                      Centroid(Polygon p):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.

                      Note: MySQL does not yet implement the following functions:

                      Centroid(MultyPolygon p):Point The mathematical centroid for this MultiPolygon as a Point. The result is not guaranteed to be on this MultiPolygon. PointOnSurface(MultiPolygon m):Point A Point guaranteed to be on this MultiPolygon.

                      Note: Functions for specific geometry type return NULL if the passed geometry is of wrong geometry type. For example Area() returns NULL if object type is neither Polygon nor MultiPolygon.

                      In the section (see section 9.5.2 Functions To Analyse Geometry Properties), we've already discussed some functions that can construct new geometries from the exising ones:

                      • Envelope(geometry g):geometry
                      • StartPoint(LineString l):Point
                      • EndPoint(LineString l):Point
                      • PointN(LineString l,integer n):Point
                      • ExteriorRing(Polygon p):LineString
                      • InteriorRingN(Polygon p, Integer N):LineString
                      • GeometryN(GeometryCollection g,integer N):Geometry

                      OpenGIS proposes a number of other functions that can produce geometries. They are designed to implement Spatial Operators.

                      Note: These functions are not yet implemented. They should appear in the future releases.

                      Intersection(Geometry g1,g2):Geometry A geometry that represents the point set intersection of g1 with g2. Union(Geometry g1,g2):Geometry A geometry that represents the point set union of g1 with g2. Difference(Geometry g1,g2):Geometry A geometry that represents the point set difference of g1 with g2. SymDifference(Geometry g1,g2):Geometry A geometry that represents the point set symmetric difference of g1 with g2. Buffer(Geometry g, double d):Geometry A geometry that represents all points whose distance from g is less than or equal to distance of d . ConvexHull(Geometry g):Geometry A geometry that represents the convex hull of g.

                      The functions described in this sections take two geometries as input parameters and return a qualitive or quantitive relation between them.

                      The current release provides some functions that can test relations between mininal bounding rectangles of two geometries. They include:

                      MBRContains(geom1,geom2) Returns 1 if the Minimum Bounding Rectangle of geom1 contains the Minimum Bounding Rectangle of geom2 . Otherwise, 0 is returned. MBRWithin(geom1,geom2) Returns 1 if the Minimum Bounding Rectangle of geom1 is within the Minimum Bounding Rectangle of geom2 . Otherwise, 0 is returned. MBRDisjoint(geom1,geom2) Returns 1 if the Minimum Bounding Rectangles of the two geometries do not intersect. Otherwise, 0 is returned. MBREqual(geom1,geom2) Returns 1 if the Minimum Bounding Rectangles of the two geometries are the same. Otherwise, 0 is returned. MBRIntersects(geom1,geom2) Returns 1 if the Minimum Bounding Rectangles of the two geometries intersect. Otherwise, 0 is returned. MBROverlaps(geom1,geom2) Returns 1 if the Minimum Bounding Rectangles of the two geometries overlaps. Otherwise, 0 is returned. MBRTouches(geom1,geom2) Returns 1 if the Minimum Bounding Rectangles of the two geometries overlaps. Otherwise, 0 is returned.

                      Note: The functions given in the list below are not yet implemented. These functions will provide full (not MBR-based only) support for spatial analysis.

                      Contains(geom1,geom2) Returns 1 if geom1 completely contains geom2 , otherwise 0 is returned. Crosses(geom1,geom2) Returns 1 if geom1 spatially crosses geom2 . If geom1 is a polygon or a multipolygon, NULL is returned. If geom2 is a point or a multipoint, NULL is returned. Otherwise 0 is returned. The term spatially crosses denotes a spatial relation when two given geometries intersect, and their intersection results in a geometry that has a dimension that is one less than the maximum dimension of the two given geometries, and the intersection is not equal to any of the two given geometries. Disjoint(geom1,geom2) Returns 1 if geom1 is spatially disjoint from geom2 , i.e. if given geometries do not intersect. Otherwise, 0 is returned. Equal(geom1,geom2) Returns 1 if geom1 is spatially equal to geom2 , otherwise 0 is returned. Intersects(geom1,geom2) Returns 1 if geom1 spatially intersects geom2 , otherwise 0 is returned. Overlaps(geom1,geom2) Returns 1 if geom1 spatially overlaps geom2 , otherwise, 0 is returned. 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(geom1,geom2) Returns 1 if geom1 spatially touches geom2 , otherwise, 0 is returned. Two geometries spatially touch if the interior of both geometries do not intersect, but the boundary of one of the geometries intersects either the boundary or the interioir of the geometries. Within(geom1,geom2) Returns 1 if geom1 is spatially within geom2 , otherwise, 0 is returned. Distance(geom1:Geometry,geom2:Geometry):Double The shortest distance between any two points in the two geometries.

                      It is known that search operations in usual databases can be optimised using indexes. This is still true for spatial databases. With help of great variery of multi-dimensional indexing methods which have already been designed in the world, it's possible to optimise spatial searches, the most typical of which are:

                      • A point query. Searching for all objects that contain a given point.
                      • A region query. Searching for all objects that overlap a given region.

                      MySQL utilises R-Trees with quadratic splitting to index spatial columns. A spatial index is built using the MBR of a geometry. For most geometries, the MBR is a minimum rectangle that surrounds the geometries. For a horizontal or a vertical linestring as well as for a point, the MBR is a degenerated rectangle, into the linestring and the point respectively.

                      MySQL can create spatial indexes in the same way it can create regular indexes. The normal syntax for creating indexes is extended with the SPATIAL keyword:

                      With CREATE TABLE : With CREATE INDEX : With ALTER TABLE :

                      Let's say we have a database with more than 32000 geometries. Geometries are stored in the field g of type GEOMETRY . The table also has a field fid , storing object IDs, with the AUTO_INCREMENT attribute.

                      The optimiser investigates if available spatial indexes can be involved in the search whenever a query with a function like MBRContains() or MBRWithin() in the WHERE clause is executed.

                      For example, let's say we want to find all objects that are in the given rectangle:

                      Now let's check the way this query is executed, using EXPLAIN :

                      Now let's check what would happen if we didn't have a spatial index:

                      Lets execute the above query, ignoring the spatial key we have:

                      The execution time for this query rises from 0.00 seconds to 0.46 seconds, when the index is not used.

                      In the future releases, spatial indexes will also be used for optimising other functions. See section 9.5.4 Functions For Testing Spatial Relations Between Geometric Objects.


                      2 Answers 2

                      I checked the shapefile that you have uploaded onto the dropbox. It turns out the shapefile is not being read correctly.

                      when I print out the records for the MAR_locations_v2.shp, this is what i get

                      Record(MULTIPOLYGON (((386523.5418971451 6432084.711430285, 386523.5418971451 6432184.711430287, 386573.541897146 6432184.711430287, 386573.541897146 6432084.711430285, 386523.5418971451 6432084.711430285))), <'Name': ''>, )

                      Record(MULTIPOLYGON (((386405.7078714892 6438043.356735789, 386405.7078714892 6438143.356735789, 386505.7078714911 6438143.356735789, 386505.7078714911 6438043.356735789, 386405.7078714892 6438043.356735789))), <'Name': ''>, )

                      and when i print the records for the AdditionalPumping_south_v2_test.shp, i get

                      this is not a complete answer to the question, however it answers the part why you are not getting the plot. You might want to save your shapefile differently in order to get it to plot.


                      Conditional Functions

                      Evaluates expr: returns the first value from the values list whose corresponding match from the matches list is equal to expr returns value_if_no_match if expr is not equal to any of the matches in the matches list

                      A null cannot be used for either a match or value, but value_if_no_match can be null.

                      ParameterDescription
                      exprany expression to match against the list of matches
                      matchesa comma-delimited list of constants to match expr against needs to be of the same data type as expr and have the same number of list items as the values list
                      valuesa comma-delimited list of constants, one of which will be returned in the event that expr matches an item in the matches list needs to be of the same data type as value_if_no_match and have the same number of list items as the matches list
                      value_if_no matchany value to return in the event of a match needs to be of the same data type as the list items in values

                      Evaluates expr: if true, returns value_if_true otherwise, if false or null, returns value_if_false see Short-Circuiting for error-checking details

                      When an integer column is used directly, this function will will interpret non-zero values as true and zero values as false.

                      Conversion Functions

                      For the CAST() and CONVERT() functions, valid destination types are:

                      • bool
                      • int8
                      • int16
                      • int
                      • long
                      • float
                      • double
                      • decimal
                      • string
                      • char1
                      • char2
                      • char4
                      • char8
                      • char16
                      • char32
                      • char64
                      • char128
                      • char256
                      • timestamp
                      • date
                      • time
                      • datetime
                      • geometry

                      Returns the equivalent of original value converted to the destination type. The style parameter is currently only applicable when the destination type is string and the original value is of timestamp or datetime type.

                      Valid style codes include:

                      Converts the given date/time expr to a string matching the given format. The format must be a string literal--expressions are not supported at this time. Arbitrary text can be injected into the format string using double-quotes.

                      The returned string will be truncated at 32 characters.

                      Valid format codes include:

                      FormatDescription
                      YYYY4-digit year
                      YY2-digit year
                      MNumber of month in year, without leading zero [1 - 12]
                      MMNumber of month in year, with leading zero [01 - 12]
                      MON3-character abbreviation of month in title case e.g., Jan
                      MONTHFull name of month e.g., January
                      CNumber of day in month, without leading zero [1 - 31]
                      DDNumber of day in month, with leading zero [01 - 31]
                      DY3-character abbreviation of day of the week e.g., Fri
                      DAYFull name of day of the week e.g., Friday
                      HAlias for H12
                      HHAlias for HH12
                      H12Hour of day in 12-hour format, without leading zero [0 - 11]
                      HH12Hour of day in 12-hour format, with leading zero [00 - 11]
                      H24Hour of day in 24-hour format, without leading zero [0 - 23]
                      HH24Hour of day in 24-hour format, with leading zero [00 - 23]
                      MIMinute of hour [00 - 59]
                      SSSecond of minute [00 - 59]
                      AMBefore or after noon designator [AM, PM]
                      PMAlias for AM

                      Date/Time Functions

                      This section comprises the following functions:

                        , which can extract parts of date/time expressions, convert back and forth between data types, and return the current date/time , which can perform more complex date/type conversions

                      Date/Time Base Functions

                      Integer fields are assumed to be seconds since the epoch long/timestamp fields are assumed to be milliseconds since the epoch.

                      Extracts the day of the week from expr [1 - 7]

                      Returns the date of the next day of the week, provided as a day name in expr, that occurs after the given date

                      Some examples, given that 2000-10-10 is a Tuesday:

                      Extracts the quarter of the year from expr [1 - 4]

                      Calculates the difference between two date/time expressions, returning the result as an integral difference in the units specified more precisely, how many whole date/time intervals of type unit need to be added to (or subtracted from) begin to equal end up to the precision of the unit specified, using the unit types and rules specified in TIMESTAMPADD.

                      For example, if unit were MONTH, only the year & month of begin and end would be used in the calculation if unit were DAY, any time portion from begin & end would be dropped, and so on. This is unlike TIMESTAMPDIFF, which will consider the entirety of both begin & end in the calculation.

                      This is symmetric with TIMESTAMPADD in all cases, as adding 1 MONTH to Mar 31st results in Apr 30th, and the TIMEBOUNDARYDIFF in MONTH units between those two dates is 1.

                      Function CallResult
                      TIMEBOUNDARYDIFF(MONTH, DATE('2000-10-10'), DATE('2000-12-31'))2
                      TIMEBOUNDARYDIFF(MONTH, DATE('2000-03-31'), DATE('2000-04-30'))1
                      TIMEBOUNDARYDIFF(MONTH, DATE('2000-12-31'), DATE('2000-10-10')) -2
                      TIMEBOUNDARYDIFF(HOUR, 978222896000, DATETIME('2000-10-10 12:34:56')) -1956

                      This function does not work with string literal date stamps (e.g., 2000-12-31 12:34:56) to use string literals in this function, first cast them to the appropriate date/time type (e.g., DATETIME('YYYY-MM-DD HH24:MI:SS'))

                      Adds the positive or negative integral amount of unit date/time intervals to the date/time in expr

                      The following date/time intervals are supported for unit:

                      ConstantDescription
                      YEARYear is modified by interval amount (not affected by leap year, etc.) overflow/underflow occurs
                      MONTHMonth is modified by interval amount and date adjusted if overflow/underflow occurs day adjusted to last day of calculated month if not a valid day for that month (e.g., Apr 31st -> Apr 30th)
                      DAYDay is modified by interval amount (time not affected by daylight savings time, etc.) date is adjusted, if overflow/underflow occurs
                      HOURHours are modified by interval amount (time not affected by daylight savings time, etc.) date is adjusted, if overflow/underflow occurs
                      MINUTEMinutes are modified by interval amount date/time are adjusted, if overflow/underflow occurs
                      SECONDSeconds are modified by interval amount date/time are adjusted, if overflow/underflow occurs
                      MILLISECONDMilliseconds are modified by interval amount date/time are adjusted, if overflow/underflow occurs
                      QUARTERMonth is modified by three times the interval amount, irrespective of the number of days in the months between day adjusting performed the same as the MONTH description, but only on final month (e.g., Jan 31st + 1 quarter will be Apr 30th, not Apr 28th because of February)
                      WEEKDay is modified by 7 times the interval amount (time not affected by daylight savings time, etc.) month & year are adjusted, if overflow/underflow occurs

                      Any of these unit types can have a SQL_TSI_ prefix prepended to them e.g., both DAY and SQL_TSI_DAY are valid unit types for specifying a day interval. They may also be single-quoted or unquoted.

                      Function CallResult (in string format)
                      TIMESTAMPADD(YEAR, 1, '2000-10-10') 2001-10-10
                      TIMESTAMPADD(MONTH, 1, '2000-01-31') 2000-02-29
                      TIMESTAMPADD(DAY, 1, '2000-12-31') 2001-01-01
                      TIMESTAMPADD(HOUR, 12, '2000-10-10 12:34:56') 2000-10-11 00:34:56.000
                      TIMESTAMPADD(MINUTE, 1, '2000-10-10 12:34:56') 2000-10-10 12:35:56.000
                      TIMESTAMPADD(SECOND, 1, '2000-12-31 23:59:59') 2001-01-01 00:00:00.000
                      TIMESTAMPADD(MILLISECOND, 1, '2000-10-10 12:34:56') 2000-10-10 12:34:56.001
                      TIMESTAMPADD(QUARTER, 1, '2000-11-30') 2001-02-28
                      TIMESTAMPADD(WEEK, 53, '2000-01-01') 2001-01-06

                      To cast the result to a string appropriate for the date/time type (e.g., YYYY-MM-DD HH24:MI:SS), pass the result to the STRING function (e.g., STRING(TIMESTAMPADD(HOUR, 12, '2000-10-10 12:34:56')) )

                      Calculates the difference between two date/time expressions, returning the result as an integral difference in the units specified more precisely, how many whole date/time intervals of type unit need to be added to (or subtracted from) begin to equal end (or get as close as possible without going past it) using the unit types and and rules specified in TIMESTAMPADD.

                      Unlike TIMEBOUNDARYDIFF, all date/time components of both begin & end will be considered in the calculation, not just those that are up to the precision of unit.

                      This is not symmetric with TIMESTAMPADD in all cases, as adding 1 MONTH to Mar 31st results in Apr 30th, but the TIMESTAMPDIFF in MONTH units between those two dates is 0.

                      Function CallResult
                      TIMESTAMPDIFF(MONTH, DATE('2000-10-10'), DATE('2000-12-31'))2
                      TIMESTAMPDIFF(MONTH, DATE('2000-03-31'), DATE('2000-04-30'))0
                      TIMESTAMPDIFF(MONTH, DATE('2000-12-31'), DATE('2000-10-10')) -2
                      TIMESTAMPDIFF(HOUR, 978222896000, DATETIME('2000-10-10 12:34:56')) -1956

                      This function does not work with string literal date stamps (e.g., 2000-12-31 12:34:56) to use string literals in this function, first cast them to the appropriate date/time type (e.g., DATETIME('YYYY-MM-DD HH24:MI:SS'))

                      Date/Time Complex Conversion Functions

                      Converts the full date to the number of milliseconds since the epoch negative values are accepted

                      Converts the full date to the number of seconds since the epoch negative values are accepted

                      Converts the timestamp to the number of milliseconds since the epoch

                      Converts the given date and time to a composite timestamp in milliseconds since the epoch

                      Converts the year and week number to the number of milliseconds since the epoch negative values are accepted

                      Converts the year and week number to the number of seconds since the epoch. Negative values are accepted. Each new week begins Sunday at midnight.

                      Error-Checking Functions

                      Errors will only be successfully caught if the expressions are floating-point types (i.e. double and float).

                      Evaluates the given expr and if it resolves to infinity or NaN, return val

                      Conceptually, this function is the same as IF_INF(IF_NAN(expr, val), val)

                      Evaluates the given expr and if it resolves to infinity, return val

                      Evaluates the given expr and if it resolves to NaN, return val

                      Geospatial/Geometry Functions

                      • Use ST_ISVALID to determine if a geometry object is valid. The functions below work best with valid geometry objects.
                      • Use the REMOVE_NULLABLEfunction to remove any nullable column types that could result from calculating a derived column (e.g., as in Projections) using one of the functions below.

                      Enhanced Performance Scalar Functions

                      The functions below all compare x and y coordinates to geometry objects (or vice versa), thus increasing their performance in queries. Each of these functions have a geometry-to-geometry version listed in the next section.

                      Calculates the minimum distance between the given x and y coordinate and geom using the specified solution type. Solution types available:

                      • 0 (default) - Euclidean returns 2-D Euclidean distance
                      • 1 - Haversine returns minimum sphere distance in meters
                      • 2 - Vincenty returns minimum spheroid distance in meters, more accurate than Haversine but slower performance

                      Note: If the x and y coordinate and geom intersect (verify using ST_INTERSECTS), the distance will always be 0.

                      Returns 1 (true) if the x and y coordinate is within the specified distance from geom using the specified solution type. Solution types available:

                      • 0 (default) - Euclidean uses degrees to calculate distance
                      • 1 - Sphere uses meters to calculate sphere distance
                      • 2 - Spheroid uses meters to calculate spheroid distance

                      Returns 1 (true) if the x and y coordinate is within the specified distance from the bounding box of geom using the specified solution type. Solution types available:

                      • 0 (default) - Euclidean uses degrees to calculate distance
                      • 1 - Sphere uses meters to calculate distance

                      Scalar Functions

                      Returns the area of the given geometry geom if it is a POLYGON or MULTIPOLYGON using the specified solution type. Returns 0 if the input geometry type is (MULTI)POINT or (MULTI)LINESTRING. Solution types available:

                      • 0 (default) - 2D Euclidean area
                      • 1 - curved surface area on a sphere in square meters
                      • 2 - curved surface area on a spheroid in square meters

                      Returns a geometry that represents all points whose distance from the given geometry geom is less than or equal to the given distance radius. The radius units can be specified by the solution type (default is in degrees) and the radius is created in the provided style. The style options are specified as a list of blank-separated key-value pairs, e.g., 'quad_segs=8 endcap=round'. If an empty style list ('') is provided, the default settings will be used. The style parameter must be specified to provide a solution type.

                      • quad_segs -- the number of segments used to approximate a quarter circle (default is 8)
                      • endcap -- the endcap style of the buffer (default is round) options are round, flat (or butt), and square
                      • join -- the join style of the buffer (default is round) options are round, mitre (or miter), and bevel
                      • mitre_limit -- the mitre ratio limit expressed as a floating point number (miter_limit is also acceptable)
                      • 0 (default) - 2D Euclidean radius distance in degrees
                      • 1 - curved surface radius distance on a sphere in meters
                      • 2 - curved surface radius distance on a spheroid in meters

                      To create a 5-meter buffer around geom using the default styles: ST_BUFFER(geom, 5, '', 1). To create a 5-foot (converting feet to meters) buffer around geom using the following styles: ST_BUFFER(geom, 5*0.3048,'quad_segs=4 endcap=flat', 1)

                      Calculates the 2-D POINT in geom1 that is closest to geom2 using the specified solution type. If geom1 or geom2 is empty, a null is returned. Solution types available:

                      • 0 (default) - Euclidean calculates the closest point using 2-D Euclidean distance
                      • 1 - Haversine calculates the closest point using sphere distance in meters
                      • 2 - Vincenty returns minimum spheroid distance in meters, more accurate than Haversine but slower performance

                      Returns only the specified type from the given geometry collection. Type is a number that maps to the following:

                      Calculates the minimum distance between the given geometries, geom1 and geom2, using the specified solution type. Solution types available:

                      • 0 (default) - Euclidean returns 2-D Euclidean distance
                      • 1 - Haversine returns minimum sphere distance in meters
                      • 2 - Vincenty returns minimum spheroid distance in meters, more accurate than Haversine but slower performance

                      Note: If geom1 and geom2 intersect (verify using ST_INTERSECTS), the distance will always be 0.

                      Calculates the minimum distance between the given points, x1, y1 and x2, y2, using the specified solution type. Solution types available:

                      • 0 (default) - Euclidean returns 2-D Euclidean distance
                      • 1 - Haversine returns minimum sphere distance in meters
                      • 2 - Vincenty returns minimum spheroid distance in meters, more accurate than Haversine but slower performance

                      Returns 1 (true) if the maximum distance between geometries geom1 and geom2 is less than or equal to the specified distance of each other using the specified solution type. If geom1 or geom2 is null, 0 (false) is returned. Solution types available:

                      • 0 (default) - Euclidean uses degrees to calculate distance
                      • 1 - Sphere uses meters to calculate distance
                      • 2 - Spheroid uses meters to calculate distance, more accurate than sphere but slower performance

                      Returns 1 (true) if the minimum distance between geometries geom1 and geom2 is within the specified distance of each other using the specified solution type. Solution types available:

                      • 0 (default) - Euclidean uses degrees to calculate distance
                      • 1 - Sphere uses meters to calculate distance
                      • 2 - Spheroid uses meters to calculate distance, more accurate than sphere but slower performance

                      Returns an ellipse using the following values:

                      • centerx -- the x coordinate or longitude used to center the ellipse
                      • centery -- the y coordinate or latitude used to center the ellipse
                      • height -- the height of the ellipse (in degrees)
                      • width -- the width of the ellipse (in degrees)

                      Returns 1 (true) if geom1 is within the specified distance of the bounding box of geom2 using the specified solution type. Solution types available:

                      • 0 (default) - Euclidean uses degrees to calculate distance
                      • 1 - Sphere uses meters to calculate distance

                      Returns the 3-dimensional version (e.g., X, Y, and Z coordinates) of geom, a provided geometry or set of geometries (e.g., via GEOMETRYCOLLECTION or WKT column name), using z as the geometry's new z-value. The provided z-values can also be derived from a numeric column. If no z is provided, a 0 will be applied.

                      If a WKT column is provided for geom and a numeric column is provided for z, the z values will be matched to the provided geometries by row in the source table. If a singular geometry is provided for geom and a column is provided for z, three-dimensional versions of the provided geometry will be returned for each z value found in the provided z column. If columns are provided for both geom and z and nulls are present in either column, the row containing null values will be skipped in the results.

                      Returns a hash string representation of the given geometry geom with specified precision (the length of the geohash string). The longer the precision, the more precise the hash is. By default, precision is set to 20 the max for precision is 32. Returns null if geom is an empty geometry.

                      The value returned will not be a geohash of the exact geometry but a geohash of the centroid of the given geometry

                      Returns the type ID of from geom. Type and ID mappings:

                      • POINT = 0
                      • LINESTRING = 1
                      • POLYGON = 3
                      • MULTIPOINT = 4
                      • MULTILINESTRING = 5
                      • MULTIPOLYGON = 6
                      • GEOMETRYCOLLECTION = 7

                      Creates a MULTIPOLYGON containing a grid of hexagons between given minimum and maximum points of a bounding box. The minimum point cannot be greater than or equal to the maximum point. The size (in meters) of the individual hexagons' sides is determined by cell_side. The cell_side cannot be greater than the width or height of the bounding box. The maximum number of cells that can be produced is determined by limit, a positive integer. Supported values for limit:

                      • -1 - No limit to the number of cells generated (effectively limited by system memory)
                      • 0 (default) - 100 million cells
                      • <n> - Custom limit of n cells

                      If the custom limit request specifies more cells (based on the bounding box and the cell_side) than the system limit, a null is returned.

                      Returns the length of the geometry if it is a LINESTRING or MULTILINESTRING. Returns 0 if another type of geometry, e.g., POINT, MULTIPOINT, etc. GEOMETRYCOLLECTIONs are also supported but the aforementioned type limitation still applies the collection will be recursively searched for LINESTRINGs and MULTILINESTRINGs and the summation of all supported geometry types is returned (unsupported types are ignored). Solution types available:

                      • 0 (default) - 2D Euclidean length
                      • 1 - length on a sphere in meters
                      • 2 - length on a spheroid in meters

                      Returns the LINESTRING that represents the longest line of points between the two geometries. If multiple longest lines are found, only the first line found is returned. If geom1 or geom2 is empty, null is returned. Solution types available:

                      • 0 (default) - Euclidean uses degrees to calculate the longest line
                      • 1 - Sphere uses meters to calculate the longest line
                      • 2 - Spheroid uses meters to calculate the longest line, more accurate than sphere but slower performance

                      Creates a LINESTRING from geom if it is a MULTIPOINT. If geom is a POINT, there must be at least one other POINT to construct a LINESTRING. If geom is a LINESTRING, it must have at least two points. Returns null if geom is not a POINT, MULTIPOINT, or LINESTRING

                      This function can be rather costly in terms of performance

                      Creates a POINT at the given coordinate

                      This function can be rather costly in terms of performance

                      Creates a POLYGON from geom. Inputs must be closed LINESTRINGs

                      This function can be rather costly in terms of performance

                      Returns the maximum distance between the given geom1 and geom2 geometries using the specifed solution type. If geom1 or geom2 is empty, null is returned. Solution types available:

                      • 0 (default) - returns maximum 2-D Euclidean distance
                      • 1 - Sphere returns maximum distance in meters
                      • 2 - Spheroid returns maximum distance in meters, more accurate than sphere but slower performance

                      Creates multiple buffers at specified distance around the given geom geometry. Multiple distances are specified as comma-separated values in an array, e.g., [10,20,30]. Valid values for outside are:

                      • FULL -- indicates that buffers will overlap or cover the given geom geometry. This is the default.
                      • OUTSIDE_ONLY -- indicates that buffers will be rings around the given geom geometry.

                      Returns the perimeter of the geometry if it is a POLYGON or MULTIPOLYGON. Returns 0 if another type of geometry, e.g., POINT, MULTIPOINT, LINESTRING, or MULTILINESTRING. GEOMETRYCOLLECTIONs are also supported but the aforementioned type limitation still applies the collection will be recursively searched for POLYGONs and MULTIPOLYGONs and the summation of all supported geometry types is returned (unsupported types are ignored). Solution types available:

                      • 0 (default) - 2D Euclidean length
                      • 1 - length on a sphere in meters
                      • 2 - length on a spheroid in meters

                      Returns a POINT using the given geohash with a precision set by the integer precision. If precision is specified, the function will use as many characters in the hash equal to precision to create the geometry. If no precision is specified, the full length of the geohash is used.

                      The POINT returned represents the center of the bounding box of the geohash

                      Creates a MULTIPOLYGON containing a square-shaped grid of points between given minimum and maximum points of a bounding box. The minimum point cannot be greater than or equal to the maximum point. The distance between the points (in meters) is determined by cell_side. The cell_side cannot be greater than the width or height of the bounding box. The maximum number of cells that can be produced is determined by limit, a positive integer. Supported values for limit:

                      • -1 - No limit to the number of cells generated (effectively limited by system memory)
                      • 0 (default) - 100 million cells
                      • <n> - Custom limit of n cells

                      If the custom limit request specifies more cells (based on the bounding box and the cell_side) than the system limit, a null is returned.

                      Returns the given geom but segmentized n number of times depending on how the max_segment_length distance (in units based on the solution type) divides up the original geometry. The new geom is guaranteed to have segments that are smaller than the given max_segment_length. Note that POINTs are not able to be segmentized. Collection geometries (GEOMETRYCOLLECTION, MULTILINESTRING, MULTIPOINT, etc.) can be segmentized, but only the individual parts will be segmentized, not the collection as a whole. Solution types available:

                      • 0 - Euclidean uses degrees to calculate distance
                      • 1 (default) - Sphere uses meters to calculate distance

                      Returns a simplified version of the given geom using an algorithm to reduce the number of points comprising a given geometry while attempting to best retain the original shape. The given tolerance determines how much to simplify the geometry. The higher the tolerance, the more simplified the returned geometry. Some holes might be removed and some invalid polygons (e.g., self-intersecting, etc.) might be present in the returned geometry. Only (MULTI)LINESTRINGs and (MULTI)POLYGONs can be simplified, including those found within GEOMETRYCOLLECTIONs any other geometry objects will be returned unsimplified.

                      The tolerance should be provided in the same units as the data. As a rule of thumb, a tolerance of 0.00001 would correspond to about one meter.

                      Returns a simplified version of the given geom using an algorithm to reduce the number of points comprising a given geometry while attempting to best retain the original shape. The given tolerance determines how much to simplify the geometry. The higher the tolerance, the more simplified the returned geometry. No holes will be removed and no invalid polygons (e.g., self-intersecting, etc.) will be present in the returned geometry. Only (MULTI)LINESTRINGs and (MULTI)POLYGONs can be simplified, including those found within GEOMETRYCOLLECTIONs any other geometry objects will be returned unsimplified.

                      The tolerance should be provided in the same units as the data. As a rule of thumb, a tolerance of 0.00001 would correspond to about one meter.

                      Creates a MULTIPOLYGON containing a grid of squares between given minimum and maximum points of a bounding box. The minimum point cannot be greater than or equal to the maximum point. The size (in meters) of the individual squares' sides is determined by cell_side. The cell_side cannot be greater than the width or height of the bounding box. The maximum number of cells that can be produced is determined by limit, a positive integer. Supported values for limit:

                      • -1 - No limit to the number of cells generated (effectively limited by system memory)
                      • 0 (default) - 100 million cells
                      • <n> - Custom limit of n cells

                      If the custom limit request specifies more cells (based on the bounding box and the cell_side) than the system limit, a null is returned.

                      Creates a MULTIPOLYGON containing a grid of triangles between given minimum and maximum points of a bounding box. The minimum point cannot be greater than or equal to the maximum point. The size (in meters) of the individual triangles' sides is determined by cell_side. The cell_side cannot be greater than the width or height of the bounding box. The maximum number of cells that can be produced is determined by limit, a positive integer. Supported values for limit:

                      • -1 - No limit to the number of cells generated (effectively limited by system memory)
                      • 0 (default) - 100 million cells
                      • <n> - Custom limit of n cells

                      If the custom limit request specifies more cells (based on the bounding box and the cell_side) than the system limit, a null is returned.

                      Returns the binary form (WKB) of a geom (WKT)

                      This function can only be used in queries against a single table.

                      Aggregation Functions

                      FunctionDescription
                      ST_AGGREGATE_COLLECT(geom)Alias for ST_COLLECT_AGGREGATE()
                      ST_AGGREGATE_INTERSECTION(geom)Alias for ST_INTERSECTION_AGGREGATE()
                      ST_COLLECT_AGGREGATE(geom)Returns a GEOMETRYCOLLECTION comprising all geometries found in the geom set. Any MULTI* geometries will be divided into separate singular geometries, e.g., MULTIPOINT((0 0), (1 1)) would be divided into POINT(0 0) and POINT(1 1) in the results the same is true for elements of a GEOMETRYCOLLECTION found in geom, where a GEOMETRYCOLLECTION within the provided geom set will also be parsed, effectively flattening it and adding the individual geometries to the resulting GEOMETRYCOLLECTION. Any empty geometries in geom are ignored even if they are part of a GEOMETRYCOLLECTION. Any duplicate WKTs will be retained.
                      ST_DISSOLVE(geom)Dissolves all geometries within a given set into a single geometry. Note that the resulting single geometry can still be a group of noncontiguous geometries but represented as a single group, e.g., a GEOMETRYCOLLECTION. Best performance when used in conjunction with adjacent geometries
                      ST_DISSOLVEOVERLAPPING(geom)Dissolves all geometries within a given set into a single geometry. Note that the resulting single geometry can still be a group of noncontiguous geometries but represented as a single group, e.g., a GEOMETRYCOLLECTION. Best performance when used in conjunction with overlapping geometries
                      ST_INTERSECTION_AGGREGATE(geom)Returns a POLYGON or MULTIPOLYGON comprising the shared portion between all geometries found in the geom set. Returns an empty GEOMETRYCOLLECTION if there is no shared portion between all geometries. Functionally equivalent to ST_INTERSECTION(ST_INTERSECTION(ST_INTERSECTION(geom1, geom2), geom3), . geomN).
                      ST_LINESTRINGFROMORDEREDPOINTS(x, y, t)Returns a LINESTRING that represents a "track" of the given points (x, y) ordered by the given sort column t (e.g., a timestamp or sequence number). If any of the values in the specified columns are null, the null "point" will be left out of the resulting LINESTRING. If there's only one non-null "point" in the source table, a POINT is returned. If there are no non-null "points" in the source table, a null is returned
                      ST_LINESTRINGFROMORDEREDPOINTS3D(x, y, z, t)Returns a LINESTRING that represents a "track" of the given 3D points (x, y, z) ordered by the given sort column t (e.g., a timestamp or sequence number). If any of the values in the specified columns are null, the null "point" will be left out of the resulting LINESTRING. If there's only one non-null "point" in the source table, a POINT is returned. If there are no non-null "points" in the source table, a null is returned
                      ST_POLYGONIZE(geom)Returns a GEOMETRYCOLLECTION containing POLYGONs comprising the provided (MULTI)LINESTRING(s). (MULTI)POINT and (MULTI)POLYGON geometries are ignored when calculating the resulting GEOMETRYCOLLECTION. If a valid POLYGON cannot be constructed from the provided (MULTI)LINESTRING(s), an empty GEOMETRYCOLLECTION will be returned.

                      Math Functions

                      Rounds expr to the nearest decimal number with scale decimal places when scale is a positive number rounds to the nearest number such that the result has -(scale) zeros to the left of the decimal point when scale is negative use scale of 0 to round to the nearest integer. Examples:

                      Determines whether a number is positive, negative, or zero returns one of the following three values:

                      Rounds expr down to the nearest decimal number with scale decimal places, following the same rules as ROUND. Examples:

                      Null Functions

                      Be mindful that no error is thrown when Kinetica tries to convert different data type in the Null functions below, so if the output is unexpected, it may be that the types used aren't of the same type.

                      FunctionDescription
                      IS_NULL(expr)Returns 1 (true) if expr is null otherwise, returns 0 (false)
                      NULLIF(expr_a, expr_b)Returns null if expr_a equals expr_b otherwise, returns the value of expr_a. Both expressions should be of the same convertible data type
                      NVL(expr_a, expr_b)Returns expr_a if it is not null otherwise, returns expr_b. Both expressions should be of the same convertible data type see Short-Circuiting for error-checking details
                      NVL2(expr, value_if_not_null, value_if_null)Evaluates expr: if expr does not return a null, value_if_not_null is returned. If expr does return a null, value_if_null is returned. Both value_if_not_null and value_if_null should be of the same data type as expr or implicitly convertible see Short-Circuiting for error-checking details
                      REMOVE_NULLABLE(expr)Alias for ZEROIFNULL
                      ZEROIFNULL(expr)Replaces null values with appropriate values based on the column type (e.g., 0 if numeric column, an empty string if charN column, etc.). Also removes the nullable column property if used to calculate a derived column.

                      String Functions

                      These functions will only work with fixed-width string fields (char1 - char256).

                      Performs a string concatenation of expr_a & expr_b use nested CONCAT calls to concatenate more than two strings

                      The resulting field size of any CONCAT will be a charN field big enough to hold the concatenated fields, e.g., concatenating a char32 column and a char64 column will result in a char128 column. Columns of type char256 cannot be used with CONCAT.

                      Returns the concatenation of expr_a and expr_b, truncated at the maximum size of expr_a. For columns, this size is explicit for string constants, this will be the smallest charN type that can hold the expr_a string.

                      (char8 is the minimum size required to hold the ABC123 value, so the result is truncated at 8 characters)

                      (char8 is the minimum size required to hold the ABCD1234 value, so no additional characters can be concatenated)

                      Returns the octet of the IP address given in expr at the position specified by part_num. Valid part_num values are constants from 1 to 4.

                      Returns whether ref_expr matches the given match_expr. The match is a string literal one with the following exceptions:

                      Left pads the given base_expr string with the pad_expr string to the given length of characters. If base_expr is longer than length, the return value is shortened to length characters. If length is larger than 256, it will be truncated to 256.

                      Returns expr with the order of characters reversed.

                      Right pads the given base_expr string with the pad_expr string to the given length of characters. If base_expr is longer than length, the return value is shortened to length characters. If length is larger than 256, it will be truncated to 256.

                      Returns a soundex value from expr. Only the first word in the string will be considered in the calculation.

                      Note: This is the algorithm used by most programming languages

                      Splits expr into groups delimited by the delim character and returns the group_num split group. If group_num is positive, groups will be counted from the beginning of expr if negative, groups will be counted from the end of expr going backwards. Two consecutive delimiters will result in an empty string being added to the list of selectable groups. If no instances of delim exist in expr, the entire string is available at group 1 (and -1 ). Group 0 returns nothing.

                      Compares expr_a to expr_b in a lexicographical sort

                      User/Security Functions

                      Returns whether the current user (or the given user, if specified) has been assigned the given role, either directly or indirectly:

                      Masks length characters of expr, beginning at the position identified by start, with * characters (or the character specified in char):


                      Empty geometries in GEOMETRYCOLLECTION - Geographic Information Systems

                      Design goals

                      1. (very) fast retrieval of data from a database using the WKB format,
                      2. (very) fast conversion of raw WKB data into a geographical toolkit,
                      3. compatibility with several different geographical toolkits.

                      There is many geographical toolkits out there, each one modeling the very same things, in slightly different ways: points, lines, polygons. Due to their personal history, some are very close to the WKB format (JTS for example), while some are further away (OpenMap). The goal of WKB4J is to allows the author of each toolkit to integrate easily WKB4J into their own toolkit while maintaining strong performance.

                      Overall design

                      This was accomplished by splitting the driver into three parts: , WKBReader, WKBParser and WKBFactories.

                      The WKBReader retrieves raw data from a data source. The data is then passed to the WKBParser. Right now the only possible datasource is a PostgreSQL database, but it is trivial to fetch data from files or other databases.

                      The WKBParser sequentially process the WKB stream and emits calls as it encounter items in the data stream.

                      Those calls are performed on a WKBFactory provided by the user. This architecture draws from both the Strategy pattern (the WKBFactory is provided by the user so it can really be anything) and the SAX API (an evenmential-base API for XML processing). A WKBFactory is free to answer to calls as it see fit, including ignoring calls. This allows th e author of each WKBFactory to implement the most efficient processing code for its own toolkit. For example, some toolkits handles only immutable objects, while some other allows for object to be constructed incremmentally.

                      • AbstractWKBFactory: this factory contains code that keep tabs on the flow of events and yell when something is wrong (for example if the WKB stream tries to insert a GeometryCollection in a LineString). It can be seen as a "validating" WKBFactory. If you're sure that nothing is wrong with your data, you can choose not derive this class and just enjoy the speed. Still the overhead of this class is very low.
                      • LoggingWKBFactory: a Decorator around an AbstractWKBFactory that logs each call to a Log4J system. During development of a new WKBFactory, you can subclass LoggingWKBFactory in order to vizualize the flow of calls. Once you're reasonably sure that your WKBFactory is correct, you can bypass LoggingWKBFactory by extending AbstractWKBFactory. Normal call events are logged to the Info level. Once again the overhead of this class is low.
                      • EmptyWKBFactory: this factory is just empty, for the author to fill the blanks. The author of a new WKBFactory shouldn't extend it but just copy it into its own application.

                      3D Support

                      Currently, there is no support for height information in the Simple Features Specification , but it seems that many projects (including PostGIS) implements the Two-and-a-half-D extensions for Simple Features proposal. One important point : it is possible to mix 2D geometries with 3D geometries in "container" geometries (GeometryCollection, MultiPolygon, MultiLineString, MultiPoint) but you can't mix 2D points with 3D points in the same geometries since list of points are actually arrays of doubles without any boundary.

                      Since it isn't standard, it isn't implemented in the normal WKBFactories and WKBParser. Instead, it is specified in the WKBFactories3D interfaces and the WKBParser3D. Factories that wish to support this specification should implements the WKBFactories3D interface. JTSFactories and PostGISFactory do but OpenMapFactory doesn't since by default, OpenMap doesn't support height information. If your data contains 3D points, you should use the WKBParser3D instead of WKBParser.

                      The implementation was straightforward, but it did required some modifications to the WKBParser to make it more generic.

                      Moot points

                      Some design decisions are up to grabs.

                      How do the driver decides that it has seen all the data for a given geometry and that it should move to another geometry.

                      We can either let the WKBFactory decides for itself by keeping depth counter, or we can have the driver performs calls to signal when geometries begin and where they end. Depth counter adds comparaison everywhere, so they put more burden on the developer of the Factory. Additionnals calls, well, the developers have to implement them, so it isn't much better. I picked the solution of additionnal calls because they actually represent the stream of WKB data while depth counters only predict what should come next.

                      SRIDs aren't actually part of the WKB format. Nevertheless some toolkits might require their objects to be created with one (JTS).


                      For type "MultiPolygon", the "coordinates" member must be an array of Polygon coordinate arrays.

                      For type "LineString", the "coordinates" member must be an array of two or more positions. A LinearRing is closed LineString with 4 or more positions. The first and last positions are equivalent (they represent equivalent points). Though a LinearRing is not explicitly represented as a GeoJSON geometry type, it is referred to in the Polygon geometry type definition.

                      In lawn : lawn_linestring(list(c(-2, 52), c(-3, 54), c(-2, 53)))


                      st_buffer computes a buffer around this geometry/each geometry. If any of endCapStyle , joinStyle , or mitreLimit are set to non-default values ('ROUND', 'ROUND', 1.0 respectively) then the underlying 'buffer with style' GEOS function is used. See postgis.net/docs/ST_Buffer.html for details.

                      st_boundary returns the boundary of a geometry

                      st_convex_hull creates the convex hull of a set of points

                      st_simplify simplifies lines by removing vertices

                      st_triangulate triangulates set of points (not constrained). st_triangulate requires GEOS version 3.4 or above

                      st_inscribed_circle returns the maximum inscribed circle for polygon geometries. For st_inscribed_circle , if nQuadSegs is 0 a 2-point LINESTRING is returned with the center point and a boundary point of every circle, otherwise a circle (buffer) is returned where nQuadSegs controls the number of points per quadrant to approximate the circle. st_inscribed_circle requires GEOS version 3.9 or above

                      st_voronoi creates voronoi tesselation. st_voronoi requires GEOS version 3.5 or above

                      st_polygonize creates polygon from lines that form a closed ring. In case of st_polygonize , x must be an object of class LINESTRING or MULTILINESTRING , or an sfc geometry list-column object containing these

                      st_line_merge merges lines. In case of st_line_merge , x must be an object of class MULTILINESTRING , or an sfc geometry list-column object containing these

                      st_centroid gives the centroid of a geometry

                      st_point_on_surface returns a point guaranteed to be on the (multi)surface.

                      st_reverse reverses the nodes in a line

                      st_node adds nodes to linear geometries at intersections without a node, and only works on individual linear geometries