Office of Planning, Environment, & Realty (HEP)
Planning
Converting Summary File 1 (SF1) Data into an Oracle platform
Note: This note was posted as e-mail to the CTPP listserve. These notes are posted for guidance and help. If you have questions on SF1 or other Census 2000 products, please contact your State Data Center.
Here is a procedure used by Darryl Scott, South Western Regional Planning Agency to convert SF1 data into an Oracle platform.
INCOMING E-mail from Darryl Scott,
Here is my experience with importing the data into Oracle.
My goal was to load all the data for the State of Connecticut, all Counties in Connecticut, all Towns in Connecticut, and all tracts, block groups, and blocks in Fairfield County from Census 2000 Summary File 1 into Oracle and link it to GIS. Below are the steps I took to accomplish that task. I do not describe the failed attempts in detail.
---
Create view ctfcgeo as
SELECT sumlev, geocomp, logrecno, state, county, cousub, cousubcc, place,
placecc, placedc, tract, blkgrp, block, msacmsa, cmsa, macci,
pmsa, necma, necmacci, ua, uatype, ur, sldu, sldl, vtd, vtdi, zcta3, zcta5,
arealand, areawatr, arealand/2590000 as area_sqmi, name,
funcstat, gcuni, pop100, hu100, sdelm, sdsec, sduni, taz, macc, uacp, stfid
FROM ctgeo
WHERE (sumlev = '040' and geocomp='00') or (sumlev='050') or
(sumlev = '060' and Name Like '% town') or (sumlev = '140' and county =
'001') or (sumlev = '150' and county = '001') or (sumlev = '101' and county = '001');
---
---
mypath="d:\gis\gisdata_temp\Census2000\sumfile1\"
file_list={"ct00005","ct00006","ct00007","ct00008","ct00009",
"ct00010","ct00011","ct00012","ct00013","ct00014","ct00015","ct00016","ct000
17","ct00018","ct00019",
"ct00020","ct00021","ct00022","ct00023","ct00024","ct00025","ct00026","ct000
27","ct00028","ct00029",
"ct00030","ct00031","ct00032","ct00033","ct00034","ct00035","ct00036","ct000
37","ct00038","ct00039"}
rec_file=TextFile.Make((mypath+"ctfcgeo_logrecno.txt").AsFileName,#FILE_PERM
_READ)
rec_source=rec_file.Read(rec_file.GetSize)
rec_file.Close
rec_list_string = {}
rec_list_number = {}
rec_list_string=rec_source.AsTokens(nl)
for each rec in rec_list_string
rec_list_number.add(rec.AsNumber)
end
for each file_prefix in file_list
data_file_name=(mypath+file_prefix+".uf1").AsFileName
newdata_file_name=(mypath+file_prefix+".dat").AsFileName
data_file=LineFile.Make(data_file_name,#FILE_PERM_READ)
newdata_file=LineFile.Make(newdata_file_name,#FILE_PERM_WRITE)
for each i in 0..(data_file.GetSize-1)
dline=data_file.ReadElt
if (dline=nil) then continue end
dline=dline.Right(dline.Count-15)
d_recno=dline.Left(7)
d_num=d_recno.AsNumber
results=rec_list_number.FindByValue(d_num)
if (results= -1) then continue end
newdata_file.WriteElt(dline)
end
newdata_file.Close
data_file.Close
end
msgbox.info("Finished Processing Files","Script")
---
That file saved hours of work because I was able to create most of my SQL and control files from it. I still had to explore the data files to see which fields had decimal places, but fortunately most of the data did not have decimal places. I also added the number of the datafile to the logrecno field name to make sure that each table had unique fields (e.g. logreno1 for ct00001, logrecno2 for ct00002, and so on.) I also used TextPad
(http://www.textpad.com) to create the SQL and control files because of its ability to select text as blocks rather than lines. The SQL and control files worked and I was able to import the data from SF1 into Oracle.
---
alter table ctgeo add
stfid VARCHAR2(16);
update ctgeo
set stfid = state||county||tract
where sumlev='140';
update ctgeo
set stfid = state||county||tract||blkgrp
where sumlev='150';
update ctgeo
set stfid = state||county||tract||block
where sumlev='101';
---
It took a little bit longer than expected, but I accomplished my goals. I imported the sf1 data into Oracle and became able to produce thematic maps in ArcView. Because of this approach, the sf1 data can be used in ArcView, Microsoft Access, and any other software that can connect to the Oracle database through an ODBC connection.
Some of the codes I used are documented in the file sf1_oracle_sql.zip.