Support » Plugin: WP-GeoMeta » 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 2 years, 2 months ago by  Michael Moore. Reason: forgot I wasn't writing Markdown
    • This reply was modified 2 years, 2 months ago by  Michael Moore.
Viewing 1 replies (of 1 total)
  • The topic ‘Distance from for mysql 5.6’ is closed to new replies.