[Solved] Land survey conversions

Discuss the spreadsheet application
Post Reply
Trey Harris
Posts: 3
Joined: Sat Jul 24, 2021 7:16 pm

[Solved] Land survey conversions

Post by Trey Harris »

I’ve searched and not found any commands to convert land survey directions and angles from one format to another. Does anyone know if there are any functions for OpenOffice that can be downloaded and installed that will accurately do these conversions?
Last edited by MrProgrammer on Sun Aug 01, 2021 5:49 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved]
OpenOffice 3.2.1 running on MS 10
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Land survey conversions

Post by MrProgrammer »

Trey Harris wrote:convert land survey directions and angles from one format to another
You are more likely to get appropriate assistance if you provide details about what formats of "directions" and "angles" you are working with and how one "converts" them. Conversion of angles between degrees:minutes:seconds and decimal degrees, or between degrees and radians, is easy, if that is part of your question. Otherwise, links to the definitions of these formats of interest and conversions would be a good starting point. People here are familar with OpenOffice, but you should not expect to find volunteers with experience in land surveying. If you don't know yourself how to do the conversions, this is probably not the right forum to ask about that subject.

[Solved] Match closest East/North to Lat/long
[Solved] Macro in spreadsheet - syntax problem?
Trey Harris wrote:… functions for OpenOffice …
Is this a spreadsheet question?
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
Trey Harris
Posts: 3
Joined: Sat Jul 24, 2021 7:16 pm

Re: Land survey conversions

Post by Trey Harris »

I am looking for spreadsheet formulas that can be used to help calculate land surveys. It is easy to convert land survey directions from one format to another one; however it does require a little time to it. It would be much quicker if there are simple to use formulas available to quickly convert degrees from one format to another. Since surveys are very common, I feel it’s likely someone may have already developed some spreadsheet commands to do this.

These are the types of commands I imagine:

Command   Format                    NE Example°       SE Example°       SW Example°       NW Example°
DMS       Degrees Minutes Seconds   N 08° 12’ 09″ E   S 23° 24’ 18″ E   S 38° 36’ 27″ W   N 53° 48’ 36″ W
DD        Degrees Decimal           N 8.2025° E       S 23.405° E       S 38.6075° W      N 53.81° W
AZ        Azimuth                   8.2025°           156.595°          218.6075°         306.19°

