Complex database for testing, U.S. Census Tiger/UA

Complex database for testing, U.S. Census Tiger/UA

Post by ml » Wed, 09 Apr 2003 21:42:09



The U.S. Census provides a database of street polygons and other data
about landmarks, elevation, etc. This was discussed in a separate thread.

The main URL is here:
http://www.census.gov/geo/www/tiger/index.html

My loader was written for the 2000 version, the 2002 version has some
difference, but it should be easy enough to ad the fields.

On my site, in the downloads section, at the bottom is the tigerua
loader. It is very raw, just hacked together to load the data. It may
take a little work to function with 2002 files, I have not looked at
that yet.

My site:
http://www.mohawksoft.com

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly

 
 
 

Complex database for testing, U.S. Census Tiger/UA

Post by Jan Wie » Wed, 09 Apr 2003 23:57:23



> The U.S. Census provides a database of street polygons and other data
> about landmarks, elevation, etc. This was discussed in a separate thread.

> The main URL is here:
> http://www.census.gov/geo/www/tiger/index.html

While yes, the tiger database (or better it's content) is interesting, I
don't think that it can be counted as a "complex database". Just that
something is big doesn't mean that.

Quote:

> My loader was written for the 2000 version, the 2002 version has some
> difference, but it should be easy enough to ad the fields.

OT:

Just out of curiosity, do you plan more on this? I was playing around
with the 2000 version a while back, but the Garmin GPS units
unfortunately use a proprietary map format, so one cannot generate his
own detail maps for download. The waypoint and route data protocol is
well known though.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

 
 
 

Complex database for testing, U.S. Census Tiger/UA

Post by pg.. » Thu, 10 Apr 2003 00:03:05



>>=20
>> The U.S. Census provides a database of street polygons and other data
>> about landmarks, elevation, etc. This was discussed in a separate
>> thread.
>>=20
>> The main URL is here:
>> http://www.veryComputer.com/
>=20
> While yes, the tiger database (or better it's content) is interesting,
> I don't think that it can be counted as a "complex database". Just that
> something is big doesn't mean that.

I guess you are right, but there are a lot of related tables. I wouldn't
call it simple, though. It can get huge, however.

Quote:>=20
>>=20
>> My loader was written for the 2000 version, the 2002 version has some
>> difference, but it should be easy enough to ad the fields.
>=20
> OT:
>=20
> Just out of curiosity, do you plan more on this? I was playing around
> with the 2000 version a while back, but the Garmin GPS units
> unfortunately use a proprietary map format, so one cannot generate his
> own detail maps for download. The waypoint and route data protocol is
> well known though.

I'm not sure what a Garmin GPS unit is, but the TigerUA DB uses longitude
and latitude. Any reasonable geographical system must somehow map to lat/lo=
ng.

Actually, I am going to download the latest version and get it installed on
a system. There is a project I plan to work on in the near future, after all
the other *I gotta do, that will make use of the data.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.veryComputer.com/

 
 
 

Complex database for testing, U.S. Census Tiger/UA

Post by cbbro.. » Thu, 10 Apr 2003 00:26:23




> > The U.S. Census provides a database of street polygons and other data
> > about landmarks, elevation, etc. This was discussed in a separate thread.

> > The main URL is here:
> > http://www.census.gov/geo/www/tiger/index.html

> While yes, the tiger database (or better it's content) is interesting, I
> don't think that it can be counted as a "complex database". Just that
> something is big doesn't mean that.

Just so.

There are doubtless interesting cases that may be tested by virtue of
having a data set that is large, and perhaps "deeply interlinked."

But that only covers cases that have to do with "largeness."  It doesn't
help ensure that PostgreSQL plays well when it gets hit by nested sets
of updates where the challenges involve ensuring the system performs OK
and does not deadlock when hit by complex sets of transactions.

So that an "interesting" database might involve not only a database, but
also a set of transactions that hit multiple tables that are to update
that database.  In effect, something like the "readers/writers" that get
used to test locking semantics.

This is something that would not be able to solely consist of a set of
tables; it would have to include streams of updates.  Something like one
of the TPC benchmarks...
--

http://www3.sympatico.ca/cbbrowne/rdbms.html
"If I  could find  a way to  get [Saddam  Hussein] out of  there, even
putting a  contract out on him,  if the CIA  still did that sort  of a
thing, assuming it ever did, I would be for it."  -- Richard M. Nixon

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly

 
 
 

Complex database for testing, U.S. Census Tiger/UA

Post by Dustin Sallin » Thu, 10 Apr 2003 01:37:04



        I think it was my first application I wrote in python which parsed
the zip files containing these data and shoved it into a postgres system.
I had multiple clients on four or five computers running nonstop for about
two weeks to get it all populated.

        By the time I was done, and got my first index created, I began to
run out of disk space.  I think I only had about 70GB to work with on the
RAID array.

# > >
# > > The U.S. Census provides a database of street polygons and other data
# > > about landmarks, elevation, etc. This was discussed in a separate thread.
# > >
# > > The main URL is here:
# > > http://www.census.gov/geo/www/tiger/index.html
# >
# > While yes, the tiger database (or better it's content) is interesting, I
# > don't think that it can be counted as a "complex database". Just that
# > something is big doesn't mean that.
#
# Just so.
#
# There are doubtless interesting cases that may be tested by virtue of
# having a data set that is large, and perhaps "deeply interlinked."
#
# But that only covers cases that have to do with "largeness."  It doesn't
# help ensure that PostgreSQL plays well when it gets hit by nested sets
# of updates where the challenges involve ensuring the system performs OK
# and does not deadlock when hit by complex sets of transactions.
#
# So that an "interesting" database might involve not only a database, but
# also a set of transactions that hit multiple tables that are to update
# that database.  In effect, something like the "readers/writers" that get
# used to test locking semantics.
#
# This is something that would not be able to solely consist of a set of
# tables; it would have to include streams of updates.  Something like one
# of the TPC benchmarks...
# --

# http://www3.sympatico.ca/cbbrowne/rdbms.html
# "If I  could find  a way to  get [Saddam  Hussein] out of  there, even
# putting a  contract out on him,  if the CIA  still did that sort  of a
# thing, assuming it ever did, I would be for it."  -- Richard M. Nixon
#
#
# ---------------------------(end of broadcast)---------------------------
# TIP 3: if posting/reading through Usenet, please send an appropriate

# message can get through to the mailing list cleanly
#
#

--
SPY                      My girlfriend asked me which one I like better.

|    Key fingerprint =  87 02 57 08 02 D0 DA D6  C8 0F 3E 65 51 98 D8 BE
L_______________________ I hope the answer won't upset her. ____________

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

 
 
 

Complex database for testing, U.S. Census Tiger/UA

Post by cbbro.. » Thu, 10 Apr 2003 04:01:07



>    I think it was my first application I wrote in python which parsed
> the zip files containing these data and shoved it into a postgres system.
> I had multiple clients on four or five computers running nonstop for about
> two weeks to get it all populated.

>    By the time I was done, and got my first index created, I began to
> run out of disk space.  I think I only had about 70GB to work with on the
> RAID array.

But this does not establish that this data represents a meaningful
"transactional" load.

Based on the sources, which presumably involve unique data, the
"transactions" are all touching independent sets of data, and are likely
to be totally uninteresting from the perspective of seeing how the
system works under /TRANSACTION/ load.

TRANSACTION loading will involve doing updates that actually have some
opportunity to *le on one another.  Multiple transactions
concurrently updating a single balance table.  Multiple transactions
concurrently trying to attach links to a table entry.  That sort of
thing.

I remember a while back when MSFT did a "enterprise scalability day,"
where they were trumpeting SQL Server performance on "hundreds of
millions of transactions."  At the time, I was at Sabre, who actually do
tens of millions of transactions per day, for passenger reservations
across lotso airlines.  Microsoft was making loud noises to the effect
that NT Server was wonderful for "enterprise transaction" work; the guys
at work just laughed, because the kind of performance they got involved
considerable amounts of 370 assembler to tune vital bits of the
systems.

What happened in the "scalability tests" was that Microsoft did much the
same thing you did; they had hordes of transactions going through that
were well, basically independent of one another.  They could "scale"
things up trivially by adding extra boxes.  Need to handle 10x the
transactions?  Well, since they don't actually modify any shared
resources, you just need to put in 10x as many servers.

And that's essentially what happens any time TPC-? benchmarks reach the
point of irrelevance; that happens every time someone figures out some
"hack" that is able to successfully partition the work load.  At that
point, they merely need to add a bit of extra hardware, and increasing
performance is as easy as adding extra processor boards.  The real world
doesn't scale so easily...
--

http://www.veryComputer.com/
Send  messages calling for fonts  not  available to the  recipient(s).
This can (in the case of Zmail) totally disable the user's machine and
mail system for up to a whole day in some circumstances.
-- from the Symbolics Guidelines for Sending Mail

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly

 
 
 

Complex database for testing, U.S. Census Tiger/UA

Post by Josh Berk » Thu, 10 Apr 2003 06:10:24


MLW,

Quote:> > The U.S. Census provides a database of street polygons and other data
> > about landmarks, elevation, etc. This was discussed in a separate threa=
d.
> >=20

Yeah, this was me.   We decided to go with the FCC database because it is m=
ore=20
managably sized and has extensive schema documentation.   Personally, I'd b=
e=20
happy to see someone put together a "huge table" test using the Tiger=20
database, but for general tests we're aiming more at the 50-100mb size.

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

 
 
 

Complex database for testing, U.S. Census Tiger/UA

Post by Merlin Moncur » Thu, 10 Apr 2003 07:30:47



> MLW,
>=20
> > > The U.S. Census provides a database of street polygons and other
data
> > > about landmarks, elevation, etc. This was discussed in a separate
> thread.

> happy to see someone put together a "huge table" test using the Tiger
> database, but for general tests we're aiming more at the 50-100mb

size.

The Tiger US street level data would be an excellent test of the polygon
storage and extraction routines.  My information might no longer be
current, but the last time I checked Tiger gave the street level data
out on cd (er, cds) as one huge table of disconnected road 'segments'
broken up by state.  Connecting the segments into longer streets for
more meaningful processing is a good benchmarking procedure. I this is
interesting strictly on that level.  It doesn't test the optimizer or
esoteric features much (except for geo features), but is a good test of
index/cache/random tuple access.  It's typical of the scientific/data
processing problem domain that is much less common (but much more
interesting!) than your average business based app.  I definitely
understand mlw's thinking.
=20
That being said, since major competitors lack the robust geo
types/indices of postgres (the only way, IMHO, to do this type of
thing); it wouldn't be a very fair test.  I would like to point out the
problem is scalable by picking one state e.g. Rhode Island :), and
building off that.  One thing at a time tho.

It's no accident we have a 'PostGIS' and not a 'MyGIS' :)=20=20

Merlin

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html