I'm creating a new table of trips in my PostgreSQL/PostGIS database, where trips are comprised of a series of individually identified points, based on where the points within each trip are. The idea is to select any trip which has at least one point that is NOT within a certain area. So that means filtering through every point to see if it's in or out of a certain spatial realm, and if any point is outside of the realm, then I put the trip_id of that point (multiple points make up a single trip, and all points within a trip share a trip_id) into this new table. The query is written as follows:

CREATE TABLE main.trips_gps_validity_21 as
    SELECT DISTINCT t.trip_id 
    FROM main.points t, main.sfoutline y
    WHERE not(ST_Contains(y.geom, t.geom_met)) 

Here, y.geom is the spatial polygon that designates my area of interest, and the t.geom_met is the geometry for each point within a trip.

When I run the inverse, i.e. the bottom line is "WHERE ST_Contains(y.geom, t.geom_met)" instead of "WHERE NOT", the spatial index is used. When the "NOT" is applied, the spatial index is ignored. Why would this be happening? Running the "NOT" query with a "limit 10" addendum takes 2.953 seconds, while the query without "NOT" takes only 90 msecs, so it seems like an index would help speed things along.

  • Proving a negative is always a tricky task. You might have better luck with ST_Disjoint
    – Vince
    Jul 28, 2020 at 17:45
  • 1
    Try a workaround be selecting where t.trip_id not in.. [fast ST_Contains query]. But if ST_Contains finds just a handful of features and NOT ST_Contains much more the difference in speed may be normal.
    – user30184
    Jul 28, 2020 at 21:18


Your Answer

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

Browse other questions tagged or ask your own question.