Community District values are incorrect

[AmandaDoyle]

Community districts do not nest with census boundaries; therefore, they should not be created via a lookup with census blocks or tracts. This issue may also apply to council districts.

Community Districts and Council Districts are assigned here

Potential solution - use the format of other geographies, where we get the value from GeoSupport if it exists, or if it does not from a spatial join. See example here.

Confirmed that these fields exist in GEO_devdb We would need to add the geo lookup function for Community District and Council Districts here

[td928]

I think the issue might be first introduced here when we added 2020 geographies. It was a lot of new codes for refactoring _spatial.sql and this one minor detail slipped introducing new logics to city council and community districts as well as the joining happening on census tract vs. census block. This happened if I remember correctly right around Baiyue was leaving with a lot of turmoil for the team.

@AmandaDoyle your diagnosis and solution all make sense to me going to start going to work on this and testing now.

[td928]

one follow-up question as I am implementing the changes for city council, I stumbled on this piece of code for creating csd

CREATE OR REPLACE FUNCTION get_csd(
    _geom geometry
  ) 
    RETURNS varchar AS $$
      SELECT  coundist::varchar
      FROM dcp_councildistricts b
      WHERE ST_Within(_geom, b.wkb_geometry)
      LIMIT 1
  $$ LANGUAGE sql;

and then

MID_devdb.geo_csd as schoolcommnty,

which based on our wiki is supposed to be school community district. Based on my reading of the code above and also data library template this seems the same as city council district. What do you think? @AmandaDoyle

[AmandaDoyle]

@td928 thanks for flagging the above. Unfortunately, I think it was an error that was an error that was introduced in a refactor. The existing function that calls FROM dcp_councildistricts b should be renamed to get_council or something intuitive. Then, a new get_csd function should be created using the logic found here. Naturally downstream changes should be made as needed.

[td928]

@td928 thanks for flagging the above. Unfortunately, I think it was an error that was an error that was introduced in a refactor. The existing function that calls FROM dcp_councildistricts b should be renamed to get_council or something intuitive. Then, a new get_csd function should be created using the logic found here. Naturally downstream changes should be made as needed.

that makes sense. Thanks @AmandaDoyle!