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.