1

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.

  • @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. – Theo F Feb 25 at 17:37
  • 1
    WHERE ST_WITHIN(adb.geom, topo.geom) OR NOT ST_WITHIN(adb.geom, topo.geom) – Cyril Feb 25 at 17:50
  • 1
    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 Feb 25 at 17:59
  • 2
    It's still not clear what result you want. Please edit the question to clarify if you want all points or all polygons. – Vince Feb 25 at 19:02
  • 1
    @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 – JGH Feb 25 at 19:20
5

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.

  • 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. – Theo F Feb 26 at 9:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.