SQL PostgreSQL add attribute from polygon to all points inside polygon but keep all points not just those that fall inside polygon

Question

I have two tables, one with points and one with polygons "in a PostgreSQL database". I need to get the attributes from the polygons "status" to the points that fall inside the polygons and the result need to show all points that the point table consists of and not only those that fall inside the polygons. I also only want 1 or 2 attributes from the polygon layer but the point layer should have all attributes *. My goal is to later create a view.

I tried to get this work with an intersect command but the result only shows points that intersect, it doesn't show the other points. Here is the code I tried with

SELECT 
  pointlayer.*, 
  polygonlayer.name
FROM 
  schema_a.pointlayer,
  schema_a.polygonlayer
WHERE ST_Intersects(schema_a.pointlayer.geom, polygonlayer.geom);

I attached an image that might explain what I want better then my text. Notice that status in image says null for points outside, but it just a way of me showing you that the attribute should be empty.

Taras · Accepted Answer · 2022-11-03 13:11:02Z

8

You are CROSS JOINing both tables on an exclusive condition - and the result set contains only pairs that satisfy this condition.

I urge you to adapt the explicit JOIN syntax; it will help you understand the set-theoretic outcome and allows for side-specific inclusion.

That being said, you want to run a LEFT JOIN to include rows of pointlayer that do not satisfy the join condition:

SELECT
  pt.*,
  py."name",
  py.status
FROM
  pointlayer AS pt
  LEFT JOIN
  polygonlayer AS py
    ON ST_Intersects(py.geom, pt.geom)
;

which can then also be used directly as View definition.

edited Nov 3, 2022 at 13:11

Taras♦

36k7 gold badges77 silver badges153 bronze badges

answered Nov 3, 2022 at 10:47

geozelot

31.5k4 gold badges38 silver badges59 bronze badges

1

That worked great! But how can I join a third table to the points? I tried adding the third table that also consists of polygons to the st intersects but that wont work for me.

esset
– esset

2022-11-03 13:37:40 +00:00
Commented Nov 3, 2022 at 13:37
@esset no, that cannot work. The atomic relation in PostgreSQL is binary, meaning that a statement can only ever relate two database objects (e.g. joining two tables) - thus there is no function accepting more than two (columnar) parameters. But of course you can join them sequentially, i.e. append another LEFT JOIN otherpolygontable AS opy ON ST_Intersects(opy.geom, pt.geom) (notice how this is another join on the pointlayer simply by specifying it in the condition) - the result set is a minimal row-set union over all three tables, with non-matching rows in pointtable included.

geozelot
– geozelot

2022-11-04 08:49:57 +00:00
Commented Nov 4, 2022 at 8:49

Add a comment |

Stack Exchange Network

SQL PostgreSQL add attribute from polygon to all points inside polygon but keep all points not just those that fall inside polygon

1 Answer 1

Your Answer

Hot Network Questions

SQL PostgreSQL add attribute from polygon to all points inside polygon but keep all points not just those that fall inside polygon

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions