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.
4 Comments
Comments RSS
TrackBack Identifier URI
User contributed tags for this post:
mysql polygon (111) - mysql point (96) - mysql geometry (83) - mysql spatial (73) - mysql lat long (57) - mysql polygon example (56) - mysql spatial data (50) - MySQL Spatial Data Types (46) - MYSQL Point data type (40) - mysql point example (39) - mysql spatial example (36) - mysql geometry example (34) - polygon mysql (29) - mysql GEOMFROMTEXT (29) - mysql spatial search (27) - Spatial Mysql (21) - mysql spatial polygon (20) - mysql spatial distance (19) - MySQL Spacial (19) - mysql geometry data type (18) - mysql spatial index (18) - mysql spatial examples (17) - geometry mysql (17) - spatial data mysql (16) - mysql spatial google maps (16) - lat long mysql (16) - mysql point in polygon (14) - mysql spatial point (14) - www.18to19.com (14) - php mysql spatial (13) - GeomFromText mysql (13) - mysql spatial within (12) - MySQL POINT type (12) - mysql insert polygon (11) - 18to19 (10) - mysql spatial database (9) - MySQL INSERT point (9) - mysql geometry distance (9) - SPATIAL INDEX mysql (9) - insert polygon mysql (9) - mysql geometry point (9) - mysql geometry examples (8) - mysql spatial index distance (8) - mysql geometry index (8) - insert polygon into mysql (8) - spatial data type mysql (8) - mysql geometry read two columns into geometry (8) - mysql create point (7) - mysql GEOMETRY polygon (7) - mysql lat long distance (7) - mysql point data (7) - mysql GeomFromText lat long (7) - coord (6) - mysql point x y (6) - mysql spatial functions (6) - mysql point distance (6) - mysql spatial database example (6) - mysql polygon data type (6) - mysql polygon datatype (6) - MYSQL and spatial data (6) - mysql Spatial index example (6) - mysql bounding box (6) - mysql geomfromtext polygon (6) - mysql geometry php (6) - mysql spatialdata type (6) - mysql create spatial database (6) - google maps polygon mysql (6) - mysql distance (5) - mysql spatial commands (5) - spacial data in mysql (5) - mysql points (5) - mysql POLYGON examples (5) - working with spatial data (5) - mysql spatial insert (5) - mysql insert polygon example (5) - mysql polygon search (5) - mysql spatial point in polygon (5) - php mysql geometry (5) - php spatial data (5) - mysql spatial distance query (5) - spatial data types mysql (5) - spatial search mysql (5) - mysql point datatype (5) - mysql lat long index (5) - mysql spatial geometry (5) - mysql spatial calculate distance (4) - mysql create spatial point (4) - mysql create spatial (4) - mysql spatial rectangle (4) - mysql create spatial index (4) - mysql point insert (4) - google maps mysql geometry (4) - mysql create spatial table (4) - spacial database (4) - mysql spatial bounding box (4) - mysql polygon insert (4) - mysql spacial index (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 php (4) - mysql convert lat long to point (4) - mysql spatial datatype (4) - mysql spatial data type (4) - MySQL insert Geometry (4) - mysql polygon functions (4) - convert lat long to point mysql (4) - mysql lat lon (4) - www;18to19.com (4) - mysql point lat long (4) - MySQL Add Spatial Index (4) - mysql spacial example (4) - mysql spatial query php draw polygon (4) - MySQL spatial query (4) - mysql convert lat long to points (4) - mysql spatial points within rectangle (4) -
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
Way late, Roberto, but:
INSERT INTO geo (polygon) VALUES( GeomFromText( ‘POLYGON((0 0,0 3,3 3,3 0,0 0))’ ));
there’s funtastic news in http://www.mysqlpoint.com. Did you give some advice? Thanks for appreciate