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.