1

I am trying to create a view from a selection. One layer contains indian states boundary as polygon and other world forest fire as points.

CREATE VIEW myview AS
SELECT *
    FROM vnp14imgtdl_nrt_global_24h a, states b
      WHERE st_within(a.geom, b.geom)
      AND a.confidence = 'high'
      order by b.statename;

If I just run the select command it runs fine, but once I try to create a view it gives error stating

ERROR: column "gid" specified more than once
SQL state: 42701
  • 3
    Best practice is to use a JOIN instead of listing tables in the FROM and to always provide an explict list of each column in the SELECT clause, prefixed by the alias of the source table, with a column alias if the name is not unique. – Vince May 23 '18 at 11:54
0

you can simply use

SELECT a.* FROM...

to show only forest fires points data AND geometries as results of the view.

6

Seems clear enough, you have specified the column "gid" twice in your select statement.

I assume that both vnp14imgtdl_nrt_global_24h and states have a "gid" column and since you asked for * in the select this is a problem.

Simply, list out the actual columns your view needs and all will be well.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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