refactor: build location model properly using patient and organization as a source

[lawrenceadams]

This PR refactors the existing location model to have a valid PK to allow referencing from the patient and care_site tables. Previously the location table was orphaned and had duplicate entries.

The new referencing works by using a variation of address and city as a natural key to join on from other models.

---

I have some ongoing thoughts/issues:

• [ ] Unsure how best to join patient/care_site to location. I was going to use address and ZIP - but not all patients have ZIP codes. This may not matter at the moment, but want a setup that won't lead to duplicate joins if someone was to have a lot more synthea data input
  • We might want to join on more to prevent this in the future
• [ ] I've gone for the somewhat arbitrary ordering of row numbers here which is done for neatness/organization but may not be best
• [ ] At the moment all care_site_types are 0 and I think we can do better! Perhaps we can use some simple heuristics like presence of PCP to set this as 38004247 | Ambulatory Primary Care Clinic / Center|, but likely too much for this PR/for now!
• [ ] I've pushed location_source_value back to being NULL - this should probably be a concatenation of the available fields

There may be a far slicker approach to this but this is the best I can see for now! Happy to hear other's thoughts

[lawrenceadams]

Fair point - I considered this but thought we could get away without creating an extra model, but it's probably more elegant/scalable doing so

I can experiment with this! Will see if the hashing approach is neater

[katy-sadowski]

Thanks! I'm leaning towards creating the extra model either way because I like the idea of a unidirectional flow from stg-->int-->mart models. (In reality it might not be possible/practical to do this in all cases, but would like to give it a shot 😃 )

[lawrenceadams]

Agree! Best to keep it uniform

[lawrenceadams]

@katy-sadowski I think that's all done now! Happy to pick apart further - we're breaking fresh ground!

P.S. I hope the presentation went well!