Decide how to handle null coordinate point entries in the Soft-Story properties dataset

[agennadi]

Context

The Soft-Story properties dataset currently contains 4941 rows, with approximately 10 rows having null values in the point field. Valid point values are essential for accurately displaying soft-story properties on the map. These missing points need to be resolved during the transformation process of the dataset.

One potential approach is to load Addresses dataset to look up the missing points based on property addresses. However, loading 400,000 addresses to resolve 10 missing points may be excessive.

Alternatively, the missing points could be added manually. While this might work for now, it is not a scalable solution for future cases.

Research is needed to identify the optimal method for handling null values in the dataset.

Definition of Done

• The optimal solution for handling null values is identified.
• The solution is shared with the backend team.

Technical Details

Check out the Soft-Story properties dataset.
Relevant ETL code can be found in backend/etl/soft_story_properties_data_handler.py.

[leela-solomon]

1. Add source column to track the nulls which we filled in.
2. Fill in the null coordinates manually or with easy automation.

[elucherini]

We can write manual UPDATE statements to fill in the nulls, making sure we also update the source column. Alternatively, we could keep a separate table and left-join it to the original, but then we end up with two tables to maintain. I think option 1 is good enough for 10 missing points.

The data source can be MapBox. The simplest solution is to use this: https://docs.mapbox.com/playground/geocoding/ , or have ChatGPT write a simple script using our API key.