recreation_resource_search_view

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

rec_resource_id

varchar

√

null

recreation_resource.rec_resource_id

Implied Constraint

name

varchar

200

√

null

closest_community

varchar

200

√

null

display_on_public_site

bool

√

null

recreation_resource_type

varchar

200

√

null

recreation_resource_type_code

varchar

2147483647

√

null

recreation_site_point

geometry

2147483647

√

null

recreation_activity

jsonb

2147483647

√

null

recreation_status

jsonb

2147483647

√

null

recreation_resource_images

jsonb

2147483647

√

null

district_code

varchar

√

null

recreation_district_code.district_code

Implied Constraint

district_description

varchar

120

√

null

access_code

varchar

√

null

recreation_access_code.access_code

Implied Constraint

access_description

varchar

120

√

null

recreation_structure

jsonb

2147483647

√

null

has_toilets

bool

√

null

has_tables

bool

√

null

is_fees

bool

√

null

is_reservable

bool

√

null

Relationships

Close relationships within degrees of separation

View Definition

SELECT DISTINCT ON (rr.rec_resource_id) rr.rec_resource_id,
    rr.name,
    rr.closest_community,
    rr.display_on_public_site,
    rrtv.description AS recreation_resource_type,
    rrtv.rec_resource_type_code AS recreation_resource_type_code,
    st_setsrid(rsp.geometry, 3005) AS recreation_site_point,
    jsonb_agg(DISTINCT jsonb_build_object('recreation_activity_code', ra.recreation_activity_code, 'description', rac.description)) FILTER (WHERE (ra.recreation_activity_code IS NOT NULL)) AS recreation_activity,
    jsonb_build_object('status_code', rs.status_code, 'description', rsc.description, 'comment', rs.comment) AS recreation_status,
    jsonb_agg(DISTINCT jsonb_build_object('ref_id', ri.ref_id, 'caption', ri.caption, 'recreation_resource_image_variants', rv.variants)) FILTER (WHERE (ri.ref_id IS NOT NULL)) AS recreation_resource_images,
    rd.district_code,
    rd.description AS district_description,
    ac.access_code,
    ac.description AS access_description,
    jsonb_agg(DISTINCT jsonb_build_object('structure_code', rsc2.structure_code, 'description', rsc2.description)) FILTER (WHERE (rsc2.structure_code IS NOT NULL)) AS recreation_structure,
        CASE
            WHEN (EXISTS ( SELECT 1
               FROM rst.recreation_structure_code rsc_1
              WHERE (((rsc_1.description)::text ~~* '%toilet%'::text) AND (rsc_1.structure_code IN ( SELECT rst_1.structure_code
                       FROM rst.recreation_structure rst_1
                      WHERE ((rst_1.rec_resource_id)::text = (rr.rec_resource_id)::text)))))) THEN true
            ELSE false
        END AS has_toilets,
        CASE
            WHEN (EXISTS ( SELECT 1
               FROM rst.recreation_structure_code rsc_1
              WHERE (((rsc_1.description)::text ~~* '%table%'::text) AND (rsc_1.structure_code IN ( SELECT rst_1.structure_code
                       FROM rst.recreation_structure rst_1
                      WHERE ((rst_1.rec_resource_id)::text = (rr.rec_resource_id)::text)))))) THEN true
            ELSE false
        END AS has_tables,
        CASE
            WHEN (EXISTS ( SELECT 1
               FROM rst.recreation_fee rf
              WHERE ((rf.rec_resource_id)::text = (rr.rec_resource_id)::text))) THEN true
            ELSE false
        END AS is_fees,
        CASE
            WHEN (EXISTS ( SELECT 1
               FROM rst.recreation_resource_reservation_info rri
              WHERE ((rri.rec_resource_id)::text = (rr.rec_resource_id)::text))) THEN true
            ELSE false
        END AS is_reservable
   FROM (((((((((((((rst.recreation_resource rr
     LEFT JOIN rst.recreation_resource_type_view_public rrtv ON (((rr.rec_resource_id)::text = (rrtv.rec_resource_id)::text)))
     LEFT JOIN rst.recreation_activity ra ON (((rr.rec_resource_id)::text = (ra.rec_resource_id)::text)))
     LEFT JOIN rst.recreation_activity_code rac ON ((ra.recreation_activity_code = rac.recreation_activity_code)))
     LEFT JOIN rst.recreation_status rs ON (((rr.rec_resource_id)::text = (rs.rec_resource_id)::text)))
     LEFT JOIN rst.recreation_status_code rsc ON ((rs.status_code = rsc.status_code)))
     LEFT JOIN rst.recreation_resource_images ri ON (((rr.rec_resource_id)::text = (ri.rec_resource_id)::text)))
     LEFT JOIN rst.recreation_site_point rsp ON (((rr.rec_resource_id)::text = (rsp.rec_resource_id)::text)))
     LEFT JOIN LATERAL ( SELECT jsonb_agg(jsonb_build_object('size_code', riv.size_code, 'url', riv.url, 'width', riv.width, 'height', riv.height, 'extension', riv.extension)) AS variants
           FROM rst.recreation_resource_image_variants riv
          WHERE (((riv.ref_id)::text = (ri.ref_id)::text) AND ((riv.size_code)::text = 'pre'::text))) rv ON (true))
     LEFT JOIN rst.recreation_district_code rd ON (((rr.district_code)::text = (rd.district_code)::text)))
     LEFT JOIN rst.recreation_access ra1 ON (((rr.rec_resource_id)::text = (ra1.rec_resource_id)::text)))
     LEFT JOIN rst.recreation_access_code ac ON (((ra1.access_code)::text = (ac.access_code)::text)))
     LEFT JOIN rst.recreation_structure rst ON (((rr.rec_resource_id)::text = (rst.rec_resource_id)::text)))
     LEFT JOIN rst.recreation_structure_code rsc2 ON ((rst.structure_code = rsc2.structure_code)))
  GROUP BY rr.rec_resource_id, rr.name, rr.closest_community, rr.display_on_public_site, rrtv.rec_resource_type_code, rsp.geometry, rrtv.description, rs.status_code, rsc.description, rs.comment, rd.district_code, rd.description, ac.access_code, ac.description;

recreation_resource_search_view

Description

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views