0

I have soil analysis data in an Excel table. For one analysis point there are values at three depths. Besides the Excel file I also have a shapefile with the points to which I want to add the soil data. What is the common way to add three values in an field of one row or similar?

Example (Excel table):

ID  point  depth   pH-value   n-value  n-value   n-value   ~40-more-values
1   1      0-20    4          .....
2   1      20-40   5
3   1      40-60   7
4   2      0-25    4
5   2      25-50   5....

This could look like the Excel file or like the following structure in a shapefile:

ID  point  depth              pH-value   n-value   ....
1   1      0-20;20-40;40-60   4;5;7      ...
2   2      ...

I think this is not nice for doing queries. But when I make one column for each depth and soil value the table would have about 120 columns.

Also when having three rows for one sample point and each depth (like in the Excel table), then I actually have three points at the same coordinates in my shapefile. Is this a good idea when I put the shapefile into a WebGIS app and want the user to get the information about the soil-sample-point by a mouse click?

Is there a common way to represent this kind of data?

  • 2
    You description does not present a very clear picture of your data and you do not say what the data will be used for. A point of 1 is not a spatial location. Do you have XY or Lat/Long? I would think you would want 6 separate columns for the point ID, its coordinates and each of the 3 depths. The rows would be the ID, cooordinates, and PH values at each depth for that point. This would convert to an XY event layer and each depth would be easy to query and represent. – Richard Fairhurst Nov 29 '14 at 18:52
  • Well yes, that would be a good solution but I forgot to mention that the soil depth are different from point to point. 0-20-40-70 and 0-30-50-80... – Antoine Nov 30 '14 at 13:56
  • Given you have many more attributes than originally indicated and depth ranges are not consistent, it might be better to use a stacked points method. As far as stacked points being selected by a mouse click, it depends on the interface. ArcGIS' Identify tool for example would select all 3 points though you have to click them in the results list to see the values. Perhaps you would want to keep points and data separate and use a Relate? Without knowing your software or end use(s) it's hard to suggest answers. The best way to represent the data for display might be different than for analysis. – Chris W Nov 30 '14 at 19:12
  • Your question is somewhat two-pronged - how should the data be represented and how to represent it a particular way. That particular way generally isn't good for analysis or doing anything with it, but for just displaying that might work. You may want to take a look at gis.stackexchange.com/questions/103090 and gis.stackexchange.com/questions/100280 which discuss solutions to get your proposed format (flatten three rows to one while keeping all values - one answer even goes into concatenating values to a single field). – Chris W Nov 30 '14 at 19:17
2

Actually, in order to be most useful you probably want to keep the tabular data as you have it with a separate record (or attribute) for each depth. Combining the data from each depth into a single field will limit the use or make it much more difficult. With it separate you can look at each depth as a different surface in interpolation or perform any other statistical analysis in a much more discrete manner.

I assume you have coordinates for a given point? If so you'll need to create points out of those xy coordinates using an XY Event Layer (covered at many other questions here). If you already have points as a shapefile or other geometry and you just need to get the analysis data as attributes you can move on to the next step. The points do need a common ID attribute that matches up to the Excel spreadsheet.

Once you have points that represent where all of the samples are you need to join the attributes to them. At the moment you'd have a one-to-many relationship between points and records. If you did a Join, the resulting attribute table would only show you the first returned record of the match. However if you then Export the results of that join you should get three points stacked on top of each other for each original point, each with a different depth record. Note the points and table need to be in a geodatabase for this to work. This does make manual selection or display in 2D more difficult.

An alternative would be to create a one-to-one relationship between points and table by renaming your points with a different scheme - for example instead of just three records called 1, have 1-1, 1-2, and 1-3. With this method, the xy coordinates would be duplicated for each record and could be put in as attributes to create with an XY Event all at once (you still end up with stacked points). If you already have the points, you'd have to copy them twice and similarly adjust the IDs to match the scheme you come up with in Excel before doing the join.

Yet another alternative, which would allow you to have a single set of points, would be to follow Richard's idea in his comment. Each point has an attribute for the pH at a given depth (three total) - the depths become columns, but the pH values are the attribute for each point in those columns - not separate columns themselves:

PointID  pH00-20  ph20-40  ph40-60
   1        4        5        7
   2       ...      ...      ...

Having a single, non-stacked set of points would be easier to work with in 2D, and you can still create different surfaces out of it (if you're going there). Of course since you have depth ranges you don't really have enough to make a 3D surface with accurate depths, more just a pH map at some representative depth in that range.

1

In geology, a common way to represent boreholes could be:

borehole  x    y   top thickness  pH-Value
 1        ..   ..    0    20         4
 1        ..   ..   20    20         5
 1        ..   ..   40    20         7
 2       ...   ...   0    30         3
 .....

This allows to represent vertical boreholes in 3D (with GRASS GIS here)

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.