I would prefer to use postgis, but have to resign to mysql. I want to create a table with spatial information and be able to access it from mapserver. As I am going to store a lot of datapoints, they may be stored just as two columns with x and y values, but as the dataset grows, that starts to be a bit slow.
I started of with a table with more than 20 columns holding point measurements and a lot of metadata related to those measurements. No, it is not practical to split that table up. I tried to add a point column to that table
alter table mobile_rawdata add column coord point
and then I copied the coordinates into that column
update rawdata set coord=point(lon,lat) where not lat is null;
again fine and dandy, but when I tried to put a spatial index on that column, I was told that that was not possible for a column with null values.
So I made a new table
create table mobile_spatial(
OGR_FID serial primary key,
SHAPE geometry not null,
rawdataid integer not null,
addtime timestamp default current_timestamp);
note that the shape column is to be of type geometry, if it is set to another spatial type, ogr will not recognice it. The column names OGR_FID and SHAPE are used as those are default values for ogr. Rawdataid pointing to the id of the old mobile_rawdata table.
Then I tested it with
ogrinfo -so MySQL:radioecology_9,user=user,password=pass mobile_spatial
but ogrinfo still did not understand that this was supposed to be a table with geometry. Reading up a bit more, I found that I needed two more tables in the database: SPATIAL_REF_SYS to store the, well, spatial reference systems and geometry_columns to store information on what kind of geometry the tables had.
I didn’t manage to find a description for those tables but according to gdal.org they will be created when a shape file is imported using ogr2ogr. Trying to import a shape file, I had them created:
CREATE TABLE geometry_columns (
`F_TABLE_CATALOG` varchar(256) DEFAULT NULL,
`F_TABLE_SCHEMA` varchar(256) DEFAULT NULL,
`F_TABLE_NAME` varchar(256) NOT NULL,
`F_GEOMETRY_COLUMN` varchar(256) NOT NULL,
`COORD_DIMENSION` int(11) DEFAULT NULL,
`SRID` int(11) DEFAULT NULL,
`TYPE` varchar(256) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
and
CREATE TABLE spatial_ref_sys (
`SRID` int(11) NOT NULL,
`AUTH_NAME` varchar(256) DEFAULT NULL,
`AUTH_SRID` int(11) DEFAULT NULL,
`SRTEXT` varchar(2048) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
the important column in the latter is the SRTEXT, eg for standard WGS84 lat lon, that is
GEOGCS[“WGS 84”, DATUM[“WGS_1984”,SPHEROID[“WGS 84”, 6378137, 298.257223563, AUTHORITY[“EPSG”,”7030″]], AUTHORITY[“EPSG”,”6326″]], PRIMEM[“Greenwich”,0,AUTHORITY[“EPSG”,”8901″]], UNIT[“degree”, 0.01745329251994328,
AUTHORITY[“EPSG”,”9122″]], AUTHORITY[“EPSG”,”4326″]]
the SRID field is to be pointed to by the SRID in the geometry_column table.
The geometry_column table has to have the following values:
F_TABLE_NAME : Name of table
F_GEOMETRY_COLUMN: Column in table that holds the geometrical information
COORD_DIMENTION: Dimentionallity of dataset, usually 2
SRID : pointer to the spatial_ref_sys table
TYPE: Type of geometry in the geometry colum, e.g. POINT or POLYGON
the last to columns may be blank.