0

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.

Update. I saw that everything stopped at the ";" in the query. I removed the ; in the query and now it's giving me a syntax error.
enter image description here

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
2
  • 1
    Are you using PGAdmin? QGIS' DB Manager? Something else? Jun 27, 2018 at 17:48
  • Updated the question. pgAdmin
    – MjonesGEO
    Jun 27, 2018 at 17:58

2 Answers 2

1

You can't put an UPDATE statement in a VIEW. Views are for queries (SELECT statement).

1

It looks kinda messy... You have a SELECT then a WITH ... AS SELECT plus and UPDATE statement in the mix. It won't process.

j.pub_utc_date at time zone 'utc' at time zone 'america/denver',j.id

And did you mean AS instead of at? You have time_zone column declared twice, no commas between columns.

https://www.postgresql.org/docs/9.6/static/sql-createview.html

Maybe you can create a view with just the lines and join them with the waze.jams, but I am not sure what is it you're trying to achieve exactly.

By the way a view is automatically updatabe if certain conditions are met (see link above), so if waze.wazejams is a table or a simple view your UPDATE statement should look like this:

UPDATE waze.wazejams AS a
SET geom_line = (SELECT ST_SetSRID(b.geom, 4326)
FROM lines AS b
WHERE a.id = b.id);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.