24158

How to query coords in between values that we have in 2 custom fields?

Question:

I have two custom fields on my posts with values that could be like

get_usp-custom-19 = 40.85150386578784 get_usp-custom-20 = 14.258907499999964

Then I receive a GET from a form with some values like this:

$lat = $_GET['usp-custom-19']; $ln = $_GET['usp-custom-20'];

Now $lat and $ln have latitude and a longitude separated values and I create a query like:

$args = array( 'post_type' => 'post', 'meta_query' => array( array( 'relation' => 'AND', array( 'key' => 'get_usp-custom-19', 'value' => $lat, 'compare' => 'BETWEEN', 'type' => 'NUMERIC', ), array( 'key' => 'get_usp-custom-20', 'value' => $ln, 'compare' => 'BETWEEN', 'type' => 'NUMERIC', ), ), ), );

But I get wrong results as it isn't comparing in between, I also tried to set it type as CHAR or DECIMALS but still, wrong results. Also I could get <em>negative coords</em> like -9.258907499999964 and I was reading about cons and sen or using abs(), but I am getting very confused now

<strong>UPDATE</strong>

Here I try to create a radius

$lat = $_GET['usp-custom-19']; $ln = $_GET['usp-custom-20']; $args = get_posts( array( 'post_type' => 'post', 'posts_per_page' => -1, ) ); $query = new WP_Query( $args ); if ( $query->have_posts() ) { while ( $query->have_posts() ) { $query->the_post(); $customCoords = usp_get_meta(false, 'usp-custom-90'); $arrayCoords = explode( ",", $customCoords ); $radiusLn = +$arrayCoords[0] + 10; $radiusLat = +$arrayCoords[1] + 10; $args = array( 'post_type' => 'post', 'meta_query' => array( array( 'relation' => 'AND', array( 'key' => 'get_usp-custom-19', 'value' => array($ln, $radiusLn), 'compare' => '>=' ), array( 'key' => 'get_usp-custom-20', 'value' => array($lat, $radiusLat), 'compare' => '<=' ), ), ), ); $query = new WP_Query( $args ); if ( $query->have_posts() ) { while ( $query->have_posts() ) { $query->the_post(); the_title(); } } } }

Answer1:

Basically, you want to make sure that the value of the key and the $lat and $ln are the same type and they can be compared, for example, decimal.

Then you can use >= and <= to compare.

$args = array( 'post_type' => 'post', 'meta_query' => array( array( 'relation' => 'AND', array( 'key' => 'get_usp-custom-19', // make sure the value of this field is the same as $lat; 'value' => $lat, 'compare' => '>=', 'type' => 'NUMERIC', ), array( 'key' => 'get_usp-custom-20', // make sure the value of this field is the same as $ln; 'value' => $ln, 'compare' => '<=', 'type' => 'NUMERIC', ), ), ), );

You might also want to change the type of $lat and $ln first.

This <a href="https://wordpress.stackexchange.com/questions/70864/meta-query-compare-operator-explanation" rel="nofollow">post</a> explains the compare operators pretty well.

Answer2:

I am guessing that your are trying to find the closest posts with respect to lat and long. In this case I think a raw SQL query is easier to use:

$post_ids = $wpdb->get_col( <<<EOD SELECT m.post_id FROM $wpdb->postmeta m, $wpdb->postmeta n WHERE m.post_id=n.post_id AND m.meta_key='get_usp-custom-19' AND n.meta_key='get_usp-custom-20' AND (POW( CAST(m.meta_value AS DECIMAL(7,4)) - $lat, 2 ) + POW( COS(0.0175 * $lat) * ( CAST(n.meta_value AS DECIMAL(7,4)) - $ln ), 2 ) ) ) < $rad * $rad EOD );

The rows in the SQL join have the lat and long and then the results are where the sum of the squares of the differences from a given ($lat,$ln) is less than $rad squared.

Addenda:

The original query omitted the COS correction which must be applied to longitude degrees. This uses the Pythagorean theorem which is only valid on flat surfaces. The two points need to be close enough so that the triangle is essentially on a flat surface. (Otherwise, you need a <a href="https://stackoverflow.com/questions/27928/calculate-distance-between-two-latitude-longitude-points-haversine-formula" rel="nofollow">"Great Circle" formula</a>.) Also, there will be a problem if the two points straddle the opposite of the Prime Meridian - the meridian of 180 degrees - International Date Line.

Response to Comments:

The query as given is indeed to inefficient for large databases. The problem is that the join is done over the entire postmeta table. To fix this you can do the join over derived tables where the left derived table is restricted to rows with latitudes close to the given latitude and the right derived table are restricted to rows with longitude close to the given longitude.

The Pythagorean theorem is indeed not valid on the surface of the sphere. However, the objective here is to find close points not compute completely accurate distances. Since, we are looking for close points the distortion of the Pythagorean theorem is very small as the points are essentially on a flat surface. In my opinion, the Haversine formula is computationally far more expensive and provides unnecessary additional accuracy with respect to this problem.

Recommend

  • Show post if true/false is yes. Advanced custom fields
  • WP_Query headache
  • Wordpress Load More Posts onclick with ajax request jquery
  • java.lang.Long cannot be cast to java.lang.String
  • split an array into two based on a index in javascript
  • Wordpress Sum meta_values from posts and order them per category
  • Wordpress category sort by date
  • gcc, static library, external assembly function becomes undefined symbol
  • ACF fields not showing on a wordpress custom taxonomy page
  • How to Circumvent Perl's string escaping the replacement string in s///?
  • Securely using the PHP exec function
  • Allow anyone to view future posts of a certain post type (events) in Wordpress
  • Trying to get random array value to echo on element class
  • How to paginate a custom WP_query() loop - Wordpress
  • Delete first match
  • How to specify order of field to add using migration in Rails 3?
  • What is an undefined reference/unresolved external symbol error and how do I fix it?
  • Wordpress -how to return just current month posts on archive.php
  • wordpress custom page template
  • A question on shared library and thread specific data
  • Get DateTime.DayOfWeek for specific culture
  • importing from phone contacts to my application in iphone
  • android Locale.Builder() doesn't mirror the page in right-to-left locales
  • Check command for validity with data from other aggregate
  • wp-query category and has tag
  • Improve function that converts serialized object in dot notation into js object
  • D: how to extract data from archive?
  • QAugmentedReality on BB10
  • How can I pass arguments to ranlib using cmake?
  • Wordpress - How to change search value using a drop down selector?
  • Open source augmented reality framework for BlackBerry
  • Building libiconv fails with the Android standalone toolchain
  • SQL Server - Get all children of a row in many-to-many relationship?
  • How to separate filename from path? basename() versus preg_split() with array_pop()
  • Criterion causing memory consumption to explode, no CAFs in sight
  • How to estimate the Kalman Filter with 'KFAS' R package, with an AR(1) transition equation
  • Splitting given String into two variables - php
  • sending mail using smtp is too slow
  • costura.fody for a dll that references another dll
  • Binding checkboxes to object values in AngularJs