w3resource

MySQL Spatial Data Types

Extensions for Spatial Data

The Open Geospatial Consortium (OGC) is 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 specification published by Open Geospatial Consortium publishes (OGC) specifies that how MySQL implements spatial extensions as a subset of the SQL with Geometry Types environment. 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 specification describes a set of SQL geometry types, as well as functions on those types to create and analyze geometry values.

Features of MySQL Spatial Data Types

MySQL spatial extensions enable the generation, storage, and analysis of geographic features:

  • Data types for representing spatial values
  • Functions for manipulating spatial values
  • Spatial indexing for improved access times to spatial columns

MySQL supports a number of Spatial Data Types

MySQL has data types that correspond to OpenGIS classes. Some of these types hold single geometry values:

  • GEOMETRY
  • POINT
  • LINESTRING
  • POLYGON

The other data types hold collections of values:

  • MULTIPOINT
  • MULTILINESTRING
  • MULTIPOLYGON
  • GEOMETRYCOLLECTION

Geometry Type

Geometry is a word that denotes a geographic feature. Originally the word geometry meant measurement of the earth. Another meaning comes from cartography, referring to the geometric features that cartographers use to map the world. It is a noninstantiable class but has a number of properties, given below are common to all geometry values created from any of the Geometry subclasses.

Name Description
type Each geometry belongs to one of the instantiable classes in the hierarchy.
SRID The full form of SRID is Spatial Reference Identifier. This system describes the coordinate space in which the geometry object is defined. In MySQL, the SRID value is just an integer associated with the geometry value.
coordinates All nonempty geometries include at least one pair of (X,Y) coordinates. Empty geometries contain no coordinates. Coordinates are related to the SRID.
interior, boundary, exterior. Every geometry occupies some position in space. The exterior of a geometry is all space not occupied by the geometry. The interior is the space occupied by the geometry. The boundary is the interface between the geometry's interior and exterior.
MBR Its MBR (minimum bounding rectangle), or envelope. This is the bounding geometry, formed by the minimum and maximum (X,Y) coordinates:
simple or nonsimple. Whether the value is simple or nonsimple. Geometry values of types (LineString, MultiPoint, MultiLineString) are either simple or nonsimple. Each type determines its own assertions for being simple or nonsimple.
closed or not closed Whether the value is closed or not closed. Geometry values of types (LineString, MultiString) are either closed or not closed. Each type determines its own assertions for being closed or not closed.
empty or nonempty Whether the value is empty or nonempty A geometry is empty if it does not have any points. Exterior, interior, and boundary of an empty geometry are not defined. An empty geometry is defined to be always simple and has an area of 0.
dimension Its dimension. A geometry can have a dimension of –1, 0, 1, or 2:
– 1 for an empty geometry.
0 for a geometry with no length and no area.
1 for a geometry with nonzero length and zero area.
2 for a geometry with nonzero area.

Example

Use the CREATE TABLE statement to create a table with a spatial column:

CREATE TABLE geotest (code int(5),descrip varchar(50), g GEOMETRY);

Here is the structure of the table:

Sample Output: 

MySQL> describe geotest;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| code    | int(5)      | YES  |     | NULL    |       | 
| descrip | varchar(50) | YES  |     | NULL    |       | 
| g       | geometry    | YES  |     | NULL    |       | 
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

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

ALTER TABLE geotest ADD pt_loca POINT; 
ALTER TABLE geotest DROP pt_loca ;

Point Type

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

Usage of Point

On a city map, a Point object could represent a rail station.

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.

Example

MySQL> SELECT X(POINT(18, 23));
+------------------+
| X(POINT(18, 23)) |
+------------------+
|               18 | 
+------------------+
1 row in set (0.00 sec)


MySQL> SELECT X(GeomFromText('POINT(18 23)'));
+---------------------------------+
| X(GeomFromText('POINT(18 23)')) |
+---------------------------------+
|                              18 | 
+---------------------------------+
1 row in set (0.00 sec)

Curve Type

A Curve is a one-dimensional geometry, in general, it represented by a sequence of points. Particular subclasses of Curve define the type of interpolation between points. The curve is a noninstantiable class.

Curve Properties

  • A Curve has the coordinates of its points.
  • A Curve is defined as a one-dimensional geometry.
  • A Curve is simple if it does not pass through the same point twice.
  • A Curve is closed if its start point is equal to its endpoint.
  • The boundary of a closed Curve is empty.
  • The boundary of a nonclosed Curve consists of its two endpoints.
  • A Curve that is simple and closed is a LinearRing.

LineString Type

A LineString is a Curve with linear interpolation between points.

Usage of LineString

LineString objects could represent a river within a country map.

LineString Properties

  • A LineString has coordinates of segments, defined by each consecutive pair of points.
  • A LineString is a Line if it consists of exactly two points.
  • A LineString is a LinearRing if it is both closed and simple.

