TransWikia.com

Why does PSQL ignore spatial index in "where NOT ST_Contains", but uses in "where ST_Contains"?

Geographic Information Systems Asked by whitney vogel on November 28, 2020

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.

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP