1

I am having two tables, table1 and table2.

I want to know which parts of table2 intersect with table1. Having that information, I want to write the attributes from table2 into table1 (where they intersect) and create a completely new table3 of it. I want to preserve the geometry (polygons) from table1

sketch

CREATE TABLE table3 AS
SELECT t1.*, t2.c, t2.d FROM table1 t1
JOIN table2 t2
ON ST_Intersects(t1.geom, t1.geom);

Would that give me my desired result?

  • Using SELECT * is a violation of best practice when JOINs are involved and you also left out naming the ST_Intersection result. – Vince Nov 17 '16 at 11:29
  • @Vince not sure if you saw my updated Q. What do you mean by "naming the ST_Intersection result" – Stophface Nov 17 '16 at 11:30
  • The point being that you don't have one. You should always explicitly name your SELECT columns. – Vince Nov 17 '16 at 11:33
  • @Vince So the way I did it is alright? I named the SELECT columns t1.*, t2.c, t2.d? Sorry, I am very new to postGIS – Stophface Nov 17 '16 at 11:38
  • No. Use of * is sloppy and dangerous. It also includes the wrong geometry field. – Vince Nov 17 '16 at 11:43

Your Answer

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

Browse other questions tagged or ask your own question.