I have two geometries and I want to write the information from one geometry into the other geometry, but only where they intersect. Sometimes, a geometry is intersected with to different geometries (see my screenshot). The yellow geometry is intersected by two geometries (thick black line). The resulting table should contain the yellow geometry twice, with the two different information from the intersecting geometries.


I tried

SELECT g.a, g.b, g.geom, t.c, t.d
FROM green_geometries g
LEFT OUTER JOIN thickBlackLine t
ON ST_Intersects(ST_PointOnSurface(g.geom), t.geom);

However, the resulting table only contains the yellow geometry once, with only one information from the intersecting geometry added to it.

*edit: thickBlackLine t is a a table, containing many polygons. In the screenshot here you only see the outline (thickBlackline) of the polygon. Maybe on the screenshot here it becomes more clear. I am trying to perform a Spatial Join on polygons, not line and polygon(s)!

screenshot 2

closed as unclear what you're asking by Vince, nmtoken, PolyGeo Dec 27 '16 at 22:33

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • The "intersects" relationship between a point and a line is fraught with complications, when you compound it with the centroid of a polygon, you're really off in the weeds. Have you tried writing the SQL for the problem you've described in the body of the text? The query you've provided is nothing like the UPDATE (or even the FROM subselect) required. – Vince Dec 27 '16 at 16:46
  • @Vince that might come across not correct here. The thick black line is the border of a polygon. Basically the "north" of the black line is a polygon with a geometry geom in the table thickBlackLine and the "south" of the black line is a polygon with a geometry geom in the table thickBlackLine – Stophface Dec 27 '16 at 16:50
  • Please edit the question to clarify that the object described as a "line" isn't a line. – Vince Dec 27 '16 at 16:55
  • 2
    Intersecting with the centroid still makes absolutely no sense if you want two rows returned. If you rendered the centroids in the question you'd understand your results. – Vince Dec 27 '16 at 17:01
  • This is really still confusing as to what you want. You're using a CTAS in your example. Can you build a table using the same method that has simplified input data, and your desired output data? – Evan Carroll Dec 27 '16 at 17:01

If I understand you right your problem is the pointonsurface.

That point will intersect only 1 polygon in table t. Use the full polygon instead. Just remove the pointonsurface function.

  • That also preserves the geometries of the green underlying polygons which are not intersected by one of the overlying ones? – Stophface Dec 27 '16 at 17:52
  • 1
    Yes, the left join should give one of each from left table even if there is no intersection – Nicklas Avén Dec 27 '16 at 17:58

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