我正在从一个表单中获取邮政编码。由于我在一个表中存储了这些编码的经纬度,因此可以将其转换为经纬度坐标。
有什么想法吗?
编辑:
SELECT lng, lat from postcodeLngLat WHERE postcode = 'CV1'
我还有另一个表格,其中存储了一些场馆的经纬度。
SELECT v.lat, v.lng, v.name, p.lat, p.lng, p.postcode, 'HAVERSINE' AS distance FROM venuepostcodes v, postcodeLngLat p WHERE p.outcode = 'CB6' ORDER BY distance
我的目标是创建一个数据网格,显示每个场馆与邮政编码(在本例中为CV1)之间的距离。我知道Haversine公式应该能够实现我想要的结果,但我不知道从哪里开始将其合并到我的查询中。我认为公式需要放在上面查询中的'HAVERSINE'
处。有什么想法吗?
编辑:
SELECT o.outcode AS lead_postcode, v.venue_name, 6371.0E * (2.0E *asin(case when 1.0E < (sqrt(square(sin(((RADIANS(CAST(o.lat AS FLOAT)))-(RADIANS(CAST(v.lat AS FLOAT))))/2.0E)) + (cos(RADIANS(CAST(v.lat AS FLOAT))) * cos(RADIANS(CAST(o.lat AS FLOAT))) * square(sin(((RADIANS(CAST(o.lng AS FLOAT)))-(RADIANS(CAST(v.lng AS FLOAT))))/2.0E))))) then 1.0E else (sqrt(square(sin(((RADIANS(CAST(o.lat AS FLOAT)))-(RADIANS(CAST(v.lat AS FLOAT))))/2.0E)) + (cos(RADIANS(CAST(v.lat AS FLOAT))) * cos(RADIANS(CAST(o.lat AS FLOAT))) * square(sin(((RADIANS(CAST(o.lng AS FLOAT)))-(RADIANS(CAST(v.lng AS FLOAT))))/2.0E))))) end )) AS distance FROM venuepostcodes v, outcodepostcodes o WHERE o.outcode = 'CB6' ORDER BY distance