search

Grist-Data-Desk / land-grab-2

Code and methodology to produce the dataset in Grist's Misplaced Trust investigation
https://grist.org/project/indigenous/land-grant-universities-indigenous-lands-fossil-fuels
Creative Commons Zero v1.0 Universal
15 stars 2 forks source link

Draft export flat file schema for downloaded university data #10

Closed mariaparazorose closed 1 year ago

mariaparazorose commented 1 year ago

Check out STL dataset and align as much as possible (likely don't need to consider managing agency/trust name)

clayton-aldern commented 1 year ago

This issue is dependent to #12

clayton-aldern commented 1 year ago

@mariaparazorose can you drop the link from #12 upon closing?

mariaparazorose commented 1 year ago

Link to Regrid data schema -- use this to help decide what fields to pull in from Regrid datset

clayton-aldern commented 1 year ago

Regrid parcel scheme link here

clayton-aldern commented 1 year ago

draft for STL:

table_name = 'STL'
fields = [GristDbField(name='object_id', constraints='varchar(50)'),
          GristDbField(name='state', constraints='char(2)'),
          GristDbField(name='state_enabling_act', constraints='varchar(50)'),
          GristDbField(name='trust_name', constraints='varchar(50)'),
          GristDbField(name='managing_agency', constraints='varchar(50)'),
          GristDbField(name='university', constraints='varchar(50)'),
          GristDbField(name='acres', constraints='double'),
          GristDbField(name='gis_acres', constraints='double'),
          GristDbField(name='rights_type', constraints='varchar(50)'),
          GristDbField(name='activity', constraints='varchar(50)'),
          GristDbField(name='reported_county', constraints='varchar(50)'),
          GristDbField(name='census_bureau_county_name', constraints='varchar(50)'),
          GristDbField(name='meridian', constraints='varchar(50)'),
          GristDbField(name='township', constraints='varchar(50)'),
          GristDbField(name='range', constraints='varchar(50)'),
          GristDbField(name='section', constraints='varchar(50)'),
          GristDbField(name='aliquot', constraints='varchar(50)'),
          GristDbField(name='block', constraints='varchar(50)'),
          GristDbField(name='data_source', constraints='varchar(200)'),
          GristDbField(name='all_cession_numbers', constraints='varchar(50)'),
          GristDbField(name='cession_num_01', constraints='varchar(50)'),
          GristDbField(name='C1_present_day_tribe', constraints='varchar(2000)'),
          GristDbField(name='C1_tribe_named_in_land_cessions_1784-1894', constraints='varchar(200)'),
          GristDbField(name='cession_num_02', constraints='varchar(50)'),
          GristDbField(name='C2_present_day_tribe', constraints='varchar(2000)'),
          GristDbField(name='C2_tribe_named_in_land_cessions_1784-1894', constraints='varchar(200)'),
          GristDbField(name='cession_num_03', constraints='varchar(50)'),
          GristDbField(name='C3_present_day_tribe', constraints='varchar(2000)'),
          GristDbField(name='C3_tribe_named_in_land_cessions_1784-1894', constraints='varchar(200)'),
          GristDbField(name='cession_num_04', constraints='varchar(50)'),
          GristDbField(name='C4_present_day_tribe', constraints='varchar(2000)'),
          GristDbField(name='C4_tribe_named_in_land_cessions_1784-1894', constraints='varchar(200)'),
          GristDbField(name='cession_num_05', constraints='varchar(50)'),
          GristDbField(name='C5_present_day_tribe', constraints='varchar(2000)'),
          GristDbField(name='C5_tribe_named_in_land_cessions_1784-1894', constraints='varchar(200)'),
          GristDbField(name='cession_num_06', constraints='varchar(50)'),
          GristDbField(name='C6_present_day_tribe', constraints='varchar(2000)'),
          GristDbField(name='C6_tribe_named_in_land_cessions_1784-1894', constraints='varchar(200)'),
          GristDbField(name='cession_num_07', constraints='varchar(50)'),
          GristDbField(name='C7_present_day_tribe', constraints='varchar(2000)'),
          GristDbField(name='C7_tribe_named_in_land_cessions_1784-1894', constraints='varchar(200)'),
          GristDbField(name='cession_num_08', constraints='varchar(50)'),
          GristDbField(name='C8_present_day_tribe', constraints='varchar(2000)'),
          GristDbField(name='C8_tribe_named_in_land_cessions_1784-1894', constraints='varchar(200)'),
          GristDbField(name='geometry', constraints='geometry'),
          GristDbField(name='geometryType', constraints='varchar(50)'),
          GristDbField(name='isRegrid', constraints='bool')]
clayton-aldern commented 1 year ago

draft for UL:

table_name = 'UL'
fields = [GristDbField(name='object_id', constraints='varchar(50)'),
          GristDbField(name='state', constraints='char(2)'),
          GristDbField(name='state_enabling_act', constraints='varchar(50)'),
          GristDbField(name='university', constraints='varchar(50)'),
          GristDbField(name='acres', constraints='float'),
          GristDbField(name='gis_acres', constraints='float'),
          GristDbField(name='rights_type', constraints='varchar(50)'),
          GristDbField(name='activity', constraints='varchar(50)'),
          GristDbField(name='reported_county', constraints='varchar(50)'),
          GristDbField(name='census_bureau_county_name', constraints='varchar(50)'),
          GristDbField(name='meridian', constraints='varchar(50)'),
          GristDbField(name='township', constraints='varchar(50)'),
          GristDbField(name='range', constraints='varchar(50)'),
          GristDbField(name='section', constraints='varchar(50)'),
          GristDbField(name='aliquot', constraints='varchar(50)'),
          GristDbField(name='block', constraints='varchar(50)'),
          GristDbField(name='data_source', constraints='varchar(200)'),
          GristDbField(name='all_cession_numbers', constraints='varchar(50)'),
          GristDbField(name='cession_num_01', constraints='varchar(50)'),
          GristDbField(name='C1_present_day_tribe', constraints='varchar(2000)'),
          GristDbField(name='C1_tribe_named_in_land_cessions_1784-1894', constraints='varchar(200)'),
          GristDbField(name='cession_num_02', constraints='varchar(50)'),
          GristDbField(name='C2_present_day_tribe', constraints='varchar(2000)'),
          GristDbField(name='C2_tribe_named_in_land_cessions_1784-1894', constraints='varchar(200)'),
          GristDbField(name='cession_num_03', constraints='varchar(50)'),
          GristDbField(name='C3_present_day_tribe', constraints='varchar(2000)'),
          GristDbField(name='C3_tribe_named_in_land_cessions_1784-1894', constraints='varchar(200)'),
          GristDbField(name='cession_num_04', constraints='varchar(50)'),
          GristDbField(name='C4_present_day_tribe', constraints='varchar(2000)'),
          GristDbField(name='C4_tribe_named_in_land_cessions_1784-1894', constraints='varchar(200)'),
          GristDbField(name='cession_num_05', constraints='varchar(50)'),
          GristDbField(name='C5_present_day_tribe', constraints='varchar(2000)'),
          GristDbField(name='C5_tribe_named_in_land_cessions_1784-1894', constraints='varchar(200)'),
          GristDbField(name='cession_num_06', constraints='varchar(50)'),
          GristDbField(name='C6_present_day_tribe', constraints='varchar(2000)'),
          GristDbField(name='C6_tribe_named_in_land_cessions_1784-1894', constraints='varchar(200)'),
          GristDbField(name='cession_num_07', constraints='varchar(50)'),
          GristDbField(name='C7_present_day_tribe', constraints='varchar(2000)'),
          GristDbField(name='C7_tribe_named_in_land_cessions_1784-1894', constraints='varchar(200)'),
          GristDbField(name='cession_num_08', constraints='varchar(50)'),
          GristDbField(name='C8_present_day_tribe', constraints='varchar(2000)'),
          GristDbField(name='C8_tribe_named_in_land_cessions_1784-1894', constraints='varchar(200)')
          GristDbField(name='geoid', constraints='varchar(200)')
          GristDbField(name='parcelnumb', constraints='varchar(200)')
          GristDbField(name='usecode', constraints='varchar(200)')
          GristDbField(name='usedesc', constraints='varchar(200)')
          GristDbField(name='zoning', constraints='varchar(200)')
          GristDbField(name='zoning_description', constraints='varchar(200)')
          GristDbField(name='struct', constraints='bool')
          GristDbField(name='multistruct', constraints='bool')
          GristDbField(name='structno', constraints='integer')
          GristDbField(name='yearbuilt', constraints='integer')
          GristDbField(name='structstyle', constraints='varchar(200)')
          GristDbField(name='parvaltype', constraints='varchar(200)')
          GristDbField(name='improvval', constraints='double')
          GristDbField(name='landval', constraints='double')
          GristDbField(name='parval', constraints='double')
          GristDbField(name='agval', constraints='double')
          GristDbField(name='saleprice', constraints='double')
          GristDbField(name='saledate', constraints='date')
          GristDbField(name='taxamt', constraints='double')
          GristDbField(name='owntype', constraints='varchar(200)')
          GristDbField(name='owner', constraints='varchar(200)')
          GristDbField(name='ownfrst', constraints='varchar(200)')
          GristDbField(name='ownlast', constraints='varchar(200)')
          GristDbField(name='owner2', constraints='varchar(200)')
          GristDbField(name='owner3', constraints='varchar(200)')
          GristDbField(name='owner4', constraints='varchar(200)')
          GristDbField(name='subsurfown', constraints='varchar(200)')
          GristDbField(name='subowntype', constraints='varchar(200)')
          GristDbField(name='mailadd', constraints='varchar(200)')
          GristDbField(name='address_source', constraints='varchar(200)')
          GristDbField(name='legaldesc', constraints='varchar(200)')
          GristDbField(name='plat', constraints='varchar(200)')
          GristDbField(name='book', constraints='varchar(200)')
          GristDbField(name='page', constraints='varchar(200)')
          GristDbField(name='block', constraints='varchar(200)')
          GristDbField(name='lot', constraints='varchar(200)')
          GristDbField(name='neighborhood', constraints='varchar(200)')
          GristDbField(name='subdivision', constraints='varchar(200)')
          GristDbField(name='qoz', constraints='varchar(200)')
          GristDbField(name='census_block', constraints='varchar(200)')
          GristDbField(name='census_blockgroup', constraints='varchar(200)')
          GristDbField(name='census_tract', constraints='varchar(200)')
          GristDbField(name='sourceurl', constraints='varchar(200)')
          GristDbField(name='recrdareano', constraints='double')
          GristDbField(name='gisacre', constraints='double')
          GristDbField(name='ll_gisacre', constraints='double')
          GristDbField(name='sqft', constraints='double')
          GristDbField(name='ll_gissqft', constraints='double')
          GristDbField(name='reviseddate', constraints='date')
          GristDbField(name='ll_uuid', constraints='varchar(200)')
          GristDbField(name='padus_public_access', constraints='varchar(200)')
          GristDbField(name='lbcs_activity', constraints='integer')
          GristDbField(name='lbcs_activity_desc', constraints='varchar(200)')
          GristDbField(name='lbcs_function', constraints='integer')
          GristDbField(name='lbcs_function_desc', constraints='varchar(200)')
          GristDbField(name='lbcs_structure', constraints='integer')
          GristDbField(name='lbcs_structure_desc', constraints='varchar(200)')
          GristDbField(name='lbcs_site', constraints='integer')
          GristDbField(name='lbcs_site_desc', constraints='varchar(200)')
          GristDbField(name='lbcs_ownership', constraints='integer')
          GristDbField(name='lbcs_ownership_desc', constraints='varchar(200)')
          GristDbField(name='lat', constraints='varchar(200)')
          GristDbField(name='lon', constraints='varchar(200)')
          GristDbField(name='taxyear', constraints='varchar(200)')
          GristDbField(name='ll_address_count', constraints='integer')
          GristDbField(name='homestead_exemption', constraints='varchar(200)')
          GristDbField(name='alt_parcelnumb1', constraints='varchar(200)')
          GristDbField(name='alt_parcelnumb2', constraints='varchar(200)')
          GristDbField(name='alt_parcelnumb3', constraints='varchar(200)')
          GristDbField(name='parcelnumb_no_formatting', constraints='varchar(200)')
          GristDbField(name='plss_township', constraints='varchar(200)')
          GristDbField(name='plss_section', constraints='varchar(200)')
          GristDbField(name='plss_range', constraints='varchar(200)'),
          GristDbField(name='geometry', constraints='geometry'),
          GristDbField(name='geometryType', constraints='varchar(50)'),
          GristDbField(name='isRegrid', constraints='bool')]
laanak08 commented 1 year ago

if interested in Postgres Geo types