Finding points not ST_EQUALS

user_6054404 0
Viewed 214

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)
postgis postgresql sql
2 Answers

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)
);
user_76355690

I worked around it like this, but is there a better way?

SELECT points_update.geom 
 FROM points_update where geom NOT IN (
    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)
     )
user_60544040
Related