麻烦问一下老师，我要在django中实现：从数据库中找到距离当前gps位置最近的数据信息，应该如何实现呢？查询语句不会写，网上也找不到。
数据模型如下:
class Block(models.Model):
""“
小区
”""
name = models.CharField(max_length=30, null=True, blank=True, verbose_name=“小区名称”)
address = models.CharField(max_length=80, null=True, blank=True, verbose_name=“详细地址”)
city = models.CharField(max_length=40, null=True, blank=True, verbose_name=“城市名称”)
district = models.CharField(max_length=80, null=True, blank=True, verbose_name=“城市名称”)
longitude = models.FloatField(verbose_name=“经度”)
latitude = models.FloatField(verbose_name=“纬度”)
members = models.ManyToManyField(‘UserProfile’,
through=‘BlockUserShip’,
through_fields=(‘block’, ‘user’),
)
class Meta:
verbose_name = "小区"
verbose_name_plural = verbose_name

[实验数据]

本实验所用数据为江苏省南京市出租车从2010年9月1日到2日的GPS位置数据，包含了7,726辆出租车的共33,042,225条位置记录。数据集已经存放在HDFS上，路径为“/data/13/1/gps/gps.csv”，各字段以制表符分隔。数据集还存放在了Hive上，表名为“bigdata_cases.gps”。
各字段的定义为：
字段	定义
VehicleId	出租车标识符
Time	数据收集时间
Longitude	经度
Latitude	纬度
Speed	速度，以千米/小时计
Direction	方向，与正北方向的夹角度数
PassengerState	载客状态，1为载客，0为空车

数据集的前5行为：
student1@master:~$ hdfs dfs -cat /data/13/1/gps/gps.csv | head -5
806584008859    2010-09-01 00:01:42 118.85504299999999  31.939446   0   0   0
806770549907    2010-09-01 00:01:45 118.74909   32.089162000000002  30  6   1
806770943693    2010-09-01 00:01:46 118.856236  32.094287999999999  26  318 1
806914743134    2010-09-01 00:01:48 118.836872  31.931645   0   0   0
806451847007    2010-09-01 00:01:51 118.795597  32.039382000000003  20  10  0

[实验步骤提示]

在以下提示步骤中，步骤1是用Hive做数据分析和数据准备，所有代码在大数据计算集群上执行，步骤2是用R语言做数据可视化。


用Hive做数据分析和数据准备

a. 统计每小时出租车的载客情况

统计每小时总共的独立出租车数量以及在该小时载过客的独立出租车数量，并计算出载过客的比例。
 hive -e \
 "select day(Time) as Day, hour(Time) as Hour, 
 count(distinct case when PassengerState=1 then VehicleId else NULL end) as Passenger, 
 count(distinct VehicleId) as Total,
 count(distinct case when PassengerState=1 then VehicleId else NULL end) / count(distinct VehicleId) as PassengerRatio
 from bigdata_cases.gps
 group by day(Time), hour(Time)
 order by Day, Hour;" \




1.csv

得到结果：

