3

postgresql 9.6, postgis 2.3
Following this Select features that do NOT intersect in PostGIS, I would like to compare one table to itself, to keep all records from table p1, even and above all those that don't have any record matching in table p2.
Actually I want to keep features that don't intersect any.

edit : code updated 

select p1.gid, p2.gid from table p1
left join table p2
on st_intersects(p1.geom, p2.geom)
and p1.gid <> p2.gid
where p2.gid is null ;

Without the and, All that I get are matching records : the left seems to be ignored.
With the and condition, no result. Where is (are) my mistake(s) ?

Leehan
  • 1,236
  • 2
  • 10
  • 20
  • I want all records from p1 : those intersecting features and those which do not. – Leehan Oct 30 '18 at 09:13
  • When it works you will save half of the time by using where p1.gid < p2.gid. If you have already compared gid=1 and gid=2 there is no need to test gid=2 against gid=1 again. – user30184 Oct 30 '18 at 09:24
  • I think your problem is and p2.gid is null. This means that you are only returning records that do not intersect any other, and missing those that do. Drop this clause, and you will get every record from p1, although you will end up with a cartesian join, note, @user30184's comment, which will at least reduce the run time by half. – John Powell Oct 30 '18 at 11:59
  • My goal at the end is to have records that do not intersect any others. That is what I am trying to do, as written in the first message. But nothing is returned for now. That is my problem. – Leehan Oct 30 '18 at 12:45
  • well, perhaps you should remove the all from you question, as it is a bit confusing. Anyway, the query as you have written it, should return only those records that don't intersect any other. I have just tested it locally on some polygons and it works as expected. I suspect your issue may lie elsewhere. – John Powell Oct 30 '18 at 12:49
  • p1.gid<>p2.gid has to be in the join and not in the where as I wrote first. Now it works as expected. – Leehan Oct 30 '18 at 13:29

0 Answers0