[postgis-users] Which *.shp files is the TIGER GeoCoder referring to, and where do co99_d00 and pl99_d00 come from?

Stephen Woodbridge woodbri at swoodbridge.com
Sun Jul 13 16:36:41 PDT 2008

Not sure I can help more then to offer you encouragement at this point. 
It seems like you are very close at this point. I guess I need to get 
8.3 loaded on some system here and test out some of my code and see how 
much of that blows up.

It would be good if you would update the wiki with how you loaded the 
data and what you had to do. This question comes up frequently on the 
list so it would be good to be able to point people to a document on how 
to do it.


Josh Long wrote:
> Steve,
> Thanks again for your help.  Just about 5 hours ago I managed to
> finish the successul installation of ... well.. everything.. on
> Postgres 8.3. It took 2 days and near 100gbs, but its done..
> I ran my first test this morning with select geocode(  'address blah blah') ;
> waited eagerly
> and then it failed...
> complained about all sorts of casts (I guess this hasn't been tested
> or run on 8.3, which is fairly new..) from types/against types to
> text.. .
> I will try and take a look at the hundreds and hundreds of SQL for the
> functions, but I'm not sure I'll have a prayer...
> May have to revisit this at another time. Has anyone gotten this to
> work on 8.3?
> Thanks in advance,
> Josh
> On Sat, Jul 12, 2008 at 5:07 PM, Stephen Woodbridge
> <woodbri at swoodbridge.com> wrote:
>> Josh,
>> I have used the tiger geocoder so I'm only guessing ...
>> You only need the county and place files
>> download these and unzip them.
>> use shp2pgsql to load them, it has an option to specify the table name.
>> If there are more than one *.shp for either of these the use -c to create
>> the table on the first one and then use -a to append to the table for all
>> the rest in the set.
>> -Steve
>> Josh Long wrote:
>>> Thanks for the quick reply.
>>> I'm looking at the files
>>> (http://www.census.gov/geo/www/cob/bdy_files.html). Do I download them
>>> all? I'm just trying to get the Tiger geocoder to work... That is, I
>>> only see references to the places and counties lookup tables in the
>>> lookup_tables.sql -- no mention of any of these other datasets, per
>>> se.. Which boundry files correspond to those requirements? What's the
>>> installation procedure, I guess, is what I'm looking for.
>>> How do I ensure those tables get created? I'm looking at the
>>> cartographic boundry files
>>> (http://www.census.gov/geo/www/cob/bdy_files.html).. Do I need te
>>> congressional districts and so on?
>>> If I do manage to figure this all out (your help being very
>>> appreciated) I'll share a detailed tutorial walking from a fresh
>>> Postgres to the finish line..
>>> Thanks,
>>> Josh
>>> On Sat, Jul 12, 2008 at 3:43 PM, Stephen Woodbridge
>>> <woodbri at swoodbridge.com> wrote:
>>>> starbuxman wrote:
>>>>> Hello everyone,
>>>>> I'm trying to setup the TIGER geocoder but am new at this sort of thing:
>>>>> I've already finished the bit where i run import/load_tiger.sh
>>>>> successfully.
>>>>> I'm using the 2006se TIGER line data. I now have all the PostGIS (on
>>>>> Postgres 8.3) and two tables Landmarks and CompleteChain (with a
>>>>> whopping 66 million records!/ 25gb footprint!) that took 28 hours to
>>>>> install.
>>>>> Now it says to import the .shp files... Only question is, what shp
>>>>> files?
>>>>> Here is the listing of the typical archive:
>>>>> Archive:  TGR02164.ZIP
>>>>>  inflating: TGR02164.RT1
>>>>>  inflating: TGR02164.RT2
>>>>>  inflating: TGR02164.RT4
>>>>>  inflating: TGR02164.RT5
>>>>>  inflating: TGR02164.RT6
>>>>>  inflating: TGR02164.RT7
>>>>>  inflating: TGR02164.RT8
>>>>>  inflating: TGR02164.RTA
>>>>>  inflating: TGR02164.RTC
>>>>>  inflating: TGR02164.RTE
>>>>>  inflating: TGR02164.RTH
>>>>>  inflating: TGR02164.RTI
>>>>>  inflating: TGR02164.RTM
>>>>>  inflating: TGR02164.RTP
>>>>>  inflating: TGR02164.RTR
>>>>>  inflating: TGR02164.RTS
>>>>>  inflating: TGR02164.RTT
>>>>>  inflating: TGR02164.RTZ
>>>>>  inflating: TGR02164.MET
>>>>> Then, finally, looking ahead: I opened up the enxt step which is
>>>>> lookup_tables.sql and it in turn tries to create tables while drawing
>>>>> from tables called
>>>>>  co99_d00  and pl99_d00  -- where do these tables get created?  How?
>>>> These the the shapefiles that you need to load. You can download them
>>>> from
>>>> Census site look for the cartographic boundary files.
>>>>> I know that the 2007 data is now using .shp files (or at least that's
>>>>> my understanding) -- do I need to download those, as well? Does that
>>>>> introduce the risk of data that's not out of sync? For that matter,
>>>>> what do I do with that data in the first place?
>>>> No!, these are a totaly different format and will take a new process to
>>>> install them into the geocoder if it is even possible.
>>>> -Steve W
>>>>> Any help is greatly appreciated, thank you,
>>>>> Joshua Long
>>>>> http://www.joshlong.com/
>>>> _______________________________________________
>>>> postgis-users mailing list
>>>> postgis-users at postgis.refractions.net
>>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users

More information about the postgis-users mailing list