Example

MySQL> SET @g = 'LINESTRING(0 0,1 2,2 4)';
Query OK, 0 rows affected (0.00 sec)
MySQL> INSERT INTO geotest VALUES (123,"Test Data",GeomFromText(@g));
Query OK, 1 row affected (0.00 sec)

Surface Type

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

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.

Polygon Type

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.

Usage of Polygon

The Polygon objects could represent districts, blocks and so on from a state map.

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.
  • A Polygon has no rings that cross. The rings in the boundary of a Polygon may intersect at a Point, but only as a tangent.
  • A Polygon has no lines, spikes, or punctures.
  • A Polygon has an interior that is a connected point set.
  • A Polygon may have holes. The exterior of a Polygon with holes is not connected. Each hole defines a connected component of the exterior.

Example

MySQL> SET @g = 'POLYGON((0 0,8 0,12 9,0 9,0 0),(5 3,4 5,7 9,3 7, 2 5))';
Query OK, 0 rows affected (0.00 sec)

MySQL> INSERT INTO geotest VALUES (123,"Test Data",GeomFromText(@g));
Query OK, 1 row affected (0.03 sec)

GeometryCollection Type

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. There are no other constraints on the elements of a GeometryCollection, although the subclasses of GeometryCollection described in the following sections may restrict membership. Restrictions 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

Example

MySQL> SET @g ='GEOMETRYCOLLECTION(POINT(3 2),LINESTRING(0 0,1 3,2 5,3 5,4 7))';
Query OK, 0 rows affected (0.00 sec)

MySQL> INSERT INTO geotest VALUES (123,"Test Data",GeomFromText(@g));
Query OK, 1 row affected (0.00 sec)

MultiPoint Type

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

Usage of MultiPoint

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

MultiPoint Properties

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

MultiCurve Type

A MultiCurve is a geometry collection composed of Curve elements. MultiCurve is a noninstantiable class.

MultiCurve Properties

  • A MultiCurve is a one-dimensional geometry.
  • A MultiCurve is simple if and only if all of its elements are simple; the only intersections between any two elements occur at points that are on the boundaries of both elements.
  • A MultiCurve boundary is obtained by applying the “mod 2 union rule” (also known as the “odd-even rule”): A point is the boundary of a MultiCurve if it is within 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.
MySQL> SET @g ='MULTIPOINT(0 0, 15 25, 45 65)';
Query OK, 0 rows affected (0.00 sec)

MySQL> INSERT INTO geotest VALUES (123,"Multipoint",GeomFromText(@g));
Query OK, 1 row affected (0.00 sec)

MultiLineString Type

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

Usage of MultiLineString

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

Example

MySQL> SET @g ='MULTILINESTRING((12 12, 22 22), (19 19, 32 18))';
Query OK, 0 rows affected (0.00 sec)

MySQL> INSERT INTO geotest VALUES (123,"Multistring",GeomFromText(@g));
Query OK, 1 row affected (0.00 sec)

MultiSurface Type

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

MultiSurface Assertions

  • Two MultiSurface surfaces have no interiors that intersect.
  • Two MultiSurface elements have boundaries that intersect at most at a finite number of points.

MultiPolygon Type

MultiPolygon is a MultiSurface object composed of Polygon elements.

Usage of MultiPolygon

A MultiPolygon could represent a system of lakes on a region map.

MultiPolygon Assertions

  • A MultiPolygon has no two Polygon elements with interiors that intersect.
  • A MultiPolygon has no two Polygon elements that cross (crossing is also forbidden by the previous assertion) or that touch at an infinite number of points.
  • A MultiPolygon may not have cut lines, spikes, or punctures. A MultiPolygon is a regular, closed point set.
  • A MultiPolygon that has more than one Polygon has an interior that is not connected. The number of connected components of the interior of a MultiPolygon is equal to the number of Polygon values in the MultiPolygon

MultiPolygon Properties

  • A MultiPolygon is a two-dimensional geometry.
  • A MultiPolygon boundary is a set of closed curves (LineString values) corresponding to the boundaries of its Polygon elements.
  • Each Curve in the boundary of the MultiPolygon is in the boundary of exactly one Polygon element.
  • Every Curve in the boundary of a Polygon element is in the boundary of the MultiPolygon.

Example

MySQL> SET @g ='MULTIPOLYGON(((0 0,11 0,12 11,0 9,0 0)),((3 5,7 4,4 7,7 7,3 5)))';
Query OK, 0 rows affected (0.00 sec)

MySQL> INSERT INTO geotest VALUES (123,"Multipolygon",GeomFromText(@g));
Query OK, 1 row affected (0.00 sec)

Summary : MySQL Data Types

MySQL Data Types slides presentation

Previous: MySQL Data Types
Next: Connecting to and disconnecting from MySQL



Follow us on Facebook and Twitter for latest update.