[Georuby-devel] Spatial adapter

Teresa Molina tmolina at chemidex.com
Mon Feb 18 13:20:52 EST 2008

I was unable to find a user group email, so please excuse me if I  
missed the correct mailing address.

I ran into an issue with Spatial Adapter for MySQL last week, and I  
wonder if I have done something wrong or if there are known issues  
with the latest version of Rails. My main issue is in  including a  
spatial index within a migration script on a column type of  
"geometry" for a MySQL MyISAM table. (Of course, I am new to  
spatially enabled databases, so if there is some fundamental error in  
what I have done, please let me know. At the moment, the best I can  
assert is the way my table and index are currently set up is the way  
that works for us now.)

I installed Spatial Adapter in order to work with a spatially aware  
column in a MySQL 5.0.45 database (developing on Mac OS X Tiger with  
Rails 2.x, Ruby 1.8 installed manually [not the original Apple  
version].). In this scenario, I'm creating a point column on a  
address table, which will be used in  conjunction with a polygon  
object to determine whether the location falls within a particular  
area. This area polygon (really only a rectangle bounds) comes from  
Google Map lat/longs in the UI, which returns with points up to 10  
decimal places in accuracy (such as  

Originally, I created the database table manually from sql scripts,  
adding the column and index directly in MySQL (being totally new to  
spatially aware features, I did this manually in order to first test  
the technique), and the above situation works as expected: I retrieve  
the point bounds from the map and search against the location table  
points to see which were contained by the bounds.

With the technique working as expected in my local development  
environment, I needed to deploy by adding the new column to our  
migration scripts. "Sexy Migrations" with Rails 2.x did not work at  
all using the column type "point" (error was that type point was not  
recognized), and I had to write my migration script the "old- 
fashioned" way ("t.point" fails; "t.column  'Geocode', :point" works;  
index was added as: add_index "cx_geographiclocation",  
"Geocode", :spatial=>true).

Once I had done that  and re-deployed the spatially aware table to my  
dev database, I tested to make certain that I was retrieving the data  
as expected. I found this was no longer working; basically, all  
selects were returning 0 rows. I found that if I truncated the  
degrees to whole numbers, I did retrieve some results, but this was  
not useful to me in practical application (we need the fine-point lat- 

Fortunately, I had backed up the original table before trying the  
migration, and upon inspection, there were two main differences: The  
first was that the original column type was "geometry" as per MySQL  
tutorial instructions. The second set of differences were in the  
index details; specifically, the index created from the migrated  
point column had "1701" for Cardinality and "Sub_part 21", whereas  
the original column had NULL for Cardinality and 32 for the Sub_part.

First, I changed the migration script to create the column as type  
"geometry", but this only worked in the rake migration when there was  
no index added; the index threw "Mysql::Error: BLOB/TEXT column  
'Geocode' used in key specification without a key length: CREATE   
INDEX index_cx_geographiclocation_on_Geocode ON cx_geographiclocation  
(Geocode)." Ultimately, I removed the index from the migration, left  
the column type as "geometry" and then manually added the index  
again. This caused the original set-up to work (see below for SQL select
), allowing me to pass in and retrieve the expected data.

However, what I need is to include the spatial index in the migration  
script on a geometry data type column. Working with sexy migrations  
would also be helpful, but it is not vital.


Index Code:

memberlink_development.cx_geographiclocation (Geocode);

SQL Select:

SELECT * FROM `cx_geographiclocation` WHERE (MBRIntersects(GeomFromWKB 
cx_geographiclocation.`Geocode`) )


Thank you for time, effort and help in this matter, as well as for  
the plugin and gems to begin with. Obviously, I would have a much  
more difficult time with this were they not available.

Teresa Molina
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://rubyforge.org/pipermail/georuby-devel/attachments/20080218/c97c2185/attachment-0001.html 

More information about the Georuby-devel mailing list