I'm trying to make a view from a table without a geometry column. My first issue is when I click on "New View" and then put the query in and click 'OK'. The query statement is replaced with some other statement for the view. It doesn't give me an error. Is there a permission issue when you create a view? I'm using pgAdmin III. Database version 9.6.
Here is the query that I want in the view.
SELECT j.speed, j.level, j.length, j.delay, j.city, j.street, j.line, j.pub_utc_date at time zone 'utc' at time zone 'america/denver',j.id from waze.jams j ORDER BY j.pub_utc_date DESC WITH lines AS( SELECT id, ST_MakeLine( ST_MakePoint( (line -> n ->> 'x')::NUMERIC, (line -> n ->> 'y')::NUMERIC ) ) AS geom FROM waze.wazejams CROSS JOIN generate_series(0, jsonb_array_length(line)) AS n GROUP BY id) UPDATE waze.wazejams AS a SET geom_line = ST_SetSRID(b.geom, 4326) FROM lines AS b WHERE a.id = b.id