Digital Humanist. Manhattan-based.
Public
Edited
Sep 28, 2023
1 fork
Importers
4 stars
viewof results = Inputs.search(species1, { label: "Search by species" })
viewof species = Inputs.table(results)
viewof species1 = db.table(
`
FROM trees
SELECT spc_common,
MEDIAN(tree_dbh::FLOAT) AS median_size,
COUNT(*) AS count
GROUP BY spc_common
ORDER by count DESC
`
)
(scatterplot.tooltip_html = function (datum) {
console.log({ ...datum });
let html = `<div class="species">${datum.spc_common}</div><div class="address">${datum.address}, ${datum.boroname}</div>`;
if (!["spc_common", "address", "boroname"].includes(selected)) {
html += `<div class="etc"><span>${selected}</span>: <span>${datum[selected]}</span></div>`;
}
return html;
})
{
results;
d3.select("#label")
.style("font-size", "48px")
.style("font-family", "sans-serif")
.style("background", "none")
.text(viewof results[0].value);
}
db.table("SELECT COUNT(*), boroname FROM trees GROUP BY ALL")
db.table("SELECT COUNT(*) FROM trees")
scatterplot.plotAPI({
background_options: {
opacity: 0.05,
size: 0.1
},
encoding: {
foreground: {
field: "spc_common",
lambda
}
}
// tooltip_html: `return "${selected}: " + datum['${selected}']`
})
lambda = `d => new Set(${JSON.stringify(selected_species)}).has(d)`
lambda_size = `d => (new Set(${JSON.stringify(
selected_species
)}).has(d) ? 2 : 0.5)`
selected_species = species.map((d) => d.spc_common)
img = fetch("https://maps1.nyc.gov/tms/1.0.0/photo/2018/21/617539/1308820.png8")
<img src="https://maps1.nyc.gov/tms/1.0.0/photo/2018/21/617539/1308820.png8" />
html`
<style type="text/css">
.tooltip {
background-color: black;
width: 400px;
font-family: sans-serif;
}
.rect {
fill: white;
}
</style>
`
db.table(
"SELECT MAX(tree_dbh::FLOAT) dia, spc_common FROM trees GROUP BY spc_common ORDER by dia DESC"
)
deepscatter = import("https://cdn.skypack.dev/deepscatter@2.13.0").then(
(d) => d.default
)
enums_made = {
return Promise.all(
categorical.map((k) => {
db.query(
`CREATE TYPE ${k} AS ENUM (SELECT "${k}" FROM trees WHERE "${k}" IS NOT NULL)`
);
})
);
}
trees2 = await db.query(
"CREATE TABLE trees2 AS SELECT * FROM 'https://benschmidt.org/treesbig.parquet'"
)
db = {
const db = await DuckDBClient.of({});
await db.query(
"CREATE TABLE trees AS SELECT * FROM 'https://benschmidt.org/trees.parquet'"
);
return db;
}
db.table("SELECT * FROM trees2 LIMIT 1")
trees2_arrow = {
trees2;
const categorical = ["TPStructure", "TPCondition", "latin", "common"];
const sels = categorical.map((d) => ` ${d}::${d} AS ${d}`).join(",");
const enums_made = await Promise.all(
categorical.map((k) => {
db.query("DROP TYPE " + k);
return db.query(
`CREATE TYPE ${k} AS ENUM (SELECT "${k}" FROM trees2 WHERE "${k}" IS NOT NULL)`
);
})
);
return db.query(`SELECT x, y * .66, ${sels} FROM trees2`);
}
(
await db.query(
`SELECT spc_common, COUNT(*) as count FROM trees GROUP BY spc_common HAVING count < 2000 ORDER BY count DESC`
)
)
.getChild("spc_common")
.toArray()
.join("\n")
d3.csvParse(flowering).map((d) => {
d.flower_start = new Date("2023-" + d.flower_start);
d.flower_end = new Date("2023-" + d.flower_end);
d.flower_mid = new Date((d.flower_end + d.flower_start) / 2);
return d;
})
flowering = `name,flower_start,flower_end,notability,color
London planetree,04-25,05-10,2,#A58B74
Honeylocust,05-25,06-10,2,#F8F2D7
Callery pear,04-05,04-20,4,#FFFFFF
Pin oak,04-15,05-05,2,#B2A48A
Norway maple,04-15,04-30,3,#FFE082
Littleleaf linden,06-25,07-10,3,#F5F5DC
Cherry,04-05,04-20,5,#FFB7C5
Japanese zelkova,04-15,04-30,2,#A7927A
Ginkgo,04-25,05-10,1,#E0DC89
Sophora,05-25,06-10,4,#FFFFFF
Red maple,04-05,04-20,3,#FFA07A
Green ash,04-15,04-30,2,#A7B28D
American linden,06-25,07-10,3,#F5F5DC
Silver maple,03-05,03-20,3,#C6E3FF
Sweetgum,04-25,05-10,3,#A8461D
Northern red oak,04-15,05-05,2,#B2A48A
Silver linden,06-25,07-10,3,#E0FFFF
American elm,03-05,03-20,3,#C6E3FF
Maple,04-05,04-20,3,#FFA07A
purple-leaf plum,04-01,04-20,4,#FF5FD9
swamp white oak,04-25,05-15,2,#B2A48A
crimson king maple,04-15,05-05,3,#8B0000
Chinese elm,08-15,09-30,2,#B2A48A
'Schubert' chokecherry,04-20,05-10,4,#8B0000
Japanese tree lilac,06-05,06-25,4,#FFFFFF
eastern redbud,04-01,04-20,5,#FF82AB
golden raintree,06-05,06-25,4,#FFD700
crab apple,04-15,05-05,5,#FF69B4
Kentucky coffeetree,05-20,06-10,2,#F8F2D7
willow oak,04-20,05-10,2,#B2A48A
dawn redwood,04-20,05-10,2,#A7B28D
hawthorn,04-20,05-15,4,#FFFFFF
sugar maple,04-15,05-05,3,#FFA07A
sycamore maple,04-20,05-10,3,#FFE082
ash,04-15,05-05,2,#A7B28D
hedge maple,04-20,05-10,3,#FFE082
common hackberry,04-15,05-05,2,#A7B28D
sawtooth oak,04-15,05-05,2,#B2A48A
Amur maackia,07-01,07-25,3,#FFFFFF
European hornbeam,04-15,05-05,2,#B2A48A
Amur maple,04-20,05-10,3,#FFA07A
serviceberry,04-01,04-20,5,#FFFFFF
black locust,05-05,05-25,4,#FFFFFF
white oak,04-25,05-15,2,#B2A48A
English oak,04-25,05-15,2,#B2A48A
Siberian elm,03-25,04-15,3,#C6E3FF
flowering dogwood,04-15,05-05,5,#FF83AA
American hornbeam,04-20,05-10,2,#B2A48A
Schumard's oak,04-25,05-15,2,#B2A48A
scarlet oak,04-25,05-15,2,#B2A48A
black oak,04-20,05-10,2,#B2A48A
bald cypress,03-25,04-15,2,#A7B28D
mulberry,04-15,05-05,3,#8B0000
Japanese maple,04-15,05-05,3,#FFA07A
white ash,04-15,05-05,2,#A7B28D
eastern redcedar,06-10,06-30,2,#B2A48A
horse chestnut,05-05,05-25,4,#FFFFFF
American hophornbeam,04-15,05-05,2,#B2A48A
tulip-poplar,05-01,05-20,4,#FFD700
Cornelian cherry,03-10,03-30,4,#FFB7C5
shingle oak,04-25,05-15,2,#B2A48A
hardy rubber tree,08-01,08-20,2,#A7B28D
katsura tree,04-20,05-10,2,#B2A48A
tree of heaven,05-25,06-15,2,#F8F2D7
magnolia,03-25,04-15,5,#FFFFFF
black cherry,04-20,05-10,3,#8B0000
river birch,04-01,04-20,2,#B2A48A
catalpa,05-25,06-15,4,#FFFFFF
paper birch,04-20,05-10,2,#B2A48A
bur oak,04-25,05-15,2,#B2A48A
Kentucky yellowwood,05-10,05-30,4,#FFFFFF
Chinese tree lilac,06-05,06-25,4,#FFFFFF
crepe myrtle,06-15,07-30,5,#FF69B4
Japanese hornbeam,04-20,05-10,2,#B2A48A
Japanese snowbell,05-25,06-15,4,#FFFFFF
Atlantic white cedar,05-01,05-20,2,#A7B28D
Norway spruce,05-10,05-30,2,#B2A48A
cockspur hawthorn,04-20,05-15,4,#FFFFFF
arborvitae,06-05,06-25,2,#A7B28D
Turkish hazelnut,03-20,04-10,3,#FFE082
kousa dogwood,05-20,06-10,5,#FF83AA
silver birch,04-20,05-10,2,#B2A48A
black walnut,04-20,05-10,2,#B2A48A
pine,04-25,05-15,2,#A7B28D
blackgum,04-15,05-05,3,#8B0000
weeping willow,04-01,04-20,2,#B2A48A
pagoda dogwood,04-15,05-05,3,#FFA
Persian ironwood,02-20,03-10,3,#FFA07A
eastern cottonwood,04-20,05-10,2,#B2A48A
American beech,04-20,05-10,2,#B2A48A
empress tree,05-10,05-30,4,#FFD700
Chinese fringetree,05-15,06-05,4,#FFFFFF
two-winged silverbell,04-20,05-10,4,#FFFFFF
paperbark maple,04-20,05-10,3,#FFA07A
Oklahoma redbud,04-01,04-20,5,#FF82AB
spruce,05-10,05-30,2,#B2A48A
white pine,04-25,05-15,2,#A7B28D
Amur cork tree,06-01,06-20,2,#A7B28D
pond cypress,03-25,04-15,2,#A7B28D
Chinese chestnut,05-15,06-05,3,#FFE082
sassafras,03-20,04-10,3,#FFE082
cucumber magnolia,04-25,05-15,4,#FFD700
mimosa,05-15,06-15,5,#FF69B4
tartar maple,04-20,05-10,3,#FFA07A
holly,05-10,05-30,3,#FFFFFF
southern magnolia,05-15,06-30,5,#FFFFFF
blue spruce,05-10,05-30,2,#B2A48A
European beech,04-20,05-10,2,#B2A48A
red horse chestnut,05-05,05-25,4,#FF6347
black maple,04-15,05-05,3,#FFA07A
trident maple,04-20,05-10,3,#FFA07A
false cypress,04-25,05-15,2,#A7B28D
red pine,04-25,05-15,2,#A7B28D
pignut hickory,04-20,05-10,2,#B2A48A
bigtooth aspen,04-01,04-20,2,#B2A48A
eastern hemlock,05-10,05-30,2,#B2A48A
Atlas cedar,05-01,05-20,2,#A7B28D
Douglas-fir,04-25,05-15,2,#A7B28D
southern red oak,04-25,05-15,2,#B2A48A
quaking aspen,04-01,04-20,2,#B2A48A
Ohio buckeye,04-15,05-05,3,#FFA07A
Himalayan cedar,05-01,05-20,2,#A7B28D
boxelder,04-15,05-05,3,#FFA07A
Shantung maple,04-20,05-10,3,#FFA07A
`
db.table(q)
q = `WITH
word_counts AS
(SELECT boroname || ': ' || nta_name category, spc_common as word, COUNT(*) count FROM trees
GROUP BY category, word
),
-- I created the counts as separate subqueries because I was unable to make PARTITION BY work correctly.
word_totals AS (
SELECT word, sum("count")::FLOAT AS word_total FROM word_counts GROUP BY word
),
category_totals AS (
SELECT category, sum("count")::FLOAT AS category_size FROM word_counts GROUP BY category
),
pmi_part1 AS (
SELECT
SUM("count") OVER ()::FLOAT as corpus_size,
*
FROM word_counts NATURAL JOIN word_totals NATURAL JOIN category_totals
),
pmi_part2 AS (
SELECT *,
greatest(word_total - "count", 0.2) AS count_excluding, -- 0.2 is a fudge here--you could call it a 'prior,', I guess. If a word doesn't appear, we pretend that it appeared *just a little* to make the logarithms work.
corpus_size - category_size AS size_excluding_category
FROM pmi_part1
),
pmi_part3 AS (
SELECT *,
category_size * (word_total / corpus_size) AS expected_here,
size_excluding_category * (word_total / corpus_size) AS expected_elsewhere,
FROM pmi_part2
),
pmi_part4 AS (
SELECT *,
2 * (
"count" * log("count" / expected_here) +
"count_excluding" * log(count_excluding / expected_elsewhere)
) AS llr,
log("count" / expected_here) AS pmi,
FROM pmi_part3),
ranked_ordered AS (
SELECT category,
*,
word || '(' || count::INT || ')' AS label,
count,
word_total,
PMI,
llr,
CASE WHEN PMI < 0 THEN 'under-represented' ELSE 'over-represented' END AS direction,
RANK() OVER (PARTITION BY category, PMI < 0 ORDER BY llr DESC) ordering,
FROM pmi_part4
)
SELECT
category, direction, list(label) terms FROM ranked_ordered
WHERE ordering < 6 GROUP BY direction, category ORDER BY direction, category`
trees.url()
import {
DuckDBClient,
arrow
} from "@bmschmidt/duckdb-client-1-20-0-arrow-10-0-1"
Plot.plot({
x: {
type: "log"
},
marks: [
Plot.arrow(citywages, {
x1: "POP_1980",
y1: "R90_10_1980",
x2: "POP_2015",
y2: "R90_10_2015",
bend: true
})
]
})
Purpose-built for displays of data
Observable is your go-to platform for exploring data and creating expressive data visualizations. Use reactive JavaScript notebooks for prototyping and a collaborative canvas for visual data exploration and dashboard creation.
