3

I am using Postgis 2.4 with posgresql 10 in Windows 10. I have the following SQL query:

WITH data (the_geom) as (
  SELECT a.gid,
          CASE WHEN ST_Within(a.geom,b.geom) THEN a.geom 
               ELSE ST_Intersection(a.geom,b.geom) 
          END AS the_geom  
  FROM source.g100_wby_lakes_r as a 
      JOIN extents.map_areas as b ON ST_Intersects(a.geom,b.geom) 
  WHERE b.map_id='AA01'
) 
INSERT into public.g100_wby_lakes_r (gid,geom) 
select * 
from data 
where st_GeometryType(the_geom)='MULTIPOLYGON';

It tries to select the intersection between two multipolygons and only insert into a new table the intersected geometries which are also multipolygons (the intersection could also result in points or lines). All works except for the WHERE clause which gives the following error:

ERROR:  column reference "the_geom" is ambiguous
LINE 4: ...id,geom) select * from data where st_GeometryType(the_geom)=...
                                                             ^
SQL state: 42702
Character: 351

Why is the_geom ambiguous? How can I change the SQL to make it work? Thank you

1

First, @ypercube is spot on and his answer here is great and to the point.

As a side note,

As a MATERIALIZED VIEW

CREATE MATERIALIZED VIEW public.g100_wby_lakes_r (gid, geom)
  AS
    SELECT *
    FROM (
      SELECT a.gid,
        CASE
          WHEN ST_CoveredBy(a.geom,b.geom)
            THEN a.geom
          ELSE ST_Intersection(a.geom,b.geom) 
        END AS the_geom  
      FROM source.g100_wby_lakes_r as a 
      JOIN extents.map_areas as b
        ON ST_Intersects(a.geom,b.geom)
        AND NOT ST_Touches(a.geom,b.geom)
      WHERE b.map_id='AA01'
    ) AS t(gid, geom)
    WHERE ST_GeometryType(geom)='MULTIPOLYGON';
  • Thank you @ypercube for your great answer and Evan for your suggestions. I have made the changes as suggested but I would like to run some more tests including speed tests as the initial results between the two options (a WITH query and an inline INSERTquery) are not consistent. I'll get back here as soon as I have done so. – user3716846 Jan 28 '18 at 5:19
  • 1
    @user3716846: Please keep in mind that both inconsistent results and low performance are completely different issues to the ambiguous reference one that you have asked in this thread. So, if you decide that you need help with either of the former, please post a new question. And if the problem with an ambiguous reference is resolved, please accept the answer that's helped you, thanks. – Andriy M Jan 28 '18 at 12:25
  • Thank you @AndriyM for your clarification, but in fairness I think the subject of this thread is How to Filter the results of a query, not about ambiguous references only. Be as it may, I accept that both answers given earlier work now, but I decided to go for the inline INSERT, rather than the WITH option, as I found it more intuitive. Although I still have a question about my WHERE clause (the crux of filtering), even though it now works, I may make it the subject of another posting. Thank you all for your great help and advice. – user3716846 Jan 29 '18 at 3:39
  • 1
    @Evan, I accept your suggestion to use an inline table as the final answer to my question. – user3716846 Jan 29 '18 at 7:17
  • 2
    @user3716846: Yes, I understand that you are working on a more or less specific problem, but various issues that you are coming across while solving that problem seem to be specific enough as well. And given that this is a Q&A site and that your question was very specific, an answer addressing and resolving that question should naturally close this thread. It's perfectly all right to ask a new question related to the same problem generally but dealing with a different issue more specifically. – Andriy M Jan 29 '18 at 7:51
9

Your CTE:

(
  SELECT a.gid,
         ... AS the_geom  
  FROM ...
) 

produces a table with 2 columns, named gid and the_geom. But the "wrap" has an obvious glitch, it only has one column:

WITH data (the_geom) AS

So it says that the table will be named data and the first column (re)named to the_geom. Now you have 2 columns named the_geom! You could have the same effect - but more clear to spot - if you had written:

WITH data (the_geom, the_geom) AS

The solution is simple. Either remove the column list from that part:

WITH data AS
  ( ...
  )

or (re)name both columns explicitly:

WITH data (gid, the_geom) AS           -- explicit
(
  SELECT a.gid,
         ... AS the_geom  
  FROM ...
) 
INSERT INTO public.g100_wby_lakes_r (gid,geom) 
SELECT gid, the_geom 
FROM data 
WHERE st_GeometryType(the_geom) = 'MULTIPOLYGON' ;

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.