Actually, I’m working on a real estate website based on wordpress where I save apartment with many custom fields on them. Among these, each apartment has an address that I decide to manage through ACF Google Maps field.
Is there a way to use wp_query and discover which apartment are nearby an address (or lat / long values) ?
Hi @frabiacca
ACF saves the google map field in a serialized string like this:
a:3:{s:7:"address";s:47:"399 King St, West Melbourne VIC 3003, Australia";s:3:"lat";s:18:"-37.81093672361003";s:3:"lng";s:18:"144.95318412780762";}
To get the nearest location based on the lat/lng, I believe you need more than wp_query. You can use the wpdb class to query do the query and calculation, but it’s not an easy task. Please take a look at this thread to learn more about the calculation: http://stackoverflow.com/questions/11112926/how-to-find-nearest-location-using-latitude-and-longitude-from-sql-database.
If you don’t understand it, I suggest you hire a developer to help you out with it, and I’d recommend looking for one on https://studio.envato.com/ or https://www.upwork.com/.
I hope this helps.
I’m a developer too, thanks for your answer 🙂
Because the latitude and longitude are stored in a serialised array, there is no direct way of utilising MySQL spatial functions.
Is there a solution out there that somehow facilitates exactly this?
you can bypass the way WordPress and ACF save these fields in MySQL, by a custom function where you can tell WordPress to save meta values in your specific table.
You’d write this kind of code in functions.php – take a look at save_post (or edit_post) action and “locations_update_geodata” transient
Thanks @frabiacca. Indeed, that’s an alternative I can think of, though it’s not quite ideal.
The big advantage that this solution can properly make use of MySQL’s geospatial functions.
There’s also this:
https://wordpress.stackexchange.com/a/183503/43252
@frabiacca @mastababa You can use this a starting point (and finish there as well most likely). Either with the WP_Query or the rest api Reference link
I’m doing something similar and using this DB call. This is using the Haversine formula.
global $wpdb;
$tablePrefix = $wpdb->prefix;
$query = $wpdb->prepare("SELECT ".$tablePrefix."posts.*,
a.meta_value street_address,
b.meta_value city,
c.meta_value state,
d.meta_value zip_code,
e.meta_value phone,
f.meta_value website,
g.meta_value latitude,
h.meta_value longitude,
( 3959 * acos( cos( radians('%s') ) * cos( radians( g.meta_value ) ) * cos( radians( h.meta_value ) - radians(%s) ) + sin( radians('%s') ) * sin( radians( g.meta_value ) ) ) ) AS distance
FROM ".$tablePrefix."posts
LEFT JOIN ".$tablePrefix."postmeta a ON ".$tablePrefix."posts.ID = a.post_ID AND a.meta_key='street_address'
LEFT JOIN ".$tablePrefix."postmeta b ON ".$tablePrefix."posts.ID = b.post_ID AND b.meta_key='city'
LEFT JOIN ".$tablePrefix."postmeta c ON ".$tablePrefix."posts.ID = c.post_ID AND c.meta_key='state'
LEFT JOIN ".$tablePrefix."postmeta d ON ".$tablePrefix."posts.ID = d.post_ID AND d.meta_key='zip_code'
LEFT JOIN ".$tablePrefix."postmeta e ON ".$tablePrefix."posts.ID = e.post_ID AND e.meta_key='phone'
LEFT JOIN ".$tablePrefix."postmeta f ON ".$tablePrefix."posts.ID = f.post_ID AND f.meta_key='website'
LEFT JOIN ".$tablePrefix."postmeta g ON ".$tablePrefix."posts.ID = g.post_ID AND g.meta_key='latitude'
LEFT JOIN ".$tablePrefix."postmeta h ON ".$tablePrefix."posts.ID = h.post_ID AND h.meta_key='longitude'
WHERE ".$tablePrefix."posts.post_type = 'acf-store' AND ".$tablePrefix."posts.post_status = 'publish'
HAVING distance < '%s'
ORDER BY distance LIMIT 0 , 100",
$center_lat,
$center_lng,
$center_lat,
$radius);
$store_data = $wpdb->get_results($query);
