GIS/GPS Experiences with pgsql?

On Tue, 16 Feb 1999, Bryan Mattern wrote:

I'm working on a personal project to develop some sort of GPS software
for Linux. I was talking with a friend the other day and he let me
borrow a "TIGER/Line CD" (!1990!) from the US Bureau of the Census. I
know the data is old, but I just wanted to test it with PostgreSQL. I
don't know all that much about GIS maps, there might be a better source
I don't know about, but the data seems pretty easy to rip apart and
pitch into PostgreSQL.

Has anyone done anything like this? If so, is there any info on the
web? I checked the old mailing list archives, but they were not much
help.

I'm using perl to get lat./long./velocity from a GPS sensor and would
like to extract data from PostgreSQL to draw/label the maps.

Any ideas or help would be great.

Hmmm, I'm not sure how the TIGER/Line CD will import, as I don't know the
format, but you'll want to look at the geometric types that PostgreSQL
supports.

Infact, it was both GIS and Astronomy that made me implement their support
in the JDBC driver in the first place :-)

If the TIGER/Line data is raster, and each feature (polygon, line,
circle, etc) doesn't exceed the block size, then postgresql should be able
to handle it.

I have in the works some Java code to draw the geometric types, and some
GPS code as well, but it's mainly aimed at the Astronomical rather than
GIS use.

Peter

--
Peter T Mount peter@retep.org.uk
Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres
Java PDF Generator: http://www.retep.org.uk/pdf

On Wed, 17 Feb 1999, Peter T Mount wrote:

[snip]

If the TIGER/Line data is raster, and each feature (polygon, line,
circle, etc) doesn't exceed the block size, then postgresql should be able
to handle it.

[snip]

Vector not raster. Right?

Gregory Maxwell wrote:

On Wed, 17 Feb 1999, Peter T Mount wrote:

[snip]

If the TIGER/Line data is raster, and each feature (polygon, line,
circle, etc) doesn't exceed the block size, then postgresql should be able
to handle it.

[snip]

Vector not raster. Right?

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.

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?

--bryan

-- Failure is not an option. It comes
bundled with your Microsoft product.

---
Bryan R. Mattern
bm@datapace.com http://www.datapace.com
--------

On Thu, 18 Feb 1999, Gregory Maxwell wrote:

On Wed, 17 Feb 1999, Peter T Mount wrote:

[snip]

If the TIGER/Line data is raster, and each feature (polygon, line,
circle, etc) doesn't exceed the block size, then postgresql should be able
to handle it.

[snip]

Vector not raster. Right?

Yes, the Tiger data is vector.

Peter

--
Peter T Mount peter@retep.org.uk
Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres
Java PDF Generator: http://www.retep.org.uk/pdf

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

Bryan Mattern wrote:

I'm working on a personal project to develop some sort of GPS software
for Linux. I was talking with a friend the other day and he let me
borrow a "TIGER/Line CD" (!1990!) from the US Bureau of the Census. I
know the data is old, but I just wanted to test it with PostgreSQL. I
don't know all that much about GIS maps, there might be a better source
I don't know about, but the data seems pretty easy to rip apart and
pitch into PostgreSQL.

Has anyone done anything like this? If so, is there any info on the
web? I checked the old mailing list archives, but they were not much
help.

I'm trying to do something similar with Digital Chart of the World, which is
in VPF format (binary not ASCII). The biggest problem that I've come across is
that some of the edges (lines) are > 8K, so I'll have to store them as blobs
rather than in tuples as arrays. I'm still learning about blobs...

I'm happy to compare notes with anyone doing similar stuff.
--

Julian Scarfe