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

Question

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,
 adb.uprn
FROM
 public.wolv_adb_slim as adb,
 public.wolv_topobuild as topo
WHERE
 ST_WITHIN(adb.geom, topo.geom)

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.

• Theo F: @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.
• Cyril Mikhalchenko: WHERE ST_WITHIN(adb.geom, topo.geom) OR NOT ST_WITHIN(adb.geom, topo.geom)
• Vince: 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: It's still not clear what result you want. Please edit the question to clarify if you want all points or all polygons.
• JGH: @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

Answer 1

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,
 adb.uprn
FROM
 public.wolv_topobuild as topo
LEFT OUTER JOIN
 public.wolv_adb_slim as adb ON ST_WITHIN(adb.geom, topo.geom)

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,
 adb.uprn
FROM
 public.wolv_adb_slim as adb
LEFT OUTER JOIN
 public.wolv_topobuild as topo ON ST_WITHIN(adb.geom, topo.geom)

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.

• Theo F: thanks Vince, that first one works great for my cause (I just had to add the CREATE TABLE part before it). I now have a polygon table which contains adb 'uprn' numbers (the points) where they fall within the polygons. Where no pointsfall within, the polygon is still created in the output, just with a null value in the uprn column. Also where multiple points fall within a polygon, the polygon is duplicated for each point, with a unique uprn stored against it. Brill.