甲骨文数据库上的全球定位系统仿真


如果你曾经开发过与全球定位系统相关的软件,你可能会遇到一些困难,特别是在生产之前。即使在上线后,由于隐私的原因,从设备中获取数据仍有可能出现问题,或者可能会出现一些损坏数据的问题。没有真实的数据,开发特性真的很难。

你可能试图伪造一些坐标,但是在后台嘲笑移动的车辆会很痛苦。另外,如果你和你的团队一起开发,你的同事不能从另一个网络访问它。每个人都应该有一个中间立场,那就是数据库!如果你没有一个生产数据库,有一些免费的数据库可以用于这种用途。只要把一些虚拟的数据放在一起就可以了。

如果您的软件将数据从设备放入数据库,下面的解决方案是模拟它的最佳方法之一。在这个解决方案中,您可以访问数据库,这模拟了数据库中的一切。你可以给出你的车辆所在的每个位置,并在这个系列的位置上模拟它,我们称之为路线。简而言之,这个模拟得到车辆的位置,检查它的索引,并根据车辆行驶的路线更新坐标。下图代表您可以创建的路线。蓝色和红色路线得6分,绿色路线得4分。这些点意味着您的车辆将被更新6次,并且将根据车辆的方向进行更新。你可以从任何地图应用程序获得坐标。我用谷歌地图获取坐标。我们将把它们插入一张桌子。我们开始吧。

我们需要两个表:一个用来记录你的车辆将要行驶的路线,另一个用来跟踪和模拟车辆。路由表将有其唯一的id、保持坐标顺序的索引号以及x和y值,即经度和纬度。车辆表将有其唯一的id、routeid、它在哪个路线上、前进或后退的方向、当前在路线上的索引数和坐标。地图上的坐标(你可以随机选择坐标,但感觉不像是模拟的)可以插入这里。您必须至少有一条路线进行模拟。

CREATE TABLE VEHICLES (
    id NUMBER, /*You can use auto incerement in 12c+ => GENERATED ALWAYS AS IDENTITY */
    direction NUMBER(1), /*Will it start at beginning (1) or ending (0) of route*/
    indexNo NUMBER, /* Default 0 if direction 1 else max indexno of route*/
    coordx NUMBER, /* langitude */
    coordy NUMBER, /* latitude */
    routeid NUMBER
);

CREATE SEQUENCE VEHICLE_ID
MINVALUE 1 
MAXVALUE 9999
START WITH 1
INCREMENT BY 1;



CREATE TABLE ROUTES(
id NUMBER,
indx NUMBER,
coordx NUMBER,
coordy NUMBER
);


INSERT INTO routes values(1,0,41.005921, 29.023677);
INSERT INTO routes values(1,1,41.004524, 29.014066);
INSERT INTO routes values(1,2,41.005776, 28.999593);
INSERT INTO routes values(1,3,40.003424, 28.988191);
INSERT INTO routes values(1,4,40.001513, 28.977231);
INSERT INTO routes values(1,5,40.002791, 28.966076);


INSERT INTO routes values(2,0,39.065495, 29.013043);
INSERT INTO routes values(2,1,40.054839, 30.023617);
INSERT INTO routes values(2,2,41.047830, 31.031840);
INSERT INTO routes values(2,3,42.041142, 31.038978);
INSERT INTO routes values(2,4,42.030413, 31.045459);
COMMIT;

SELECT * FROM routes ORDER BY 1,2;

运行上述代码块后,我们有了初始数据,并准备好采取行动!接下来,我们需要从查看路线表开始。为此,运行下面的代码块。

CREATE OR REPLACE PROCEDURE START_NEW_VEHICLE(routeID IN NUMBER, DIRECTION IN NUMBER DEFAULT 1) 
IS
v_MAXINDEX number;
v_COORDX NUMBER;
v_COORDY NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('This procedure will create new vehicle on route : ' || routeID || ' and ' || ' direction : ' || DIRECTION);

select max(INDX) into v_MAXINDEX from routes where id = routeID;

SELECT coordx, coordy INTO v_coordx, v_coordy FROM routes WHERE id = routeid AND indx = CASE WHEN direction = 1 THEN 0 ELSE v_maxindex END;

IF DIRECTION = 1 THEN
    INSERT INTO VEHICLES (id,direction,indexno,COORDX,COORDy,ROUTEID) values(VEHICLE_ID.nextVal, DIRECTION,0, v_coordx,v_coordy, ROUTEID );
ELSE
    INSERT INTO VEHICLES (id,direction,indexno,COORDX,COORDy,ROUTEID) values(VEHICLE_ID.nextVal, DIRECTION,v_MAXINDEX, v_coordx,v_coordy, ROUTEID );
    END IF;

    COMMIT;
END START_NEW_VEHICLE;
/


CALL START_NEW_VEHICLE(
 routeID => 1,
 direction => 1);

SELECT * FROM vehicles;

该过程需要两个参数。第一个,routeid,是车辆将要行驶的路线,第二个是车辆将要出发的地方。开始还是结束?默认情况下,车辆将从值1开始(0表示结束)。如果你调用这个过程,它会查看带有给定routeid的路由表,获取它的第一个索引的坐标,然后用这个信息在车辆表中插入一个新的记录。接下来,我们需要进行模拟的过程。