Day	Hour	Passenger	Total	PassengerRatio
1	0	4258	7457	0.5710071074158509
1	1	3760	7453	0.5044948342949148
1	2	3156	7457	0.42322649859192707
1	3	2600	7482	0.3475006682705159
1	4	1219	7462	0.16336102921468776
1	5	1356	7454	0.18191574993292192
1	6	2141	7479	0.2862682176761599
1	7	3798	7494	0.5068054443554844
1	8	5345	7504	0.7122867803837953
1	9	5998	7512	0.7984558040468583
1	10	6198	7508	0.8255194459243473
1	11	6246	7497	0.8331332533013205
1	12	6424	7526	0.8535742758437417
1	13	5810	7486	0.7761154154421587
1	14	6004	7485	0.8021376085504343
1	15	6124	7486	0.818060379374833
1	16	6166	7505	0.8215856095936043
1	17	6085	7507	0.8105767949913414
1	18	5957	7501	0.7941607785628583
1	19	6292	7519	0.836813406038037
1	20	5788	7507	0.7710137205275076
1	21	5644	7491	0.7534374582832732
1	22	5314	7486	0.7098584023510554
1	23	6	11	0.5454545454545454
2	0	6	10	0.6
2	1	5	10	0.5
2	2	527	2179	0.24185406149609912
2	3	245	3115	0.07865168539325842
2	4	420	2747	0.15289406625409538
2	5	156	1881	0.08293460925039872
2	7	4653	7496	0.6207310565635006
2	8	5951	7504	0.793043710021322
2	9	6261	7483	0.8366965120940799
2	10	6324	7500	0.8432
2	11	6231	7497	0.8311324529811924
2	12	6075	7484	0.8117316942811331
2	13	5862	7493	0.7823301748298412
2	14	5956	7508	0.7932871603622802
2	15	6114	7531	0.8118443765768159
2	16	6172	7550	0.8174834437086093
2	17	5943	7561	0.7860071419124455
2	18	5948	7564	0.7863564251718668
2	19	5999	7553	0.794253938832252
2	20	5842	7549	0.773877334746324
2	21	5688	7536	0.7547770700636943
2	22	5481	7535	0.7274054412740544
2	23	5203	7527	0.6912448518666136

b. 统计每小时载客出租车的平均车速

统计每小时正在载客的出租车的平均车速。
hive -e \
"select day(Time) as Day, hour(Time) as Hour, 
avg(Speed) as speed
from bigdata_cases.gps
where PassengerState=1
group by day(Time), hour(Time)
order by day, hour;" \


2.csv

得到结果：

Day	Hour	Speed
1	0	26.40957333997252
1	1	26.484185465929794
1	2	25.639321183088185
1	3	25.07481635824269
1	4	23.407378384509105
1	5	25.52963627342468
1	6	29.044904348241705
1	7	24.384438390355054
1	8	18.168545668750205
1	9	18.274947503908844
1	10	19.536583655156143
1	11	20.64434407258455
1	12	21.36393650253309
1	13	23.031837592073078
1	14	21.183105455791274
1	15	19.92800990942307
1	16	19.278142206512133
1	17	19.409214001377936
1	18	17.76479025717124
1	19	20.073146396873305
1	20	22.744269452020934
1	21	23.00955957379188
1	22	23.94621109754561
1	23	25.748275862068965
2	0	13.095555555555556
2	1	11.86861313868613
2	2	21.301467562748595
2	3	19.34041468388851
2	4	25.451918616210484
2	5	26.88686302010285
2	7	17.94664964407304
2	8	18.322770974793492
2	9	20.82934053237346
2	10	21.53841368936541
2	11	22.018161086650508
2	12	22.64793409392082
2	13	23.1530217969407
2	14	21.25080310758536
2	15	20.629242391066345
2	16	20.087993670711402
2	17	18.5745157655044
2	18	15.634892457581637
2	19	18.011473668637095
2	20	21.97895318156716
2	21	22.934715533298963
2	22	22.65631225433132
2	23	23.96024022264538

c. 统计每小时载客出租车的利用率

统计每小时所有出租车载客的总时间和行驶的总时间，并计算出载客时间的比例，即为利用率。
hive -e \
"select day(a.Time) as Day, hour(a.Time) as Hour,
sum(case when a.PassengerState=1 then unix_timestamp(a.PostTime) - unix_timestamp(a.Time) else 0 end) as Passenger,
sum(unix_timestamp(a.PostTime) - unix_timestamp(a.Time)) as Total,
sum(case when a.PassengerState=1 then unix_timestamp(a.PostTime) - unix_timestamp(a.Time) else 0 end) / sum(unix_timestamp(a.PostTime) - unix_timestamp(a.Time)) as PassengerRatio
from (
select VehicleId, Time, PassengerState, 
lead(Time) over (partition by VehicleId order by Time) as PostTime
from bigdata_cases.gps
) a
group by day(a.Time), hour(a.Time)
order by Day, Hour;" \


3.csv

得到结果：

