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.
Best Answer
To obtain all
topo
rows, whether they have anadb
or not, useLEFT OUTER JOIN
:Conversely, obtain all
adb
rows, whether they have atopo
or not, use: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.