Optimizing Data Base for GeoData

[Robert Chavez]

> > Finally, if anyone wishes to use PostgreSQL as a low-cost solution for
> > writing a GIS type application, I can heartily recommend it.  It comes
> > with some nice geographic data types and functions.  It also plays well
> > with Perl.

I've do a lot of the same things with my PostGreSQL database set up.
Indexing does help performance.  I usually perform all my queries by
bounding box -- I've got a table/index of all the layers and their
bounding boxes which also helps eliminate the need for hitting the layers
themselves over and over again for usesr queries.

> Did you investigate the use of Sybase, Informix, DB2, or Oracle 8i for 
> the Linux platform? Almost all of these are available for Linux now at 
> no or low cost (Sybase came with RedHat 6.1; Oracle can be obtained with 
> an unexpiring demo license) and most/all? support stored procedures and 
> extensible data types. 

I have Oracle for Linux, but haven't tried making the mapscript connection
yet.  The problem with these databases (I have experience only with Oracle
and DB2) is that they are extremely difficult to set up initially, and
true site licenses can be prohibitively expensive (at least for my
office).  Once they are up and running, they really are quite nice. I'd
say if you have access to, and dedicated support for, Oracle or DB2 by all
means use them, but if you don't it may not be worth the investment in
time and effort that it takes to get them off the ground.

I'd have to say PostGres is a good fully functional alternative is easier
to manage on one's own, and is catching up with the big boys.  PostGreSQL
7.0 has just been released and boasts better performance -- although I
haven't tried it yet. There's also a FAQ about fine tuning PostGres for
better performance: http://www.postgresql.org/docs/faq-english.html#3.10

> database engine to feed the mapserver. It would be even nicer if folks
> built their interfaces following the OpenGIS "Simple Features via SQL"  
> access model. Then different brands of compatible databases could be
> accessed using the same SQL calls. This doesn't eliminate the need for
> database tuning, but allows for a common request interface.

Doug, could you supply a link to the OpenGIS access model?  I haven't been
able to locate it.  Thanks.

Rob Chavez
Programmer
The Perseus Project