How to query SQL for coords nearest a point?

Question

This is what i am trying to do:

From a form I GET the coordinates:

&usp-custom-20=45.492384716155605&usp-custom-19=9.206341950000024

The I query SQl to find all posts near those coordinates, since each posts have custom fields with lat and lng

Post 1:

usp-custom-19 = 45.492384716155605
usp-custom-20 = 9.206341950000024

Post 2:

usp-custom-19 = 45.512383456255605
usp-custom-20 = 9.326341933210024

Then I run this:

$posts = $wpdb->get_col( "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");
foreach($posts as $post) { 
    the_title();
}

But I get nothing.

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 "Great Circle" formula.) 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.

have you verified that `$posts` is empty? `get_col` doesn't return an array of post objects, so your `foreach` loop isn't going to do anything there. — Milo, Nov 28 '18 at 16:33
Note that the fact this query is a post meta query that tries to find post IDs via their meta key/value pairs, and not the other way around, makes this query very slow/expensive on a database server. Not only that, but comparing multiple values making it a multidimensional query makes it even more expensive, and even worse, performing a calculation means this is one of the most expensive queries you could perform. The performance will degrade significantly as more rows are added to that table — Tom J Nowell, Nov 28 '18 at 16:37
A moderately sized database would be brought down by a handful of users making these kinds of requests. You will need to investigate special measures, e.g. elastic search, geoindexes, etc. As for the greater circle, that's a math question not a WordPress question, pythagoras' theorem might tell you the distance on a rectangles surface, but the earth is curved, your distance value will be distorted significantly at higher latitudes, and be smaller than reality in most situations — Tom J Nowell, Nov 28 '18 at 16:39
This appears to be identical to https://stackoverflow.com/questions/53523533/how-to-query-sql-for-coords-nearest-a-point, although the usernames are different. — ADyson, Nov 28 '18 at 16:41
What is wordpress.se for then ? If they are not to solve programming problems !! — Madhur Bhaiya, Nov 28 '18 at 16:44
@MadhurBhaiya It is for programming problems **specific to WordPress**. For example "How do I dynamically enqueue scripts when working with CPT who use special custom taxonomy while all being placed in a shortcode" — kero, Nov 28 '18 at 16:46
@kero `$posts = $wpdb->get_col`, `$wpdb->postmeta` does seem to be Wordpress related code. — Madhur Bhaiya, Nov 28 '18 at 16:49
@kero Is wordpress not using PHP, MySQL ? :P By this logic, all Wordpress related queries are primarily related to PHP and MySQL only, and should be handled in SO only, and there is no need of Wordpress.SE :P Anyways, this chat is more like Meta discussion.. peace out.. :-) — Madhur Bhaiya, Nov 28 '18 at 17:20
@TomJNowell thanks, makes sense do you have any code solution? — rob.m, Nov 28 '18 at 18:22
@ADyson had logged with another account, deleted that. I was on another machines and didn't noticed it, then reposted it but had left the other machines and went out of office. — rob.m, Nov 28 '18 at 18:23
@Milo yes it is empty, any other solution? I had a look at many but I can't figure out how to aply it to my case. e.g. there is this answer https://wordpress.stackexchange.com/questions/243687/post-queries-by-latitude-and-longitude — rob.m, Nov 28 '18 at 18:24
also why was this question migrated to SO while I pasted it on wordpress stackexchange since it is in regards of wordpress? — rob.m, Nov 28 '18 at 18:26
@rob.m It is hard to make out because of Wordpress specific references in your code. But I think you are looking for Haversine function. Check this answer: https://stackoverflow.com/a/53269497/2469308 — Madhur Bhaiya, Nov 28 '18 at 18:37
@MadhurBhaiya I am more looking into using this which is wordpress related but I can't figure out on how to apply it with my case https://wordpress.stackexchange.com/questions/243687/post-queries-by-latitude-and-longitude — rob.m, Nov 28 '18 at 18:39
@MadhurBhaiya this is the haversine formula http://plugins.svn.wordpress.org/wp-geoposts/trunk/query.php but how can I use it with my case? — rob.m, Nov 28 '18 at 18:41
this might be the solution https://gschoppe.com/wordpress/location-searches/ — rob.m, Nov 28 '18 at 19:32
Possible duplicate of [PHP MySql and geolocation](https://stackoverflow.com/questions/3606139/php-mysql-and-geolocation) — Peter O., Nov 28 '18 at 23:49
@Tom J Nowell - please see the 'Response to Comments' section im my updated answer https://stackoverflow.com/questions/53490990/how-to-query-coords-in-between-values-that-we-have-in-2-custom-fields/53492228#53492228 — , Nov 29 '18 at 23:35
this is what I ended up with `$post_ids = $wpdb->get_col("SELECT j.post_id FROM $wpdb->postmeta j, $wpdb->postmeta k where j.post_id = k.post_id and j.meta_key='usp-custom-19' and k.meta_key='usp-custom-20' and ( 6371 * acos( cos( radians($lat) ) * cos( radians( j.meta_value ) ) * cos( radians( k.meta_value ) - radians($ln) ) + sin( radians($lat) ) * sin(radians(j.meta_value)) ) ) < 50");` — rob.m, Nov 30 '18 at 09:08

score 0 · Accepted Answer · answered Nov 30 '18 at 09:08

This is what I ended up with

$post_ids = $wpdb->get_col("SELECT j.post_id FROM $wpdb->postmeta j, $wpdb->postmeta k where j.post_id = k.post_id and j.meta_key='usp-custom-19' and k.meta_key='usp-custom-20' and ( 6371 * acos( cos( radians($lat) ) * cos( radians( j.meta_value ) ) * cos( radians( k.meta_value ) - radians($ln) ) + sin( radians($lat) ) * sin(radians(j.meta_value)) ) ) < 50");

How to query SQL for coords nearest a point?

1 Answers1

Linked