def _export_review_skeleton(project_id=None, skeleton_id=None, format=None, subarbor_node_id=None): """ Returns a list of segments for the requested skeleton. Each segment contains information about the review status of this part of the skeleton. If a valid subarbor_node_id is given, only data for the sub-arbor is returned that starts at this node. """ # Get all treenodes of the requested skeleton treenodes = Treenode.objects.filter(skeleton_id=skeleton_id).values_list( 'id', 'parent_id', 'location_x', 'location_y', 'location_z') # Get all reviews for the requested skeleton reviews = get_treenodes_to_reviews_with_time(skeleton_ids=[skeleton_id]) # Add each treenode to a networkx graph and attach reviewer information to # it. g = nx.DiGraph() reviewed = set() for t in treenodes: # While at it, send the reviewer IDs, which is useful to iterate fwd # to the first unreviewed node in the segment. g.add_node(t[0], {'id': t[0], 'x': t[2], 'y': t[3], 'z': t[4], 'rids': reviews[t[0]]}) if reviews[t[0]]: reviewed.add(t[0]) if t[1]: # if parent g.add_edge(t[1], t[0]) # edge from parent to child else: root_id = t[0] if subarbor_node_id and subarbor_node_id != root_id: # Make sure the subarbor node ID (if any) is part of this skeleton if subarbor_node_id not in g: raise ValueError("Supplied subarbor node ID (%s) is not part of " "provided skeleton (%s)" % (subarbor_node_id, skeleton_id)) # Remove connection to parent parent = g.predecessors(subarbor_node_id)[0] g.remove_edge(parent, subarbor_node_id) # Remove all nodes that are upstream from the subarbor node to_delete = set() to_lookat = [root_id] while to_lookat: n = to_lookat.pop() to_lookat.extend(g.successors(n)) to_delete.add(n) g.remove_nodes_from(to_delete) # Replace root id with sub-arbor ID root_id=subarbor_node_id # Create all sequences, as long as possible and always from end towards root distances = edge_count_to_root(g, root_node=root_id) # distance in number of edges from root seen = set() sequences = [] # Iterate end nodes sorted from highest to lowest distance to root endNodeIDs = (nID for nID in g.nodes() if 0 == len(g.successors(nID))) for nodeID in sorted(endNodeIDs, key=distances.get, reverse=True): sequence = [g.node[nodeID]] parents = g.predecessors(nodeID) while parents: parentID = parents[0] sequence.append(g.node[parentID]) if parentID in seen: break seen.add(parentID) parents = g.predecessors(parentID) if len(sequence) > 1: sequences.append(sequence) # Calculate status segments = [] for sequence in sorted(sequences, key=len, reverse=True): segments.append({ 'id': len(segments), 'sequence': sequence, 'status': '%.2f' % (100.0 * sum(1 for node in sequence if node['id'] in reviewed) / len(sequence)), 'nr_nodes': len(sequence) }) return segments
def _skeleton_for_3d_viewer(skeleton_id, project_id, with_connectors=True, lean=0, all_field=False): """ with_connectors: when False, connectors are not returned lean: when not zero, both connectors and tags are returned as empty arrays. """ skeleton_id = int(skeleton_id) # sanitize cursor = connection.cursor() # Fetch the neuron name cursor.execute( '''SELECT name FROM class_instance ci, class_instance_class_instance cici WHERE cici.class_instance_a = %s AND cici.class_instance_b = ci.id ''' % skeleton_id) row = cursor.fetchone() if not row: # Check that the skeleton exists cursor.execute('''SELECT id FROM class_instance WHERE id=%s''' % skeleton_id) if not cursor.fetchone(): raise Exception("Skeleton #%s doesn't exist!" % skeleton_id) else: raise Exception("No neuron found for skeleton #%s" % skeleton_id) name = row[0] if all_field: added_fields = ', creation_time, edition_time' else: added_fields = '' # Fetch all nodes, with their tags if any cursor.execute( '''SELECT id, parent_id, user_id, location_x, location_y, location_z, radius, confidence %s FROM treenode WHERE skeleton_id = %s ''' % (added_fields, skeleton_id) ) # array of properties: id, parent_id, user_id, x, y, z, radius, confidence nodes = tuple(cursor.fetchall()) tags = defaultdict(list) # node ID vs list of tags connectors = [] # Get all reviews for this skeleton if all_field: reviews = get_treenodes_to_reviews_with_time(skeleton_ids=[skeleton_id]) else: reviews = get_treenodes_to_reviews(skeleton_ids=[skeleton_id]) if 0 == lean: # meaning not lean # Text tags cursor.execute("SELECT id FROM relation WHERE project_id=%s AND relation_name='labeled_as'" % int(project_id)) labeled_as = cursor.fetchall()[0][0] cursor.execute( ''' SELECT treenode_class_instance.treenode_id, class_instance.name FROM treenode, class_instance, treenode_class_instance WHERE treenode.skeleton_id = %s AND treenode.id = treenode_class_instance.treenode_id AND treenode_class_instance.class_instance_id = class_instance.id AND treenode_class_instance.relation_id = %s ''' % (skeleton_id, labeled_as)) for row in cursor.fetchall(): tags[row[1]].append(row[0]) if with_connectors: if all_field: added_fields = ', c.creation_time' else: added_fields = '' # Fetch all connectors with their partner treenode IDs cursor.execute( ''' SELECT tc.treenode_id, tc.connector_id, r.relation_name, c.location_x, c.location_y, c.location_z %s FROM treenode_connector tc, connector c, relation r WHERE tc.skeleton_id = %s AND tc.connector_id = c.id AND tc.relation_id = r.id ''' % (added_fields, skeleton_id) ) # Above, purposefully ignoring connector tags. Would require a left outer join on the inner join of connector_class_instance and class_instance, and frankly connector tags are pointless in the 3d viewer. # List of (treenode_id, connector_id, relation_id, x, y, z)n with relation_id replaced by 0 (presynaptic) or 1 (postsynaptic) # 'presynaptic_to' has an 'r' at position 1: for row in cursor.fetchall(): x, y, z = imap(float, (row[3], row[4], row[5])) connectors.append((row[0], row[1], 0 if 'r' == row[2][1] else 1, x, y, z, row[6])) return name, nodes, tags, connectors, reviews return name, nodes, tags, connectors, reviews
def _export_review_skeleton(project_id=None, skeleton_id=None, format=None, subarbor_node_id=None): """ Returns a list of segments for the requested skeleton. Each segment contains information about the review status of this part of the skeleton. If a valid subarbor_node_id is given, only data for the sub-arbor is returned that starts at this node. """ # Get all treenodes of the requested skeleton treenodes = Treenode.objects.filter(skeleton_id=skeleton_id).values_list( 'id', 'parent_id', 'location_x', 'location_y', 'location_z') # Get all reviews for the requested skeleton reviews = get_treenodes_to_reviews_with_time(skeleton_ids=[skeleton_id]) # Add each treenode to a networkx graph and attach reviewer information to # it. g = nx.DiGraph() reviewed = set() for t in treenodes: # While at it, send the reviewer IDs, which is useful to iterate fwd # to the first unreviewed node in the segment. g.add_node(t[0], {'id': t[0], 'x': t[2], 'y': t[3], 'z': t[4], 'rids': reviews[t[0]]}) if reviews[t[0]]: reviewed.add(t[0]) if t[1]: # if parent g.add_edge(t[1], t[0]) # edge from parent to child else: root_id = t[0] if subarbor_node_id and subarbor_node_id != root_id: # Make sure the subarbor node ID (if any) is part of this skeleton if subarbor_node_id not in g: raise ValueError("Supplied subarbor node ID (%s) is not part of " "provided skeleton (%s)" % (subarbor_node_id, skeleton_id)) # Remove connection to parent parent = g.predecessors(subarbor_node_id)[0] g.remove_edge(parent, subarbor_node_id) # Remove all nodes that are upstream from the subarbor node to_delete = set() to_lookat = [root_id] while to_lookat: n = to_lookat.pop() to_lookat.extend(g.successors(n)) to_delete.add(n) g.remove_nodes_from(to_delete) # Replace root id with sub-arbor ID root_id=subarbor_node_id # Create all sequences, as long as possible and always from end towards root distances = edge_count_to_root(g, root_node=root_id) # distance in number of edges from root seen = set() sequences = [] # Iterate end nodes sorted from highest to lowest distance to root endNodeIDs = (nID for nID in g.nodes() if 0 == len(g.successors(nID))) for nodeID in sorted(endNodeIDs, key=distances.get, reverse=True): sequence = [g.node[nodeID]] parents = g.predecessors(nodeID) while parents: parentID = parents[0] sequence.append(g.node[parentID]) if parentID in seen: break seen.add(parentID) parents = g.predecessors(parentID) if len(sequence) > 1: sequences.append(sequence) # Calculate status segments = [] for sequence in sorted(sequences, key=len, reverse=True): segments.append({ 'id': len(segments), 'sequence': sequence, 'status': '%.2f' % (100.0 * sum(1 for node in sequence if node['id'] in reviewed) / len(sequence)), 'nr_nodes': len(sequence) }) return segments
def _skeleton_for_3d_viewer(skeleton_id, project_id, with_connectors=True, lean=0, all_field=False): """ with_connectors: when False, connectors are not returned lean: when not zero, both connectors and tags are returned as empty arrays. """ skeleton_id = int(skeleton_id) # sanitize cursor = connection.cursor() # Fetch the neuron name cursor.execute( '''SELECT name FROM class_instance ci, class_instance_class_instance cici WHERE cici.class_instance_a = %s AND cici.class_instance_b = ci.id ''' % skeleton_id) row = cursor.fetchone() if not row: # Check that the skeleton exists cursor.execute('''SELECT id FROM class_instance WHERE id=%s''' % skeleton_id) if not cursor.fetchone(): raise Exception("Skeleton #%s doesn't exist!" % skeleton_id) else: raise Exception("No neuron found for skeleton #%s" % skeleton_id) name = row[0] if all_field: added_fields = ', creation_time, edition_time' else: added_fields = '' # Fetch all nodes, with their tags if any cursor.execute( '''SELECT id, parent_id, user_id, location_x, location_y, location_z, radius, confidence %s FROM treenode WHERE skeleton_id = %s ''' % (added_fields, skeleton_id) ) # array of properties: id, parent_id, user_id, x, y, z, radius, confidence nodes = tuple(cursor.fetchall()) tags = defaultdict(list) # node ID vs list of tags connectors = [] # Get all reviews for this skeleton if all_field: reviews = get_treenodes_to_reviews_with_time(skeleton_ids=[skeleton_id]) else: reviews = get_treenodes_to_reviews(skeleton_ids=[skeleton_id]) if 0 == lean: # meaning not lean # Text tags cursor.execute("SELECT id FROM relation WHERE project_id=%s AND relation_name='labeled_as'" % int(project_id)) labeled_as = cursor.fetchall()[0][0] cursor.execute( ''' SELECT treenode_class_instance.treenode_id, class_instance.name FROM treenode, class_instance, treenode_class_instance WHERE treenode.skeleton_id = %s AND treenode.id = treenode_class_instance.treenode_id AND treenode_class_instance.class_instance_id = class_instance.id AND treenode_class_instance.relation_id = %s ''' % (skeleton_id, labeled_as)) for row in cursor.fetchall(): tags[row[1]].append(row[0]) if with_connectors: if all_field: added_fields = ', c.creation_time' else: added_fields = '' # Fetch all connectors with their partner treenode IDs cursor.execute( ''' SELECT tc.treenode_id, tc.connector_id, r.relation_name, c.location_x, c.location_y, c.location_z %s FROM treenode_connector tc, connector c, relation r WHERE tc.skeleton_id = %s AND tc.connector_id = c.id AND tc.relation_id = r.id ''' % (added_fields, skeleton_id) ) # Above, purposefully ignoring connector tags. Would require a left outer join on the inner join of connector_class_instance and class_instance, and frankly connector tags are pointless in the 3d viewer. # List of (treenode_id, connector_id, relation_id, x, y, z)n with relation_id replaced by 0 (presynaptic) or 1 (postsynaptic) # 'presynaptic_to' has an 'r' at position 1: for row in cursor.fetchall(): x, y, z = imap(float, (row[3], row[4], row[5])) connectors.append((row[0], row[1], 0 if 'r' == row[2][1] else 1, x, y, z, row[6])) return name, nodes, tags, connectors, reviews return name, nodes, tags, connectors, reviews
def _export_review_skeleton(project_id=None, skeleton_id=None, subarbor_node_id=None): """ Returns a list of segments for the requested skeleton. Each segment contains information about the review status of this part of the skeleton. If a valid subarbor_node_id is given, only data for the sub-arbor is returned that starts at this node. """ # Get all treenodes of the requested skeleton cursor = connection.cursor() cursor.execute(""" SELECT t.id, t.parent_id, t.location_x, t.location_y, t.location_z, ARRAY_AGG(svt.orientation), ARRAY_AGG(svt.location_coordinate) FROM treenode t LEFT OUTER JOIN suppressed_virtual_treenode svt ON (t.id = svt.child_id) WHERE t.skeleton_id = %s GROUP BY t.id; """, (skeleton_id,)) treenodes = cursor.fetchall() # Get all reviews for the requested skeleton reviews = get_treenodes_to_reviews_with_time(skeleton_ids=[skeleton_id]) if 0 == len(treenodes): return [] # The root node will be assigned below, depending on retrieved nodes and # sub-arbor requests root_id = None # Add each treenode to a networkx graph and attach reviewer information to # it. g = nx.DiGraph() reviewed = set() for t in treenodes: # While at it, send the reviewer IDs, which is useful to iterate fwd # to the first unreviewed node in the segment. g.add_node(t[0], {'id': t[0], 'x': t[2], 'y': t[3], 'z': t[4], 'rids': reviews[t[0]], 'sup': [[o, l] for [o, l] in zip(t[5], t[6]) if o is not None]}) if reviews[t[0]]: reviewed.add(t[0]) if t[1]: # if parent g.add_edge(t[1], t[0]) # edge from parent to child else: root_id = t[0] if subarbor_node_id and subarbor_node_id != root_id: # Make sure the subarbor node ID (if any) is part of this skeleton if subarbor_node_id not in g: raise ValueError("Supplied subarbor node ID (%s) is not part of " "provided skeleton (%s)" % (subarbor_node_id, skeleton_id)) # Remove connection to parent parent = g.predecessors(subarbor_node_id)[0] g.remove_edge(parent, subarbor_node_id) # Remove all nodes that are upstream from the subarbor node to_delete = set() to_lookat = [root_id] while to_lookat: n = to_lookat.pop() to_lookat.extend(g.successors(n)) to_delete.add(n) g.remove_nodes_from(to_delete) # Replace root id with sub-arbor ID root_id=subarbor_node_id if not root_id: if subarbor_node_id: raise ValueError("Couldn't find a reference root node in provided " "skeleton (%s)" % (skeleton_id,)) else: raise ValueError("Couldn't find a reference root node for provided " "subarbor (%s) in provided skeleton (%s)" % (subarbor_node_id, skeleton_id)) # Create all sequences, as long as possible and always from end towards root distances = edge_count_to_root(g, root_node=root_id) # distance in number of edges from root seen = set() sequences = [] # Iterate end nodes sorted from highest to lowest distance to root endNodeIDs = (nID for nID in g.nodes() if 0 == len(g.successors(nID))) for nodeID in sorted(endNodeIDs, key=distances.get, reverse=True): sequence = [g.node[nodeID]] parents = g.predecessors(nodeID) while parents: parentID = parents[0] sequence.append(g.node[parentID]) if parentID in seen: break seen.add(parentID) parents = g.predecessors(parentID) if len(sequence) > 1: sequences.append(sequence) # Calculate status segments = [] for sequence in sorted(sequences, key=len, reverse=True): segments.append({ 'id': len(segments), 'sequence': sequence, 'status': '%.2f' % (100.0 * sum(1 for node in sequence if node['id'] in reviewed) / len(sequence)), 'nr_nodes': len(sequence) }) return segments
def _export_review_skeleton(project_id=None, skeleton_id=None, subarbor_node_id=None): """ Returns a list of segments for the requested skeleton. Each segment contains information about the review status of this part of the skeleton. If a valid subarbor_node_id is given, only data for the sub-arbor is returned that starts at this node. """ # Get all treenodes of the requested skeleton cursor = connection.cursor() cursor.execute(""" SELECT t.id, t.parent_id, t.location_x, t.location_y, t.location_z, ARRAY_AGG(svt.orientation), ARRAY_AGG(svt.location_coordinate) FROM treenode t LEFT OUTER JOIN suppressed_virtual_treenode svt ON (t.id = svt.child_id) WHERE t.skeleton_id = %s GROUP BY t.id; """, (skeleton_id,)) treenodes = cursor.fetchall() # Get all reviews for the requested skeleton reviews = get_treenodes_to_reviews_with_time(skeleton_ids=[skeleton_id]) if 0 == len(treenodes): return [] # The root node will be assigned below, depending on retrieved nodes and # sub-arbor requests root_id = None # Add each treenode to a networkx graph and attach reviewer information to # it. g = nx.DiGraph() reviewed = set() for t in treenodes: # While at it, send the reviewer IDs, which is useful to iterate fwd # to the first unreviewed node in the segment. g.add_node(t[0], {'id': t[0], 'x': t[2], 'y': t[3], 'z': t[4], 'rids': reviews[t[0]], 'sup': [[o, l] for [o, l] in zip(t[5], t[6]) if o is not None]}) if reviews[t[0]]: reviewed.add(t[0]) if t[1]: # if parent g.add_edge(t[1], t[0]) # edge from parent to child else: root_id = t[0] if subarbor_node_id and subarbor_node_id != root_id: # Make sure the subarbor node ID (if any) is part of this skeleton if subarbor_node_id not in g: raise ValueError("Supplied subarbor node ID (%s) is not part of " "provided skeleton (%s)" % (subarbor_node_id, skeleton_id)) # Remove connection to parent parent = g.predecessors(subarbor_node_id)[0] g.remove_edge(parent, subarbor_node_id) # Remove all nodes that are upstream from the subarbor node to_delete = set() to_lookat = [root_id] while to_lookat: n = to_lookat.pop() to_lookat.extend(g.successors(n)) to_delete.add(n) g.remove_nodes_from(to_delete) # Replace root id with sub-arbor ID root_id=subarbor_node_id if not root_id: if subarbor_node_id: raise ValueError("Couldn't find a reference root node in provided " "skeleton (%s)" % (skeleton_id,)) else: raise ValueError("Couldn't find a reference root node for provided " "subarbor (%s) in provided skeleton (%s)" % (subarbor_node_id, skeleton_id)) # Create all sequences, as long as possible and always from end towards root distances = edge_count_to_root(g, root_node=root_id) # distance in number of edges from root seen = set() sequences = [] # Iterate end nodes sorted from highest to lowest distance to root endNodeIDs = (nID for nID in g.nodes() if 0 == len(g.successors(nID))) for nodeID in sorted(endNodeIDs, key=distances.get, reverse=True): sequence = [g.node[nodeID]] parents = g.predecessors(nodeID) while parents: parentID = parents[0] sequence.append(g.node[parentID]) if parentID in seen: break seen.add(parentID) parents = g.predecessors(parentID) if len(sequence) > 1: sequences.append(sequence) # Calculate status segments = [] for sequence in sorted(sequences, key=len, reverse=True): segments.append({ 'id': len(segments), 'sequence': sequence, 'status': '%.2f' % (100.0 * sum(1 for node in sequence if node['id'] in reviewed) / len(sequence)), 'nr_nodes': len(sequence) }) return segments