2

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?

Fill empty cells

2

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...

2
  • Thank you for the answer. For me this solutions worked without error.
    – Peter
    Oct 12 '15 at 8:00
  • @Peter ok good - gotta figure out what this error means... Thanks!!! Oct 12 '15 at 14:49

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.