Re: [GENERAL] GIS/GPS Experiences with pgsql? - Mailing list pgsql-general

> Actually, it's just text.  Here's a sample record:
>
> 10003 43140280 B Smallwood Road A31 13131891899301893018 9501 9501 227
> 222  -82521645+33638976 -82528956+33639940
>
> ...the CD-ROM "database" is about 600MB.

i never looked at the tiger that was that old, but i know that the new
ones fill five discs (and all the data is zipped, so i'm thinking its in
the order of at 10 GB).  anyway, on to the topic at hand...

i've done some of this for work, so i don't think i can release any of
the source for importing tiger, plus i know that the tiger format has
changed since 92 (and 95), so you might have to do quite a bit of work
on it anyway.  all my comments are based on the data structure of the
tiger 95/97, so YMMV.

the data on the discs are pretty clean (normalized and logically
organized, with tlids and polyids as a primary key), so the biggest
chore is to convert the appropriate data into native geometric data
types.  essentially, convert all nodes into a point type, chains into a
path (open path), and polygons into polygons. then select out what parts
you want.  index the chains and polygons based on the bounding box using
rtrees, and you'll be pretty well set.  depending on your desired
accuracy (and the zoom of your map) you may want to look into using
different map projections to flatten out the data due to the earth's
curvature.  it shouldn't be bad if you're only drawing a map of 1 or 2
miles, though.  there are a couple of different free libraries to do
that sort of thing.

what i did was write a little c program to import the data as it was,
then another c program for each of the data conversions.  for example,
one of these programs was to make a chain into a native path type, so i
selected the beginning and ending points from type one, matched the tlid
to type 2, and created an insert statement with the coordinates
formatted properly to make a path (insert into chains (tlid, chain_path)
values ('12738127231',
'[(-90.1234,45.12342),(-90.34545,45.3984)]::path')

it's really pretty simple once you dive into the tiger structures
(there's a huge 250 page PDF that gives you all the necessary info, but
it's darn dry reading.)

>
> It should present no problem to extract the important data w/perl.
>
> In related news, I read on slashdot.org today, in the "Bruce Perens
> Resigns From OSI" article:
>
> "...I'm Bruce Perens. You may know me as the primary author of the
> Debian Free Software Guidelines and the Open Source Definition. I wrote
> the Electric Fence malloc() debugger, and some pieces of Debian. And you
> may remember me for having brought the TIGER map database to free
> software. If you want copies of that, you can get them through Dale
> Scheetz..."
>
> Anybody know WTF he is talking about?

he bought the tiger97 cds to donate to public, opensource software
projects (for example, one person that got them with the intent to build
a GPS navigation system on linux to use on an auto-pc).  i think you
have to explain to him a good reason for him to give you a copy.   they
are public domain, so once you have them, you can do anything you want
with them, it's just a matter of the $1500 to get them to start with.

jeff