Smathermather's Weblog

Remote Sensing, GIS, Ecology, and Oddball Techniques

Archive for July, 2010

Shapfile spatial indices and PostGIS dumps

Posted by smathermather on July 19, 2010

Another short post… . I have in place a framework for maintenance of our boundary data that works really well. I feed an edited shapefile into the database, and PostGIS creates all the versions of the boundary that need created. The database then exports the managed data to shapefile so there is a database version and a shapefile version depending on need.

When all was said and done, I had several shapefiles that displayed at some zoom levels in ArcGIS, but not others. Google to the rescue:
http://forums.esri.com/Thread.asp?c=93&f=1730&t=164942
I had outdated spatial indices that are not created by pgsql2shp, and since they didn’t reflect the real geometry, ArcGIS was choking on them and not displaying them properly. In the above link, Kirk Kuykendall suggests fixing the index in ArcCatalog. For me, the geometry is simple enough, I just deleted the index (*.sbn) with no noticeable performance hit.

Posted in Database, PostGIS, SQL | Tagged: , , , | 2 Comments »

pgagent — when triggers are too much

Posted by smathermather on July 8, 2010

This will be one of my shorter posts. pgadmin III allows for the development of scheduled tasks that run on your PostgreSQL database. As my PostGIS triggers have gotten more complicated, and since they run per transaction to keep ACID compliance, a big mess can be made by just updating a dataset (this is what vacuum is good for), and the time for inserts can be astronomical. Take, e.g. my previous post. Each record insert, because of the trigger, takes about 30 seconds to complete. That’s way to slow, especially if I want updates to happen during the business day. Enter pgagent.

pgagent has been well covered by other’s blogs, so I’ll post two links that were helpful to me for administering it in Windows:

http://www.mkyong.com/database/how-to-install-pgagent-on-windows-postgresql-job-scheduler/

http://www.postgresonline.com/journal/index.php?/archives/19-Setting-up-PgAgent-and-Doing-Scheduled-Backups.html

Posted in Database, PostGIS, SQL | Tagged: , , , , | 1 Comment »