[Georuby-devel] Spatial adapter

Guilhem Vellut guilhem.vellut at gmail.com
Tue Feb 19 06:21:05 EST 2008

Can you send the entire (unsexy) migration code for the table
containing the spatial column, including the spatial index creation,
as well as a sample of how the spatial data is loaded and retrieved
from the db? I will have a look. About sexy migrations I think it will
be added soon by Shoaib.
Thank you.

On Feb 18, 2008 7:20 PM, Teresa Molina <tmolina at chemidex.com> wrote:
> 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
> "bounds"=>"[[41.05450196329048,-88.868408203125],[36.81808022778526,-100.458984375]]").
> 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-longs).
> 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(0x010400000002000000010100000040F39AEBF986444000000000943756C00101000000C4EB57DAB668424000000000601D59C0,-1),
> 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.
> Sincerely,
> Teresa Molina
> _______________________________________________
> Georuby-devel mailing list
> Georuby-devel at rubyforge.org
> http://rubyforge.org/mailman/listinfo/georuby-devel

More information about the Georuby-devel mailing list