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

[Kevin Newman]

Once you have that table setup, and data going in, you could pull out 
posts doing useful things like location based order by with a SQL 
statement like this:

SELECT
     p.post_title, p.post_content,
     g.geo_id, g.geo_key, AsText(g.geo_value),
     GLength(LineStringFromWKB(LineString(
       POINT(40.9435272,-74.7296006), # Add your search location here - 
lat, lng
       AsBinary(geo_value))))
     AS distance
FROM wp_postgeo g
LEFT JOIN wp_posts p ON p.ID = g.post_id
WHERE p.post_type <> "revision" AND p.post_status <> "auto-draft"
ORDER BY distance ASC;

Pretty nifty stuff.

Kevin N.



On 12/14/10 6:44 PM, Kevin Newman 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.