[postgis-users] How do you use PostGIS?

[Bill Binko]

On Sat, 30 Apr 2005, Paul Ramsey wrote:

> Anyhow, the question is this:
> 
>     "How are you using PostGIS in your organization?"
> 
...
> 
> If you could say in your response, at a minimum:
> 
> - Name of the organization

Right now, I'm operating as an individual (Bill Binko).  However, I will
be incorporating and have the name Mapshine.com registered.

> - Data being managed in the database

Consolidated public data.  For example: roads, census information, water 
mgt. data, property data, etc.

> - How the data is being accessed / manipulated

The data is being access via Mapserver.  I use the USGS Mapsurfer, and I
will be using PHP/Mapscript and/or Chameleon for a new interface shortly.

Shapefiles are loaded via shp2pgsql

Additional data is loaded via PHP using the PEAR DB libraries.  This is 
mainly data that is separate from the shapes, such as statistics that are 
joined to the census tract tables.  

I also load data with straight lat/long data (like sinkhole data), and 
data with addresses (I use geocoder.us's software to geocode with the 
TIGER data).

> - Why you chose to use PostGIS for the application

I have been very happy using PostgreSQL for years on all of my side 
projects.  I know how to leverage its features, and was glad to see a GIS 
package built on such a strong foundation.

...
> - What PostGIS feature or PostGIS-related software (not necessarily 
> internal to PostGIS) would make your life much easier/better?
> 

My biggest problem is upgrading.  My databases are now large enough that a 
"build a new database and import/export" approach is fairly painful.  If 
it is absolutely required, then a very simple script that exports, 
creates, sets up, imports in one go would be a godsend.

The only other thing that I'd really like to see is a mechanism to 
reproject data that's in the database in a simple way.  For example:

reproject(oldTable, newTable, newSRID)
or
changeProjection(table, newSRID);

It's possible that this is out there, but I haven't found it.

>From more of a wishlist perspective, I'd love it if you could remove the 
need to overconstrain with && in order for it to use the indexes.  Of 
course, I think I understand why this limitation is there, but it would be 
nice.

Finally, from a documentation standpoint, I'd like to have more 
information about the types of indexing that's most effective.  I have 
gotten huge benefits in the past using partial indexes and functional 
indexes in PostgreSQL.  I now try to use the same techniques, but I'm not 
sure how effective they are.  For example, when I create tables with 
complex shapes, I always create an index like this:

create index centroid_idx on tablename using GIST (centroid(shape_col));

That way, if I need to distance calculations or the like, and centroids 
are good enough, it should use the index.  However, I can't tell if it's 
effective like it is in non-GIS world.

Thanks for the great software.
Bill