CAVE Views

This page describes the views created in brain_and_nerve_cord. Views are automatically materialized by CAVE.

Some of these views are intended for analysis and be queries with CAVEclient. Others are intended to be used with the Google SQL Export function or dump_csv_table endpoint in the materialization engine.

somas_v1

somas_v1 consists of a correct view of somas where the representative points are associated with the correct neurons. somas_v1a includes representative points defined by the centroid of the bounding box. somas_v1b corrects this in cases where the point from somas_v1a does not land on the right neuron.

CREATE VIEW somas_v1 AS
SELECT somas_v1a.id,
    somas_v1a.created,
    somas_v1a.deleted,
    somas_v1a.superceded_id,
    somas_v1a.valid,
    COALESCE(somas_v1b.pt_position, somas_v1a.pt_position) AS pt_position,
    COALESCE(somas_v1b__wclee_fly_cns_001.pt_supervoxel_id, somas_v1a__wclee_fly_cns_001.pt_supervoxel_id) AS pt_supervoxel_id,
    COALESCE(somas_v1b__wclee_fly_cns_001.pt_root_id, somas_v1a__wclee_fly_cns_001.pt_root_id) AS pt_root_id
   FROM (((somas_v1a
     JOIN somas_v1a__wclee_fly_cns_001 ON ((somas_v1a.id = somas_v1a__wclee_fly_cns_001.id)))
     LEFT JOIN somas_v1b ON (((somas_v1a.id = somas_v1b.target_id) AND (somas_v1b.deleted IS NULL) AND (somas_v1b.valid IS TRUE))))
     LEFT JOIN somas_v1b__wclee_fly_cns_001 ON ((somas_v1b.id = somas_v1b__wclee_fly_cns_001.id)));

synapses_v1_human_readable

synapses_v1_human_readable contains the entire synapses table, with the use of PostGIS functions to extract human readable values from the WKB.

CREATE VIEW synapses_v1_human_readable AS
SELECT a.id AS synapse_id,
  ST_X(a.pre_pt_position) AS pre_pt_x,
  ST_Y(a.pre_pt_position) AS pre_pt_y,
  ST_Z(a.pre_pt_position) AS pre_pt_z,
  ST_X(a.post_pt_position) AS post_pt_x,
  ST_Y(a.post_pt_position) AS post_pt_y,
  ST_Z(a.post_pt_position) AS post_pt_z,
  ROUND(ST_X(a.ctr_pt_position)) AS ctr_pt_x,
  ROUND(ST_Y(a.ctr_pt_position)) AS ctr_pt_y,
  ROUND(ST_Z(a.ctr_pt_position)) AS ctr_pt_z,
  a.size AS size,
  b.pre_pt_supervoxel_id AS pre_pt_supervoxel_id,
  b.pre_pt_root_id AS pre_pt_root_id,
  b.post_pt_supervoxel_id AS post_pt_supervoxel_id,
  b.post_pt_root_id AS post_pt_root_id
FROM synapses_v1 AS a
JOIN synapses_v1__wclee_fly_cns_001 AS b
ON a.id = b.id
WHERE a.valid IS true;

synapses_v1_backbone_proofread_counts

synapses_v1_backbone_proofread_counts contains the pairwise synapse counts between neruons in backbone_proofread. Counts are directional (from pre to post). Note: backbone_proofread may contain multiple entries for the same root_id, which the inner selects are used to filter out.

CREATE VIEW synapses_v1_backbone_proofread_counts AS
SELECT syn.pre_pt_root_id AS pre_pt_root_id, syn.post_pt_root_id AS post_pt_root_id, COUNT(*) AS n
FROM synapses_v1__wclee_fly_cns_001 AS syn
INNER JOIN (
  SELECT DISTINCT pt_root_id
  FROM backbone_proofread__wclee_fly_cns_001 AS bpm1
  INNER JOIN backbone_proofread AS bp1
  ON bpm1.id = bp1.id AND bp1.proofread = True AND bp1.valid = True) root_ids_pre
ON root_ids_pre.pt_root_id = syn.pre_pt_root_id
INNER JOIN (
  SELECT DISTINCT pt_root_id
  FROM backbone_proofread__wclee_fly_cns_001 AS bpm1
  INNER JOIN backbone_proofread AS bp1
  ON bpm1.id = bp1.id AND bp1.proofread = True AND bp1.valid = True) root_ids_post
ON root_ids_post.pt_root_id = syn.post_pt_root_id 
GROUP BY (syn.pre_pt_root_id, syn.post_pt_root_id)

synapses_v1_neuron_region

synapses_v1_neuron_region returns a list of counts of the pre- and post-synaptic sites, by neuron, by region.

CREATE VIEW synapses_v1_neuron_region AS
SELECT
COALESCE(a.pre_pt_root_id, b.post_pt_root_id) AS root_id,
COALESCE(a.tag, b.tag) AS region,
COALESCE(a.n, 0) AS pre_n,
COALESCE(b.n, 0) AS post_n
FROM
    (SELECT syn_pre.pre_pt_root_id AS pre_pt_root_id, region.tag AS tag, COUNT(*) AS n
FROM synapses_v1__wclee_fly_cns_001 AS syn_pre
INNER JOIN synapses_v1_regions_test AS region ON syn_pre.id = region.target_id
GROUP BY (syn_pre.pre_pt_root_id, region.tag)
) a
FULL JOIN
    (SELECT syn_post.post_pt_root_id AS post_pt_root_id, region.tag AS tag, COUNT(*) AS n
FROM synapses_v1__wclee_fly_cns_001 AS syn_post
INNER JOIN synapses_v1_regions_test AS region ON syn_post.id = region.target_id
GROUP BY (syn_post.post_pt_root_id, region.tag)
) b
ON
  a.pre_pt_root_id = b.post_pt_root_id AND a.tag = b.tag