Navigating the spatial data support in MySQ L 4.1 | Lenz Grimmer | International PHP Conference 2003, Frankfurt
Navigating the spatial data support in MySQL 4.1
Navigating-The-Spatial-Data-Support.sxi
Lenz Grimmer <lenz@mysql.com>

Senior Release Engineer

International PHP Conference 2003 Frankfurt , Germany

MySQL AB 2003

Copyr ight 2003 MyS QL AB

Content of this session

What geometries are OpenGIS geometry classes and their properties Geometry data exchange formats Creating a spatial database in MySQL Analysing spatial information Optimizing analysis by use of spatial indexes World map application demo
OGC and OGC specifications
MySQL implements spatial extensions following the specification of the OpenGIS Consortium (OGC)

http://www.opengis.org/

OpenGIS Simple Feature Specifications For SQL
http://www.opengis.org/techno/implementation.htm
MySQL implements a subset of the SQL With Geometry Types environment The specifications describe a set of SQL geometry types, as well as functions on those types to create and analyse geometry values.

Term definitions

A geographic/geospatial feature is anything in the world that has a location. Feature examples: 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. Geometry is another word that denotes a geographic feature. General definition: A point or an aggregate of points representing anything in the world that has a location.

OpenGIS Geometry Model

Geometry

Surface

GeometryCollection

LineString

Polygon

MultiCurve

MultiSurface

MultiPoint

MultiLineStrin g

MultiPolygo n

Class Geometry
A Geometry value has the following properties:
The type that a geom etry belongs to. Its SRID, the identifier of a geom etry's associated Spatial Reference System. Geom etry's coordinates (related to the SRID). Its interior, boundary and exterior. Its MBR (Minim um bounding rectangle), or Envelope: ((MINX MINY, MAXX MINY, MAXX MAXY, MINX MAXY, MINX MINY)) The quality of being simple or non-simple. The quality of being closed or not closed. The quality of being empty or not empty. Its dimension:
-1 == empty geometries. 0 == no length and no area. Points and MultiPoints. 1 == non-zero length and no area. LineStrings and MultiLineStrings. 2 == non-zero area. Polygons and MultiPolygons.

Class Point

A Point is a geometry that represents a single location in coordinate space. Examples: A city on a world map. A bus stop on a city map. Point properties: X-coordinate value. Y-coordinate value. The Boundary of a Point is an empty set. The dimension is 0.

Class Curve

A Curve is an one-dimensional geometry, usually represented by a sequence of points Curve properties: Coordinates of its points. Dimension is 1. 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 is simple if it does not pass through the same point twice. A simple closed Curve is a Ring.

Class LineString

A LineString is a Curve with linear interpolation between points. LineString examples: A river on a world map. A street on a city map. LineString properties: Coordinates of LineString segments. A LineString consisting of two points is a Line. A closed simple LineString is a LinearRing.

Class Surface

A Surface is a two-dimensional geometry. It is a non-instantiable class. Its only instantiable subclass is Polygon. Surface properties: Dimension is 2. 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 is a set of closed curves.

Class Polygon

A Polygon is a planar Surface representing a multisided geometry Polygon examples: A park on a city map A country on a world map The assertions for polygons: The boundary of a Polygon is a set of LinearRings. No two rings in the boundary cross. No cut lines, spikes or punctures. Polygons are simple geometries

Class GeometryCollection

A GeometryCollection is a collection of one or more geometries of any class. Subclasses restrictions: All Elements must be in the same Spatial Reference System All Elements must be of the same type. Dimension. Other constraints (e.g. on the degree of spatial overlap between elements).

Class MultiPoint

A MultiPoint is a collection of Points. MultiPoint examples: An archipelago of islands on a world map. A set of a sport center's ticket windows on a city map. MultiPoint properties: Dimension is 0. A MultiPoint is simple if no two Points are equal. The boundary is empty set.

Class MultiCurve

A MultiCurve is a collection of Curves. MultiCurve properties: A MultiCurve is simple if elements are simple and don't intersect. A MultiCurve is defined as a one-dimensional geometry. A MultiCurve is closed if all of its elements are closed. "Mod 2 union" rule for the boundary (A point is in the boundary of a MultiCurve if it is in the boundaries of an odd number of MultiCurve elements).

