Question 88841 in PostgreSQL Question posted in
The official documentation can be found here.

Is there a way to determine degree centrality of an apache age graph – Postgresql

Is there a way to determine the degree centrality of a graph made with apache age to determine the importance of the nodes? I need to analyze the influence of the nodes in the following graph:

CREATE TABLE people (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE connections (
    person_id INTEGER REFERENCES people(id),
    friend_id INTEGER REFERENCES people(id),
    PRIMARY KEY (person_id, friend_id)
);

and here is the sample data

-- People
INSERT INTO people (name) VALUES
('Alice'),
('Bob'),
('Charlie'),
('David'),
('Eve');

-- Connections
INSERT INTO connections (person_id, friend_id) VALUES
(1, 2),
(1, 3),
(2, 3),
(2, 4),
(3, 4),
(4, 5);

I need guidance about how to write cypher query to determine degree centrality and how to sort the nodes based on their influence.

Tags:

3

Answers


  1. 0

    First, to create this graph in Apache AGE, you can use the following cypher query:

    SELECT create_graph('graph');
SELECT * FROM cypher('graph', $$
    CREATE (:people {name: 'Alice'}), (:people {name: 'Bob'}), (:people {name: 'Charlie'}), (:people {name: 'David'}), (:people {name: 'Eve'})
$$) AS (n agtype);
SELECT * FROM cypher('graph', $$
    MATCH (a:people), (b:people)
    WHERE a.name = 'Alice' AND (b.name = 'Bob' OR b.name = 'Charlie')
    CREATE (a)-[e:friend]->(b)
    RETURN e
$$) AS (v agtype);
SELECT * FROM cypher('graph', $$
    MATCH (a:people), (b:people)
    WHERE a.name = 'Bob' AND (b.name = 'Charlie' OR b.name = 'David')
    CREATE (a)-[e:friend]->(b)
    RETURN e
$$) AS (v agtype);
SELECT * FROM cypher('graph', $$
    MATCH (a:people), (b:people)
    WHERE a.name = 'Charlie' AND b.name = 'David'
    CREATE (a)-[e:friend]->(b)
    RETURN e
$$) AS (v agtype);
SELECT * FROM cypher('graph', $$
    MATCH (a:people), (b:people)
    WHERE a.name = 'David' AND b.name = 'Eve'
    CREATE (a)-[e:friend]->(b)
    RETURN e
$$) AS (v agtype);

    To get the degree centrality of the graph (how many connections a node has) and order by it, you can use:

    SELECT * FROM cypher('graph', $$
    MATCH (v:people)-[r:friend]-(w:people)
    RETURN v.name, count(r)
    ORDER BY count(r) DESC
$$) AS (name agtype, influence agtype);
    Login or Signup to reply.
  2. 0

    The following query will give you the required degree centrality and the result will be sorted in descending order

    MATCH (p:people)-[r:connections]-()
RETURN p.name, degree(r) as centrality_det
ORDER BY centrality_det DESC
    Login or Signup to reply.
  3. 0

    You can determine the degree centrality of nodes and sort them based on their influence by following this approach:

    SELECT * FROM cypher('graph', $$
    MATCH (v:people)-[r:friend]-(w:people)
    WITH v.name AS name, count(r) AS friends
    ORDER BY count(r) DESC                                                                                                                              
    RETURN name, friends
$$) AS (name agtype, influence agtype);

    It will return:

       name    | influence
-----------+-----------
 "Bob"     | 3
 "Charlie" | 3
 "David"   | 3
 "Alice"   | 2
 "Eve"     | 1
(5 rows)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search