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

Question

I want to classify timestamp data types in a PostgreSQL table with regards to whether they can be considered "at day" or "at night". In other words I want to be able to calculate sunrise and sunset times accurately, given a particular GPS position.

I know plpgsql and plpython.

Answer 1

Take a look at these links:

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

Answer 2

I know this is yonks old, but I thought I'd share since I found no quick solution. This uses the Sun class (see below), which I constructed by 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']

It seems to be accurate to within a few minutes, at least where I live. For greater accuracy, the zenith param in the calcSunTime() method could use fine tuning. See the above link for more info.

# 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's mean anomaly
        M = (0.9856 * t) - 3.289

        #4. calculate the Sun's 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's 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's declination
        sinDec = 0.39782 * math.sin(TO_RAD*L)
        cosDec = math.cos(math.asin(sinDec))

        #7a. calculate the Sun's 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 (e.g. 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

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 would be:

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

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