[postgis-users] Loading Data / Refreshing every 5 minutes

[Greg Williamson]

Chris --



>  I am loading NWS Radar data every 5 minutes from two different sites.
>  What is the most effective way to load them?
>  right now I'm doing this to load them:
>  
>  "shp2pgsql -D -a -s 4269 comp.shp compbase05 > compbase05.sql
>  psql -c "TRUNCATE TABLE compbase05" -d wxgis
>  psql -d wxgis -f compbase05.sql "
>  
>  Would I be better to load them into a temp table remove the old table
>  and copy the temp table to replace the old?

Temp tables have some speed advantages in that they are not logged; OTH
TRUNCATE is also fast.

I have generally handled this sort of thing with a temp table to load data into;
do verification and data massaging there and then copy the data.

The slowest part of all this may well be (a) reindexing and (b) running vacuum.

Since truncate doesn't require a vacuum that would leave recreating indexing
and running analyze on the new table.

>  The GIS viewer is missing the radar data sometimes so I'm looking for
>  the most effective and fastest way to load and refresh the data.

I am not sure what you mean -- perhaps not seen because there is a window in
which data is not there ?

HTH,

Greg Williamson