add population density measure

[hwchen]

@jspeis is this a measure to add to yg_total_population?

[jspeis]

@hwchen sure. What do you think about simply adding in a col with the area (in sq miles) for each geo and then have a calculatedmember that would compute density? (pop/area)

[jspeis]

also I assume we'll want to base it on land area (not counting water area ... I seem to recall the shapes2017 tables may have had a aland column with the land area)

[hwchen]

There is an aland; it's in sq meters, which I'll convert to sq miles by dividing by 2,589,988, as per https://www.census.gov/quickfacts/fact/note/US/LND110210

I'm thinking of setting it as a property of the geo level. Have you used a property in a calculated member before?

[jspeis]

I've never tried that before

[hwchen]

Just wanted to double check: were you thinking of putting the land area in the fact table, or just using the one in the dim table? I'm wondering if I got something wrong, but I think that the area must be used as a property (from your comments above, I think you might be thinking of something else).

Since it's easy to return a property in the results, if I can't get a calculated member to work I'll just let the frontend do the calculation.

[jspeis]

Originally I was thinking of putting it in as a column in the fact table. But the approach you described could work for initial usage as well.

[hwchen]

I'm trying a little more to calculate it from a dim property.

Putting it in the fact table has two issues:

• during etl requires being able to pull in shapes tables. Not terrible, but adds complication.
• doing it after etl means doing a conditional join (for each geoid, join from the associated shape table). (Ironically, this part would be easier if we were using one col per geo).

[jspeis]

let me know how it goes!

[hwchen]

    <CalculatedMember name="Area sq miles" dimension="Measures">
      <Formula> CASE
            WHEN [Geography].[Tract].Level is [Geography].[Tract].[Tract] THEN (cast([Geography].[Tract].CurrentMember.Properties("Area") as integer) / 2589988)
            WHEN [Geography].[Place].Level is [Geography].[Place].[Place] THEN (cast([Geography].[Place].CurrentMember.Properties("Area") as integer) / 2589988)
            WHEN [Geography].[Zip].Level is [Geography].[Zip].[Zip] THEN (cast([Geography].[Zip].CurrentMember.Properties("Area") as integer) / 2589988)
            WHEN [Geography].[County].Level is [Geography].[County].[County] THEN (cast([Geography].[County].CurrentMember.Properties("Area") as integer) / 2589988)
            WHEN [Geography].[State].Level is [Geography].[State].[State] THEN (cast([Geography].[State].CurrentMember.Properties("Area") as integer) / 2589988)
            WHEN [Geography].[Nation].Level is [Geography].[Nation].[Nation] THEN (cast([Geography].[Nation].CurrentMember.Properties("Area") as integer) / 2589988)
            WHEN [Geography].[Puma].Level is [Geography].[Puma].[Puma] THEN (cast([Geography].[PUMA].CurrentMember.Properties("Area") as integer) / 2589988)
            WHEN [Geography].[Msa].Level is [Geography].[Msa].[Msa] THEN (cast([Geography].[MSA].CurrentMember.Properties("Area") as integer) / 2589988)
            <!--
            WHEN [Geography].[County Subdivision].Level is [Geography].[County Subdivision].[County Subdivision] THEN [Measures].[mea_county_subdivision]
            -->
            WHEN 1 = 1 THEN 0
            END
      </Formula>
    </CalculatedMember>

    <CalculatedMember name="Density" dimension="Measures">
      <Formula>
          [Measures].[Veterans].CurrentMember / [Measures].[Area sq miles]
      </Formula>
    </CalculatedMember>

[hwchen]

Live in lassen. https://github.com/Datawheel/acs-etl-rs/commit/9210baac92c4c2a32d2d05e45463146a131b3aa0