Re: [postgis-users] Loading Data / Refreshing every 5 minutes
My very uneducated perspective would be that it depends on how much of the data (ie what sort of subset) the user is requesting. If they are just getting the whole table every time, there may be limited benefit to large amounts of indexing. If you are loading the radar shapefile for continental Australia, but your users are on the coast somewhere between Exmouth and Karratha, then I think an index is probably essential for any sort of performance. As a vague point of interest, why are you converting the shapefile to postgis for 5 minutes, then deleting the whole lot - there might be very little speed benefit to doing this. Could you not just load the new data into the same table (ie append) with a field (something like createdon timestamp not null default now() or similar) for the date and time. Then the user request could be modified to get the most recently loaded data... or loops of the last 20 minutes... or something like that. Just a thought, it might save the blank pages. cheers Ben On 03/05/2011, at 11:17 AM, Chris Brisendine wrote: > Every once and a while when a wms user (on Geoserver) goes to refresh the > view (Latest Radar Image) they get a blank page, just catching it when there > is no data in the table. > There is not any data verification on my end its just a simple conversion of > a polygon to postgis for geoserver to use. Do I need to worry about indexing > and vacuuming since the data changes > so much? I am very new to pgsql and still trying to learn everything... > > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Loading Data / Refreshing every 5 minutes
Every once and a while when a wms user (on Geoserver) goes to refresh the view (Latest Radar Image) they get a blank page, just catching it when there is no data in the table. There is not any data verification on my end its just a simple conversion of a polygon to postgis for geoserver to use. Do I need to worry about indexing and vacuuming since the data changes so much? I am very new to pgsql and still trying to learn everything... ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Loading Data / Refreshing every 5 minutes
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 ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Loading Data / Refreshing every 5 minutes
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? 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. Thanks, Chris ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
