Search code examples
sqlpostgresqlpostgis

Finding points not ST_EQUALS

In PostgreSQL + Postgis I can run the following sql, it returns those points which are the same. I'm trying to find those points which are different so I van embed this into an insert query to add in any updated points. So how do I get this top do the opposite of ST_EQUALS? I prefer a spatial query solution.

SELECT 
points_old.geom as points_old,
points_update.geom as points_update
    
FROM public.points_old
INNER JOIN public.points_update
ON ST_EQUALS(points_old.geom, points_update.geom)
Solution

  • You can make use of not exists and drop one of the join:

    SELECT points_update.geom 
FROM points_update 
WHERE NOT EXISTS (
    SELECT 1
    FROM public.points_old
    WHERE ST_EQUALS(points_old.geom, points_update.geom)
);