I am attempting to create an employment change map using a hex grid. I have individual establishment data as points with attached employment figures. When I ran the spatial join using the postGIS extension in QGIS 2.6 it returns all of the hexes where the join is true, as expected. But for cartographic sake I would like the whole net return where I could symbolize the null cells myself and not just have them drop out.

Is there a way to do this through postGIS similar to the return all features option in ArcMap's spatial join? My sample code right now.

--view joining employment data to hexagon grid
   CREATE VIEW food_employment_2yr_hex AS
SELECT a.gid AS gid, sum(b.avgemp) AS Emp_2010, sum(c.avgemp) AS Emp_2012,   a.geom AS geom
    FROM hex_grid_pdx_nad83 a, qcew_2010_nad83 b, 
    qcew_2012_nad83 c
    WHERE ST_Within(b.geom, a.geom) AND ST_Within(c.geom,a.geom)
    GROUP BY a.gid;
  • 2
    Google OUTER JOIN
    – Vince
    Jul 18, 2015 at 19:21
  • I found something similar here: gis.stackexchange.com/questions/111082/spatial-joining-syntax that looks like it may answer the question. not a pure outer join, but allows that functionality for spatial joins Jul 18, 2015 at 19:32
  • 1
    If you are keeping all the features then the spatial join becomes irrelevant. It is simply a full (or outer join) as Vince says. Jul 19, 2015 at 9:18
  • 1
    You can also use a LEFT JOIN to keep all the records from one of the tables. An OUTER JOIN keeps both tables.
    – Mike T
    Jul 20, 2015 at 22:34


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.