myles henderson
it is myles henderson
Archive for programming
August 10, 2009 at 6:17 am · Filed under microsoft, GPS, waypoints, VBA, code, programming, Excel, america
We needed to convert GPS waypoints which were in Excel and in decimal form to degree, minute, second form. Logically, we wanted an Excel function to do this, rather than do the whole thing by hand.
I found a link to microsoft which gives a horribly broken implementation of the functionality. Take a look and see if you can spot the fail.
Here is, what I believe to be, a working Excel 2007 function which will convert decimal latitude and longitude into degrees. This likely will work in other version of Excel.
You of course have to use this at your own risk; I assume no liability for whatever you do with this.
Function Convert_Degree(Decimal_Deg, DegType) As Variant With Application
Degrees = Fix(Decimal_Deg)
minutes = (Decimal_Deg - Degrees) * -60
seconds = (minutes - Fix(minutes)) * -60
seconds = Round(seconds, 4) If DegType = "Lat" Then
If Decimal_Deg < 0 Then
Direction = " W"
Else
Direction = " E"
End If
End If
If DegType = "Lon" Then
If Decimal_Deg > 0 Then
Direction = " N"
Else
Direction = " S"
End If
End If
If Decimal_Deg < 0 Then
Degrees = Degrees * -1
End If
If minutes < 0 Then
minutes = minutes * -1
End If
If seconds < 0 Then
seconds = seconds * -1
End If
Convert_Degree = " " & Degrees & "° " & Fix(minutes) & "' " _
& seconds & Chr(34) & Direction
End With
End Function
To use this function, open Excel, press Alt-F8. Paste the above code in. Press Alt-F8. In your worksheet, use =Convert_Degree(cellreference,”type”) where cellreference is the cell that you want to convert and degreetype is either Lat or Long.
November 16, 2007 at 7:28 am · Filed under programming, postgresql, sql
I like the plpgsql procedural language for PostgreSQL more than it is appropriate to like a procedural language. I find the reward:effort ratio to be quite high. Since one can do so many cool things with it, why waste time writing boring insert functions for tables?
Here’s my plpgsql function which generates the create function statements for tables in a database.
download the function
The above code is a rolled back transaction which creates the generator function and then calls it so that one can view the results. By itself, it leaves no lasting impact on the database.
The insert generator accepts no input and returns a varchar. The string it returns can be executed against the database to create one function for each table in the database.
The newly created functions will accept as input all of the fields of the table and returns the primary key of the new record or 0 (zero) if no record was inserted.
The insert generator is limited to working with somewhat simple tables, as it is not aware of multiple column primary keys.
The resulting functions assume that they are being supplied clean input. You should do something to scrub your data before handing it off to the insert functions generated using this method. There’s a lot else that this doesn’t do, so you should use it at your own risk. I make no guarantees that it will work in your environment or that it is suitable for any particular use.
I will post a function which generates update functions in the coming days.
I have tested this code on against PostgreSQL versions 8.1.9 and 7.4.18.
