[postgis-users] Greetings, and road data questions

[Andy Turk]

> I have a Perl map generating toy that makes maps in whatever detail
> desired, given a center point and a block size.  The database works
> amazingly well for small values, but gets rather slow rather quickly,
> even with filtering out unwanted rows using the SQL query:
> 
>         SELECT cfcc, path FROM cp WHERE path && <box definition>
>                 AND (cfcc LIKE 'A1_' OR cfcc LIKE 'A2_' ...)

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?

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

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. In
my experience, this wasn't any quicker than using a straight geometry
index followed by a sequential filtering of the matching rows to look at
the feature code. My queries tend to be "small" and return usually less
than 1000 rows based on the geometries alone.

> 
> My next plan is to split the waterways, railways, and roads into their
> own tables.
> 
> The ultimate plan will probably involve generating ploygon information
> from the files and combining many short road segments into one longer
> one, for all the A1, A2, and A3 road types.

Check out the "joined" roads dataset which is included by Avenza. They
join all the segments within a single county.
 
> (1)  How are others using the tiger/line data?  Are they mostly doing
>      what I plan, or is there an easier way that I'm overlooking?
> 
> (2)  Garmin and others have fairly detailed maps, both as a windoze
>      application and as a GPS product.  Where do they get their data?

www.avenza.com ($500 for all of TIGER/2000 in shapefile format)
www.geographic.com (commercial map data)
www.na.teleatlas.com (commercial map data)
www.navtech.com (commercial map data)

> (4)  If you wanted to write a mapping application that would track
>      multiple objects and show both an overview map of where they are,
>      and detailed maps for each, would you use tiger/line or DLG, or
>      something entirely different?

Many of the commercial datasets start from TIGER and then massage it to
remove errors. From what I've seen, the structure of the commercial
databases are somewhat similar to TIGER--especially the ones designed
for geocoding.