PostgreSQL PostGIS – How to Perform Spatial Join But Keep All Features That Don’t Intersect

attribute-joinspgadmin-4postgispostgresqlspatial-join

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 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.

Related Question