(Sorry, I tried to edit this to keep the columns aligned, but I couldn't figure out how to do this. So I used periods to try to align them)


These commands could be used to convert any direction to the desired format. The command would be able to automatically identify the current format of the angle and, if it is different from the desired format, convert it and show that result in the cell. If the line direction is already in the desired format, then it should remain the same. The format to be converted must be able to reference a properly formatted line direction in another cell without affecting the original format.

For instance:

The formula “=dms(N 8.2025° E)” would display the equivalent DMS value formatted as “N 08° 12’ 09″ E”
The formula “=dd(156.595°)” would display the equivalent DD value formatted as “S 23.405 E”
The formula “=az(S 38° 36’ 27″ W)” would display the equivalent AZ value formatted as “218.6075”

Also, there should be a formula to quickly calculate the resulting angles between two lines, regardless of the line formats. There are two possible angles, each with a value from 0 to 180 degrees. So I’m thinking the commands should be AGS for the smaller of the two angles and AGL for the larger of the two angles. If the two lines are the same direction, then the AGS would be 0 and AGL would be 180 degrees. When the directions of the two lines are perpendicular, then both the AGS and AGL would be 90 degrees.

For instance:

Using the DMS format, the formula “=ags(N 08° 12’ 09″ E, S 23° 24’ 18″ E) would result in “31.6075°”
Using the DMS format, the formula “=agl(N 08° 12’ 09″ E, S 23° 24’ 18″ E) would result in “148.3925°”
Using the DD format, the formula “=ags(N 8.2025° E, S 23.405° E) would result in “31.6075°”
Using the DD format, the formula “=agl(N 8.2025° E, S 23.405° E) would result in “148.3925°”
Using the AZ format, the formula “=ags(8.2025°, 156.595°) would result in “31.6075°”
Using the AZ format, the formula “=agl(8.2025°, 156.595°) would result in “148.3925°”


So my question is, are there any commands available that can be added to OpenOffice that would preform these tasks?
Last edited by MrProgrammer on Sun Jul 25, 2021 5:53 am, edited 2 times in total.
Reason: Add formatting for readability
OpenOffice 3.2.1 running on MS 10
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Land survey conversions

Post by MrProgrammer »

Hi, and welcome to the forum.
Trey Harris wrote:The formula =dms(N 8.2025° E) would …
The formula =dd(156.595°) would …
The formula =az(S 38° 36’ 27″ W) would …
This part of the post suggests you need to start by reading the following tutorials. None of N 8.2025° E, or 156.595° or S 38° 36’ 27″ W are valid numbers, so they must be marked as text (quoted) when used in a formula if you want to avoid a syntax error. There is no way to add new numeric formats unless you are an experienced OpenOffice developer. =dd(156.595) (without °) would be valid though.
[Tutorial] Ten concepts that every Calc user should know
[Tutorial] Calc formula terms
Trey Harris wrote:So my question is, are there any commands available that can be added to OpenOffice that would perform these tasks?
I am not aware of any extensions for these conversions.
Trey Harris wrote:Since surveys are very common, I feel it’s likely someone may have already developed some spreadsheet commands to do this.
OK. You can perform a web search as well as I can. In ten years on the forum the only topics I recall about surveying are the ones I previously linked.
Trey Harris wrote:It is easy to convert land survey directions from one format to another one; however it does require a little time to it. It would be much quicker if there are simple to use formulas available to quickly convert degrees from one format to another.
This attachment uses formulas to convert between DMS, DD, and AZ formats. It correctly converts all of your examples.
202107250000.ods
(20.9 KiB) Downloaded 108 times
You may want to set the Calc → Calculate option Limit decimals for General number format. Options are set with OpenOffice → Preferences on a Mac, Tools → Options on other platforms.
Limit.gif
Trey Harris wrote:The command would be able to automatically identify the current format of the angle and, if it is different from the desired format, convert it and show that result in the cell.
Calc works well with numbers, but parsing text is difficult. Good luck with that. My attachment needs you to identify the current format, which is presumably the easy part. It does minimal parsing to remove the non-numeric characters so it can access the numeric values. It may fail mysteriously if the format is wrong, say, NE 08° 12’ 09″ or N 08°12’09″ E instead of N 08° 12’ 09″ E. I supppose you could perform a complex series of calculations to determine the format and catch any errors, however I have no interest in helping with that.
Trey Harris wrote:Also, there should be a formula to quickly calculate the resulting angles between two lines, regardless of the line formats.
It's very simple to calculate AGS and AGL, but my attachment requires the angles in decimal degrees (as a number, not text, so no ° symbol). Conversion from other formats is done in the other sheet.
Trey Harris wrote:I am looking for spreadsheet formulas that can be used to help calculate land surveys.
Calc allows you to write user-defined functions in any of several programming languages, but usually this is Basic or Python. Writing your own function will be considerably more difficult than using the built-in spreadsheet functions, but would allow for more complex processing to detect varying formats. We have a separate macro forum for that if you want to pursue the project. The volunteers in the macro forum specialize in that aspect of OpenOffice. Begin by reading See http://www.pitonyak.org/oo.php. Expect to spend at least a week on this, and several more if you're not familiar with computer languages.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
Trey Harris
Posts: 3
Joined: Sat Jul 24, 2021 7:16 pm

Re: Land survey conversions

Post by Trey Harris »

Okay, thanks for the help and suggestions.
OpenOffice 3.2.1 running on MS 10
Post Reply