Calculate sunrise and sunset times for a given GPS coordinate in PostgreSQL

Question

I want to classify the timestamp data types in a PostgreSQL table as to whether they can be considered “day” or “night”. In other words, I want to be able to accurately calculate the time of sunrise and sunset, given the specific GPS position.

I know plpgsql and plpython.

Answer 1

Take a look at these links:

• Calibration of sunrise and sunset in Python ;
• Skyfield Project (a new incarnation of PyEphem)
• PyEphem project;
• astral project;

Answer 2

I know that these are old yonks, but I thought I would share it since I did not find a quick solution. This uses the Sun class (see below) that I built following this link .

 from Sun import Sun coords = {'longitude' : 145, 'latitude' : -38 } sun = Sun() # Sunrise time UTC (decimal, 24 hour format) print sun.getSunriseTime( coords )['decimal'] # Sunset time UTC (decimal, 24 hour format) print sun.getSunsetTime( coords )['decimal'] 

This seems to be accurate for a few minutes, at least where I live. For greater accuracy, the anti-aircraft parameter in the calcSunTime () method can use fine tuning. See the above link for more information.

 # save this as Sun.py import math import datetime class Sun: def getSunriseTime( self, coords ): return self.calcSunTime( coords, True ) def getSunsetTime( self, coords ): return self.calcSunTime( coords, False ) def getCurrentUTC( self ): now = datetime.datetime.now() return [ now.day, now.month, now.year ] def calcSunTime( self, coords, isRiseTime, zenith = 90.8 ): # isRiseTime == False, returns sunsetTime day, month, year = self.getCurrentUTC() longitude = coords['longitude'] latitude = coords['latitude'] TO_RAD = math.pi/180 #1. first calculate the day of the year N1 = math.floor(275 * month / 9) N2 = math.floor((month + 9) / 12) N3 = (1 + math.floor((year - 4 * math.floor(year / 4) + 2) / 3)) N = N1 - (N2 * N3) + day - 30 #2. convert the longitude to hour value and calculate an approximate time lngHour = longitude / 15 if isRiseTime: t = N + ((6 - lngHour) / 24) else: #sunset t = N + ((18 - lngHour) / 24) #3. calculate the Sun mean anomaly M = (0.9856 * t) - 3.289 #4. calculate the Sun true longitude L = M + (1.916 * math.sin(TO_RAD*M)) + (0.020 * math.sin(TO_RAD * 2 * M)) + 282.634 L = self.forceRange( L, 360 ) #NOTE: L adjusted into the range [0,360) #5a. calculate the Sun right ascension RA = (1/TO_RAD) * math.atan(0.91764 * math.tan(TO_RAD*L)) RA = self.forceRange( RA, 360 ) #NOTE: RA adjusted into the range [0,360) #5b. right ascension value needs to be in the same quadrant as L Lquadrant = (math.floor( L/90)) * 90 RAquadrant = (math.floor(RA/90)) * 90 RA = RA + (Lquadrant - RAquadrant) #5c. right ascension value needs to be converted into hours RA = RA / 15 #6. calculate the Sun declination sinDec = 0.39782 * math.sin(TO_RAD*L) cosDec = math.cos(math.asin(sinDec)) #7a. calculate the Sun local hour angle cosH = (math.cos(TO_RAD*zenith) - (sinDec * math.sin(TO_RAD*latitude))) / (cosDec * math.cos(TO_RAD*latitude)) if cosH > 1: return {'status': False, 'msg': 'the sun never rises on this location (on the specified date)'} if cosH < -1: return {'status': False, 'msg': 'the sun never sets on this location (on the specified date)'} #7b. finish calculating H and convert into hours if isRiseTime: H = 360 - (1/TO_RAD) * math.acos(cosH) else: #setting H = (1/TO_RAD) * math.acos(cosH) H = H / 15 #8. calculate local mean time of rising/setting T = H + RA - (0.06571 * t) - 6.622 #9. adjust back to UTC UT = T - lngHour UT = self.forceRange( UT, 24) # UTC time in decimal format (eg 23.23) #10. Return hr = self.forceRange(int(UT), 24) min = round((UT - int(UT))*60,0) return { 'status': True, 'decimal': UT, 'hr': hr, 'min': min } def forceRange( self, v, max ): # force v to be >= 0 and < max if v < 0: return v + max elif v >= max: return v - max return v 

Answer 3

I use this to calculate the times of sunrise, sunset, dawn and dusk.

You just need to replace the X with the coordinates. Please note that the return time is UTC, so you need to add time zones if necessary.

 request = Request('http://api.sunrise-sunset.org/json?lat=-XX.XXXXX&lng=XX.XXXXX&formatted=0') response = urlopen(request) timestring = response.read() utcsunrise = timestring[34:39] utcsunset = timestring[71:76] utcmorning = timestring[182:187] utcnight = timestring[231:236] 

Answer 4

Use Astral (current version 1.6). The first example in the documentation shows the calculation of sunrise and sunset for a given location. A simpler example with custom latitude and longitude:

 from datetime import date import astral loc = astral.Location(('Bern', 'Switzerland', 46.95, 7.47, 'Europe/Zurich', 510)) for event, time in loc.sun(date.today()).items(): print(event, 'at', time) 

gives:

 noon at 2018-03-12 12:39:59+01:00 sunset at 2018-03-12 18:30:11+01:00 sunrise at 2018-03-12 06:49:47+01:00 dusk at 2018-03-12 20:11:39+01:00 dawn at 2018-03-12 05:08:18+01:00 

You can then use this as a starting point for writing your own postgres (or postgis) functions using plpython instead of plr.

Answer 5

I am using Sun.py. Today I got the value minutes = 60 , UT = 12.9979740551

Sun class:

 #10. Return min = round((UT - int(UT))*60,0) #add this after min calculate if min == 60: hr += 1 min = 0