[wp-hackers] MySQL's spatial data support and GeoData in wordpress
CaptainN at unFocus.com
Thu Dec 16 01:25:18 UTC 2010
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:
g.geo_id, g.geo_key, AsText(g.geo_value),
POINT(40.9435272,-74.7296006), # Add your search location here -
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.
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):
> 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.
>> 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).
>> Kevin N.
More information about the wp-hackers