8586 |
Visualizing local election results with Datasette, Observable and MapLibre GL |
select
Precinct_name,
precincts.geometry,
total_ballots,
json_group_object(
candidate_name,
total_votes
) as votes_by_candidate
from
election_results
join precincts on election_results.Precinct_name = precincts.precinct_id
where
Contest_title = "Granada Community Services District Members, Board of Directors"
group by
Precinct_name,
precincts.geometry,
total_ballots; |
https://simonwillison.net/e/8586 |
8586 |
Visualizing local election results with Datasette, Observable and MapLibre GL |
sql = `
select
Precinct_name,
precincts.geometry,
Split_name,
Reporting_flag,
Update_count,
Pct_Id,
Pct_seq_nbr,
Reg_voters,
Turn_Out,
Contest_Id,
Contest_seq_nbr,
Contest_title,
Contest_party_name,
Selectable_Options,
candidate_id,
candidate_name,
Candidate_Type,
cand_seq_nbr,
Party_Code,
total_ballots,
total_votes,
total_under_votes,
total_over_votes,
[Vote Centers_ballots],
[Vote Centers_votes],
[Vote Centers_under_votes],
[Vote Centers_over_votes],
[Vote by Mail_ballots],
[Vote by Mail_votes],
[Vote by Mail_under_votes],
[Vote by Mail_over_votes]
from
election_results join precincts on election_results.Precinct_name = precincts.precinct_id
where "Contest_title" = "Granada Community Services District Members, Board of Directors"
limit 101;` |
https://simonwillison.net/e/8586 |
8586 |
Visualizing local election results with Datasette, Observable and MapLibre GL |
// Select the contest
viewof contest = Inputs.select(contests, { label: "Choose a contest" })
// ---
// And the candidate
viewof candidate = Inputs.radio(
candidates,
{
label: "Choose a candidate",
value: candidates[0]
}
)
// ---
// Show the map itself
Plot.plot({
width,
height: 600,
legend: true,
color: { scheme: "blues", legend: true },
projection: {
type: "mercator",
domain: data2
},
marks: [
Plot.geo(data2, {
strokeOpacity: 0.1,
fill: "ratio",
tip: true
})
]
})
# ---
data2 = ({
type: "FeatureCollection",
features: raw_data2.map((d) => ({
type: "Feature",
properties: {
precinct: d.Precinct_name,
total_ballots: d.total_ballots,
ratio: JSON.parse(d.votes_by_candidate)[candidate] / d.total_ballots
},
geometry: JSON.parse(d.geometry)
}))
})
// ---
raw_data2 = query(
`select
Precinct_name,
precincts.geometry,
total_ballots,
json_grop_object(
candidate_name,
total_votes
) as votes_by_candidate
from
election_results
join precincts on election_results.Precinct_name = precincts.precinct_id
where Contest_title = :contest
group by
Precinct_name,
precincts.geometry,
total_ballots;`,
{ contest }
)
// ---
raw_data2 = query(
`select
Precinct_name,
precincts.geometry,
total_ballots,
json_group_object(
candidate_name,
total_votes
) as votes_by_candidate
from
election_results
join precincts on election_results.Precinct_name = precincts.precinct_id
where Contest_title = :contest
group by
Precinct_name,
precincts.geometry,
total_ballots;`,
{ contest }
)
// ---
// Fetch the available contests
contests = query("select distinct Contest_title from election_results").then(
(d) => d.map((d) => d.Contest_title)
)
// ---
// Extract available candidates for selected contest
candidates = Object.keys(
JSON.parse(raw_data2[0].votes_by_candidate)
)
// ---
function query(sql, params = {}) {
return fetch(
`https://datasette-public-office-hours.datasette.cloud/data/-/query.json?${new URLSearchParams(
{ sql, _shape: "array", ...params }
).toString()}`,
{
headers: {
Authorization: `Bearer ${secret}`
}
}
).then((r) => r.json());
} |
https://simonwillison.net/e/8586 |
8480 |
Optimizing Datasette (and other weeknotes) |
select count(*) from (
select * from libfec_SA16 limit 10001
) |
https://simonwillison.net/e/8480 |
8480 |
Optimizing Datasette (and other weeknotes) |
select date(column_to_test) from (
select * from mytable limit 100
)
where column_to_test glob "????-??-*" |
https://simonwillison.net/e/8480 |
8480 |
Optimizing Datasette (and other weeknotes) |
select date(column_to_test) from (
select * from mytable
)
where column_to_test glob "????-??-*"
limit 100; |
https://simonwillison.net/e/8480 |
8382 |
Building search-based RAG using Claude, Datasette and Val Town |
// The SQL query from earlier
const sql = `select
blog_entry.id,
blog_entry.title,
blog_entry.body,
blog_entry.created
from
blog_entry
join blog_entry_fts on blog_entry_fts.rowid = blog_entry.rowid
where
blog_entry_fts match :search
order by
rank
limit
10`;
async function runSearch(keywords) {
// Turn the keywords into "word1" OR "word2" OR "word3"
const search = keywords.map(s => `"${s}"`).join(" OR ");
// Compose the JSON API URL to run the query
const params = new URLSearchParams({
search,
sql,
_shape: "array",
});
const url = "https://datasette.simonwillison.net/simonwillisonblog.json?" + params;
const result = await (await fetch(url)).json();
return result;
} |
https://simonwillison.net/e/8382 |
8382 |
Building search-based RAG using Claude, Datasette and Val Town |
select
blog_entry.id,
blog_entry.title,
blog_entry.body,
blog_entry.created
from
blog_entry
join blog_entry_fts on blog_entry_fts.rowid = blog_entry.rowid
where
blog_entry_fts match :search
order by
rank
limit
10 |
https://simonwillison.net/e/8382 |
8380 |
Language models on the command-line |
llm embed-multi links \
-d simonwillisonblog.db \
--sql 'select id, link_url, link_title, commentary from blog_blogmark' \
-m 3-small --store
|
https://simonwillison.net/e/8380 |
8380 |
Language models on the command-line |
select id, link_url, link_title, commentary from blog_blogmark
|
https://simonwillison.net/e/8380 |