[postgis-users] Greetings, and road data questions

[Paul Ramsey]

Michael Graff wrote:

> 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_' ...)

You might want to test out Dave's new histogram stats features (see 
earlier posts on details). The stats should allow the system to decide 
whether the attribute or spatial indexes will be more selective for a 
given query, and choose appropriately. In the default (non-stats) 
situation, the spatial is always used. On mid-sized queries on TIGER 
though, you might get burned both ways -- using spatial, you get a hugh 
window of data which is then sequence scanned for the attribute values; 
using attribute, you get the whole dataset filtered quickly by 
attribute, but the results must then be windowed spatially.

I would also be interested in a quantification of the 'gets slow 
quickly'. Does the database get slow, or does the perl application 
processing the data get slow, or a measurable combination of the two? 
Any GIS app, when hit with enough data volume, will get slow, hence all 
the various strategies for using data which is "scale appropriate" for 
the task. TIGER is "scale appropriate" for local level mapping, but 
quickly because needlessly detailed as the view gets wider.

-- 
       __
      /
      | Paul Ramsey
      | Refractions Research
      | Email: pramsey at refractions.net
      | Phone: (250) 885-0632
      \_