Day	Hour	Passenger	Total	PassengerRatio
1	0	7095657	26718724	0.265568707547561
1	1	6064334	27223971	0.22275714296051813
1	2	4804754	26990898	0.17801386230276592
1	3	3741590	27248583	0.13731319533202882
1	4	1409216	27171164	0.051864395651213174
1	5	1529474	26881007	0.05689794284864402
1	6	2491744	27014891	0.09223594498308359
1	7	5491842	27089971	0.2027260198986555
1	8	11465755	27143530	0.4224120812584067
1	9	15124532	27521886	0.5495456234358358
1	10	14689703	27472348	0.53470868234488
1	11	14310622	27422351	0.5218597778140904
1	12	13357718	28312497	0.4717958292410592
1	13	11311445	27270035	0.41479393040749674
1	14	13089708	27595631	0.4743398692350974
1	15	14068412	27634367	0.5090911617407412
1	16	14751893	27394349	0.5385013164576388
1	17	13556173	27415544	0.49447032676061436
1	18	13071200	27576153	0.4740037524450927
1	19	12485630	27933084	0.44698358405394834
1	20	11268531	27776822	0.4056810746744174
1	21	12256920	27547401	0.4449392521639337
1	22	84651975	208940995	0.40514775475248405
1	23	8388	41022	0.20447564721369021
2	0	12702	35978	0.3530490855522819
2	1	12478	39939	0.3124264503367636
2	2	1115170	9534887	0.11695681343680318
2	3	424528	7926314	0.05355932152069676
2	4	875639	18153132	0.04823624925990733
2	5	949100	17638080	0.05380971171465375
2	7	3919390	9547005	0.4105360791159112
2	8	14705378	28521412	0.5155908129653609
2	9	14001764	26725269	0.5239148013814192
2	10	13872016	27304917	0.508040951012596
2	11	12864680	27323762	0.47082389313740913
2	12	12156640	27130169	0.4480856717110756
2	13	11046548	27102516	0.4075838567902699
2	14	12666754	27173682	0.4661405105130766
2	15	13725740	27160821	0.5053507035004575
2	16	14459637	27551027	0.5248311433181783
2	17	12361111	26985433	0.45806606104856645
2	18	13820382	27239384	0.5073676409128782
2	19	14369189	27257101	0.5271723137394545
2	20	12115073	27100794	0.4470375665008191
2	21	12152858	27112889	0.4482317616540237
2	22	12834472	27062913	0.47424576947795677
2	23	10539314	26607742	0.3960995262206015

d. 得到各出租车在3时、8时和13时的瞬时载客情况

得到各出租车在3时、8时和13时第一次同步数据时的载客情况，为之后做数据可视化做准备。
hive -e \
"select a.VehicleId, a.Longitude, a.Latitude, a.PassengerState
from (
select VehicleId, Longitude, Latitude, PassengerState,
row_number() over (partition by VehicleId order by Time) as Rank
from bigdata_cases.gps
where day(Time)=1 and hour(Time)=3
) a
where a.Rank=1;" \


4-1.csv

hive -e \
"select a.VehicleId, a.Longitude, a.Latitude, a.PassengerState
from (
select VehicleId, Longitude, Latitude, PassengerState,
row_number() over (partition by VehicleId order by Time) as Rank
from bigdata_cases.gps
where day(Time)=1 and hour(Time)=8
) a
where a.Rank=1;" \


4-2.csv

hive -e \
"select a.VehicleId, a.Longitude, a.Latitude, a.PassengerState
from (
select VehicleId, Longitude, Latitude, PassengerState,
row_number() over (partition by VehicleId order by Time) as Rank
from bigdata_cases.gps
where day(Time)=1 and hour(Time)=13
) a
where a.Rank=1;" \


4-3.csv

e. 得到各载客出租车在3时、8时和13时的瞬时速度

得到各载客出租车在3时、8时和13时第一次同步数据时的瞬时速度，为之后做数据可视化做准备。

hive -e \
"select a.VehicleId, a.Longitude, a.Latitude, a.Speed
from (
select VehicleId, Longitude, Latitude, Speed,
row_number() over (partition by VehicleId order by Time) as Rank
from bigdata_cases.gps
where day(Time)=1 and hour(Time)=3 and PassengerState=1
) a
where a.Rank=1;" \


5-1.csv

