MySQL Geometry

GIS Howtos

Pretty much all there is to know about MYSQL GIS functionality.

While a proper MySQL GIS extension is in the works currently 5.0 only incorporates a very basic subset of tools.

• What it can do is store GIS shapes, lines and points
• What it cant do is understand or convert projections, or do related maths such as distance, area and intersection.

So you must be mindful of the projection of your data, if you use lat,long wgs84, then it will compatible with gmap, but be hard to do maths on. If you use a local transverse mercator or any proj that uses meters, then you can do easy maths. Probably the solution at present is to use both.

Row types can be either a catchall GEOMETRY which can hold any of the std shapes, or you can use a specific type such as POINT or LINESTRING, POLYGON or MULTIPOLYGON etc.

WKT is used for all geometrys, and remember that wkt uses X,Y=Long,Lat (ie.not lat,long). Also watch the commas, the x and y in wkt dont have commas, but commas are used to seperate points in a line or polygon.

To create some mysql geometrys:

//create the table

CREATE TABLE places (

id int auto_increment,

name varchar(255),

coords POINT NOT NULL,

PRIMARY KEY(id),

SPATIAL INDEX(coords)

)

//add the geometry field later

ALTER TABLE places ADD coords POINT NOT NULL;

ALTER TABLE places ADD SPATIAL INDEX(coords);

ALTER TABLE places DROP INDEX coords;

INSERT into places set id=1, name=`Home`,coords= POINT(-40 170);

INSERT into places set id=1, name=`Home`,coords= GeomFromText(`POINT(-40 170)`);

//convert from gmap url format (lat,long) string to geometry

update places set coords= GeomFromText(concat(`POINT(`, REPLACE(urlcoords, `,`,` `) ,`)`) )

To view some geometrys:

//view

SELECT id,name,astext(coords) from places;

SELECT Y(coords), X(coords) FROM places;

SELECT concat(Y(coords),`,`,X(coords)) FROM places;

//filter

SELECT id,name from places

WHERE X(coords)>-40 -- long

AND    Y(coords)>170 -- lat

//get type

SELECT GeometryType(GeomFromText(`POINT(1 1)`))

//get extents

SELECT AsText(Envelope(GeomFromText(`LineString(1 1,2 2,3 3)`)))

//?

Overlap()

//get points by bounds -40 -45 --> 170 180

select coords from places where

MBRContains(

GeomFromText(`Polygon((-40 170, -45 170, -45 180, -40 -180, -40 170))`),

coords

);

If you want to do rough distances on the sphere:

//get points in x mile radius for latlong (spherical)

//answer in miles (1 miles = 1.609344 km)

SET slat= -40;

SET slong= 170;

SELECT id,

3956 * 2 * ASIN(SQRT(POWER(SIN((@slat - Y(coords)) *

pi()/180 / 2), 2) +

COS(@slat * pi()/180) * COS(Y(coords) * pi()/180) *

POWER(SIN((@slong -X(coords)) * pi()/180 / 2), 2) )) as distance

FROM places where distance < 10;

//optimisation to above

//filter first by bounding rectangle

set dist=   10;

set lon1 = @slong-dist/abs(cos(radians(@slat))*69);

set lon2 = @slong+dist/abs(cos(radians(@slat))*69);

set lat1 = @slat-(dist/69);

set lat2 = @slat+(dist/69);

SELECT id,{...} FROM places

WHERE

MBRContains(

GeomFromText(`Polygon((@lon1 @lat1, @lon1 @lat2, @lon2 @lat2, @lon2 @lat1, @lon1 @lat1))`),

coords

);

Or more accurate distances, (if your geometrys are projected):

//distance in NZTM (places less than 10km away as crow flys)

SET dist=10000;

SET sx= 600000;

SET sy= 150000;

SELECT POWER(2,SQRT(abs(@sx-X(coords))+SQRT(abs(@sy-Y(coords))))) as distance

FROM places where

MBRContains(

GeomFromText(`POLYGON((@sx-@dist @sy-@dist,@sx+@dist @sy-@dist,

@sx+@dist @sy+@dist,@sx-@dist @sy+@dist,

@sx-@dist @sy-@dist ))`),

coords

)

and distance < @dist;

//or simply

select GLENGTH(LINESTRING(POINT(@sx,@sy),coords)) as distance from places where distance<10000;

//area

SELECT Area(GeomFromText(`Polygon((0 0,0 3,3 0,0 0),(1 1,1 2,2 1,1 1))`));

The manual:

[http://dev.mysql.com/doc/refman/5.0/en/analysing-spatial-information.html]