Class MultiLineString

A MultiLineString is a MultiCurve whose elements are LineStrings. MultiLineString examples: A river with its tributaries on a world map. A highway system on a city map.
Class MultiSurface A MultiSurface is a collection of surfaces. MultiSurface assertions: The interiors may not intersect. The boundaries may intersect at a finite number of points.

Class MultiPolygon

MultiPolygon is a MultiSurface whose elements are Polygons. MultiPolygon examples: A lakes system on a world map. A set of towers of the same building on a city map. MultiPolygon assertions: The interiors of two Polygons that are elements of a MultiPolygon may not intersect. The boundaries of elements may not cross The boundaries of elements may touch a finite number of points. MultiPolygon properties: MultiPolygon is defined as two-dimensional geometry. The boundary of a MultiPolygon is a set of closed LineStrings corresponding to the boundaries of its
Supported Spatial Data Formats
Well-Known Text (WKT) representation
Designed to exchange geometry data in ASCII format
Well-Known Binary (WKB) representation
Designed to exchange geometry data as binary streams Defined in the OpenGIS specifications as well as in the ISO "SQL/MM Part 3: Spatial" standard.
Internally, MySQL stores geometry values in a format that is not identical to either WKT or WKB format. Currently, only MyISAM tables fully support spatial data columns with indexes.
Well-Known Text (WKT) Representation

WKT examples:

POINT(10 10) LINESTRING(10 10, 20 20, 30 40) POLYGON((10 10, 10 20, 20 20, 20 15, 10 10)) MULTIPOINT(10 10, 20 20) MULTILINESTRING((10 10, 20 20), (15 15, 30 15)) MULTIPOLYGON(((10 10, 10 20, 20 20, 20 15, 10 10)), ((60 60, 70 7, 80 60, ))) GEOMETRYCOLLECTION(POINT(10 10), POINT(30 30), LINESTRING(15 15, 20 20))
BNF of WKT: http://www.opengis.org/techno/implementation.htm
Well-Known Binary (WKB) Representation POINT(1,1) in WKB representation:
0101000000000000000000F03F000000000000F03F
Byte order : 01 WKB type : 01000000 X : 000000000000F03F Y : 000000000000F03F

MySQL Spatial Data Types

GEOMETRY POINT LINESTRING POLYGON MULTIPOINT MULTILINESTRING MULTIPOLYGON GEOMETRYCOLLECTION
Functions to create a geometry using its WKT GeomFromText(wkt,srid) PointFromText(wkt,srid) LineFromText(wkt,srid) PolyFromText(wkt,srid) MPointFromText(wkt,srid) MLineFromText(wkt,srid) MPolyFromText(wkt,srid) GeomCollFromText(wkt,srid)
Functions to create a geometry using its WKB GeomFromWKB(wkt,srid) PointFromWKB(wkt,srid) LineStringFromWKB(wkt,srid) PolyFromWKB(wkt,srid) MPointFromWKB(wkt,srid) MLineFromWKB(wkt,srid) MPolyFromWKB(wkt,srid) GeomCollFromWKB(wkt,srid)

Creating Spatial Columns

CREATE TABLE mysql> CREATE TABLE g1 (p1 GEOMETRY); Query OK, 0 rows affected (0.02 sec) ALTER TABLE mysql> ALTER TABLE g1 ADD p2 POINT; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0
Populating Spatial Columns
Using WKT functions: INSERT INTO geom VALUES (GeomFromText('POINT(1 1)')) INSERT INTO geom VALUES (GeomFromText('POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))')) INSERT INTO geom VALUES (GeomFromText('GEOMETRYCOLLECTION (POINT(1 1),LINESTRING(0 0,1 1,2 2,3 3,4 4))')) INSERT INTO geom VALUES (PointFromText('POINT(1 1)')) INSERT INTO geom VALUES (LineStringFromText('LINESTRING(0 0,1 1,2 2)')) INSERT INTO geom VALUES (GeomCollFromText('GEOMETRYCOLLECTION (POINT(1 1),LINESTRING(0 0,1 1,2 2,3 3,4 4))')) Using WKB functions: INSERT INTO geom VALUES (GeomFromWKB (0x0101000000000000000000F03F000000000000F03F)); INSERT INTO geom VALUES (GeomFromWKB(?)); INSERT INTO geom VALUES (GeomFromWKB ('\0\0\0\0\0\0\0\0\0?\0\0\0\0\0\0?'));

