Working With Spatial Data in MySQL

It’s MySQL spatial data week here, though I am spreading out the posts to, um, ease the pain (or boredom). Anyway, here are some commands/functions I don’t want to forget about later:

Start with an existing table called geometry, add a spatial column and index it:

ALTER TABLE geometry ADD coord POINT NOT NULL;
CREATE SPATIAL INDEX coord ON geometry (coord);

Insert some data; think in terms of POINT(X Y) or POINT(lat lon):

INSERT INTO geometry (coord) VALUES( GeomFromText( 'POINT(40 -100)' ));
INSERT INTO geometry (coord) VALUES( GeomFromText( 'POINT(1 1)' ));

Get those X,Y coordinates back from the table:

SELECT X(coord), Y(coord) FROM geometry

Get points within a bounding rectangle:

SELECT MBRContains(
	GeomFromText( 'POLYGON((0 0,0 3,3 3,3 0,0 0))' ),
	coord
)
FROM geometry

9 thoughts on “Working With Spatial Data in MySQL

  1. Hi,

    We were planning to implement spatial database for a community site that we are building and were reading articles related to the same. As oracle/sql server seems to be heavily prices, we were thinking if we could use mysql for that purpose.

    We were thinking about integrating google maps along with spatial database and wanted to know if there had been successful implementation of spatial databases with mysql and its comparison with services offered by oracle/ms-sql.

    Any information or references related to this highly appreciated.

    With regards,
    Haran

  2. Hi, thanks for this useful info. I tried POINT data in mysql and everything is ok, i do use PHP to generate KML to show POINT data as placemarks using X() and Y() POINT functions but i cannot get x, y coordinates from a POLYGON. I tried GEOMETRY and POLYGON data type to store POLYGON data but i there is no X() Y() function to read x, y coord from a POLYGON just like POINT does. Could somebody bring some SQL example to query X, Y coordinates from a POLYGON or GEOMETRY field with a POLYGON((lat long,lat long ,lat long , ……. ,lat long)) insert within it?

    Thank you very much!

    Kind regards,

    Att. Roberto Jimenez
    desarrollo.web.suscripciones@live.com.mx

  3. Way late, Roberto, but:

    INSERT INTO geo (polygon) VALUES( GeomFromText( ‘POLYGON((0 0,0 3,3 3,3 0,0 0))’ ));

  4. I would also love to see some documentation on how to take spatial data from mySQL and export as a KML? There seems to be some documentation floating around for points but not for polygons.

  5. Hi there,

    Hoping someone can help me find out why my point_in_polygon function is not working?

    I am using the myWithin function, (from mysql site) which works perfectly but I need to call my function from a java program which will give me the polygon parameters in the following format:

    SELECT point_in_polygon(‘(5|5)’,'(0|0)|(0|10)|(10|10)|(10|0)|(0|0)’);

    So I thought I could easily write a function to modify the parameters and use them with the myWithin function – see below:

    Drop function point_in_polygon;

    DELIMITER //
    CREATE FUNCTION point_in_polygon(str1 TEXT,str2 TEXT)
    RETURNS INT(1) DETERMINISTIC
    BEGIN
    DECLARE result INT(1) DEFAULT 0;
    DECLARE pt TEXT;
    DECLARE poly1 TEXT;
    DECLARE poly2 TEXT;
    SET pt = CONCAT(‘\’POINT’,REPLACE(str1, “|”,” “),’\”);
    SET poly1 = CONCAT(‘\’POLYGON(‘, REPLACE(str2, “)|(“,”, “),’)\”);
    SET poly2 = REPLACE(poly1, “|”,” “);
    SELECT myWithin(pointfromtext(pt),polyfromtext(poly2)) into result;
    RETURN result;
    END;
    //

    DELIMITER ;

    Trouble is when I call it I get:
    mysql> SELECT point_in_polygon(‘(5|5)’,'(0|0)|(0|10)|(10|10)|(10|0)|(0|0)’);
    ERROR 1416 (22003): Cannot get geometry object from data you send to the GEOMETRY field

    I guess I am not using the variables correctly – can anyone point out my error?
    Any help would be greatly appreciated.

    S.

  6. Honestly, don’t bother with PHP and MySQL for geospatial. There are very few GIS libraries for the language, and MySQLs geospatial support is dismal. I strongly suggest everyone look at Python and PostgreSQL with PostGIS, which has support for a lot more data formats, query types, and a few ORMs which make life significantly easier.

Comments are closed.