hive -e \
"select a.VehicleId, a.Longitude, a.Latitude, a.Speed
from (
select VehicleId, Longitude, Latitude, Speed,
row_number() over (partition by VehicleId order by Time) as Rank
from bigdata_cases.gps
where day(Time)=1 and hour(Time)=8 and PassengerState=1
) a
where a.Rank=1;" \
> 5-2.csv

hive -e \
"select a.VehicleId, a.Longitude, a.Latitude, a.Speed
from (
select VehicleId, Longitude, Latitude, Speed,
row_number() over (partition by VehicleId order by Time) as Rank
from bigdata_cases.gps
where day(Time)=1 and hour(Time)=13 and PassengerState=1
) a
where a.Rank=1;" \


5-3.csv


用R语言做数据可视化

a. 载入相关程序包

载入相关程序包。将Hive输出的结果文件复制到R语言可访问的路径如“D:\workspace\”。

执行以下R语言代码：
> library(ggplot2)
> library(ggmap)
> library(dplyr)

Attaching package: 'dplyr'

The following objects are masked from 'package:stats':

    filter, lag

The following objects are masked from 'package:base':

intersect, setdiff, setequal, union

其中，由于程序包ggmap所用的是谷歌的地图服务，需要修改程序包中源代码“get_googlemap.R”和“geocode.R”中谷歌API的地址，将URL从“http://maps.googleapis.com/maps/api/”替换为“http://maps.google.cn/maps/api/”，并重新用Rtools编译打包。本实验系统中的RStudio已完成修改，不需要再做替换。
b. 可视化每小时出租车的平均速度和利用率

画出每小时出租车的平均速度和利用率的曲线图，其中横坐标表示小时，纵坐标表示出租车利用率，颜色表示日期，线条粗细表示平均速度。
> data2 <- read.table("D:/workspace/2.csv", sep = "\t")
> data3 <- read.table("D:/workspace/3.csv", sep = "\t")
> names(data2) <- c("Day", "Hour", "Speed")
> names(data3) <- c("Day", "Hour", "Passenger", "Total", "PassengerRatio")
> data23 <- cbind(data2, data3[5])
> data23$Day <- as.factor(data23$Day)
> ggplot(data23, aes(x = Hour, y = PassengerRatio, group = Day)) + geom_line(aes(colour = Day, 
+     size = Speed))

b. 画出3时、8时和13时出租车载客情况的地理信息图

画出3时、8时和13时出租车载客情况的地理信息图，其中颜色表示载客情况，红色为空车，蓝色为载客。需要对经度做+0.006的校正，对维度做−0.003的校正。
> data4.1 <- read.table("D:/workspace/4-1.csv", sep = "\t")
> names(data4.1) <- c("VehicleId", "Longitude", "Latitude", "PassengerState")
> data4.1$PassengerState <- as.factor(data4.1$PassengerState)
> ggmap(get_googlemap(center = "Nanjing", zoom = 12, maptype = "roadmap"), extent = "device") + 
+     geom_point(data = data4.1, aes(x = Longitude + 0.006, y = Latitude - 0.003, 
+         colour = PassengerState), alpha = 0.5, size = 2) + labs(title = "2010/9/1 3:00")
Map from URL : http://maps.google.cn/maps/api/staticmap?center=Nanjing&zoom=12&size=640x640&maptype=roadmap&sensor=false
Information from URL : http://maps.google.cn/maps/api/geocode/json?address=Nanjing&sensor=false
Warning: Removed 1051 rows containing missing values (geom_point).


> data4.2 <- read.table("D:/workspace/4-2.csv", sep = "\t")
> names(data4.2) <- c("VehicleId", "Longitude", "Latitude", "PassengerState")
> data4.2$PassengerState <- as.factor(data4.2$PassengerState)
> ggmap(get_googlemap(center = "Nanjing", zoom = 12, maptype = "roadmap"), extent = "device") + 
+     geom_point(data = data4.2, aes(x = Longitude + 0.006, y = Latitude - 0.003, 
+         colour = PassengerState), alpha = 0.5, size = 2) + labs(title = "2010/9/1 8:00")
Map from URL : http://maps.google.cn/maps/api/staticmap?center=Nanjing&zoom=12&size=640x640&maptype=roadmap&sensor=false
Warning: Removed 1078 rows containing missing values (geom_point).


