Get all geom points that are more than 3 meters from the linestring, at big scale

Question

With the help of Osmosis i was able to import all the data of a Open Street Map city, (Nodes, ways, etc...). In another table at same db, i have Millions locations in that city, these locations are also geom points.

The image below shows a way that is a linestring and the green dots represents locations that are geom points.

Is it possible to query at really big scale without iterating over each location, to get all the locations (geom points) that are more than 3 meters away from its closest way (linestring) within a all the ways i have in the db?

My first approach was this query

SELECT locations.geom FROM locations
JOIN ways
ON ST_DWithin(
        (ways.linestring)::geography,
        (locations.geom)::geography,
3) = false;

and

SELECT locations.geom FROM locations,ways
WHERE ST_DWithin(
    (ways.linestring)::geography,
    (locations.geom)::geography,
    3) = false;

with these two indexes:

CREATE INDEX ways_geography_cast_gpx ON ways USING GIST (geography(ways.linestring));
CREATE INDEX locations_geography_cast_gpx ON locations USING GIST (geography(locations.geom));

But it takes lots of time.

Answer 1

You've got two interacting things that might slow down your query:

• Trying to do a "all things disjoin" or "all things further" style query
• Using functional indexes

I have found in the past that using col::geography syntax does not play well with functional indexes, so using geography(col) in the query is required.

Counter-intuitively, finding "the things not" is frequently sped up by "finding the things that are" and then not returning them. So, like this:

SELECT locations.geom 
FROM locations
LEFT JOIN ways
ON ST_DWithin(
    geography(ways.linestring),
    geography(locations.geom),
    3)
WHERE ways.id IS NULL;

The LEFT JOIN nulls out the values of the right-hand relation in the join when the join condition is false, so this is a short-hand way to extract all the things that "don't" from a query.

Same idea, different implementation, might work better:

SELECT locations.geom
FROM locations
EXCEPT 
SELECT locations.geom 
FROM ways
JOIN locations
ON ST_DWithin(
    geography(ways.linestring),
    geography(locations.geom),
    3)