Investigate alternative ways to storing geometry data from project table
Opened this issue · 2 comments
Currently, the districts column is stored as a jsonb field which stores JSON/GeoJSON on the project level. It holds derived districts data and can change through update transactions. jsonb has the flexibility of storing objects with non-fixed property/key list. Storing coordinates along with properties conforms with GeoJSON standards and allows geometry related operations. Meanwhile, such un-encoded coordinate data can be bulky to store and may take longer time in frequent update transactions.
This issue is to investigate alternative ways to storing geometry data from project table and check/test with some examples/instances if such alternative(s) can alleviate the above mentioned potential bottlenecks. An ideal result of this issue would be to have such alternatives listed out and be ready for more discussions in an ADR.
To @maurizi @BryanQuigley @dmcglone : please feel free to share more details and have discussions here
I think this would be a good situation for a bake-off; if I recall correctly, the GeoJSON-in-jsonb approach was taken to avoid high processing costs associated with encoding / decoding GeoJSON from/to PostGIS geometries, which has caused problems for us on other projects. So we might want to prototype a different approach to make sure that it doesn't just trade one set of problems for a different set.