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
```
|