# PostgreSQL PostGIS: Spatial join but keep all features that don't intersect

Spatial joining a point table to a polygon table, creating a new output table. So far I can only get the output table to contain features that satisfy the spatial join. However, I want the output to contain ALL features, even the ones that don't intersect. I also want to choose which columns are pulled through into the final output (in the case below, 5 columns from 'topo' and only 1 from 'adb')

Here's my code as it stands at the moment:

DROP TABLE IF EXISTS public.wolv_topobuild_uprns_all;
CREATE TABLE public.wolv_topobuild_uprns_all AS
SELECT
topo.id,
topo.geom,
topo.ogc_fid,
topo.theme,
topo.class,
FROM
public.wolv_topobuild as topo
WHERE


From my research it seems I want to mix this spatial join with a full outer join, but being able to specify the columns I want in the output table.

I want the resulting output table to contain all of the original polygons but with the chosen metadata from the points where they fall inside a polygon. There will be some points that lie outside of the polygons... we can ignore them from the final output.

• @Cyril do I need to specify? It's a spatial join, so it uses the geom column to make the join. There is no common column between the two tables. – Theo F Feb 25 at 17:37
• WHERE ST_WITHIN(adb.geom, topo.geom) OR NOT ST_WITHIN(adb.geom, topo.geom) – Cyril Feb 25 at 17:50
• This is a clear case where using JOIN is mandated, because then you can specify a LEFT OUTER JOIN to return rows in the FROM table which have no match. – Vince Feb 25 at 17:59
• It's still not clear what result you want. Please edit the question to clarify if you want all points or all polygons. – Vince Feb 25 at 19:02
• @Cyril is it wrong to do a join on a condition and its opposite. It is the same as no condition at all, so the same as a cross product. The solution here is as @Vince pointed out, to choose the table that should always be included and to apply a left outer join using a spatial ON condition – JGH Feb 25 at 19:20

To obtain all topo rows, whether they have an adb or not, use LEFT OUTER JOIN:

SELECT
topo.id,
topo.geom,
topo.ogc_fid,
topo.theme,
topo.class,
FROM
public.wolv_topobuild as topo
LEFT OUTER JOIN


Conversely, obtain all adb rows, whether they have a topo or not, use:

SELECT
topo.id,
topo.geom,
topo.ogc_fid,
topo.theme,
topo.class,

Note that points ON a polygon boundary will not have a match, whereas a ST_Intersects could return two boundaries in that case.
Best practice is to always use JOIN to join.