San Francisco Tree Search

A simple Datasette demo application.

Try it out here: https://sf-trees.com/

The data comes from the San Francisco Department of Public Works.

How I built this

I used csvs-to-sqlite to convert their CSV file into a SQLite database like this:

csvs-to-sqlite Street_Tree_List.csv sf-trees.db \
    -c qLegalStatus -c qSpecies -c qSiteInfo \
    -c PlantType -c qCaretaker -c qCareAssistant \
    -f qLegalStatus -f qSpecies -f qAddress \
    -f qSiteInfo -f PlantType -f qCaretaker \
    -f qCareAssistant -f PermitNotes

I then deployed the resulting SQLite database using datasette publish now:

https://san-francisco.datasettes.com/sf-trees/Street_Tree_List

I composed a SQL query for searching the list of trees, using SQLite's full-text search feature:

select
    Latitude,
    Longitude,
    qSpecies.value as qSpecies,
    qAddress
from
    Street_Tree_List
    join qSpecies
        on Street_Tree_List.qSpecies = qSpecies.id
where
    Street_Tree_List.rowid in (
        select
            rowid
        from
            [Street_Tree_List_fts]
        where [Street_Tree_List_fts] match :search
    )

You can try this query out using Datasette.

Finally, I used Leaflet and Leaflet.markercluster to construct a simple search interface.