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 (113) - mysql point (102) - mysql geometry (88) - mysql spatial (77) - mysql polygon example (59) - mysql lat long (58) - MySQL Spatial Data Types (54) - mysql spatial data (52) - MYSQL Point data type (43) - mysql point example (42) - mysql spatial example (37) - mysql geometry example (34) - polygon mysql (30) - mysql GEOMFROMTEXT (29) - mysql spatial search (27) - Spatial Mysql (23) - mysql spatial polygon (20) - mysql spatial distance (19) - MySQL Spacial (19) - lat long mysql (19) - spatial data mysql (18) - mysql geometry data type (18) - mysql spatial index (18) - mysql spatial examples (17) - geometry mysql (17) - mysql spatial google maps (16) - mysql spatial point (16) - www.18to19.com (15) - mysql point in polygon (14) - php mysql spatial (14) - mysql spatial within (13) - GeomFromText mysql (13) - MySQL POINT type (13) - mysql insert polygon (11) - MySQL INSERT point (11) - 18to19 (10) - spatial data type mysql (10) - mysql spatial database (9) - mysql geometry distance (9) - SPATIAL INDEX mysql (9) - insert polygon mysql (9) - mysql geometry point (9) - mysql bounding box (9) - mysql geometry examples (8) - mysql lat long distance (8) - mysql spatial index distance (8) - mysql geometry index (8) - insert polygon into mysql (8) - mysql geometry read two columns into geometry (8) - mysql create point (7) - point mysql (7) - mysql GEOMETRY polygon (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 POLYGON examples (6) - mysql spatial database example (6) - mysql spatial insert (6) - mysql polygon data type (6) - mysql polygon datatype (6) - MYSQL and spatial data (6) - mysql spatial point in polygon (6) - mysql Spatial index example (6) - mysql geomfromtext polygon (6) - mysql geometry php (6) - mysql spatialdata type (6) - mysql create spatial database (6) - spatial data types mysql (6) - google maps polygon mysql (6) - mysql distance (5) - mysql spatial commands (5) - spacial data in mysql (5) - mysql points (5) - working with spatial data (5) - mysql insert polygon example (5) - mysql polygon search (5) - php mysql geometry (5) - php spatial data (5) - mysql spatial distance query (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 spatial lat long (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 spatial POLYGON example (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) -
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