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

[Casey Bisson]

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.

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.  

--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.
>> _______________________________________________
>> wp-hackers mailing list
>> wp-hackers at lists.automattic.com
>> http://lists.automattic.com/mailman/listinfo/wp-hackers
> 
> 
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers