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.
left outer join
using a spatialON
condition