cast((180.0 * (cast(floor(cast(client_longitude as double) * 20037508.34 / 180.0 / 300) as int) * 300 + 150) / 20037508.34) as decimal(30,5))/(45.0/3600)分析
时间: 2024-04-03 14:35:16 浏览: 16
这是一段SQL语句,用于将客户经度转换为相应的网格编号。具体的解释如下:
1. 首先将客户经度 `client_longitude` 转换为地理坐标系下的投影坐标,使用的是墨卡托投影,即将经度转换为X坐标。
2. 将投影坐标进行离散化,将其划分为以300为边长的正方形网格。这里用到的是地理坐标系下的网格编号。
3. 将网格编号转换为中心点的经度坐标,最后将其转换为度分秒的形式。
综上所述,这段SQL语句的作用是将客户经度转换为一个地理坐标系下的网格编号,并将该网格中心点的经度坐标转换为度分秒的形式。
相关问题
select concat(radio_mcc,Radio_MNC) as plmn,Radio_Brand,Radio_Network_Generation,left(Test_Timestamp,7) as month,traffic_bytes,traffic_direction,Location_Longitude as longtition,Location_Latitude as latitude cast((180.0 * (cast(floor(cast(Location_Longitude as double) * 20037508.34 / 180.0 / 300) as int) * 300 + 50) / 20037508.34) as decimal(30,10)) as grid_longitude, case when ln(tan((90.0 +cast(Location_Latitude as double)) * pi() / 360.0)) / pi() >1 then cast((57.295779513082323 * (2.0 * atan(exp((cast(floor(20037508.34/300) as int)*300 + 50) /20037508.34 * 3.141592653589793)) - 1.570796326794897)) as decimal(30,10)) when ln(tan((90.0 +cast(Location_Latitude as double)) * pi() / 360.0)) / pi() < -1 then cast((57.295779513082323 * (2.0 * atan(exp((cast(floor(-20037508.34/300) as int)*300 + 50) /20037508.34 * 3.141592653589793)) - 1.570796326794897)) as decimal(30,10)) else cast((57.295779513082323 * (2.0 * atan(exp((cast(floor(ln(tan((90.0 +cast(Location_Latitude as double)) * pi() / 360.0)) / pi() * 20037508/300) as int)*300 + 50) /20037508.34 * 3.141592653589793)) - 1.570796326794897)) as decimal(30,10)) end as grid_latitude, avg(throughput_receive) as download_bps, sum(test_samples) as samples from public_standard.ps04_p3_ntr__no where Radio_Network_Generation not in ('WiFi','Unknown','2G') and concat(radio_mcc,Radio_MNC)!='' and concat(radio_mcc,Radio_MNC) in (select a.plmn from (select concat(radio_mcc,Radio_MNC) as plmn,count(0) as num from public_standard.ps04_p3_ntr__no where concat(radio_mcc,Radio_MNC)!='' group by concat(radio_mcc,Radio_MNC) order by num desc limit 4)a ) group by radio_mcc,Radio_MNC,Radio_Brand,Radio_Network_Generation,month,traffic_bytes,traffic_direction,grid_longitude,grid_latitude,Location_Longitude,Location_Latitude;改为正确写法
你提供的查询语句有一些错误,请注意以下修改:
```
SELECT
CONCAT(radio_mcc, Radio_MNC) AS plmn,
Radio_Brand,
Radio_Network_Generation,
LEFT(Test_Timestamp, 7) AS month,
traffic_bytes,
traffic_direction,
Location_Longitude AS longtition,
Location_Latitude AS latitude,
CAST((180.0 * (CAST(FLOOR(CAST(Location_Longitude AS DOUBLE) * 20037508.34 / 180.0 / 300) AS INT) * 300 + 50) / 20037508.34) AS DECIMAL(30, 10)) AS grid_longitude,
CASE
WHEN LN(TAN((90.0 + CAST(Location_Latitude AS DOUBLE)) * PI() / 360.0)) / PI() > 1 THEN CAST((57.295779513082323 * (2.0 * ATAN(EXP((CAST(FLOOR(20037508.34 / 300) AS INT) * 300 + 50) / 20037508.34 * 3.141592653589793)) - 1.570796326794897)) AS DECIMAL(30, 10))
WHEN LN(TAN((90.0 + CAST(Location_Latitude AS DOUBLE)) * PI() / 360.0)) / PI() < -1 THEN CAST((57.295779513082323 * (2.0 * ATAN(EXP((CAST(FLOOR(-20037508.34 / 300) AS INT) * 300 + 50) / 20037508.34 * 3.141592653589793)) - 1.570796326794897)) AS DECIMAL(30, 10))
ELSE CAST((57.295779513082323 * (2.0 * ATAN(EXP((CAST(FLOOR(LN(TAN((90.0 + CAST(Location_Latitude AS DOUBLE)) * PI() / 360.0)) / PI() * 20037508 / 300) AS INT) * 300 + 50) / 20037508.34 * 3.141592653589793)) - 1.570796326794897)) AS DECIMAL(30, 10))
END AS grid_latitude,
AVG(throughput_receive) AS download_bps,
SUM(test_samples) AS samples
FROM
public_standard.ps04_p3_ntr__no
WHERE
Radio_Network_Generation NOT IN ('WiFi', 'Unknown', '2G')
AND CONCAT(radio_mcc, Radio_MNC) != ''
AND CONCAT(radio_mcc, Radio_MNC) IN (
SELECT a.plmn
FROM (
SELECT CONCAT(radio_mcc, Radio_MNC) AS plmn, COUNT(0) AS num
FROM public_standard.ps04_p3_ntr__no
WHERE CONCAT(radio_mcc, Radio_MNC) != ''
GROUP BY CONCAT(radio_mcc, Radio_MNC)
ORDER BY num DESC
LIMIT 4
) a
)
GROUP BY
radio_mcc, Radio_MNC, Radio_Brand, Radio_Network_Generation, month, traffic_bytes, traffic_direction, grid_longitude, grid_latitude, Location_Longitude, Location_Latitude;
```
这样修改后的查询语句应该是正确的了。请注意检查你的数据库中的表名和字段名是否与查询语句中的相匹配。
round([CLIENT_LONGITUDE]/round([CLIENT_LATITUDE]/(0.000009*[grid_size]),0)*0.000009*[grid_size],0) * round([CLIENT_LATITUDE]/(0.000009*[grid_size]),0)*0.000009*[grid_size]
这是一个SQL查询语句,用于计算经纬度所在网格的网格编码。其中,[CLIENT_LONGITUDE]和[CLIENT_LATITUDE]分别表示客户端的经度和纬度,[grid_size]表示网格的大小。具体而言,该查询语句先将[CLIENT_LATITUDE]除以[grid_size]并四舍五入得到行网格数,再将[CLIENT_LONGITUDE]除以([CLIENT_LATITUDE]/(0.000009*[grid_size]))并四舍五入得到列网格数,最后将行网格数和列网格数相乘并乘以[grid_size]和0.000009得到该经纬度所在网格的左下角坐标,也就是该网格的网格编码。