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.