Sean Ferenci wrote:
> I am going through your documentation on how to load Tiger data that was
> at:
> http://geoserver.org/display/GEOSDOC/Loading+TIGER+polygons
> I cannot find either the tigerpoly.py script nor the recommended
> tigerpolygonformation.zip fil
> Thanks in advance for your help.
As others pointed out, you almost certainly want to use the newer
tiger files that are distributed as shapefiles; but if you really
need to load the old TIGER format, attached is a little C program
that converts the old files to postgres/postgis insert statements.
#include <stdlib.h> // exit
#include <string.h> // strncmp
#include <sys/types.h> // open
#include <sys/stat.h> // open
#include <fcntl.h> // open
#include <stdio.h> // printf
#include <unistd.h> // read
enum fmt_enum {L,R};
enum typ_enum {A,N};
int gettgrfilenum(char *a) {
int n=0;
int i=0;
for (i=0;a[i]!=0;i++); // end
i-=5; // skip ext
if (i<4) return -1; // long enough
n= (a[i ]-'0')
+ 10*(a[i-1]-'0')
+ 100*(a[i-2]-'0')
+ 1000*(a[i-3]-'0')
+ 10000*(a[i-4]-'0');
return n;
}
int main(int argc, char *argv[]) {
if (argc < 3) {
fprintf(stderr,"Usage: tgr2sql [rt1|rt2] <filename>.RT1 [<filename>.RT1 ...] \n");
}
int n;
char rt1_buf[230];
int rt1_lengths[] = {1,4,10,1,1,2,30,4,2,3,11,11,11,11,1,1,1,1,5,5,5,5,1,1,1,1,2,2,3,3,5,5,5,5,5,5,6,6,4,4,10,9,10,9};
int rt1_fmt [] = {L,L,R ,R,L,L,L ,L,L,L,R ,R ,R ,R ,L,L,L,L,L,L,L,L,L,L,L,L,L,L,L,L,L,L,L,L,L,L,L,L,L,L,R ,R,R ,R};
int rt1_typ [] = {A,N,N ,N,A,A,A ,A,A,A,A ,A ,A ,A ,A,A,A,A,N,N,N,N,A,A,A,A,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N ,N,N ,N};
char rt2_buf[210];
int rt2_lengths[] = {1,4,10,3,10,9,10,9,10,9,10,9,10,9,10,9,10,9,10,9,10,9,10,9};
int rt2_fmt [] = {L,L,R ,R,R ,R,R ,R,R ,R,R ,R,R ,R,R ,R,R ,R,R ,R,R ,R,R ,R};
int rt2_typ [] = {A,N,N ,N,N ,N,N ,N,N ,N,N ,N,N ,N,N ,N,N ,N,N ,N,N ,N,N ,N};
char rt4_buf[58+2];
int rt4_lengths[] = {1,4,10,3,8,8,8,8,8};
int rt4_fmt [] = {L,L,R ,R,R,R,R,R,R};
int rt4_typ [] = {A,N,N ,N,N,N,N,N,R};
char rt5_buf[56+2];
int rt5_lengths[] = {1,4,5,8,2,30,4,2};
int rt5_fmt [] = {L,L,L,R,L,L ,L,L};
int rt5_typ [] = {A,N,N,N,A,A ,A,A};
char rt7_buf[74+2];
int rt7_lengths[] = {1,4,5,10,1,3,30,10,9,1};
int rt7_fmt [] = {L,L,L,R ,L,L,L ,R ,R,L};
int rt7_typ [] = {A,N,N,N ,A,A,A ,N ,N,A};
char rt8_buf[36+2];
int rt8_lengths[] = {1,4,5,5,10,10,1};
int rt8_fmt [] = {L,L,L,L,R ,R ,L};
int rt8_typ [] = {A,N,N,A,N ,N ,A};
char rt6_buf[76+2];
int rt6_lengths[] = {1,4,10,3,11,11,11,11,1,1,1,1,5,5};
int rt6_fmt [] = {L,L,R ,R,R ,R ,R ,R ,L,L,L,L,L,L};
int rt6_typ [] = {A,N,N ,N,A ,A ,A ,A ,A,A,A,A,N,N};
char rti_buf[127+2];
int rti_lengths[] = {1,4,5,10,10,10,5,10,5,10,10,17,10,10,10};
int rti_fmt [] = {L,L,L,R ,R ,R ,L,R ,L,R ,L ,L ,L ,L ,L};
int rti_typ [] = {A,N,N,N ,N ,N ,A,N ,A,N ,A ,A ,A ,A ,A};
char rtc_buf[122+2];
int rtc_lengths[] = {1,4,2,3,4,5,2,1,2,1,4,5,4,6,5,3,3,4,1,1,60};
int rtc_fmt [] = {L,L,L,L,L,L,L,L,L,L,L,L,L,R,L,L,L,L,L,L,L };
int rtc_typ [] = {A,N,N,N,A,N,A,A,A,A,N,N,N,A,N,N,N,N,N,N,A };
char rta_buf[210+2];
int rta_lengths[] = {1,4,5,5,10,2,3,6,4,1,1,5,4,1,5,3,5,5,5,5,5,5,5,5,4,4,4,2,5,3,6,3,3,5,6,6,5,3,5,3,5,5,4,5,1,6,6,11};
int rta_fmt [] = {L,L,L,L,L ,L,L,L,L,L,L,L,L,L,L,L,L,L,L,L,L,L,L,L,L,L,L,R,L,R,R,R,R,R,R,L,L,L,L,L,L,L,L,L,L,L,L,L };
int rta_typ [] = {A,N,N,A,N ,N,N,N,N,A,A,N,N,A,N,N,N,N,N,N,N,A,A,A,N,N,N,N,A,A,A,A,A,A,A,A,N,N,N,N,N,N,A,A,A,A,A,A };
char rtp_buf[45+2];
int rtp_lengths[] = {1,4,5,5,10,10,9,1};
int rtp_fmt [] = {L,L,L,L,R ,R ,R,L};
int rtp_typ [] = {A,N,N,A,N ,N ,N,N};
char *buf ;
int *lengths ;
int *fmt ;
int *typ ;
int numlen;
int buflen;
char *tablename;
char *columnnames;
if (strncmp(argv[1],"rt1",3) == 0) {
buf = rt1_buf ;
lengths = rt1_lengths;
fmt = rt1_fmt ;
typ = rt1_typ ;
numlen = sizeof(rt1_lengths)/sizeof(int);
buflen = sizeof(rt1_buf);
tablename = "tgr.rt1";
columnnames = "rt,version,tlid,side1,source,fedirp,fename,fetype,fedirs,"
"cfcc,fraddl,toaddl,fraddr,toaddr,friaddl,toiaddl,friaddr,"
"toiaddr,zipl,zipr,aianhhfpl,aianhhfpr,aihhtlil,aihhtlir,"
"census1,census2,statel,stater,countyl,countyr,cousubl,"
"cousubr,submcdl,submcdr,placel,placer,tractl,tractr,"
"blockl,blockr,frlong,frlat,tolong,tolat";
} else if (strncmp(argv[1],"rt2",3) == 0) {
buf = rt2_buf ;
lengths = rt2_lengths;
fmt = rt2_fmt ;
typ = rt2_typ ;
numlen = sizeof(rt2_lengths)/sizeof(int);
buflen = sizeof(rt2_buf);
tablename = "tgr.rt2";
columnnames = "rt,version,tlid,rtsq,long1,lat1,long2,lat2,long3,lat3,long4,lat4,long5,lat5,long6,lat6,long7,lat7,long8,lat8,long9,lat9,long10,lat10";
} else if (strncmp(argv[1],"rt4",3) == 0) {
buf = rt4_buf ;
lengths = rt4_lengths;
fmt = rt4_fmt ;
typ = rt4_typ ;
numlen = sizeof(rt4_lengths)/sizeof(int);
buflen = sizeof(rt4_buf);
tablename = "tgr.rt4";
columnnames = "rt,version,tlid,rtsq,feat1,feat2,feat3,feat4,feat5";
} else if (strncmp(argv[1],"rt5",3) == 0) {
buf = rt5_buf ;
lengths = rt5_lengths;
fmt = rt5_fmt ;
typ = rt5_typ ;
numlen = sizeof(rt5_lengths)/sizeof(int);
buflen = sizeof(rt5_buf);
tablename = "tgr.rt5";
columnnames = "rt,version,file,feat,fedirp,fename,fetype,fedirs";;
} else if (strncmp(argv[1],"rtp",3) == 0) {
buf = rtp_buf ;
lengths = rtp_lengths;
fmt = rtp_fmt ;
typ = rtp_typ ;
numlen = sizeof(rtp_lengths)/sizeof(int);
buflen = sizeof(rtp_buf);
tablename = "tgr.rtp";
columnnames = "rt,version,file,cenid,polyid,polylong,polylat,water";
} else if (strncmp(argv[1],"rt7",3) == 0) {
buf = rt7_buf ;
lengths = rt7_lengths;
fmt = rt7_fmt ;
typ = rt7_typ ;
numlen = sizeof(rt7_lengths)/sizeof(int);
buflen = sizeof(rt7_buf);
tablename = "tgr.rt7";
columnnames = "rt,version,file,land,source,cfcc,laname,lalong,lalat,filler";
} else if (strncmp(argv[1],"rt6",3) == 0) {
buf = rt6_buf ;
lengths = rt6_lengths;
fmt = rt6_fmt ;
typ = rt6_typ ;
numlen = sizeof(rt6_lengths)/sizeof(int);
buflen = sizeof(rt6_buf);
tablename = "tgr.rt6";
columnnames = "rt,version,tlid,rtsq,fraddl,toaddl,fraddr,toaddr,friaddl,toiaddl,friaddr,toiaddr,zipl,zipr";
} else if (strncmp(argv[1],"rt8",3) == 0) {
buf = rt8_buf ;
lengths = rt8_lengths;
fmt = rt8_fmt ;
typ = rt8_typ ;
numlen = sizeof(rt8_lengths)/sizeof(int);
buflen = sizeof(rt8_buf);
tablename = "tgr.rt8";
columnnames = "rt,version,file,cenid,polyid,land,filler";
} else if (strncmp(argv[1],"rti",3) == 0) {
buf = rti_buf ;
lengths = rti_lengths;
fmt = rti_fmt ;
typ = rti_typ ;
numlen = sizeof(rti_lengths)/sizeof(int);
buflen = sizeof(rti_buf);
tablename = "tgr.rti";
columnnames = "rt,version,file,tlid,tzids,tzide,cenidl,polyidl,cenidr,polyidr,source,ftseg,rsi1,rsi2,rsi3";
} else if (strncmp(argv[1],"rtc",3) == 0) {
buf = rtc_buf ;
lengths = rtc_lengths;
fmt = rtc_fmt ;
typ = rtc_typ ;
numlen = sizeof(rtc_lengths)/sizeof(int);
buflen = sizeof(rtc_buf);
tablename = "tgr.rtc";
columnnames = "rt,version,state,county,datayr,fips,fipscc,placedc,lsadc,entity,ma,sd,aianhh,vtdtract,uauga,aitsce,csacnecta,cbsanecta,commreg,rsc2,name";
} else if (strncmp(argv[1],"rta",3) == 0) {
buf = rta_buf ;
lengths = rta_lengths;
fmt = rta_fmt ;
typ = rta_typ ;
numlen = sizeof(rta_lengths)/sizeof(int);
buflen = sizeof(rta_buf);
tablename = "tgr.rta";
columnnames = "rt,version,file,cenid,polyid,statecu,countycu,tract,block,blocksufcu,rsa1,aianhhfpcu,aianhhcu,aihhtlicu,anrccu,aitscecu,aitscu,concitcu,cousubcu,submcdcu,placecu,sdelmcu,sdseccu,sdunicu,rsa20,rsa21,rsa22,cdcu,zcta5cu,zcta3cu,rsa4,rsa5,rsa6,rsa7,rsa8,rsa9,cbsacu,csacu,nectacu,cnectacu,metdivcu,nectadivcu,rsa14,rsa15,rsa16,rsa17,rsa18,rsa19";
} else {
printf("unsupported type %s\n",argv[1]);
exit(0);
}
#define SQL
int counter=0;
int tgrnum = -1;
while (argc>=3) {
fprintf(stderr,"file %s\n",argv[2]);
tgrnum = gettgrfilenum(argv[2]);
//fprintf(stderr,"HI %d\n",tgrnum);
int fd = open(argv[2],O_RDONLY);
#ifdef SQL
printf("BEGIN;\n");
#endif
while ( (n=read(fd,buf,buflen))>0 ) {
counter++;
if (counter%1000==0) fprintf(stderr,"line %d\n",counter);
#ifdef POSTGRES_COPY_TABLE
{
int j=0;
for (int i=0;i<numlen;i++) {
for (int k=0;k<lengths[i];k++)
putchar(buf[j++]);
putchar((i<numlen-1) ? '\t' : '\n');
}
}
#endif
#ifdef SQL
{
int j=0;
printf("insert into %s (tigerfile,%s) values (%d,",tablename,columnnames,tgrnum);
for (int i=0;i<numlen;i++) {
if (R == fmt[i] && N == typ[i]) {
int done = 0;
for (int k=0;k<lengths[i];k++) {
if (buf[j]!=' ') done = 1;
putchar(buf[j++]);
}
if (!done) printf("null");
} else {
int trim=1;
int rtrim = lengths[i]-1;
while (rtrim > 0 && buf[j+rtrim]==' ') rtrim --;
putchar('\'');
for (int k=0;k<=rtrim;k++) {
if (buf[j+k] == ' ' && trim==1) {continue;}
trim = 0;
putchar(buf[j+k]);
if (buf[j+k] == '\'') putchar(buf[j+k]);
}
j+=lengths[i];
putchar('\'');
}
if (i<numlen-1) putchar(',');
}
printf(");");
if (counter % 100 == 0) printf("\n");
}
#endif
}
#ifdef SQL
printf("\nCOMMIT;\n");
#endif
close(fd);
argv++;
argc--;
}
}
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
