DataUSA / datausa-tracker

0 stars 0 forks source link

fix unmatched University geographies #320

Closed davelandry closed 3 years ago

davelandry commented 3 years ago

Test: Georgia (prod vs ruby)

mcperez2 commented 3 years ago

issue description: opeid and opeid6 was updated with a wrong number format where left zeros was cut in opeid.

Solution: the following sql queries figure out the problem in monetDB (backend)

the table ipeds_dims.university in postgres (zcube) was modified using the same sql queries.

create table ipeds_dims.university_backup_opeid as 
(select * from ipeds_dims.university)

create table ipeds_dims.university_opeid as 
(select * from ipeds_dims.university)

update ipeds_dims.university_opeid set opeid = lpad(opeid,8,'0'), opeid6 = left(lpad(opeid,8,'0'),6)

drop table ipeds_dims.university

create table ipeds_dims.university as 
(select * from ipeds_dims.university_opeid)

drop table ipeds_dims.university_opeid

next step: clean cache