> data4.3 <- read.table("D:/workspace/4-3.csv", sep = "\t")
> names(data4.3) <- c("VehicleId", "Longitude", "Latitude", "PassengerState")
> data4.3$PassengerState <- as.factor(data4.3$PassengerState)
> ggmap(get_googlemap(center = "Nanjing", zoom = 12, maptype = "roadmap"), extent = "device") + 
+     geom_point(data = data4.3, aes(x = Longitude + 0.006, y = Latitude - 0.003, 
+         colour = PassengerState), alpha = 0.5, size = 2) + labs(title = "2010/9/1 13:00")
Map from URL : http://maps.google.cn/maps/api/staticmap?center=Nanjing&zoom=12&size=640x640&maptype=roadmap&sensor=false
Warning: Removed 888 rows containing missing values (geom_point).

c. 画出3时、8时和13时载客出租车瞬时速度的地理信息图

画出3时、8时和13时载客出租车瞬时速度的地理信息图，其中颜色表示瞬时速度，越接近红色速度越慢，越接近绿色速度越快。仅考虑车速小于等于80码且大于0的样本。需要对经度做+0.006的校正，对维度做−0.003的校正。
> data5.1 <- read.table("D:/workspace/5-1.csv", sep = "\t")
> names(data5.1) <- c("VehicleId", "Longitude", "Latitude", "Speed")
> summary(data5.1)
   VehicleId           Longitude        Latitude         Speed       
 Min.   :8.064e+11   Min.   :  0.0   Min.   : 0.00   Min.   :  0.00  
 1st Qu.:8.066e+11   1st Qu.:118.8   1st Qu.:32.03   1st Qu.:  0.00  
 Median :8.068e+11   Median :118.8   Median :32.05   Median :  7.00  
 Mean   :8.067e+11   Mean   :117.8   Mean   :31.78   Mean   : 16.98  
 3rd Qu.:8.068e+11   3rd Qu.:118.8   3rd Qu.:32.08   3rd Qu.: 27.00  
 Max.   :8.088e+11   Max.   :119.9   Max.   :38.86   Max.   :196.00  
> data5.1 <- filter(data5.1, Speed > 0 & Speed <= 80)
> ggmap(get_googlemap(center = "Nanjing", zoom = 12, maptype = "roadmap"), extent = "device") + 
+     geom_point(data = data5.1, aes(x = Longitude + 0.006, y = Latitude - 0.003, 
+         colour = Speed), alpha = 0.5, size = 2) + scale_colour_gradient(limits = c(0, 
+     80), trans = "sqrt", low = "red", high = "green") + labs(title = "2010/9/1 3:00")
Map from URL : http://maps.google.cn/maps/api/staticmap?center=Nanjing&zoom=12&size=640x640&maptype=roadmap&sensor=false
Warning: Removed 78 rows containing missing values (geom_point).


> data5.2 <- read.table("D:/workspace/5-2.csv", sep = "\t")
> names(data5.2) <- c("VehicleId", "Longitude", "Latitude", "Speed")
> data5.2 <- filter(data5.2, Speed > 0 & Speed <= 80)
> ggmap(get_googlemap(center = "Nanjing", zoom = 12, maptype = "roadmap"), extent = "device") + 
+     geom_point(data = data5.2, aes(x = Longitude + 0.006, y = Latitude - 0.003, 
+         colour = Speed), alpha = 0.5, size = 2) + scale_colour_gradient(limits = c(0, 
+     80), trans = "sqrt", low = "red", high = "green") + labs(title = "2010/9/1 8:00")
Map from URL : http://maps.google.cn/maps/api/staticmap?center=Nanjing&zoom=12&size=640x640&maptype=roadmap&sensor=false
Warning: Removed 345 rows containing missing values (geom_point).


