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
Posted June 12, 2008 by Casey Bisson
Categories: Technology. Tags: geolocation, mysql, MySQL spatial functions, spatial data, spatial functions.
2 Comments
Comments RSS
TrackBack Identifier URI
Leave a comment
User contributed tags for this post:
mysql polygon (82) - mysql geometry (54) - mysql spatial (53) - mysql spatial data (41) - mysql point (39) - mysql lat long (31) - mysql polygon example (31) - mysql GEOMFROMTEXT (26) - mysql spatial example (23) - داستانهای سک (20) - mysql point example (20) - mysql geometry example (19) - polygon mysql (18) - MySQL Spatial Data Types (17) - MYSQL Point data type (16) - mysql spatial search (15) - mysql spatial examples (15) - MySQL Spacial (15) - Spatial Mysql (13) - mysql spatial google maps (13) - mysql point in polygon (12) - GeomFromText mysql (12) - سک3 (11) - mysql spatial polygon (11) - mysql spatial index (11) - mysql spatial distance (10) - spatial data mysql (10) - mysql insert polygon (10) - mysql spatial within (10) - geometry mysql (10) - mysql spatial database (9) - php mysql spatial (9) - mysql geometry data type (9) - www.18to19.com (9) - insert polygon mysql (8) - mysql geometry examples (8) - mysql geometry read two columns into geometry (8) - mysql spatial point (7) - wwwسكس.com (7) - 18to19 (7) - insert polygon into mysql (7) - spatial data type mysql (7) - MySQL POINT type (7) - coord (6) - MySQL INSERT point (6) - mysql geometry distance (6) - SPATIAL INDEX mysql (6) - mysql geometry point (6) - mysql GEOMETRY polygon (6) - mysql geometry index (6) - mysql spatialdata type (6) - سک (5) - mysql spatial commands (5) - spacial data in mysql (5) - mysql POLYGON examples (5) - mysql spatial database example (5) - mysql insert polygon example (5) - mysql spatial index distance (5) - mysql spatial calculate distance (4) - mysql point x y (4) - mysql spatial functions (4) - mysql create spatial point (4) - mysql create spatial (4) - mysql spatial rectangle (4) - mysql create spatial table (4) - spacial database (4) - mysql create point (4) - mysql point distance (4) - mysql polygon data type example (4) - mysql long lat (4) - geometry mysql data type (4) - geometry to x y mysql (4) - google maps mysql spatial (4) - mysql spatial insert (4) - mysql Spatial index example (4) - mysql bounding box (4) - mysql lat lon (4) - www;18to19.com (4) - mysql spatial query php draw polygon (4) - mysql convert lat long to points (4) - mysql distance (3) - x y coordinate mysql (3) - spatial geometry mysql (3) - سك3 (3) - mysql point insert (3) - www.18 tv.com (3) - insert polygon mysql spatial (3) - mysql points (3) - google maps mysql geometry (3) - mysql spatial bounding box (3) - mysql polygon insert (3) - working with spatial data (3) - mysql spacial index (3) - mysql index lat lon (3) - mysql distance x y (3) - clip xy data mysql php (3) - mysql lat long distance (3) - mysql polygon data type (3) - mysql polygon datatype (3) - geometry spatial index mysql (3) - MYSQL and spatial data (3) - mysql spatial inserting lat long (3) - mysql data type geometry (3) - mysql create spatial index from long lat (3) - to generate spatial data from MySQL (3) - mysql spatial point in polygon (3) - spatial search in mysql (3) - spacial mysql (3) - MySQL lat,long (3) - mysql point map (3) - mysql geomfromtext polygon insert (3) -
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
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