Distance from for mysql 5.6

  • Not strictly support (and I think your plugin is fantastic btw).

    But any ideas how to find closes post objects to a given point using you plugin when you are on mysql 5.6?

    ST_distance returns a distance in degrees which is not very useful and st_distance sphere is not available…

    Any help is appreciated as I’d prefere not to have to build custom tables etc and I can do a great circle calculation using plain meta but it will be slow…

Viewing 1 replies (of 1 total)
  • Plugin Author Michael Moore

    (@stuporglue)

    Hi shawfactor,

    Thanks for the plugin review and kind words. I’m so sorry I didn’t see your message sooner! I hope you’ve already solved your problem, but if not, perhaps this will help:

    The most accurate way would be to use a great circle calculation. You could use a custom function like wp_distance_point_m(point1,point2) which is included in WP-GeoMeta, or which you could incorporate into your own code.

    Your query would then be something like this (untested): 

    SELECT 
post_id, 
wp_distance_point_m( ST_GeomFromText('POINT($myLong $myLat)'), meta_value) AS distance
FROM wp_postmeta_geo g
ORDER BY 
wp_distance_point_m( ST_GeomFromText('POINT($myLong $myLat)'), meta_value)
LIMIT 1

    It would avoid scanning the entire meta table, but it still isn’t making use of spatial indexes.

    ——

    If speed is an issue you could cheat a little. If you know that you only want to show the closest post if it’s within 100km, you could create a square (or a fake circle) and use ST_Intersects in your where clause, which would reduce which points need a distance measurement.

    Again, untested, but something like this should work.

    SELECT 
post_id, 
wp_distance_point_m( ST_GeomFromText('POINT($myLong $myLat)'), meta_value) AS distance
FROM wp_postmeta_geo g
WHERE ST_INTERSECTS(meta_value, wp_buffer_point_m(ST_GeomFromText('POINT($myLong $myLat)'), 100, 8) )
ORDER BY 
wp_distance_point_m( ST_GeomFromText('POINT($myLong $myLat)'), meta_value)
LIMIT 1
    • This reply was modified 7 years, 8 months ago by Michael Moore. Reason: forgot I wasn't writing Markdown
    • This reply was modified 7 years, 8 months ago by Michael Moore.
Viewing 1 replies (of 1 total)
  • The topic ‘Distance from for mysql 5.6’ is closed to new replies.

Tags