Results 1 to 8 of 8
  1. #1
    fanfan is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    4

    3D data set with stacked points at different elevations->populate each elevation.

    Hi,

    As this is a access question, I am posting this question this forum. I have also posted this question for QGIS to know if this can be done also through this software.
    https://gis.stackexchange.com/questions/314787/qgis-3d-data-set-with-stacked-points-at-different-elevations-is-it-possible-t
    I have been trying this for nearly a year, and i still unable to find a way around this.

    I have a 3D dataset consisting of xyz and property. The data points are along vertical axis (boreholes), and at different XYZ locations (at other borehole location). The data points are not all at the same elevation between each borehole location due to different soil intervals, and the property value is only provided for the Top of the interval (where the new soil type starts from a top down direction).



    My goal is to populate the borehole with properties between different soil Tops of interval, so that i can map properties at different horizontal elevation slices.
    I found how to do this with Cross Join Query in Ms Access but this creates very large data sets which can make the software crash as it provides every permutation possible. Once that is done, the data needs to be filtered. So I am wondering if there would be a better way to do this than Cross Join, if there is a way to do something similar without having to generate this huge dataset by a Cross Join query. For example it would resemble something like this in plain english: show points at elevation 110 masl at each Borehole location, if no point at elevation 110, then pick next one up.

    here is the query in SQL
    SELECT [GEOMOD_m&masl_7].BORE_HOLE_ID, [GEOMOD_m&masl_7].ZONE, [GEOMOD_m&masl_7].EAST83, [GEOMOD_m&masl_7].NORTH83, [GEOMOD_m&masl_7].ELEVATION, [GEOMOD_m&masl_7].LAYER, [GEOMOD_m&masl_7].MAT1, [GEOMOD_m&masl_7].[_code_formation_Material_DES], [GEOMOD_m&masl_7].MAT2, [GEOMOD_m&masl_7].[_code_formation_Material_1_DES], [GEOMOD_m&masl_7].MAT3, [GEOMOD_m&masl_7].[_code_formation_Material_2_DES], [GEOMOD_m&masl_7].FORMATION_TOP_DEPTH, [GEOMOD_m&masl_7].FORMATION_END_DEPTH, [GEOMOD_m&masl_7].FORMATION_END_DEPTH_UOM, [GEOMOD_m&masl_7].TOP_MASL, [GEOMOD_m&masl_7].BOT_MASL, [elevation range].[elevation range]
    FROM [GEOMOD_m&masl_7], [elevation range];

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    It will be better if you illustrate what you are trying to achieve using some example data and the outcome required. Your sql code is meaningless to us without some understanding of the data and the context in which it is used.

  3. #3
    fanfan is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    4
    see example below using a cross join query and then filtering by keeping intervals below tops and above bottoms, and reappending to the table of tops

    BH_ID UTM_ZONE UTM_EAST UTM_NORTH DEM_ELEV TOT_DEPTH DEPTH_ELEV ACCURACY STRATUM_DESCRIPTION MATERIAL_COLOR MATERIAL_1 MATERIAL_2 MATERIAL_3 MATERIAL_4 TOP_DEPTH BOTTOM_DEPTH ORIG_D_UOM Tot_Depth_MASL Top_Depth_MASL Bot_Depth_MASL
    589406 17 589111 4887522 236 7.6 0 Not Applicable gravel msa si
    Gravel Medium Sand Silt
    0 4.6 Meter 228.399993896484 232 231.4
    589406 17 589111 4887522 236 7.6 0 Not Applicable gravel msa si
    Gravel Medium Sand Silt
    0 4.6 Meter 228.399993896484 233 231.4
    589406 17 589111 4887522 236 7.6 0 Not Applicable fsa msa
    Fine Sand Medium Sand

    4.6 7.6 Meter 228.399993896484 229 228.4
    589406 17 589111 4887522 236 7.6 0 Not Applicable fsa msa
    Fine Sand Medium Sand

    4.6 7.6 Meter 228.399993896484 230 228.4
    589406 17 589111 4887522 236 7.6 0 Not Applicable fsa msa
    Fine Sand Medium Sand

    4.6 7.6 Meter 228.399993896484 231 228.4
    589406 17 589111 4887522 236 7.6 0 Not Applicable gravel msa si
    Gravel Medium Sand Silt
    0 4.6 Meter 228.399993896484 235 231.4
    589406 17 589111 4887522 236 7.6 0 Not Applicable gravel msa si
    Gravel Medium Sand Silt
    0 4.6 Meter 228.399993896484 234 231.4
    589406 17 589111 4887522 236 7.6 0 Not Applicable fsa msa
    Fine Sand Medium Sand

    4.6 7.6 Meter 228.399993896484 231.4 228.4
    589406 17 589111 4887522 236 7.6 0 Not Applicable gravel msa si
    Gravel Medium Sand Silt
    0 4.6 Meter 228.399993896484 236 231.4

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    The correct name for this is actually a Cartesian join AKA no join which as you say creates a record for every combination of the two tables.
    So if you have 1000 records in one table and 500 in the other, the query has 500,000 records.
    Are you sure you need this type of query?
    Are there no common fields in both tables that you can use to create a join?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    fanfan is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    4
    i would like to create a new data point between the top and the bottom of a soil deposit, so that i can create a 3d interpolation in second phase using a different software package (grass gis). I need to create these intervals to give Weight to the data point in the interpolation process.
    All I am trying to do is to add records in between Tops and Bottoms with the same property as the Tops.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I repeat

    It will be better if you illustrate what you are trying to achieve using some example data and the outcome required
    instead, you are providing a solution that you are not happy with

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    You don't show filter criteria in query.

    Suggest provide raw data in a form we can work with. Can build tables in post or attach files. Follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    fanfan is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    4
    I solved this by running the Cartesian query and adding criteria that it should be for values between Top and Bottom of intervals. this way it only shows the 1 m intervals between the Top and Bottoms. This creates much less records. Then I append the newly created interval records to the Tops only table by using an Append Query.

    Thanks!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 11-02-2015, 03:28 PM
  2. Exclude data points where fields are equal
    By jysupert in forum Queries
    Replies: 1
    Last Post: 09-02-2014, 03:30 PM
  3. Replies: 1
    Last Post: 05-04-2012, 02:22 PM
  4. Reporting stacked data from qry in columns
    By SPW_12 in forum Reports
    Replies: 3
    Last Post: 01-17-2012, 04:34 PM
  5. Assign elevation to a FE before distribution!!
    By thekruser in forum Programming
    Replies: 4
    Last Post: 09-16-2010, 01:41 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums