[postgis-users] Greetings, and road data questions

[Michael Graff]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Andy Turk <andy at streetlight.com> writes:

> We're using a derivative of the TIGER data as produced by Avenza
> (www.avenza.com). The db size is about the same and our geographic
> queries are very snappy. Have you got a GiST index created for your
> tables? Also, have you given postgres enough shared memory so that
> significant portions of the index can be held in RAM?

I've got a GiST index on the data, yes.  For small datasets (less
than 4 degree boxes) in mostly rural areas (middle of Iowa, where
I grew up) I get the results back in perhaps 30 seconds.

> In  SQL, do an "EXPLAIN SELECT cfcc, path ..." to see what's going
> on.

Or, in 7.2, "explain analyze select ..." for more info :)

> One thing to explore is the use of multi-key indexes. I.e., you could
> have a single index that uses both a geometry field and a CFCC
> field.

I tried doing this, but it didn't work.  What's the syntax?

I tried:

        CREATE INDEX cp_path_idx1 ON cp USING GIST
           (path GIST_GEOMETRY_OPS, cfcc)

and

        CREATE INDEX cp_path_idx1 ON cp USING GIST
           (path GIST_GEOMETRY_OPS, cfcc char_ops)

but both fail with errors.

> Check out the "joined" roads dataset which is included by Avenza. They
> join all the segments within a single county.

It's not typically hard to join them.  I have a Perl scrip that will
do this, in fact, after generating a table with tlid and start and end
points.  It takes multiple days to run, but afterwards it has produced
a lot of new line segments.

The problem is address ranges.  I sort of want to retain that as much
as possible, since one thing I'd like to be able to do is "show me a
map of 1234 south 4th ave in Chicago" or some such.  I suppose I could
retain that info by using a clever "from point 3 to 4 in line 123123,
the address range is 1200 to 1299" or something.

BTW, the machine I'm using is a box I threw together for this side
project.  It only has 512 MB of ram currently, but if this becomes
serious it'll end up with 3.5 or so.  It has two Athlon XP 2000+ CPUs.

Thanks,
- --Michael
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.7 (NetBSD)
Comment: See http://www.flame.org/~explorer/pgp for my keys

iD8DBQE9sGHAl6Nz7kJWYWYRAvgpAJ4z7PNJEQoxcEn9KbHkNdlSNj/XWACfeAeb
lrUdOYqCru+vsMQr5wh13e8=
=2eCm
-----END PGP SIGNATURE-----