Geo queries

We are using some geo spatial extensions of our MySQL Database.
Note: the gis extension is actived in MySQL by default, but our version of phpMyAdmin didn’t show any geo data types.

MySQL GIS datatypes

(from http://dev.mysql.com/tech-resources/articles/4.1/gis-with-mysql.html)

Test Spatial Relationships Between Geometries

(returns true or flase)

• http://dev.mysql.com/doc/refman/5.0/en/functions-that-test-spatial-relationships-between-geometries.html

Example:

SELECT p.user_name AS player, o.user_name AS opponent,  Intersects(p.line, o.line) 
FROM lines2 p, lines2 o 
WHERE p.game_id = 1 AND p.game_id = o.game_id
   AND p.user_name = 'Kojote' AND o.user_name != p.user_name

get points where lines intersect

• http://dev.mysql.com/doc/refman/5.0/en/functions-that-create-new-geometries-from-existing-ones.html#spatial-operators

Example:

SELECT p.user_name AS player, o.user_name AS opponent, Intersection(p.line, o.line) 
FROM lines2 p, lines2 o 
WHERE p.game_id = 1 AND p.game_id = o.game_id
   AND p.user_name = 'Kojote' AND o.user_name != p.user_name

Note: Doesn’t work with our two mysql servers, reason unknown. error: #1305 – FUNCTION gtug_muc_campout.Intersection does not exist Maybe a MySQL Bug

other links

• http://dev.mysql.com/doc/refman/5.1-olh/de/creating-spatial-values.html
• http://mapki.com/wiki/Spatial_Data_in_MySQL
• List of databases with geospatital extensions