Keep empty GRID Cells in PostgreSQL (after join)

Question

I want to join a 100*100m Grid with a point layer. The points contain the number of people living in the grid cell. There are some cells with no polulation, which I want to keep with the Attribute "pop_count = 0". My SQL-Query unfortunately gives back only the cells in which the attribut >0 exists (see picture).

    DROP TABLE IF EXISTS public.routing_join;
    CREATE TABLE public.routing_join as
    SELECT pop_count, grid.wkb_geometry
      FROM population, grid
      WHERE ST_Intersects(population.wkb_geometry, grid.wkb_geometry);

How can I change my query to keep the empty cells?

Answer 1

I would try using a join to intersect the features, rather than in a where clause, which gives you the option to use RIGHT JOIN:

      DROP TABLE IF EXISTS public.routing_join;
        CREATE TABLE public.routing_join as
        SELECT pop_count, grid.wkb_geometry
          FROM population
   right join grid on ST_Intersects(population.wkb_geometry, grid.wkb_geometry);

However: running this I get the correct geometry back, but I get an error:

NOTICE:  gserialized_gist_joinsel: jointype 1 not supported

There are some posts about this, so I'm not sure what's going on...