Mailund on the Internet

Ok, I managed to hide the spatial queries behind stored procedures.  Good news, since now I don't have to write this functionality in the different languages I will access the database in.

If you recall, I store genomic positions as geometric points so I can efficiently query regions.  This means I have to store a "point" together with each genomic position, and that I need to create "rectangles" when I'm querying a genomic segment.

Storing a point together with a genomic position, I handle as this:

CREATE PROCEDURE insert_orangutan_position (
  chromosome TINYINT ,
  chromosome_pos INT ,
  alignment_pos INT )
BEGIN
  DECLARE point POINT;
  SET point = GeomFromText(CONCAT('POINT(', chromosome, ' ', chromosome_pos, ')'));
  INSERT INTO orangutan_genome (chromosome, chromosome_pos, alignment_pos, genomic_point)
  VALUES (chromosome, chromosome_pos, alignment_pos, point);
END|

and querying I handle like this:

CREATE PROCEDURE query_orangutan_segment (
  chromosome TINYINT ,
  chromosome_start INT ,
  chromosome_end INT)
BEGIN
  DECLARE region POLYGON;
  SET region = GeomFromText(CONCAT('POLYGON((',
                             chromosome-.1, ' ', chromosome_start-.1, ', ',
                             chromosome+.1, ' ', chromosome_start-.1, ', ',
                             chromosome+.1, ' ', chromosome_end+.1, ', ',
                             chromosome-.1, ' ', chromosome_end+.1, ', ',
                             chromosome-.1, ' ', chromosome_start-.1,
                        '))'));
  SELECT chromosome, chromosome_pos, HC1, HC2, HO, CO
  FROM orangutan_genome INNER JOIN posterior_probabilities USING(alignment_pos)
  WHERE CONTAINS(region, genomic_point)
  ORDER BY chromosome_pos;
END|

I still need to write some accessing functions in my script languages, but those are very simple wrappers now.

--

85-107=-22

I need to finish the database of CoalHMM results this week, so we can start the analyses next week, so today I will write the code for querying genomic position with spatial indices.

I'll first try with stored procedures, something I know absolutely nothing about, so I've googled for tutorials and found:

• A DIY Approach to Stored Procedures in MySQL
• MySQL Stored Procedures

If you know of any better, please let me know.

If all this fails, I'll have to hack up a Python module and some R code for accessing the database.  I'll probably need that anyway, but the more I can share between them by putting it in the database the better.

--

85-106=-21

Check out Make My Head Grow!

The object is to grow your head (by slamming it into the floor) and then use your gigantic head to push the opponent out of the game (by slamming your head against the wall).

Weird, but it managed to win all five categories of the Nordic Game Jam.

It is not really my kind of game -- I prefer strategy games -- but since four of the seven authors are students here in Århus, I thought I should mention it anyway.

--

85-105=-20