Oracle Developers
Published in

Oracle Developers

Detect Conflicts of Interest using Graphs

Photo by Sasun Bughdaryan on Unsplash
CREATE PROPERTY GRAPH graph1
VERTEX TABLES (
coi_employee AS employee
KEY (employee_id)
PROPERTIES ARE ALL COLUMNS EXCEPT (postal)
, coi_form_master AS form_master
KEY (form_id)
, coi_supplier AS supplier
KEY (company_id)
, coi_purchase AS purchase
KEY (invoice_no)
)
EDGE TABLES (
coi_form_interest_with_key
KEY (id)
SOURCE KEY(form_id) REFERENCES form_master
DESTINATION KEY(company_id) REFERENCES supplier
LABEL has_interest
, coi_purchase AS approved_by
KEY (invoice_no)
SOURCE KEY (invoice_no) REFERENCES purchase
DESTINATION KEY (approver_employee_id) REFERENCES employee
, coi_purchase AS supplied_by
KEY (invoice_no)
SOURCE KEY (invoice_no) REFERENCES purchase
DESTINATION KEY (company_id) REFERENCES supplier
, coi_form_master AS declared_by
KEY (form_id)
SOURCE KEY (form_id) REFERENCES form_master
DESTINATION KEY (employee_id) REFERENCES employee
)
%python-pgx

statement = """
CREATE PROPERTY GRAPH coi_graph1
VERTEX TABLES (
...
)
EDGE TABLES (
...
)
"""
session.prepare_pgql(statement).execute()
%python-pgx

graph1 = session.get_graph("COI_GRAPH1")
graph1

PgxGraph(name: COI_GRAPH, v: 3800, e: 4759, directed: True, memory(Mb): 3)
SELECT *
FROM MATCH (s1)<-[r1:has_interest]-(d)-[r2:declared_by]->(e)<-[r3:approved_by]-(p)-[r4:supplied_by]->(s2) ON coi_graph1
LIMIT 1
SELECT *
FROM MATCH (
(s1)<-[r1:has_interest]-(d)-[r2:declared_by]->(e),
(e)<-[r3:approved_by]-(p)-[r4:supplied_by]->(s2)
) ON coi_graph1
LIMIT 1s
SELECT *
FROM MATCH (
(s1)<-[r1:has_interest]-(d)-[r2:declared_by]->(e),
(e)<-[r3:approved_by]-(p)-[r4:supplied_by]->(s2)
) ON coi_graph1
WHERE e.first_name = '${First name}' AND e.last_name = '${Last name}'
SELECT *
FROM MATCH (
(s1)<-[r1:has_interest]-(d)-[r2:declared_by]->(e),
(e)<-[r3:approved_by]-(p)-[r4:supplied_by]->(s2)
) ON coi_graph1
LIMIT 3
CREATE VIEW coi_same_number_1 AS
SELECT ROWNUM AS id, e1.employee_id AS e1_id, e2.employee_id AS e2_id
FROM coi_employee e1, coi_employee e2
WHERE e1.EMERGENCY_CONTACT_PHONE = e2.phone;
CREATE VIEW coi_same_number_2 AS
SELECT ROWNUM AS id, e.employee_id, s.company_no
FROM coi_employee e, coi_supplier s
WHERE e.EMERGENCY_CONTACT_PHONE = s.phone;
CREATE PROPERTY GRAPH graph2
VERTEX TABLES (
...
)
EDGE TABLES (
...
, coi_emergency_contact_1
KEY (id)
SOURCE KEY(employee_id_1) REFERENCES employee
DESTINATION KEY(employee_id_2) REFERENCES employee
LABEL emergency_contact
NO PROPERTIES
, coi_emergency_contact_2
KEY (id)
SOURCE KEY(employee_id) REFERENCES employee
DESTINATION KEY(company_id) REFERENCES supplier
LABEL emergency_contact
NO PROPERTIES
)
CREATE TABLE coi_employee_geom AS
SELECT
t.*,
SDO_GEOMETRY(
2001, -- Geometry type (two-dementional point)
4326, -- Coordinate system ID (SRID)
SDO_POINT_TYPE(t.address_lon, t.address_lat, NULL), NULL, NULL
) AS address_geom
FROM coi_employee t;

INSERT INTO user_sdo_geom_metadata VALUES (
'coi_employee_geom',
'address_geom',
SDO_DIM_ARRAY(
SDO_DIM_ELEMENT('LONGITUDE', -180.0, 180.0, 0.05),
SDO_DIM_ELEMENT('LATITUDE', -90.0, 90.0, 0.05)
),
4326
);
COMMIT;

CREATE INDEX idx_coi_employee_geom
ON coi_employee_geom (address_geom)
INDEXTYPE IS mdsys.spatial_index_v2;
CREATE VIEW coi_within_500m AS
SELECT
ROWNUM AS id,
e.employee_id,
s.company_id,
SDO_GEOM.SDO_DISTANCE(e.address_geom, s.address_geom, 0.005, 'unit=M') AS dist
FROM
coi_employee_geom e,
coi_supplier_geom s
WHERE
SDO_GEOM.SDO_DISTANCE(e.address_geom, s.address_geom, 0.005, 'unit=M') < 500;
CREATE PROPERTY GRAPH graph2
VERTEX TABLES (
...
)
EDGE TABLES (
...
, coi_within_500m
KEY (id)
SOURCE KEY(employee_id) REFERENCES employee
DESTINATION KEY(company_id) REFERENCES supplier
LABEL within_500m
)
SELECT a1, b, a2
FROM MATCH TOP 1 SHORTEST (e1) (-[r]-(v))+ (e2) ONE ROW PER STEP (a1, b, a2) ON graph2
WHERE e1.employee_id = '${Employee 1}' AND e2.employee_id = '${Employee 2}'
AND COUNT(v) = COUNT(DISTINCT v)
  • The pattern (-[r]-(v))+ traces one or more edges in both directions.
  • ONE ROW PER STEP (a1, b, a2) decomposes the detected path into steps and maps them to the variables a1, b, a2.
  • The condition COUNT(v) = COUNT(DISTINCT v) is added to eliminate paths that walk through the same vertices multiple times.
SELECT a1, b, a2
FROM MATCH TOP 50 SHORTEST (e1) (-[r]-(v))+ (e2) ONE ROW PER STEP (a1, b, a2) ON graph2
WHERE e1.first_name = '${First name}' AND e1.last_name = '${Last name}'
AND e1 = e2
AND COUNT(v) = COUNT(DISTINCT v)
AND COUNT(r) = COUNT(DISTINCT r)

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Ryota Yamanaka

Product Manager of Oracle Spatial and Oracle Graph. The views expressed here are my own. The scripts in my articles are not supported by Oracle. tw @oraryotas