Closed bnordgren closed 8 years ago
Breaking the rasterizing into two stages leads me directly to a state where I need to do an "UPSERT", which is a feature lacking in PostgreSQL 9.4, but is planned for 9.5.
Documentation on how to perform this in 9.4 and earlier is here: http://stackoverflow.com/questions/17267417/how-do-i-do-an-upsert-merge-insert-on-duplicate-update-in-postgresql
Bryce, I’m running this on 9.5 on my home setup, and have no problem with moving to 9.5 so you can add an upsert. I looked into this back when 9.5 came out to prevent duplication in the threshold and active_fire tables but did not do for a reason I can’t remember.
[Forest Service Shield]
Carl Albury Remote Sensing Specialist
Forest Service Contractor Remote Sensing Applications Center
p: 801-975-3351 calbury@fs.fed.usmailto:calbury@fs.fed.us
2222 West 2300 South Salt Lake City, UT 84119 www.fs.fed.ushttp://www.fs.fed.us/ [USDA Logo]http://usda.gov/[Forest Service Twitter]https://twitter.com/forestservice[USDA Facebook]https://www.facebook.com/pages/US-Forest-Service/1431984283714112
Caring for the land and serving people
From: bnordgren [mailto:notifications@github.com] Sent: Wednesday, March 09, 2016 3:50 PM To: firelab/viirs_ba viirs_ba@noreply.github.com Subject: Re: [viirs_ba] figure of merit fixes (#14)
Breaking the rasterizing into two stages leads me directly to a state where I need to do an "UPSERT", which is a feature lacking in PostgreSQL 9.4, but is planned for 9.5.
Documentation on how to perform this in 9.4 and earlier is here: http://stackoverflow.com/questions/17267417/how-do-i-do-an-upsert-merge-insert-on-duplicate-update-in-postgresql
— Reply to this email directly or view it on GitHubhttps://github.com/firelab/viirs_ba/issues/14#issuecomment-194551961.
This electronic message contains information generated by the USDA solely for the intended recipients. Any unauthorized interception of this message or the use or disclosure of the information it contains may violate the law and subject the violator to civil or criminal penalties. If you believe you have received this message in error, please notify the sender and delete the email immediately.
It's probably a good thing to keep the code compatible with 9.4 and below. We run a variety of installations on a variety of platforms, so limiting ourselves to the latest and greatest isn't in our best interest. :) I've toyed with the "Bulk upsert with lock" pattern described in the above link and it seems to be working. I just need to adapt the code to dynamic schemas now. And I'll close this ticket out.
It does seem that the "Bulk upsert with lock" pattern would quite naturally apply to the loops in viirs_threshold_2_fireevents and viirs_activefire_2_fireevents, and may result in speed improvements by eliminating the loops. I'm thinking about breaking off a separate ticket for those two.
bulk upsert was relatively straightforward and is done in ec4d61f9fd9c71cd9edba01cf513c2da47dc1c20
Now I have rasterized at two resolutions and am having issues combining the two. Question pending on the postgis-users mail list.
Stupid user flipped sign of his y pixel size. Multiresolution rasterization is all ducky as of f6235afaa07915c413d5294d3ba263ca0d0e503d
My initial cut at figure of merit code requires two improvements: