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

Josh Long starbuxman at gmail.com
Sun Jul 13 19:35:13 PDT 2008


You read my mind. I was preparing those notes in .HTML format and
wanted to share 'em.

I posted them to
and will eventually look back around and convert the markup from HTML
to the wiki format.

If anybody wants to they're more than welcome to. I just don't have
the balance of the time for the day, is all.

Similarly, I've attached the HTML version to this email.

Any changes / upgrades / corrections (which I'm sure are all in some
capacity really merited!) are appreciated.


On Sun, Jul 13, 2008 at 4:36 PM, Stephen Woodbridge
<woodbri at swoodbridge.com> wrote:
> 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.
> -Steve
> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

Joshua Long
Sun Certified Java Programmer
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080713/23556981/attachment.html>

More information about the postgis-users mailing list