(Oracle database)

Given the coordinates schema we used in the examples, and the additional structures (index), write a PL/SQL procedure called knncloak which implements the two-rounds NN cloaking SKA technique. The procedure must receive as argument the ID of the querying user, and an integer K. The procedure must print on the screen the cloaking region (i.e., minimum bounding rectangle) obtained using the 2-round KNN.

(create.sql)

--- create table coordinates

CREATE TABLE coordinates

(

id NUMBER PRIMARY KEY,

point SDO_GEOMETRY

);

--- create a spatial index on coordinates.point column

--- create a 500x500 spatial grid, with x and y values ranging from 0 to 500

INSERT INTO user_sdo_geom_metadata

( TABLE_NAME

, COLUMN_NAME

, DIMINFO

, SRID)

VALUES

( 'coordinates'

, 'POINT'

, SDO_DIM_ARRAY(--- 500x500 grid

SDO_DIM_ELEMENT('X', 0, 500, 0.005),

SDO_DIM_ELEMENT('Y', 0, 500, 0.005)

)

, null -- SRID

);

CREATE INDEX coordinates_sidx ON coordinates(point) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

--- load the coordinate points (x,y) from the 30_x_y_coordinates.txt file into the table

INSERT INTO coordinates VALUES (

1, SDO_GEOMETRY(

2001 -- 2 dimensional point

, null -- SDO SRID

, SDO_POINT_TYPE(460,80,null)

, null

, null

)

);

INSERT INTO coordinates VALUES (

2, SDO_GEOMETRY(

2001 -- 2 dimensional point

, null -- SDO SRID

, SDO_POINT_TYPE(200,140,null)

, null

, null

)

);

INSERT INTO coordinates VALUES (

3, SDO_GEOMETRY(

2001 -- 2 dimensional point

, null -- SDO SRID

, SDO_POINT_TYPE(240,260,null)

, null

, null

)

);

INSERT INTO coordinates VALUES (

4, SDO_GEOMETRY(

2001 -- 2 dimensional point

, null -- SDO SRID

, SDO_POINT_TYPE(320,200,null)

, null

, null

)

);

INSERT INTO coordinates VALUES (

5, SDO_GEOMETRY(

2001 -- 2 dimensional point

, null -- SDO SRID

, SDO_POINT_TYPE(340,100,null)

, null

, null

)

);

INSERT INTO coordinates VALUES (

6, SDO_GEOMETRY(

2001 -- 2 dimensional point

, null -- SDO SRID

, SDO_POINT_TYPE(60,100,null)

, null

, null

)

);

INSERT INTO coordinates VALUES (

7, SDO_GEOMETRY(

2001 -- 2 dimensional point

, null -- SDO SRID

, SDO_POINT_TYPE(80,380,null)

, null

, null

)

);

INSERT INTO coordinates VALUES (

8, SDO_GEOMETRY(

2001 -- 2 dimensional point

, null -- SDO SRID

, SDO_POINT_TYPE(240,460,null)

, null

, null

)

);

INSERT INTO coordinates VALUES (

9, SDO_GEOMETRY(

2001 -- 2 dimensional point

, null -- SDO SRID

, SDO_POINT_TYPE(380,260,null)

, null

, null

)

);

INSERT INTO coordinates VALUES (

10, SDO_GEOMETRY(

2001 -- 2 dimensional point

, null -- SDO SRID

, SDO_POINT_TYPE(400,280,null)

, null

, null

)

);

INSERT INTO coordinates VALUES (

11, SDO_GEOMETRY(

2001 -- 2 dimensional point

, null -- SDO SRID

, SDO_POINT_TYPE(400,420,null)

, null

, null

)

);

INSERT INTO coordinates VALUES (

12, SDO_GEOMETRY(

2001 -- 2 dimensional point

, null -- SDO SRID

, SDO_POINT_TYPE(220,100,null)

, null

, null

)

);

INSERT INTO coordinates VALUES (

13, SDO_GEOMETRY(

2001 -- 2 dimensional point

, null -- SDO SRID

, SDO_POINT_TYPE(160,60,null)

, null

, null

)

);

INSERT INTO coordinates VALUES (

14, SDO_GEOMETRY(

2001 -- 2 dimensional point

, null -- SDO SRID

, SDO_POINT_TYPE(60,80,null)

, null

, null

)

);

INSERT INTO coordinates VALUES (

15, SDO_GEOMETRY(

2001 -- 2 dimensional point

, null -- SDO SRID

, SDO_POINT_TYPE(140,460,null)

, null

, null

)

);

INSERT INTO coordinates VALUES (

16, SDO_GEOMETRY(

2001 -- 2 dimensional point

, null -- SDO SRID

, SDO_POINT_TYPE(180,300,null)

, null

, null

)

);

INSERT INTO coordinates VALUES (

17, SDO_GEOMETRY(

2001 -- 2 dimensional point

, null -- SDO SRID

, SDO_POINT_TYPE(460,360,null)

, null

, null

)

);

INSERT INTO coordinates VALUES (

18, SDO_GEOMETRY(

2001 -- 2 dimensional point

, null -- SDO SRID

, SDO_POINT_TYPE(80,320,null)

, null

, null

)

);

INSERT INTO coordinates VALUES (

19, SDO_GEOMETRY(

2001 -- 2 dimensional point

, null -- SDO SRID

, SDO_POINT_TYPE(200,60,null)

, null

, null

)

);

INSERT INTO coordinates VALUES (

20, SDO_GEOMETRY(

2001 -- 2 dimensional point

, null -- SDO SRID

, SDO_POINT_TYPE(320,280,null)

, null

, null

)

);

);

********************************************

(q1.sql)

--- find the nearest 4 neighbors to point (240,460)

SELECT p.id, sdo_nn_distance(1) distance

FROM coordinates p

WHERE SDO_NN(p.point,

SDO_GEOMETRY(2001

, null

, SDO_POINT_TYPE(0,0,null)

, null

, null

),

'sdo_num_res=5',

1

)='TRUE'

;

--ORDER BY 2;

********************************************************

(q2.sql)

--- find the nearest 4 neighbors to the point with id=11

SELECT p.id, sdo_nn_distance(1) distance

FROM coordinates p, coordinates p1

WHERE p1.id=11 and SDO_NN(p.point,

p1.point,

'sdo_num_res=5',

1

)='TRUE'

ORDER BY 2;

******************************************

(q3.sql)

--- create a MBR using the indexes of all the returned geometries

SELECT sdo_aggr_mbr(point)

FROM coordinates c

WHERE c.id in (

(SELECT p.id

FROM coordinates p

WHERE SDO_NN(p.point,

SDO_GEOMETRY(2001

, null

, SDO_POINT_TYPE(240,460,null)

, null

, null

),

'sdo_num_res=5',

1

)='TRUE'

)

);