> data5.3 <- read.table("D:/workspace/5-3.csv", sep = "\t")
> names(data5.3) <- c("VehicleId", "Longitude", "Latitude", "Speed")
> data5.3 <- filter(data5.3, Speed > 0 & Speed <= 80)
> ggmap(get_googlemap(center = "Nanjing", zoom = 12, maptype = "roadmap"), extent = "device") + 
+     geom_point(data = data5.3, aes(x = Longitude + 0.006, y = Latitude - 0.003, 
+         colour = Speed), alpha = 0.5, size = 2) + scale_colour_gradient(limits = c(0, 
+     80), trans = "sqrt", low = "red", high = "green") + labs(title = "2010/9/1 13:00")
Map from URL : http://maps.google.cn/maps/api/staticmap?center=Nanjing&zoom=12&size=640x640&maptype=roadmap&sensor=false
Warning: Removed 280 rows containing missing values (geom_point).

前言
飘零定位平台于 2019/02/07 正式上线第一代（地址：https://blog.csdn.net/piaolin_ying/article/details/86772610），上线后反响良好，每日都有新增用户，但由当时技术水平限制，后续运营难以接续以及平台扩展性还有待提升等问题，平台处于废弃状态。
因此本人于 2021/01/01 将平台从架构设计到技术栈进行了全新的重构，新平台使用了全新的架构模式，极大提高了平台扩展性，同时对用户界面进行了优化，与定位模块一同上线，感谢若依提供了框架工具。
更新内容
平台

全新UI体验，优化人机交互
取消邮箱验证，使用验证码注册，减少注册成本
密码严格加密保护，防止隐私泄露
全站使用HTTPS

定位模块

每个用户可同时拥有多个配置
使用HTTPS，极大加高成功概率
一键复制，一键预览
使用HTML5原生定位模块，兼容性高，需要用户确认

访问链接
https://47.106.39.62/（域名备案中，暂用IP访问）
功能浏览
平台浏览

平台首页


定位模块浏览

定位模块配置



定位模块



位置预览


使用方法
平台注册登录


定位模块

新增定位模块配置，用户可自定义网站标题与内容，为定位网页的标题与内容。




复制链接发送给目标，目标打开后会看到用户配置的信息，同时获取目标经纬度信息，精准度与目标手机或电脑中的位置信息模块有关，与平台无关，在此我使用电脑使用演示。




预览位置信息



联系方式


博客：https://www.cnblogs.com/pianling


CSDN：https://blog.csdn.net/piaolin_ying


QQ：1934707825


邮箱：pianlin_ying@163.com


更多模块
更多模块正在开发中，敬请期待。。。

在微信朋友圈上传图片时，点击位置可以自动识别照片拍摄的地理位置
过去我们发送朋友圈时，可以显示自己所在的位置信息，而现在自动读取照片拍摄位置让不少人联想到了图像识别技术。事实上，微信所做的并没有这么复杂，有业内人士告诉新功能是基于图片位置信息（即Exif的GPS定位信息）实现的。

什么是Exif？
Exif（Exchangeable Image File）是一种图像文件格式，它最早是由日本电子和信息技术协会（JEITA）为相机相机厂商制定一套标准。
现在，任何一部手机拍出的照片都有一组Exif参数，Exif除了有像素信息之外，还包含了光圈、快门、白平衡、ISO、焦距、日期时间等各种图像信息以及相机品牌、型号、色彩编码、拍摄时录制的声音以及GPS全球定位系统数据。
理论上，只要不经过特殊处理，每张图片的Exif信息会一直保留。
微信做了什么？
微信这次的新功能很有可能就是调用了Exif中的GPS全球定位系统数据，这些数据都来自于手机内部的传感器以及陀螺仪。
雷锋网在某Exif查看器上对手机拍摄的照片进行了试验，结果显示只要图片未被压缩，日期时间、拍照设备型号、像素以及光圈快门等信息都能记录下来，而位置信息则需要用户打开GPS才能展现出来。


如上图所示，Exif涵盖的位置信息有经度、纬度以及海拔，而这组数据对应的刚好就是小编拍摄照片时的位置。可以确定的是，微信的新功能就解析了这组数据。

看到这里你应该就知道微信是如何精准识别用户上传图片的拍摄位置了，是不是比大家想象的要简单呢？