4

Have a set of points cali_dairies and wanted to delete some of the locations in southern California. How do I do it?

0

2 Answers 2

6

A bit more idiomatic, using a potentially faster NOT EXISTS:

WITH
  -- get a GEOMETRY representing the areas to keep
  aoi AS (
    SELECT
      ST_SetSRID(
        ST_Extent(geom),
        4326
      ) AS ext
    FROM
      weather_points
  )
DELETE
FROM
  cali_dairies AS cd
WHERE
  -- for each row execute the actual check; we only need to know if the condition is met once...
  NOT EXISTS (
    -- ...so whenever this query would return at least one row, it breaks out of execution
    SELECT
      1
    FROM
      aoi
    -- the actual condition is a bounding box overlap check, fully index driven
    WHERE
      cd.geom && aoi.ext
  )
;

Update:

The query above is suitable for the general case of comparing against a set/table of AOIs. If, and only if, the compared set contains a single row only, the issue with disjoint queries do not exist, and the query can be further simplified:

WITH
  -- get a GEOMETRY representing the areas to keep
  aoi AS (
    SELECT
      ST_SetSRID(
        ST_Extent(geom),
        4326
      ) AS ext
    FROM
      weather_points
  )
DELETE
FROM
  cali_dairies AS cd
USING
  aoi
WHERE
  NOT cd.geom && aoi.ext
;

Make sure you have an index on cali_dairies.geom.

6
  • Should that be EXISTS instead of NOT EXISTS?
    – dr_jts
    Dec 7, 2023 at 19:09
  • Why is this faster than using WHERE ST_Intersects(cd.geom, aoi.geom)?
    – dr_jts
    Dec 7, 2023 at 19:10
  • 2
    @dr_jts looks like OP wants to.delete from an anti-join on that extent. && should comply wirh the actual index condition and does not invoke a function call.
    – geozelot
    Dec 7, 2023 at 20:40
  • 1
    EXISTS cannot really play out its advantages here as there cannot be an early exit. However, this reads "Traverse the table, check for index driven box overlap and delete if not found." while the LEFT OUTER JOIN in OPs answer reads something like "Create a left open set intersection on index supported geometric containment check and with a filter for NULL column values, gather column values into a hash map, traverse the table, check column values against the hash and delete if found..
    – geozelot
    Dec 7, 2023 at 20:54
  • 1
    This is such a cool query. I wished I understood it better. I bet if someone would break down each part, a lot of people would appreciate it Dec 10, 2023 at 12:12
3

I didn't see a good answer, I did use this question/answer (especially the second answer by Paul Ramsey) which was very helpful: Querying points inside and outside of polygons?

Here is my solution

-- create rectangle polygon to act as boundary (4 coordinates of yet another points table)
create view v_dairies_in_target as
    select 1 as id, geom from 
    (select st_makepolygon(st_geomfromtext(
        'linestring('||min(lon)||' '||max(lat)||','
        ||min(lon)||' '||min(lat)||','||max(lon)||' '||min(lat)||','
        ||max(lon)||' '||max(lat)||','
        ||min(lon)||' '||max(lat)||')'))::geometry(Polygon,4326) as geom from weather_points) as sq;

-- delete points not in the rectangle
delete from cali_dairies where ogc_fid = any (
    select pts.ogc_fid as pts from cali_dairies pts 
    left join v_dairies_in_target poly on (st_within(pts.geom, poly.geom))
    where poly.id is null);

drop view v_dairies_in_target;

select * from cali_dairies -- check it!
commit;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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