CREATE OR REPLACE PROCEDURE SIMULATE_VEHICLES
IS
v_MAXINDEX number;
v_COORDX NUMBER;
v_COORDY NUMBER;
v_indexno NUMBER;
CURSOR c_vehicles IS
SELECT * FROM vehicles;
BEGIN

FOR vehicle IN c_vehicles /* For every vehicle has started, update its location, if there is, to next one */
LOOP
select max(INDX) into v_MAXINDEX from routes where id = vehicle.routeID;

IF (V_MAXINDEX = VEHICLE.INDEXNO AND VEHICLE.DIRECTION = 1) OR ( 0 = VEHICLE.INDEXNO AND VEHICLE.DIRECTION = 0) /* This means vehicle ended its route */
THEN

UPDATE VEHICLES 
SET indexno = case when vehicle.direction = 0 then 0 else v_maxindex end,
direction = case when  vehicle.direction = 0 then 1 else 0 end
where id = vehicle.id; -- this will keep vehicle in loop going start to finish and finish to start

/*DELETE FROM VEHICLES WHERE ID = VEHICLE.ID;  -- instead of updating record you can delete and insert again. But this will increase ID 
START_NEW_VEHICLE(VEHICLE.ROUTEID, VEHICLE.DIRECTION);*/

COMMIT;
CONTINUE; /* */
END IF;

SELECT coordx, coordy, CASE WHEN  vehicle.direction = 1 THEN VEHICLE.INDEXNO+1 ELSE VEHICLE.INDEXNO-1 eND  INTO v_coordx, v_coordy, v_indexno FROM routes 
WHERE id = vehicle.routeid AND indx = CASE WHEN vehicle.direction = 1 THEN VEHICLE.INDEXNO+1 ELSE VEHICLE.INDEXNO-1 END;

UPDATE VEHICLES SET coordx = v_coordx, coordy = v_coordy, indexno = v_indexno WHERE id = VEHICLE.id;
COMMIT;
END LOOP;

END SIMULATE_VEHICLES;
/

上述过程计算每辆车的下一个坐标。它循环通过车辆,检查是否有该方向的下一个位置,如果有,它将车辆更新到该位置。已经完成的每个控制都是通过路由表完成的。每次这个程序运行,车辆向前移动。但是如何随着时间的推移而运行呢?

我们可以在任何地方启动这个程序,比如后端,但是要小心。如果你同时从不同的来源打电话给他们,你会有一些不必要的行为。根据你的需要来安排可以在你调用这个程序的地方完成。但是,您可以使用甲骨文的内置调度程序来调度它。下面的代码块将安排此过程每25秒运行一次。


BEGIN

Dbms_scheduler.drop_job('SCH_SIMULATE_VEHICLES');

  DBMS_SCHEDULER.CREATE_JOB (
   job_name           =>  'SCH_SIMULATE_VEHICLES',
   job_type           =>  'PLSQL_BLOCK',
   job_action         =>  'BEGIN DDS_ETL.SIMULATE_VEHICLES(); END;',
   start_date         =>  sysdate,
   repeat_interval    =>  'FREQ=SECONDLY;INTERVAL=25',
   end_date           =>  NULL, /* You can give some date that this job will end.*/
   enabled            =>  TRUE,
   auto_drop          =>  FALSE /* Dont drop job after its completed */
   );

    --dbms_scheduler.disable('SCH_SIMULATE_VEHICLES'); -- to disable job
   commit;
END;
/

/* Check jobs status*/
select * from  DBA_SCHEDULER_JOB_RUN_DETAILS where job_name = 'SCH_SIMULATE_VEHICLES' order by log_date desc;

select * from  vehicles;

就这样!现在,您的数据库中已经运行了模拟。只要设置新的路线,开始新的旅程。希望这篇文章有所帮助!


额外:

如果您只想模拟一辆车,可以使用以下程序:

CREATE OR REPLACE PROCEDURE SIMULATE_VEHICLE(p_vehicle_id NUMBER)
IS
v_MAXINDEX number;
v_COORDX NUMBER;
v_COORDY NUMBER;
v_indexno NUMBER;
rec_vehicle vehicles%rowtype;

BEGIN
        SELECT * INTO rec_vehicle from vehicles where id = p_vehicle_id;

select max(INDX) into v_MAXINDEX from routes where id = rec_vehicle.routeID;

IF (V_MAXINDEX = rec_vehicle.INDEXNO AND rec_vehicle.DIRECTION = 1) OR ( 0 = rec_vehicle.INDEXNO AND rec_vehicle.DIRECTION = 0) /* This means vehicle ended its route */
THEN
RETURN; /* Then do nothing for this vehicle record. This record could be removed or restarted here*/
END IF;

SELECT coordx, coordy, CASE WHEN  rec_vehicle.direction = 1 THEN rec_vehicle.INDEXNO+1 ELSE rec_vehicle.INDEXNO-1 eND  INTO v_coordx, v_coordy, v_indexno FROM routes 
WHERE id = rec_vehicle.routeid AND indx = CASE WHEN rec_vehicle.direction = 1 THEN rec_vehicle.INDEXNO+1 ELSE rec_vehicle.INDEXNO-1 END;

UPDATE VEHICLES SET coordx = v_coordx, coordy = v_coordy, indexno = v_indexno WHERE id = rec_vehicle.id;

COMMIT;
END SIMULATE_VEHICLE;
/

cALL SIMULATE_VEHICLE(4);

select * from vehicles;