-
Notifications
You must be signed in to change notification settings - Fork 13
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Create polygons for sites table in BETYdb #174
Comments
@JeffWhiteAZ or @smarshall-bmr
|
These are the plot locations as given in the gantry coordinate system for the first and second planting respectively. The y coordinates change a negligible amount as to the point that the y coordinates are given as the same between plantings. The x axis is offset to the north approximately 50cm for the second planting. |
@dlebauer I have a few questions related to the R code:
However, I found that the corners points I used are actually the six points SW SE CW CE NW NE in the sheet below. I am not sure what is the relationship between the three lat&lons in the sheet and which one is the truth.
Here is an example of how to add lat1 and lon1 for X1 and Y1, add lat2 and lon2 for X2 and Y2 to grid data frame using the projection I provided before.
|
@tingli3 I answered your questions inline above |
@dlebauer Thank you. The true SW and SE lat & lon will be very important in the projection definition. If USDA is the truth lat & lon for the SW and SE, I need to update the projection based on it and make sure after projection the SW will be (0,0) and SE will be something like (27.***, 0). Please tell me whether my understanding is correct. However, here the coordinate system we generated using R code start at (0, 0) and ends at (24.384, 215.500). The four points given by USDA is approximately 27.5m * 110m. If that is the case, I wonder whether the four corners of the gantry plot layout should match with the 4 points of USDA. Specifically, whether the SW point of the plot1 is the SW point of USDA points and SE point of the plot16 is the SE point of USDA points, and how to handle the difference. |
@tingli3 there are two different issues. First, is to determine the coordinate reference systems. That should be covered in issue terraref/reference-data#32. There are four (or more) points used to align the two (MAC and USDA) GPS systems. These are in concrete posts around the edge of the field. There are also points used to align the gantry coordinate system with the MAC RTK GPS. Second is this issue, to determine the bounding boxes of the individual plots in these different coordinate reference systems. I will send you an email with a complete discussion of how these points were collected. |
@dlebauer So, what is the X and Y in the R code corresponds to ? Where is the true (0,0) ? |
@tingli3 First, I apologize on behalf of all involved that this is such a mess. But to try and clarify, there are a three (X,Y) coordinate systems
To map the plots (3) to the gantry (1), it is necessary to align the X,Y pairs in the R code with the X,Y measured coordinates provided by @smarshall-bmr in this spreadsheet: https://docs.google.com/spreadsheets/d/1eQSeVMPfrWS9Li4XlJf3qs2F8txmddbwZhjOfMGAvt8/edit#gid=883764630 |
@dlebauer If this is not the case, I think the first step is to find out the bounding box of gantry field , and how the gantry should be converted (set 0,0 X-axis Y-axis for it), and how it is relate the to the XY of the plots. Is it possible to use the min and max in the https://docs.google.com/spreadsheets/d/1eQSeVMPfrWS9Li4XlJf3qs2F8txmddbwZhjOfMGAvt8/edit#gid=883764630 to get bounding box of the gantry coordinates? If this is the case, which sheet (RangeCentersSorghum1 or RangeCentersSorghum2) should be used? And it seems that range 1 and range 54 does not have value in RangeCentersSorghum1 and the value in this two sheet are different. If it is hard to know the exact bounding box of gantry, will it possible for us to define a coordinate for it? That is, the (0,0) of the plots may related to a (small value, small value) of the defined coordinate with X increasing to the East. |
@tingli3 I think it would help if you could create a map with the different coordinates reference systems and available reference points. I am confused that x'=f(y) instead of f(x), and it would really help to clarify. Otherwise, I am afraid that the discussion we are having here will add to the confusion. ... and as you sort this out please document the issues and solutions terraref/documentation#7 |
@tingli3 RangeCentersSorghum1 and RangeCentersSorghum2 are for the first and second planting seasons, respectively. I believe the GPS reference points that compare UA MAC and USDA GPS's correspond to concrete pylons that are in the ground a few meters outside of the planted area. |
@dlebauer For the two planting seasons, are the coordinates recorded in the same gantry coordinate system? Which one should be used? Finding the exact bounding box of the gantry data is very important. |
For the two planting seasons, the coordinates are recorded in the same system. We will need to recreate the plot boundaries each time a new crop is planted, and the bounding box of the planted area as well as the plots themselves will change. |
@dlebauer And, as the bounding box is changing and (0,0) point is unknown, the new gantry coordinates with X increasing to the east will also not start exactly at (0,0). The (0,0) point of the plots may corresponding to a gantry coordinate not exactly (0,0). Is that OK? |
Yes that is okay. Only the plots change. I think we should discuss in
|
I was very confused and when you (@tingli3) pointed out x'=f(y) I realized that I misunderstood the gantry coordinate system. But drawing a map helped me. This may be incorrect, but it is my current working hypothesis of how the different coordinate systems overlay. If x goes positive to the north and y goes positive to the east, we don't need a coordinate system with x going east. That was only because if x increases to the west / to the left, images, plots, and perspective are inverted. So, we should review these maps and be able to encode this in a database and use it to subset data and visualize the experimental design. [p.s. Did anyone else ( @pless @LTBen @rjstrand @smarshall-bmr @doctorroboto et al ) catch on that x is increasing to the north?] |
This R code corrects the order of plotID and add column and range ID to each plot. Grid[i,] corresponds to plot i. The X1, X2, Y1 and Y2 need to be updated using the true gantry coordinates.
|
This is the R code for projection. Lat&lon can be returned through gantry2latlon(gantry_x, gantry_y) e.g. gantry2latlon(3.8,0). How the choose of the point (currently CE) and projection will effect the error needs to be experimented later.
|
Next:
Due Oct 14th. |
From Andy French on Sept 2, 2016:
|
@tingli3 Upon Andy comment 3: need to confirm with him the EPSG number: |
@tingli3 Upon Andy comment 2: Need to clarify if the 2.5m is on x or y or both directions by going through the email. Ask if needed. |
@tingli3 Upon Andy comment 2: need to ask Andy or Bob whether the Lematec coord derived using the linear regression results for MAC coord system has been corrected in the definition of b0, b1 and b2. |
@tingli3 Need to confirm with Andy and Bob or @dlebauer whether the lat lon info associated with Lemnatec gantry raw data is the corrected values based on Andy's comment 1. This is related to the USDA point coord in column F-G row 4-7 in Coordinate Reference System Summary |
I tweaked the code to insert polygons for season 2 by adding betydb_insert[[i]] <- paste("INSERT INTO sites (sitename, geometry, updated_at, created_at) VALUES (",
paste0("'MAC Field Scanner Field Plot ", grid$plot[i], " Season 2', "),
geom_latlon, ", now(), now());")
}
to the final for loop. and then filled out the information by running update sites set city = 'Maricopa', state = 'Arizona', country = 'USA', time_zone = 'America/Phoenix', greenhouse = 'f', user_id = 6000000004, updated_at = now() where sitename like 'MAC Field%'; the |
@dlebauer (and @tingli3) Please don't include the |
@yanliu-chn I think the lines in the MAC --> USDA (M2U) section of https://github.com/terraref/documentation/blob/version1/spatial-reference-system-data.md are mis-labelled: Mx/Ux is the longitude and My/Uy is the latitude, correct? So is 0.000020308287 the longitude offset or the y-offset (which is really latitude)? Also, are you still assuming Mx/Ux are in UTM units here, or are these in degrees? @tingli3's script seems to assume they are degrees; he applies the offsets after doing the proj4 projection. If they really are degrees, could you clarify this in the documentation? |
@gsrohde Thanks for pointing this out. I just fixed it. It was a typo. You are right. Mx/Ux is longitude in degrees; My/Uy is latitude in degrees. I have specified in the original doc that it was to convert from MAC lat,lon to USDA lat,lon. |
@yanliu-chn Thanks for fixing. I guess using the terms "lat" and "lon" makes it clear that in the MAC --> USDA (M2U) section we are no longer working in UTM coordinates. What was confusing to me is that in the Gantry --> MAC section, the variable |
@dlebauer, @yanliu-chn I'm not sure which SQL statements I should be looking at. The statements generated by the code in SQL.zip mentioned in comment #174 (comment) doesn't seem to directly relate to BETYdb, but @dlebauer references SQL statements designed for BETYdb in his comment #174 (comment). |
@gsrohde I unassigned you when I realized more work needed to be done. But I went ahead and inserted these .... for this case the incorrect use of now() is trivial, but now that I know I won't specify this. |
@dlebauer Please give me an example of the sql statement I should generate. |
@tingli3 please see #174 (comment) |
@tingli3 ... but leave out the |
@gsrohde Can you give me an example sql? |
@tingli3 The file David attached to the comment #174 (comment) has several example insertion statements. Here's the first (but with the timestamp columns omitted, as they should be):
|
season1_sql.txt |
|
@dlebauer The season 1 and season 2 sql statements are based on the example given. If season 1 is different from season 2, please give me another example. Also, I have added the code. |
@tingli3 I already inserted season 2 For season 1, the first line would be UPDATE sites
SET geometry =
ST_GeomFromText('POLYGON((
-111.975049873054 33.0745274579941 353,
-111.975033515714 33.0745274783365 353,
-111.975033525032 33.0745545428892 353,
-111.975049882377 33.0745545225468 353,
-111.975049873054 33.0745274579941 353))', 4326));
WHERE sitename =
'MAC Field Scanner Field Plot 1'; P.S. I reformatted your R code following http://adv-r.had.co.nz/Style.html so ... please pull changes before updating. |
@tingli3 all set - thanks! Closing - can you please update documentation (terraref/documentation#7)? |
Description
The spring planting plots are described in this spreadsheet on google drive
I began working with these data in R. The following code uses the above dimensions to generate the points and then convert them to polygons in WKT that would be suitable for entering into the database
(20160421_plots.csv.txt)
except that we need to
The integer values for 'plot' are 1-864 are associated with plots in the terraref instance of BETYdb named "MAC Field Scanner Field Plot i"
Here are the sites in TERRA-REF BETYdb that we are trying to update: https://terraref.ncsa.illinois.edu/bety/sites.json
Here are the sql statements in a system that puts (0,0) at the bottom right of the field:
insert_site_geometries.sql.txt
Related issues:
The text was updated successfully, but these errors were encountered: