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
Improve this question
1
  • 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. Commented May 23, 2018 at 11:54

2 Answers 2

Reset to default
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.

Improve this answer
0

you can simply use

SELECT a.* FROM...

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

Improve this answer

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.