<commit_before>-- Note: This was adapted from survey_detail.sql.  Try to keep changes in sync
WITH trees_for_this_survey AS(
  SELECT
    id,
    survey_id,
    1.000*(CASE WHEN curb_location='OnCurb' THEN 2.5 ELSE 12 END) curb_offset,
    1.000*distance_to_tree dist
  FROM survey_tree
  ORDER BY id
),
aggregated_trees AS (
  SELECT survey_id, array_agg(curb_offset) width,
         array_agg(0) length, array_agg(dist) dist, array_agg(id) tree_ids
  FROM trees_for_this_survey
  GROUP BY survey_id
),
aggs AS (
  SELECT
    s.blockface_id,
    s.is_mapped_in_blockface_polyline_direction,
    s.is_left_side as left_side,
    s.user_id, b.geom,
    r.survey_id, width, length, dist,
    r.tree_ids
  FROM
    aggregated_trees r, survey_survey s, survey_blockface b
  WHERE
    r.survey_id = s.id AND b.id = s.blockface_id
),<commit_msg>Fix sorting in tree shapefile query<commit_after>-- Note: This was adapted from survey_detail.sql.  Try to keep changes in sync
WITH trees_for_this_survey AS(
  SELECT
    id,
    survey_id,
    1.000*(CASE WHEN curb_location='OnCurb' THEN 2.5 ELSE 12 END) curb_offset,
    1.000*distance_to_tree dist
  FROM survey_tree
),
aggregated_trees AS (
  SELECT survey_id,
         array_agg(curb_offset ORDER BY id) width,
         array_agg(0) length,
         array_agg(dist ORDER BY id) dist,
         array_agg(id ORDER BY id) tree_ids
  FROM trees_for_this_survey
  GROUP BY survey_id
),
aggs AS (
  SELECT
    s.blockface_id,
    s.is_mapped_in_blockface_polyline_direction,
    s.is_left_side as left_side,
    s.user_id, b.geom,
    r.survey_id, width, length, dist,
    r.tree_ids
  FROM
    aggregated_trees r, survey_survey s, survey_blockface b
  WHERE
    r.survey_id = s.id AND b.id = s.blockface_id
),
{'fraction_non_alphanumeric': 0.07547169811320754, 'fraction_numerical': 0.014865637507146942, 'mean_word_length': 3.4080604534005037, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '7237372', 'n_tokens_mistral': 690, 'n_tokens_neox': 644, 'n_words': 171}