If you ever developed GPS-related software, you have probably faced some difficulty getting real GPS data, especially before production. Even after going live, there might still be problems with getting data from devices because of privacy or there could be some problems that corrupt the data. Without real data, it is really hard to develop features.
You might have tried to fake some coordinates, but mocking moving vehicles on the backend could be very painful. Also, if you are developing with your team, your colleagues can't access it from another network. There should be some middle ground for everyone, and that is a database! If you don't have a database on production, there are some free DBs available for this kind of usage. Just put some little dummy data on using them together.
If your software puts data to database from devices, the below solution is one of the best ways to simulate it. In this solution, you can access to the database and this simulates everything on the DB. You can give every location that your vehicle can be and simulate it on that series of locations, which we call routes. In a nutshell, this simulation gets the vehicle's position, checks its index, and updates coordinates from the route that vehicle is on. The below picture represents routes that you can create. Blue and red routes have 6 points and the green one has 4 points. These points mean that your vehicle will be updated 6 times and it will be updated by the vehicle's direction. You can get coordinates from any map application. I used Google Maps for getting coordinates. We will insert them into a table. Let's get started.
We will need 2 tables: one for keeping routes that your vehicles will move on, and one for tracking and simulating vehicles. The routes table will have its unique id, index number for keeping the order of coordinates, and x and y values, which are longitude and latitude. The vehicles table will have its unique id, routeid, which route it is on, direction for going backward or forward, number of indexes, which is currently on the route and coordinates. Coordinates from maps (you can just randomize the coordinates, but it won't feel like simulating) will can be inserted here. You have to have at least 1 route for simulation.
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;
After running the above code block, we have initial data and are ready for action! Next, we need to start vehicles from looking at the routes table. To do that, run the below code block.
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;
This procedure takes 2 parameters. The first one, routeid, is the route that the vehicle will be on, and the second one is where the vehicle will start at. Beginning or ending? By default, the vehicles will start at the beginning with value 1 (0 for ending). If you call this procedure, it will look at the routes table with given routeid, take its first index's coordinates, and insert a new record to the vehicles table with this info. Next, we need the procedure that the simulation goes on.
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; /
The above procedure calculates the next coordinates for every single vehicle. It loops through vehicles, checks if there is next location for that direction, and if there is, it updates the vehicle to that location. Every control that has been done is made through the routes table. Every time this procedure runs, vehicles move forwards. But how to run this with time?
We can trigger this procedure from anywhere we want, like backends, but be careful. If you call them from different sources at the same time, you will have some unwanted behaviors. Scheduling it with your needs can be done where you call this procedure. However, you can schedule it with Oracle's built-in scheduler. The below code block will schedule this procedure to run every 25 seconds.
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;
That's it! You now have the simulation running on your database. Just put new routes and start a new trip. Hope this article helps!
If you want to simulate just one vehicle, you can use the below procedure:
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;