a friend of mine, a landscape gardener, asked me wether it is possible to represent his garden (mainly big trees and a few flower fields) in a GIS so he could administrate these on his computer by just selecting certain objects and then see a number of attributes of the object of his choice. I told him that that was no problem...

...the next think he asked me I found more interesting and usefull!

...he also wants to have reminders that tell him, when he has to water certain trees or flowers for instance or cut trees that stand close to pathways through his park.

this sounded very usefull to me but after 30 mins of google I haven't really found anything usefull in this matter.

Does anyone know if there is a way to maybe create Trigger functions in a postgresql database that triggers a reminder at certain dates or intervals?!

  • This question need more information. One solution is create view from geometry and maintenance tables which has calculated column, then load view to qgis and see if where is "red" color ... Is that what you need or do you really want to send email on certain dates or... (if so see : stackoverflow.com/questions/12002662/… for start) – simplexio Jun 3 '15 at 8:55
  • Both the date functions and reminder mechanism are at the edge of valid scope for GIS. You might find existing answers elsewhere in the stackexchange hierarchy. – Vince Jun 3 '15 at 11:34
  • You mention geodatabase in the title, are you using ArcGIS Server/ArcMap to serve and style the layers? – artwork21 Jun 3 '15 at 12:05
  • Thanks for your answers! I havent really thought about whether I want an email notification or something else...but you idea with view / color sounds good! @artwork21 : I planned using free software like QGIS and a PostGIS database – adryanh Jun 3 '15 at 12:11

We already using a similar solution for our existing service. Postgres/postgis(with triggers for calculated fields) connected to gmail + google calendar via zapier ( http://zapier.com ) Unfortunately to connect to postgresql via zapier is not free.

  • thanks! even though its's not free I'll check it out!! – adryanh Jun 3 '15 at 14:45

I'm doing something like this with OSM data - looking for changes in specified tags (milestones, hydrants) and if there is some change I'm sending mail. Resolution is for linux. Workflow is very simple

  1. Selecting interesting data (e.g. somefield older than 48 hours) and writing response into file
  2. checking what is size of file
  3. If greater then just empty columns I'm sending mail

some code:


#echo "<html>" > /home/osm/scripts/reports/mail.html
cat /home/osm/scripts/reports/html_head.txt > /home/osm/scripts/reports/mail.html
echo "Some text here as begining <br /><br />" > /home/osm/scripts/reports/mail.html
psql -f /home/osm/scripts/reports/d_repo_addr_nostreet_24_mail.sql -H -d osm2pgsql >> /home/osm/scripts/reports/mail.html
echo "<br /><br />" >> /home/osm/scripts/reports/mail.html
echo "And some text here as ending" >> /home/osm/scripts/reports/mail.html
echo "</html>" >> /home/osm/scripts/reports/mail.html
sed -e 's/&lt;/</g' -i /home/osm/scripts/reports/mail.html
sed -e 's/&quot;/"/g' -i /home/osm/scripts/reports/mail.html
sed -e 's/&amp;/&/g' -i /home/osm/scripts/reports/mail.html
sed -e 's/&gt;/>/g' -i /home/osm/scripts/reports/mail.html

size=$(stat -c %s /home/osm/scripts/reports/mail.html)
echo 'Mail size: ' $size
if [ "$size" -ge 400 ]; then
mail -a "Content-type: text/html" -s "Some title" mail@domain.com < /home/osm/scripts/reports/mail.html


400b is size of empty file (when no records was returned from query) I'm sure there is simplest way or more elegant but this one is also working :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.