{"id":665,"date":"2010-01-29T14:54:23","date_gmt":"2010-01-29T13:54:23","guid":{"rendered":"http:\/\/sickel.net\/blogg\/?p=665"},"modified":"2010-01-30T12:23:50","modified_gmt":"2010-01-30T11:23:50","slug":"setting-up-a-mysql-database-for-spatial-data","status":"publish","type":"post","link":"https:\/\/sickel.net\/blogg\/?p=665","title":{"rendered":"Setting up a mysql database for spatial data"},"content":{"rendered":"

I would prefer to use postgis<\/a>, 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. <\/p>\n

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<\/p>\n

alter table mobile_rawdata add column coord point <\/p>\n

and then I copied the coordinates into that column<\/p>\n

update rawdata set coord=point(lon,lat) where not lat is null;<\/p>\n

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. <\/p>\n

So I made a new table<\/p>\n

create table mobile_spatial(
\nOGR_FID serial primary key,
\nSHAPE geometry not null,
\nrawdataid integer not null,
\naddtime timestamp default current_timestamp);<\/p>\n

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. <\/p>\n

Then I tested it with <\/p>\n

ogrinfo -so MySQL:radioecology_9,user=user,password=pass mobile_spatial<\/p>\n

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.<\/p>\n

I didn’t manage to find a description for those tables but according to gdal.org<\/a> they will be created when a shape file is imported using ogr2ogr. Trying to import a shape file, I had them created:<\/p>\n

CREATE TABLE geometry_columns (
\n `F_TABLE_CATALOG` varchar(256) DEFAULT NULL,
\n `F_TABLE_SCHEMA` varchar(256) DEFAULT NULL,
\n `F_TABLE_NAME` varchar(256) NOT NULL,
\n `F_GEOMETRY_COLUMN` varchar(256) NOT NULL,
\n `COORD_DIMENSION` int(11) DEFAULT NULL,
\n `SRID` int(11) DEFAULT NULL,
\n `TYPE` varchar(256) NOT NULL
\n) ENGINE=InnoDB DEFAULT CHARSET=latin1;<\/p>\n

and<\/p>\n

CREATE TABLE spatial_ref_sys (
\n `SRID` int(11) NOT NULL,
\n `AUTH_NAME` varchar(256) DEFAULT NULL,
\n `AUTH_SRID` int(11) DEFAULT NULL,
\n `SRTEXT` varchar(2048) DEFAULT NULL
\n) ENGINE=InnoDB DEFAULT CHARSET=latin1;<\/p>\n

the important column in the latter is the SRTEXT, eg for standard WGS84 lat lon, that is <\/p>\n

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,
\nAUTHORITY[“EPSG”,”9122″]], AUTHORITY[“EPSG”,”4326″]]<\/p>\n

the SRID field is to be pointed to by the SRID in the geometry_column table. <\/p>\n

The geometry_column table has to have the following values:
\nF_TABLE_NAME : Name of table
\nF_GEOMETRY_COLUMN: Column in table that holds the geometrical information
\nCOORD_DIMENTION: Dimentionallity of dataset, usually 2
\nSRID : pointer to the spatial_ref_sys table
\nTYPE: Type of geometry in the geometry colum, e.g. POINT or POLYGON
\nthe last to columns may be blank.<\/p>\n","protected":false},"excerpt":{"rendered":"

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 … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[20],"tags":[],"class_list":["post-665","post","type-post","status-publish","format-standard","hentry","category-mapserver"],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/pnVtD-aJ","jetpack_sharing_enabled":true,"jetpack_likes_enabled":true,"jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sickel.net\/blogg\/index.php?rest_route=\/wp\/v2\/posts\/665","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sickel.net\/blogg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sickel.net\/blogg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sickel.net\/blogg\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/sickel.net\/blogg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=665"}],"version-history":[{"count":7,"href":"https:\/\/sickel.net\/blogg\/index.php?rest_route=\/wp\/v2\/posts\/665\/revisions"}],"predecessor-version":[{"id":672,"href":"https:\/\/sickel.net\/blogg\/index.php?rest_route=\/wp\/v2\/posts\/665\/revisions\/672"}],"wp:attachment":[{"href":"https:\/\/sickel.net\/blogg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=665"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sickel.net\/blogg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=665"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sickel.net\/blogg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=665"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}