Support

Account

Home Forums Bug Reports Google Maps field normalization

Solved

Google Maps field normalization

  • The Google Maps field, while abstracting away the geocoding process for the user, stores the location data as a serialized array containing 3 string values: the address, latitude and longitude.

    This means that it’s impossible to query posts by distance from a supplied location using the stored latitude and longitude (which is a fairly obvious use of location data), because storing the location data as a serialized array breaks 1st Normal Form.

    Suggested fix: Store address, latitude and longitude as atomic meta values.

  • Workaround for anyone experiencing the same problem: hook to the ‘acf/save_post’ action and use update_post_meta() to duplicate the latitude and longitude data to hidden meta fields, enabling a custom query to be crafted to return posts by distance:

    `function location_search($latitude, $longitude, $maxdistance = 5) {
    global $wpdb;
    $query = “SELECT wp_posts.post_title as post_title,
    wp_posts.ID as post_id,
    latitude.meta_value as latitude,
    longitude.meta_value as longitude,
    ( 3959 * acos(
    cos( radians( $latitude ) ) *
    cos( radians( CONVERT( latitude.meta_value, DECIMAL( 10, 6 ) ) ) ) *
    cos( radians( CONVERT( longitude.meta_value, DECIMAL( 10, 6 ) ) ) – radians( $longitude ) ) +
    sin( radians( $latitude ) ) * sin( radians( CONVERT( latitude.meta_value, DECIMAL( 10, 6 ) ) ) )
    ) ) AS distance
    FROM wp_postmeta as latitude, wp_postmeta as longitude,
    wp_posts
    WHERE (wp_posts.ID = latitude.post_id
    AND latitude.meta_key = ‘_latitude’ )
    AND (wp_posts.ID = longitude.post_id
    AND longitude.meta_key = ‘_longitude’ )
    HAVING distance < $maxdistance
    ORDER BY distance ASC”;
    return $query;
    }`

  • @RenaissanceDesign did you get this working?

    Do you have a possible example of the code you used to query the posts as you mentioned above?

  • Also looking for this solution.I am able to save my lat/long values into separate custom fields.

    I will play around with your function example and see if I can get that working.

    This might also be of value: http://wpquestions.com/question/show/id/7926

  • Just thought I would add too this post.

    I had this same issue but did not want to deal with keeping the normalized postmeta fields _latitude and _longitude in sync with the ACF postmeta.

    This query “deserializes” the ACF google_maps field so no normalization is required. Obviously it would be better if ACF map field was just normalized by the plugin and not stored as serialized PHP object.

    
    function get_nearest_location_page($latitude, $longitude, $maxdistance = 100) {
    	global $wpdb;
    	$location_query = <<<EOF
    	SELECT wp_posts.post_title as post_title,
    	wp_posts.ID as post_id,
    	( 3959 * acos(
    	cos( radians( %f ) ) *
    	cos( radians( CONVERT(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(location.meta_value,';',4),':',-1),'"',''),  DECIMAL( 10, 6 )) ) ) *
    	cos( radians( CONVERT(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(location.meta_value,';',6),':',-1),'"',''),  DECIMAL( 10, 6 ))  ) - radians( %f ) ) +
    	sin( radians( %f ) ) * sin( radians( CONVERT(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(location.meta_value,';',4),':',-1),'"',''),  DECIMAL( 10, 6 ))  ) )
    	) ) AS distance
    	FROM wp_postmeta as location, wp_posts
    	WHERE wp_posts.post_type = "page"
    	AND wp_posts.ID = location.post_id
    	AND location.meta_key LIKE "service_locations_%%"
    	HAVING distance < %f
    	ORDER BY distance ASC
    	LIMIT 1
    EOF;
    	$location_query_string = $wpdb->prepare($location_query, $latitude, $longitude, $latitude, $maxdistance);
    	return $wpdb->get_results($location_query_string, ARRAY_A);
    }
    

    in this case my field is actually an ACF Pro repeater field named service_locations.

Viewing 5 posts - 1 through 5 (of 5 total)

The topic ‘Google Maps field normalization’ is closed to new replies.