Can't update table off ST_INTERSECT Query in Netezza

Question

So basically I have two simple line datasets in Netezza, I want to create a table which just includes lines from dataset 1 that do not intersect any lines from dataset 2.

ST_DISJOINT doesn't seem to work, it returns thousands and thousands of duplicate values, I assume this is because Netezza runs the query row by row and returns a record everytime two specific lines don't intersect?

So I thought I would simply just flag each line from dataset 1 and then just select those without a flag, using UPDATE DATSET 1 WHERE ST_INTERSECTS(dataset1,dataset2).

However this returns a many to one relationship error. Is there a way I can make it only check for the first intersect or somethign to stop it trying to assign multiple values to individual records?

I feel that there is probably a simpler solution to my problem (selecting all lines from set 1 that never intersect any lines from set 2), any help would be greatly appreciated.

Cheers!

score 0 · Accepted Answer · answered Oct 20 '14 at 22:27

ST_DISJOINT will return a boolean value of true or false when comparing columns that each contain a geometry. I'm not sure I understand how your data is stored based on the wording of your question, but if your tables were called dataset1 and dataset2, each with the geometry stored in a column called geoms, you could use the following approach.

insert into dataset1 values (inza..ST_WKTTOSQL('LineString(0 0, 3 0)'));
insert into dataset1 values (inza..ST_WKTTOSQL('LineString(0 1, 3 1)'));
insert into dataset1 values (inza..ST_WKTTOSQL('LineString(2 2, 3 2)'));

insert into dataset2 values (inza..ST_WKTTOSQL('LineString(0 0, 0 3)'));
insert into dataset2 values (inza..ST_WKTTOSQL('LineString(1 2, 1 3)'));
insert into dataset2 values (inza..ST_WKTTOSQL('LineString(-1 0, -1 4)'));

select count(1) from dataset1 a 
where not exists (
select 1 from dataset2 b where inza..ST_DISJOINT(a.geoms,b.geoms)=FALSE
);

 COUNT
-------
     1
(1 row)

select count(1) from dataset2 a 
where not exists (
select 1 from dataset1 b where inza..ST_DISJOINT(a.geoms,b.geoms)=FALSE
);

 COUNT
-------
     2
(1 row)

Can't update table off ST_INTERSECT Query in Netezza

1 Answers1