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

[Kevin Newman]

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