[wp-hackers] MySQL's spatial data support and GeoData in wordpress

[Kevin Newman]

On 12/16/10 1:55 AM, Casey Bisson wrote:
> That's very close to the structure I've been playing with, but I'd suggest adding a geo point column to that structure to store a centroid. It's a lot more efficient to index and calculate distances between them than for unconstrained areas.
My understanding of MySQL isn't all that advanced, nor am I familiar 
with GIS terms (if GIS is the right term itself lol) - I'm not even sure 
what you said. I used a GEOMETRY type instead of POINT, because I though 
it might be useful to put types in there other than POINT - but I have 
no idea what kind of affect that has on performance. I do know that 
doing the distance calculation on my existing table is not utilizing the 
geo_key (according to EXPLAIN) - it's also doing a filesort.

I also didn't have any luck using the Distance function in MySQL - it 
just plain didn't work.

> But, I wonder if the correct model for a table like this is actually the tag/taxonomy tables. Just as any one post can relate to multiple locations, a single locations can relate to multiple posts.
I haven't looked into that at all. Still new to WordPress in general, 
but you might be right. :-)
> --Casey Bisson
> http://MaisonBisson.com
>
>
> On Dec 14, 2010, at 3:44 PM, Kevin Newman<CaptainN at unFocus.com>  wrote:
>
>> My suggestion for a table structure, that might even be appropriate enough for core (if there are genuine advantages of the spacial column type) would be to model it after the postmeta table. This would allow for any general purpose GEOMETRY (the MySQL type) data to be stored in there by meta_key:
>>
>> CREATE TABLE `wp_postgeo` (
>>   `geo_id` bigint(20) unsigned NOT NULL auto_increment,
>>   `post_id` bigint(20) unsigned NOT NULL default '0',
>>   `geo_key` varchar(255) default NULL,
>>   `geo_value` GEOMETRY,
>>   PRIMARY KEY (`geo_id`),
>>   KEY `geo_value` (`geo_value`(255)),
>>   KEY `post_id` (`post_id`),
>>   KEY `geo_key` (`geo_key`)
>> ) ENGINE=MyISAM
>>
>> Some notes;
>> - I have no idea how big the index should be for KEY `geo_value` - I set it to 255, because I needed to set it to something to get MySQL to create the table.
>> - I set the type to MyISAM becuase I the docs say that is the only engine that supports spatial indexes.)
>> - The GEOMETRY column type actually allows for any of the other geometry based data formats to be stored within that column (most geocoding would be storing a POINT type for example): http://dev.mysql.com/tech-resources/articles/4.1/gis-with-mysql.html
>>
>> There may also be a case for a `wp_usergeo` table.
>>
>> Kevin N.
>>
>>
>>
>> On 12/14/10 3:07 PM, Kevin Newman wrote:
>>> It looks like there are some older threads on the subject of how to best store geo location data, but MySQL's built in spatial column support didn't come up, so I thought I'd mention it as a possible standard for geo data storage.
>>>
>>> http://maisonbisson.com/blog/post/12147/working-with-spatial-data-in-mysql/
>>> http://dev.mysql.com/doc/refman/5.0/en/creating-spatial-columns.html
>>>
>>> My SQL skills are rusty, but I think we can still use that support even with the current Meta data storage format suggested here http://codex.wordpress.org/Geodata by using temp tables and/or subqueries (which if I remember correctly still use temp tables). I wonder if it'd be better to standardize on a static table, to avoid the overhead of the temp tables to gain efficient features like distance based sorting (if it's truly worth it to use spatial columns in MySQL - which I'm not even sure about).
>>>
>>> Thoughts?
>>>
>>> Kevin N.