The locations and clusters endpoints represent the majority of Falling Fruit traffic. A few challenges stand in the way of optimizing these further:

• Each location can have many types. This makes filtering locations by type slower than if a location had only one type.
• In cities with dense tree inventories, there can be upwards of 100,000 locations to filter, even at zoom level 12-13 where clusters currently switch to locations. This results in slow location queries over these areas (seconds rather than milliseconds).
• Since there are thousands of types, we cannot index or cache locations for each type filter combination.

Simple things that could be done immediately include:

• Remove RANDOM ordering of returned locations. This is slow and results in different results to identical queries. To ensure that returned locations are spatially randomly distributed, use a generated and index random id column.
• Limit decimal places of returned location coordinates to 5–6.

But for real gains, deeper re-engineering will be needed. Ideas include:

• Restructure the database and/or the SQL query to speedup n-nearest-location searches when used concurrently with a type filter.
• Use cached vector tiles of locations / clusters for the most common filter combinations: muni (on/off) / type filter presets (all/forager/freegan).
• Cache arbitrary vector tiles on request to adapt to user preference dynamically.
• Perform filtering of locations on the client side.
• Either drop clusters (require users to zoom in to filter locations) or perform type filtering of clusters on the client side. This would require type counts for each grid cell. The center of mass of each type would also need to be included for cluster coordinates to be adjustable based on the filter.
• Switch to Mapbox.