1

I add a new geometry column to the "mad" table so I could update it with the polygons from an Intersection with the polygons of the table "clip".

Since my database is huge an it was taking a lot of time to comput I opt for this solution "Getting intersections the faster way" (http://postgis.net/2014/03/14/tip_intersection_faster/). These SELECT worked for me but I wanted to do an UPDATE. I tried using this command:

UPDATE mad SET geom_p=CASE WHEN ST_CoveredBy(mad.geom,clip.geom)
    THEN mad.geom 
    ELSE ST_Multi(ST_Intersection(mad.geom,clip.geom)) END AS geom FROM mad AS mad 
  INNER JOIN clip AS clip 
     ON (ST_Intersects(mad.geom,clip.geom) AND NOT ST_Touches(mad.geom,clip.geom)));

ERROR:  syntax error at or near "AS"
LINE 1: ...(ST_Intersection(mad.geom,b.geom)) END AS geom FR...
1

You get a syntax error, try :

  UPDATE minimad SET geom_p=CASE WHEN ST_CoveredBy(minimad.geom,b.geom)
        THEN minimad.geom 
        ELSE ST_Multi(ST_Intersection(minimad.geom,b.geom)) 
        END
  FROM minimad, min b
  WHERE ST_Intersects(minimad.geom,b.geom) AND NOT ST_Touches(minimad.geom,b.geom);

Also you can use the inner join:

  UPDATE minimad SET geom_p=CASE WHEN ST_CoveredBy(minimad.geom,b.geom)
        THEN minimad.geom 
        ELSE ST_Multi(ST_Intersection(minimad.geom,b.geom)) 
        END
  FROM minimad
  INNER JOIN min as b ON ST_Intersects(minimad.geom,b.geom) AND NOT ST_Touches(minimad.geom,b.geom);
  • I try it and got these ERROR: syntax error at or near "SELECT" LINE 3: ELSE SELECT ST_Multi(ST_Intersection(minimad.geom,minimini.... – Alejandra Apr 29 '16 at 22:20
  • Sorry. Check my updated code. – Jose Hermosilla Rodrigo Apr 30 '16 at 3:48
  • Thank you for answering @Jose, I got a new error: ERROR: relation "mad" does not exist LINE 1: UPDATE mad SET geom_p=CASE WHEN ST_CoveredBy(mad.geom,mini.g... – Alejandra May 3 '16 at 15:01
  • Try replacing mad for minimad and see if it works. Edited my anwser! – Jose Hermosilla Rodrigo May 3 '16 at 15:20
  • Thank you Jose for your answer. I could't make the UPDATE to work so I end up creating a new table. – Alejandra May 26 '16 at 22:56
0

I could not make the UPDATE work so I created a new table

CREATE TABLE madclip AS SELECT mad.gid,mad.cat,
  CASE WHEN ST_CoveredBY(mad.geom,clip.geom) 
     THEN (ST_DUMP(geom)).geom::geometry (Polygon, 904483)
     ELSE (ST_DUMP(ST_Intersection(mad.geom,clip.geom))).geom::geometry   
      (Polygon, 904483) END AS geom 
  FROM mad AS mad INNER JOIN clip as clip 
      ON (ST_Intersects(mad.geom,clip.geom) 
      AND NOT ST_Touches(mad.geom,clip.geom));

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.