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 '15 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 – elmuertefurioso Jul 18 '15 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. – John Powell Jul 19 '15 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 '15 at 22:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.