《oracle统计格网包含的gps车辆点位》由会员分享，可在线阅读，更多相关《oracle统计格网包含的gps车辆点位（6页珍藏版）》请在金锄头文库上搜索。
1、数据情况格网信息按照经纬度划分成正方形经度纬度开始结束开始结束以西南点划分111132.944300.87035569696725.19纬度0.00026995 0.00013497 经度0.00031016 0.00015508 开始经度开始纬度结束经度结束纬度宽高121.542229.8459121.632129.90169990.8525387.593333 180 333180格网数据示例如下:IDLONGIDLATID1028121.5435529.885291042121.5435529.8896331057121.5435529.8942851072121.5435529.898
2、9371087121.5438229.8477611102121.5438229.8524131116121.5438229.856756点位信息数据示例如下:CAR_NUMBERLONGIDLATIDBT0704121.59796929.889105BT0704121.59420829.888193BT0704121.58890529.886772BT0704121.58471729.881863查询每一类car有多少条gps数据：select car_number, count(car_number) as total from taxi_shape group by car_number
3、order by 2;查询优化添加并行计算：/*+parallel(t,5) parallel(t2,5) use_hash(t,t2) full(t) full(t2)*/第一节 使用SDO_GEOMETRY存储空间信息点位数据创建点位空间数据表create table taxishap as select * from taxi;taxishap表添加字段 shpae，类型为mdsys.sdo_geometry;根据xy更新shapeDECLAREBEGIN /*+parallel(t,5) */ - 并行 update taxishap t set shape = SDO_GEOMETR
4、Y(POINT(|t.longid| |t.latid|),4326) where car_number=BT0031;END;预估更新400万条记录 2小时根据用户表填写空间元数据INSERT INTO USER_SDO_GEOM_METADATAVALUES( taxishap,shape,MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT(x, 121.0, 122.0,0.005),MDSYS.SDO_DIM_ELEMENT(y, 29.0, 30.0,0.005),4326);建立空间索引CREATE INDEX SPATIALIDX_taxishap
5、ON taxishap (shape)INDEXTYPE IS MDSYS.SPATIAL_INDEX;格网数据根据格网中心点和格网间距更新格网数据1.添加字段 shpae，类型为mdsys.sdo_geometry;2.根据格网中心点更新格网：- POLYGON (5.0 1.0, 8.0 1.0, 8.0 6.0, 5.0 7.0, 5.0 1.0) - dy = 0.00031016 , dy/2 = 0.00015508 - dx = 0.00026995, dx/2 = 0.00013497DECLAREdx number;dy number;BEGIN dx := 0.000134
6、97; dy := 0.00015508; update position t set shape = SDO_GEOMETRY(POLYGON(|(t.longid-dx)| |(t.latid-dy)| ,|(t.longid+dx)| |(t.latid-dy)| ,|(t.longid+dx)| |(t.latid+dy)| ,|(t.longid-dx)| |(t.latid+dy)| ,|(t.longid-dx)| |(t.latid-dy)| ),4326);END;- 11g，198 秒- 10g，执行时间较长，分开Where id =10000 and id =20000
7、and id =30000 and id =40000 and id =50000 and id =60000 and id 70000根据用户表填写空间元数据INSERT INTO USER_SDO_GEOM_METADATAVALUES(position,shape,MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT(x, 121.0, 122.0,0.005),MDSYS.SDO_DIM_ELEMENT(y, 29.0, 30.0,0.005),4326);建立空间索引CREATE INDEX SPATIALIDX_position ON position
8、(shape) INDEXTYPE IS MDSYS.SPATIAL_INDEX;查询gps点位和格网的空间相机关系查询，查询结果保存到新表drop table taxi_result;create table taxi_result as/*+parallel(t,5) */select atable.car_number,atable.time, atable.velocity, atable.angle, atable.distance, btable.idfrom taxigrid atable , position btable where sdo_relate(atable.sha
9、pe, btable.shape,MASK=INSIDE ) = TRUE;- 40w条gps记录 123 秒统计格网内包含的gps点位个数select id, count(id) as total from taxi_result group by idorder by 2 desc;测试：100w条记录Gps表：taxispw格网表：position获取数据范围select min(longid) from taxispw; 121.542206select max(longid) from taxispw; 121.632095select min(latid) from taxispw
10、; 29.845901select max(latid) from taxispw ; 29.9016填写空间元数据INSERT INTO USER_SDO_GEOM_METADATAVALUES(taxispw,shape,MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT(x, 121.542206, 121.632095,0.0001),MDSYS.SDO_DIM_ELEMENT(y, 29.845901, 29.9016,0.0001),4326);创建索引CREATE INDEX SPATIALIDX_taxispwON taxispw (shape)
11、INDEXTYPE IS MDSYS.SPATIAL_INDEX;- 创建索引耗时108秒查询drop table taxispw_result;create table taxispw_result tablespace UBOSS_STS_WAREHOUSE as/*+parallel(t,5) */select atable.car_number,atable.time, atable.velocity, atable.angle, atable.distance, btable.idfrom taxispw atable , position btable where sdo_rela
12、te(atable.shape, btable.shape,MASK=INSIDE ) = TRUE;-耗时140.5秒测试2：7882915条记录查询要分析的记录条数：select count(*) from taxitest where to_char(time,dd) = 07获取数据范围select min(longid) from taxitest where to_char(time,dd) = 07; 121.542206select max(longid) from taxitest where to_char(time,dd) = 07; 121.632095 select min(latid) from taxitest where to_char(time,dd) = 07; 29.845901 select max(latid) from taxitest where to_char(time,dd) = 07; 29.9016 填写空间元数据INSERT INTO USER_SDO_GEOM_METADATAVALUES(taxitest,shape,MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT(x, 121.542206, 121.632095,0.0001),MDSYS
