step_03_create_pipes_pts_postgresql_spatial_VIEW

CREATE OR REPLACE VIEW public.pipe_pts
AS SELECT pipes.grid_no,
    pipes.mxassetnum,
    pipes.STATUS,
    pipes.x AS lon, -- very important!!
    pipes.y AS lat, -- very important!!
    st_lineinterpolatepoint(st_linemerge(pipes.shape), 0.50::DOUBLE PRECISION) AS pipes_pt
   FROM ( SELECT ml.grid_no,
            ml.mxassetnum,
            ml.STATUS,
            st_y(st_lineinterpolatepoint(st_linemerge(ml.shape), 0.50::DOUBLE PRECISION)) AS y,
            st_x(st_lineinterpolatepoint(st_linemerge(ml.shape), 0.50::DOUBLE PRECISION)) AS x,
            ml.shape
           FROM ml
        UNION
         SELECT ml_rem.grid_no,
            ml_rem.mxassetnum,
            ml_rem.STATUS,
            st_y(st_lineinterpolatepoint(st_linemerge(ml_rem.shape), 0.50::DOUBLE PRECISION)) AS y,
            st_x(st_lineinterpolatepoint(st_linemerge(ml_rem.shape), 0.50::DOUBLE PRECISION)) AS x,
            ml_rem.shape
           FROM ml_rem
        UNION
         SELECT ll.grid_no,
            ll.mxassetnum,
            ll.STATUS,
            st_y(st_lineinterpolatepoint(st_linemerge(ll.shape), 0.50::DOUBLE PRECISION)) AS y,
            st_x(st_lineinterpolatepoint(st_linemerge(ll.shape), 0.50::DOUBLE PRECISION)) AS x,
            ll.shape
           FROM ll
        UNION
         SELECT ll_rem.grid_no,
            ll_rem.mxassetnum,
            ll_rem.STATUS,
            st_y(st_lineinterpolatepoint(st_linemerge(ll_rem.shape), 0.50::DOUBLE PRECISION)) AS y,
            st_x(st_lineinterpolatepoint(st_linemerge(ll_rem.shape), 0.50::DOUBLE PRECISION)) AS x,
            ll_rem.shape
           FROM ll_rem) pipes;

-- Permissions

--ALTER TABLE public.pipe_pts OWNER TO postgres;
--GRANT ALL ON TABLE public.pipe_pts TO postgres;