如何查询最接近点的坐标的 SQL? [英] How to query SQL for coords nearest a point?

问题描述

这就是我想要做的:

从表单中我获取坐标:

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

I 查询 SQl 以查找这些坐标附近的所有帖子，因为每个帖子都有带有 lat 和 lng

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

帖子 1:

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

帖子 2:

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

然后我运行这个:

$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();
}

但我一无所获.

这使用了仅在平面上有效的勾股定理表面.这两个点需要足够接近，以便三角形基本上是在一个平坦的表面上.(否则，你需要一个伟大的圆"公式.)另外，如果两点跨越本初子午线的对立面 - 180 度的子午线学位 - 国际日期变更线.

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.

推荐答案

这就是我的结局

$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");

这篇关于如何查询最接近点的坐标的 SQL?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！