AMANDA Query WZDX Release 1.0

[TracyLinder]

• [x] Tracy will add some query info
• [x] Charlie will write the query
• [x] Tracy & Charlie will validate the data output
  • Here is a validation checklist

[TracyLinder]

AMANDA query info

• Folder type = 'RW'
• Subtype = 50500 (TURP)
• Folder Status = 'Active'
• Folder indate > 12/31/2017 This is important to filter out 'LA' permits
• Event start date = info code (fi) 75980
• Event end date = fi 75985
• Extension start date = fi 75985
• Extension end date = fi 75994

Start/End Date logic for TURPS TURPS have 2 sets of activity dates. Original dates, and extension dates.

• If extension start/end dates have a value, use those dates
• If extension dates are null, use the original dates

[TracyLinder]

@Charlie-Henry I added helpful things for the AMANDA query. I'm not sure I remembered it all, but it should be good to go.

[TracyLinder]

Updates to the query:

• [ ] Remove alleys
• [ ] Include unmetered parking lanes

[Charlie-Henry]

Here's the query, we can review if you're interested @TracyLinder?

SELECT
    f.FOLDERRSN,
    f.FOLDERNAME,
    f.INDATE,
    f.ISSUEDATE,
    f.FOLDERDESCRIPTION,
    f.FOLDERCONDITION,
    f.CUSTOMFOLDERNUMBER,
    TO_CHAR(fi.START_DATE, 'YYYY-MM-DD HH24:MI') AS START_DATE,
    TO_CHAR(fi.END_DATE, 'YYYY-MM-DD HH24:MI') AS END_DATE,
    TO_CHAR(fi.EXTENSION_START_DATE, 'YYYY-MM-DD HH24:MI') AS EXTENSION_START_DATE,
    TO_CHAR(fi.EXTENSION_END_DATE, 'YYYY-MM-DD HH24:MI') AS EXTENSION_END_DATE,
    ff.LOCATION_NAME,
    ff.CLOSURE_TYPE,
    ff.SEGMENT_ID,
    ff.LENGTH,
    ff.WIDTH,
    ff.NUM_LANES
FROM
    folder f
    LEFT OUTER JOIN (
    SELECT
        FOLDERRSN,
        MAX(
            CASE WHEN INFOCODE = 75980 THEN
                INFOVALUEDATETIME
            END) AS START_DATE,
        MAX(
            CASE WHEN INFOCODE = 75985 THEN
                INFOVALUEDATETIME
            END) AS end_date,
        MAX(
            CASE WHEN INFOCODE = 75993 THEN
                INFOVALUEDATETIME
            END) AS extension_start_date,
        MAX(
            CASE WHEN INFOCODE = 75994 THEN
                INFOVALUEDATETIME
            END) AS extension_end_date
    FROM
        FOLDERINFO
    GROUP BY
        FOLDERRSN) fi ON f.FOLDERRSN = fi.FOLDERRSN
    LEFT OUTER JOIN (
    SELECT
        FOLDERRSN,
        C01 AS location_name,
        C02 AS closure_type,
        N01 AS segment_id,
        N02 AS length,
        N03 AS width,
        N04 AS num_lanes
    FROM
        FOLDERFREEFORM
    WHERE
        FREEFORMCODE = 1010
        AND C02 in('Traffic Lane : Dimensions', 'Closure : Full Road', 'Closure : Alley', 'Closure : Sidewalk', 'Parking Lane : Dimensions')) ff ON ff.FOLDERRSN = f.FOLDERRSN
WHERE
    f.FOLDERTYPE = 'RW'
    AND f.SUBCODE = 50500 -- Temporary use of ROW permits (TURPs)
    AND f.STATUSCODE = 50010 -- active permits
    AND f.INDATE > TO_DATE('2017-12-31', 'yyyy-mm-dd') -- this filters out old 'LA' permits
        AND ff.segment_id IS NOT NULL

[Charlie-Henry]

@TracyLinder I think we can set up some time to review my first attempt of setting this this feed up. I passed the JSON schema validator with this ✅.

Map: https://austin.maps.arcgis.com/apps/mapviewer/index.html?webmap=4ca960105b34455ebea0bbede29bfdec

Things to talk about:

• What can we filter out so we can have the most accurate data in the feed? I see some closures on Lamar and S 1st that are an entire month long which are probably not happening
• What to do when a permit has an invalid segment ID? I am searching this CTM street segment layer in arcGIS online, maybe the ROW folks are using a different layer?

[TracyLinder]

Update to exclude secondary permits from the data

• If folder info 72101 (Secondary) is YES, exclude the permit

[Charlie-Henry]

@TracyLinder, I've updated the AGOL map to include the folderrsn and I also filtered out those secondary permits

https://austin.maps.arcgis.com/apps/mapviewer/index.html?webmap=4ca960105b34455ebea0bbede29bfdec

[TracyLinder]

Tracy will do a quick spot check of the map data, then schedule a meeting with stakeholders to review further.

[TracyLinder]

Stakeholder Review 8/20/24

• Refined TURP full closure should be when 'Full Closure' is yes; everything else is partial.
• The EX folder does have enough data to include in the WZDX, but it will need to have it's own query (b/c the differences in folder configuration makes a single query difficult.)
• Charlie suggests speaking to the MMC about how they might benefit from this data set. (Lance, Allison R.)

Next Steps:

• [ ] Updating TURP query to remove the things Charlie added for EX & validate again
• [ ] Build an EX query (Charlie)
• [ ] Meet w/ MMC (Tracy schedule)