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;
//add point
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]
