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

[Casey Bisson]

Don't get me wrong, I think you need both a point and a geometry column: one to support searching the data and the other to display the area it represents. 

Searching for points near a given point should work efficiently, but mysql has to do a lot more work on geometry columns to answer questions like that. I don't have the docs in front of me, but I'm not sure if the distance function works on geometry values (maybe just points). 

--Casey

On Dec 16, 2010, at 12:01 PM, Kevin Newman <CaptainN at unFocus.com> wrote:

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