Fetching Spatial Data

Using WKT Representation: mysql> select AsText(p1) from g1; +-------------------------+ | AsText(p1) | +-------------------------+ | POINT(1 1) | | LINESTRING(0 0,1 1,2 2) | +-------------------------+ 2 rows in set (0.00 sec) mysql> Using WKB Representation: SELECT AsBinary(g) FROM geom;

Analysing Spatial Data

Ways of Analysing Spatial Data. Interactive SQL program: mysql or MySQLCC. Application programs in any languages supporting a MySQL client API. Four major function groups: 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.
Functions To Convert Between Geometries Between Different Formats
GeomFromWKT(string wkt [,integer srid]): geometry
Converts WKT representation into internal geom etry form at. Converts WKB representation into internal geometry form at Converts internal geom etry form at into WKT representation. Converts internal geom etry form at into WKB representation.
GeomFromWKB(binary wkb [,integer srid]): geometry AsWKT(geometry g): string AsWKB(geometry g): binary
mysql> select AsText(GeomFromText('LineString(1 1,2 2,3 3)')); +-------------------------------------------------+ | AsText(GeomFromText('LineString(1 1,2 2,3 3)')) | +-------------------------------------------------+ | LINESTRING(1 1,2 2,3 3) | +-------------------------------------------------+
Functions To Analyse Geometry Properties
mysql> select GeometryType(GeomFromText('POINT(1 1)')); +------------------------------------------+ | GeometryType(GeomFromText('POINT(1 1)')) | +------------------------------------------+ | POINT | +------------------------------------------+ mysql> select Dimension(GeomFromText('LineString(1 1,2 2)')); +------------------------------------------------+ | Dimension(GeomFromText('LineString(1 1,2 2)')) | +------------------------------------------------+ | 1 | +------------------------------------------------+ mysql> select SRID(GeomFromText('LineString(1 1,2 2)',101)); +-----------------------------------------------+ | SRID(GeomFromText('LineString(1 1,2 2)',101)) | +-----------------------------------------------+ | 101 | +-----------------------------------------------+ mysql> select AsText(Envelope(GeomFromText('LineString(1 1,2 2)',101))); +-----------------------------------------------------------+ | AsText(Envelope(GeomFromText('LineString(1 1,2 2)',101))) | +-----------------------------------------------------------+ | POLYGON((1 1,2 1,2 2,1 2,1 1)) | +-----------------------------------------------------------+

X(point p):Double

Functions To Analyse Point Properties
The x-coordinate value for this point.

Y(point p):Double

