Setting up a mysql database for spatial data

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.

This entry was posted in mapserver. Bookmark the permalink.