I have what seems to me to be a bit of a strange coordinate transform problem I need to solve.
I have a 3D grid of points that I have surveyed X,Y's for in state plane coordinates. Specifically they are a series of lines for example 6 lines spaced 20' apart with 50 points on each line that are 15' apart. The entire grid typically has an angle to it relative to SPC north so both the X and Y are changing even if on the same line.
We are using a computer that takes geological readings on these points and the software for it has no way to account for real world coordinates. It just assumes the first line starts at (0,0) and goes to (735,0), the second starts at (0,20) and goes to (735,20), the third starts at (0,40) and goes to (735,40) and so on. The results it gives me are in a CSV file with an X,Y, Depth, and reading value but the X,Y are based off these bogus coordinates. The positions of the results though are not exactly on the surveyed points. It fills in the full space of the grid so for example I will have data points at locations (0,1),(0,2)....(1,0),(2,1)...................
My client wants us to give them this CSV file with real world coordinates. How can I transform them?
If you can assign a point number to each geological reading, and then assign the identical point number to each surveyed point, then you could use Excel's Lookup function to replace the coordinates. I think this would be better than a coordinate transformation since you're assigning the coordinates of the exact survey position, not just the best-fit transformation result.
If the grid in the fake coordinates and the grid in the SPC system are aligned then Excel should handle it very well with a simple shift function.
FrozenNorth, post: 420085, member: 10219 wrote: If you can assign a point number to each geological reading, and then assign the identical point number to each surveyed point, then you could use Excel's Lookup function to replace the coordinates. I think this would be better than a coordinate transformation since you're assigning the coordinates of the exact survey position, not just the best-fit transformation result.
I have point numbers for both but the problem is I need coordinates for points within the grid that are not surveyed.
I have real world coordinates for 101 (0,0),102 (15,0),103 (30,0) and 201(20,0),202 (20,15),203 (20,30) and I need to get real world world coordinates for something like (1,1) or (5,8). The geologic readings CSV file has values for points that are in between my surveyed points it has thousands of them but I only have 300 surveyed points. They are all within my surveyed grid but not on actual surveyed points.
MightyMoe, post: 420088, member: 700 wrote: If the grid in the fake coordinates and the grid in the SPC system are aligned then Excel should handle it very well with a simple shift function.
I am not sure I understand. I have a CSV file with SPC coordinates for 300 points which fill up a rectangular space. My fake coordinate system is a rectangle the exact same dimensions. I need to be able to get real world coordinates from any point in the rectangle even if it was not an actual surveyed point.
Drilldo, post: 420089, member: 8604 wrote: I have point numbers for both but the problem is I need coordinates for points within the grid that are not surveyed.
I have real world coordinates for 101 (0,0),102 (15,0),103 (30,0) and 201(20,0),202 (20,15),203 (20,30) and I need to get real world world coordinates for something like (1,1) or (5,8). The geologic readings CSV file has values for points that are in between my surveyed points it has thousands of them but I only have 300 surveyed points. They are all within my surveyed grid but not on actual surveyed points.
Got it. Then yes, coordinate system transformation. You might be able to do this by dummying up a "localization" in your GPS software or you could just do a rigid translate/rotate in CAD, if you have access to CAD. CAD seems the easiest to me.
I don't have CAD. What is the cheapest software that could do this for me? Unfortunately this is not a one time problem I will need to do this about 5 times a month for the foreseeable future.
I had BricsCAD at one point in the past. It's pretty affordable.
https://www.bricsys.com/estore/
You could hand calc the transformation parameters like difference in Northing Easting and the rotation required. Then apply those parameters to your coordinates in excel using formulas. Could set up one spreadsheet template to do it.
Drilldo, post: 420076, member: 8604 wrote: I have what seems to me to be a bit of a strange coordinate transform problem I need to solve.
I have a 3D grid of points that I have surveyed X,Y's for in state plane coordinates. Specifically they are a series of lines for example 6 lines spaced 20' apart with 50 points on each line that are 15' apart. The entire grid typically has an angle to it relative to SPC north so both the X and Y are changing even if on the same line.
We are using a computer that takes geological readings on these points and the software for it has no way to account for real world coordinates. It just assumes the first line starts at (0,0) and goes to (735,0), the second starts at (0,20) and goes to (735,20), the third starts at (0,40) and goes to (735,40) and so on. The results it gives me are in a CSV file with an X,Y, Depth, and reading value but the X,Y are based off these bogus coordinates. The positions of the results though are not exactly on the surveyed points. It fills in the full space of the grid so for example I will have data points at locations (0,1),(0,2)....(1,0),(2,1)...................
My client wants us to give them this CSV file with real world coordinates. How can I transform them?
Sure. This is easy, like really easy. Bring in bogus points and add 1000 to them or whatever. Scale, rotate and translate the bogus values to your values. Export to CSV and email to client.
Unless I'm missing something, it's just a rotation and translation.
Also, since you don't have CAD, you could bring in the dummy points and do the very same thing inside your DC, export them after and voila.
Kris Morgan, post: 420108, member: 29 wrote: Unless I'm missing something...
He doesn't have CADD...
Kris Morgan, post: 420109, member: 29 wrote: Also, since you don't have CAD, you could bring in the dummy points and do the very same thing inside your DC, export them after and voila.
Ha--such a good point Kris. I'm kind of embarrassed now (being an office guy) that I recommended Excel, CAD, or GPS software, when I remember doing this in the field way back in HP48+TDS days.
You could also do a site calibration in Trimble Access or TBC.
Excel spreadsheet would be my solution if I didn't have access to CAD. I believe you can link the work sheets to run a simple translation, scaling and rotation. Once you have it down you could use it over and over assuming parameters don't change or customize each to a particular set of parameters.
Couldn't you just import the bogus CSV into the data collector then translate and rotate in there?
Free cogo software including coordinate transformation http://www.underhill.ca/software/copan-windows
I have a coordinate transformation spreadsheet you could use.
You just need to give it two points with coordinates in both systems.
It cantranslate a table of points in either direction.
Just let me know if this sounds like it would solve your problem.
We used to use site grids a lot for rectangular buildings. I think onboard "reference line" solves this problem for most people these days.
attached
Drilldo, post: 420076, member: 8604 wrote: I have what seems to me to be a bit of a strange coordinate transform problem I need to solve.
I have a 3D grid of points that I have surveyed X,Y's for in state plane coordinates. Specifically they are a series of lines for example 6 lines spaced 20' apart with 50 points on each line that are 15' apart. The entire grid typically has an angle to it relative to SPC north so both the X and Y are changing even if on the same line.
We are using a computer that takes geological readings on these points and the software for it has no way to account for real world coordinates. It just assumes the first line starts at (0,0) and goes to (735,0), the second starts at (0,20) and goes to (735,20), the third starts at (0,40) and goes to (735,40) and so on. The results it gives me are in a CSV file with an X,Y, Depth, and reading value but the X,Y are based off these bogus coordinates. The positions of the results though are not exactly on the surveyed points. It fills in the full space of the grid so for example I will have data points at locations (0,1),(0,2)....(1,0),(2,1)...................
My client wants us to give them this CSV file with real world coordinates. How can I transform them?
I've scrolled through and read the comments below, providing that you are versed in excel formula writing you can easily do any sort of translation that you need to because all you are really doing is simply manipulation numbers. Two things concern me though, your client is asking you to provide coordinates on boring locations that are not necessarily on your design points, therefor, you are giving them positions that are not based on reality. I would be very hesitant to do that. From a liability stand point I would stake the grid in the field and then go back and field locate the actual locations and issue only the coordinates of the true locations. If you are using excel you can easily include the design point coordinates, the actual coordinates and the deltas for x & y to indicate the difference between the two. While CAD packages can be expensive, you don't necessarily Need a full blown package to be up and running. Carlson Survey is an excellent package. For about $1,600 you can purchase a Survey package that runs on a free CAD platform compatible with AutoCAD. It will do just about anything any Surveyor needs to do. Carlson makes transformations/translations quick and easy. When you buy one package they will give you a license for your office PC and allow you to install a copy of a lap top for use in the field at no additional cost. I have been using it since 2002 and swear by it. Although I am not as sharp in my excel skills as I used to be, I can still develop the formulas to do what I need to do but I always like to visualize my results just to double check myself. With Carlson you can calculate your grid points and easily transform or translate points from a different coordinate system very quickly and easily, then plot them on screen for verification in a matter of a few minutes. I have used other programs that can, of course, do the same but Carlson saves you the expense of having to purchase a CAD platform and it can be used on a daily basis for every survey project you work on. Not only do you get that visual verification but the program will pay for itself in weeks through production and the lack of seeking alternative methods.