Confirm

Expand all | Collapse all

Feature Activity Log

  • 1.  Feature Activity Log

    Posted 12-22-2019 19:11

    I have been trying to create an SQL to show both the Feature Activity Logs & some details from the feature.  What I am getting is a lot of duplication when adding these in even when using where.  I haven't had any training on SQL so mainly use the wizard tools, however as 'Feature Activity Logs' don't seem to link tables with features this has made it difficult.

    The base SQL which shows only the Activity Log information (and nothing from the feature) does provide me with the right numbers:

    SELECT
    feature_activity.activity_key,
    feature_activity.feature_key,
    feature_activity.activity_number,
    activity_class.activity_code,
    activity_class.activity_name,
    feature_activity.activity_date,
    feature_activity.activity_desc,
    feature_activity.activity_notes,
    feature_activity.finance_per_code,
    feature_activity.budget_cost,
    feature_activity.activity_status,
    feature_activity.logged_by,
    feature_activity.logged_time
    FROM
    activity_class,
    feature_activity
    WHERE
    activity_class.activity_code = feature_activity.activity_code

    However, when I start adding in additional selections from the associated feature, ie - Central Asset ID, Feature Group, Feature Type, Centroid X & Y, 3x measurement types (Area, Width, Length) & the condition to the SQL I see either duplicates, the wrong data or no data at all.

    If anyone already has something they use or can help with how I need to add these that would be a great.



    ------------------------------
    Kristy Honor
    Adelaide Hills Council
    SA
    ------------------------------


  • 2.  RE: Feature Activity Log

    Posted 12-22-2019 21:02
    Hi Kristy,

    I'm not really familiar with the Feature Activity Logs, we only have 53 fairly old records of which there are 29 unique features so some features have multiple activity logs, could this be the source of duplicates?

    I was able to use the SQL Wizard to create a join to the feature activity log as the feature_key can be used to link to the feature table but will require some extra lines to be inserted manually.
    I started with the Feature Table and selected information such as the site, location, coordinates and feature type/group name then added the join manually, this result returned all 53 records in the FAL table so no additional records were added.


    There is likely a better solution but hopefully this helps, see below for my query and the manually inserted info in bold

    SELECT
    site.site_code,
    central_site.site_name,
    feature.plot_number,
    feature.feature_location,
    feature_type.feature_type_name,
    feature_group.feature_group_name,
    feature.feat_cent_east,
    feature.feat_cent_north,
    feature.central_asset_id,
    feat_measurement.feature_quantity,
    feat_measurement_2.feature_quantity,

    feature_activity.activity_key,
    feature_activity.feature_key,
    feature_activity.activity_number,
    activity_class.activity_code,
    activity_class.activity_name,
    feature_activity.activity_date,
    feature_activity.activity_desc,
    feature_activity.activity_notes,
    feature_activity.finance_per_code,
    feature_activity.budget_cost,
    feature_activity.activity_status,
    feature_activity.logged_by,
    feature_activity.logged_time

    FROM
    central_site,
    feat_measurement,
    feat_measurement feat_measurement_2,
    feature,
    feature_group,
    feature_type,
    site,

    activity_class,
    feature_activity

    WHERE
    central_site.site_code = site.site_code AND
    site.site_code = feature.site_code AND
    feature_group.feature_group_code = feature_type.feature_group_code AND
    feature_type.feature_type_code = feature.feature_type_code AND
    feature.site_code = feat_measurement.site_code AND
    feature.plot_number = feat_measurement.plot_number AND
    feat_measurement.measurement_code = 'AREA' AND
    feature.site_code = feat_measurement_2.site_code AND
    feature.plot_number = feat_measurement_2.plot_number AND
    feat_measurement_2.measurement_code = 'NUMB'  AND

    activity_class.activity_code = feature_activity.activity_code AND
    feature.feature_key = feature_activity.feature_key
    ​​

    ------------------------------
    James Corletto
    Team Leader Strategic Asset Management
    City of Salisbury
    Salisbury
    ------------------------------



  • 3.  RE: Feature Activity Log

    Posted 12-22-2019 22:40
    Thanks James - This has confirmed that I was in the right direction, and why I have been having an issue with firstly duplicates and then not the correct amount of 'Feature Activities'.  I was trying to select all of them in the one data source, however with the inclusion of specific measurements or conditions this limited my selection if the feature group I was after didn't hold that information.

    Really appreciate the help,
    Thanks
    Kristy

    ------------------------------
    Kristy Honor
    Adelaide Hills Council
    SA
    ------------------------------