The y-coordinate value for this point
Functions To Analyse LineString Properties
StartPoint(LineString l):Point
The start point of this LineString. The end point of this LineString. Returns the specified point N in this Linestring. The length of this LineString in its associated spatial reference. Returns 1 (TRUE) if this LineString is closed (StartPoint() == EndPoint()). The num ber of points in this LineString.
EndPoint(LineString l):Point
PointN(LineString l,integer n):Point
GLength(LineString l):Double
IsClosed(LineString l):Integer
NumPoints(LineString l):Integer
Functions To Analyse MultiLineString Properties GLength(MultiLineString m):Double
The Length of this MultiLineString which is equal to the sum of the lengths of the elements.
IsClosed(MultiLineString m):Integer
Returns 1 (TRUE) if this MultiLineString is closed (StartPoint() = EndPoint() for each LineString in this MultiLineString).
Functions To Analyse Polygon Properties Area(Polygon p):Double
The area of this Polygon, as measured in the spatial reference system of this Polygon.
NumInteriorRings(Polygon p):Integer
Returns the number of interior rings in this Polygon.
ExteriorRing(Polygon p):LineString
Returns the exterior ring of this Polygon as a LineString.
InteriorRingN(Polygon p, Integer N): LineString
Returns the N-th interior ring for this Polygon as a LineString.
Functions To Analyse MultiPolygon Properties Area(MultiPolygon m):Double
The area of this MultiPolygon, as measured in the spatial reference system of this MultiPolygon.
Functions To Analyse GeometryCollection Properties
NumGeometries(GeometryCollection g):Integer
Returns the number of geometries in this GeometryCollection.
GeometryN(GeometryCollection g,integer N): Geometry
Returns the N-th geometry in this GeometryCollection.
TODO: Functions That Create New Geometries From Existing Ones Spatial Operators Intersection(Geometry g1,g2):Geometry Union(Geometry g1,g2):Geometry Difference(Geometry g1,g2):Geometry SymDifference(Geometry g1,g2):Geometry Buffer(Geometry g, double d):Geometry ConvexHull(Geometry g):Geometry
Testing Relations On Geometry Minimal Bounding Rectangles MBRContains(geom1,geom2) MBRWithin(geom1,geom2) MBRDisjoint(geom1,geom2) MBREqual(geom1,geom2) MBRIntersects(geom1,geom2) MBROverlaps(geom1,geom2) MBRTouches(geom1,geom2)
TODO: Functions That Test Spatial Relationships Between Geometries Contains(geom1,geom2) Crosses(geom1,geom2) Disjoint(geom1,geom2) Equal(geom1,geom2) Intersects(geom1,geom2) Overlaps(geom1,geom2) Touches(geom1,geom2) Within(geom1,geom2)

Spatial Indexes

The most typical database searches:

Searching for all values that are greater than a given one. Searching for all values that are less than a given one. Searching for all values that are equal to a given one.
The most typical spatial searches:
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 utilizes R-Trees with quadratic splitting to index spatial columns.

Creating Spatial Indexes

Creating with ALTER TABLE: ALTER TABLE g ADD SPATIAL KEY(g); With CREATE TABLE: CREATE TABLE g (g GEOMETRY NOT NULL, SPATIAL INDEX(g)); With CREATE INDEX: CREATE SPATIAL INDEX sp_index ON g (g);

Using a Spatial Index

Spatial search without index
mysql> select fid,AsText(g) from g where mysql> MBRContains(GeomFromText('Polygon((30000 15000,31000 15000,31000 16000,30000 16000,30000 15000))'),g);.. 20 rows in set (0.46 sec)

Creating an index:

mysql> ALTER TABLE g ADD SPATIAL KEY(g); Query OK, 32376 rows affected (4.05 sec) Records: 32376 Duplicates: 0 Warnings: 0
The same search with index
mysql> SELECT fid,AsText(g) FROM g WHERE mysql> MBRContains(GeomFromText('Polygon((30000 15000,31000 15000,31000 16000,30000 16000,30000 15000))'),g);.. 20 rows in set (0.00 sec)
Examining query plan with EXPLAIN
mysql> EXPLAIN SELECT fid,AsText(g) FROM g WHERE mysql> MBRContains(GeomFromText('Polygon((30000 15000,31000 15000,31000 16000,30000 16000,30000 15000))'), g); +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | g | range | g | g | 32 | NULL | 50 | Using where | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
OpenGIS Features That Are Not Yet Implemented Union(geom1,geom2) - spatial operators. Contains(geom1,geom2) - non-MBR, accurate spatial relations. Distance(geom1,geom2) - distance between two geometries. Area(GeomFromText('POLYGON((0 0,0 1,1 1,1 0,0 0))',SRID)) - should return a result depending on SRID.

GIS application demo

A small C application that uses the MySQL client library and the GD library to draw a zoomable world map It can be used either in standalone mode or as a CGI script All map data is stored inside a MySQL database SQL queries extract the data relevant for the requested coordinates Source code is available online at
http://www.mysql.com/Downloads/Presentations/MySQL-User-Conference-2003/gd.tar.gz