Spatial Data in MySQL

From Google Mapki

Setting up the field

MySQL 4.1 introduced datatypes that are designed to hold geospatial data. It also allows you to use special geospatial functions to pull back points based upon that data.

For example, you can supply the points of a polygon and run a query to pull back all points within that polygon or all polylines that fall within that polygon or go inside it.

A good reference is MySQL documentation which seems to clearly explain how to set up the tables, but is less clear on insertion of data.

The GEOMETRY datatype is a binary ("WKB") format, to convert to / from this to a text ("WKT") format, use:

AsText(<fieldname>) to convert from WKB to WKT

GeomFromText('<text>') to convert from WKT to WKB.

The format of WKT is:

For a point - POINT(x y)

For a line - LINESTRING(x y,x y,x y,...)

For a polygon - POLYGON(x1 y1,x2 y2,...,xn, yn, x1, y2)

To create a table with a geometry field in it:

CREATE TABLE `locations` (
  `location_id` int(10) NOT NULL auto_increment,
  `name` text NOT NULL,
  `point` geometry NOT NULL default \'\',
  PRIMARY KEY  (`location_id`),
  SPATIAL KEY `point` (`point`(32))
) ENGINE=MyISAM;

Inserting Data

Beware when using phpmyadmin or similar to edit a table with a geometry type field in it as it does not seem to be able to edit any of the fields as it is having problem with the binary format of the field.

Replace <lat> and <lng> with your lat and lng values

Inserting a point:

INSERT INTO `locations` (name, point) VALUES ("location name",GeomFromText('POINT(<lng> <lat>)'))

Inserting a line:

INSERT INTO `locations` (name, point) VALUES ("location name",GeomFromText('Linestring(<lng> <lat>,<lng> <lat>,<lng> <lat>,<lng> <lat>,...)')

Inserting a polygon:

INSERT INTO `locations` (name, point) VALUES ("location name","Polygon((30000 15000,31000 15000,31000 16000,30000 16000,30000 15000))")

Reading Data

Reading the geometry field is pointless.

Instead use something like:

SELECT name, AsText(point) AS point FROM locations WHERE 1

Spatial selects can also be used:

SELECT name, AsText(point) AS point FROM locations WHERE Intersects(point,GeomFromText('LineString(<lng> <lat>,<lng> <lat>,<lng> <lat>,...)'))

The lat, lng pairs are a line describing the area to be selected. AFAIK in 4.1 the code is bodged and only works properly with rectangles, but it should have full functionality at some point. Intersects is very useful for passing the bounds of the viewport (or, as I prefer the viewport area plus a "buffer" zone around the edge) and getting back all points or lines that reside in or cross the bounds area.