Spike: Data Loading Checklist for Socrata API Metrics #1987
Description
Dependency
- Reopen when there is a need to analyze these results, or if the user flow is modified and it changes the amount of data we might expect to load
Overview
We need to create a process for measuring Socrata API response times, given our use-cases, so that the design team knows how long their loading-screen content should remain on-screen.
More Info (optional)
Design team is wanting to know whether to skip certain flows for very-fast response times, in the case that data always takes a certain time to load. I.e., we want to obtain a general minimum load time.
Action Items
- Copy the template from the copy block in Resources into a comment
Resources/Instructions
### System Information
- Platform: (Postman, Thunder Client, etc)
- Request Status: (Open / Closed)
- Request Shortname (i.e. : Open Tickets From 2025 March, Electronic Waste)
- Request Duration (e.g. 10.57 seconds)
<paste your request body here with triple backticks before and after>
- Parent Ticket: Epic: Prevent Map Crashing and Reduce Page Load Time #1890
Query Example: Open Tickets From 2025 March, Electronic Waste
https://data.lacity.org/resource/h73f-gn57.json?$where=status = 'Open' AND createddate between '2025-06-04' and '2025-06-18' AND requesttype = 'Electronic Waste'
Query Example: Open Tickets From 2025 Last Two Weeks, Bulky Items or Animal Remains
https://data.lacity.org/resource/h73f-gn57.json?$where=status = 'Open' AND createddate between '2025-06-04' and '2025-06-18' AND (requesttype = 'Bulky Items' OR requesttype = 'Animal Remains')
Query Example: Number of SRs Grouped By NC
https://data.lacity.org/resource/b7dx-7gc3.json?$select=ncname, nc as nc_id, status, COUNT(status) as total_open&$group=ncname, nc_id, status&$where=status = 'Open' AND createddate between '2024-10-26' and '2024-10-29T23:59:59'
Resource Base URLs
2025
https://data.lacity.org/resource/h73f-gn57.json
2024
https://data.lacity.org/resource/b7dx-7gc3.json
2023
https://data.lacity.org/resource/4a4x-mna2.json
All Service Request Types
[
{
"requesttype": "Bulky Items",
"total": "167505"
},
{
"requesttype": "Dead Animal Removal",
"total": "6508"
},
{
"requesttype": "Electronic Waste",
"total": "9849"
},
{
"requesttype": "Feedback",
"total": "278"
},
{
"requesttype": "Graffiti Removal",
"total": "121791"
},
{
"requesttype": "Homeless Encampment",
"total": "24031"
},
{
"requesttype": "Illegal Dumping Pickup",
"total": "32735"
},
{
"requesttype": "Metal/Household Appliances",
"total": "26074"
},
{
"requesttype": "Multiple Streetlight Issue",
"total": "8149"
},
{
"requesttype": "Other",
"total": "2791"
},
{
"requesttype": "Report Water Waste",
"total": "357"
},
{
"requesttype": "Single Streetlight Issue",
"total": "6219"
}
]How does our app __currently__ query data
This code block was abridged to focus on the specific fields we are querying, i.e. CreatedDate !
async getAllRequests(startDate, endDate) {
const { conn } = this.context;
// ...
let selectSQL = '';
try {
if (startYear === endYear) {
// ...
const tableName = `requests_${startYear}`;
selectSQL = `SELECT * FROM ${tableName} WHERE CreatedDate BETWEEN '${startDate}' AND '${endDate}'`;
} else {
// If the dates span multiple years, create two queries and union them.
// ...
}
// ...
const requestsAsArrowTable = await conn.query(selectSQL);
const requests = ddbh.getTableData(requestsAsArrowTable);
return requests;
} catch (e) {
console.error("Error during database query execution:", e);
}
}Notes on CreatedDate
There is one aspec-t that bothers me about how we are utilizing StartDate and EndDate: we are using these fields to filter the CreatedDate response field. But what question does this really answer? So, if I say, give me all of the Service Requests that were created-on June 1st through June15, that sounds useful for seeing what SRs were made in the last two weeks. But, what does it mean to ask "give me all of the SRs that were created-on Jan 1st through June 1st"?
Let's consider the two statuses, Open and Closed. Looking at all Open SRs created in the last two weeks is still useful -- it tells you "what's the latest activity (ongoing)". Finding all the Closed SRs created in the last two weeks gives you a slightly different lens: "what and where are all the quick-closing tickets?" Perhaps this can be used as a means to determine "which NCs close tickets very quickly", or "which SR types get closed very quickly". This also leads you to ask, "are these tickets closing very quickly because the responsible party is actually well resourced and is able to quickly address problems?". Conversely, you could ask, what NCs are closing tickets very quickly without actually solving the problem -- why are they allowed to do that? Are there any responsible parties that might want to close tickets quickly on their system, just to give the impression that they are actually being serviced?
Ramblings to continue...