We have two tables which has polygon geometry stored in them. I would like to fetch the polygons present in one table and not present in another table.. As of now I am doing a left outer join and using STAsText() but it is taking lot of time.. FYI, we have 120 million polygons in both the tables. Is there a fast way of fetching? (maybe using the spatial indexing, I am not aware of this)

select newPolygon.* from table1 newPolygon left join table2 oldPolygon on newPolygon.Shape.STAsText() = oldPolygon.Shape.STAsText() where oldPolygon.Shape is null 

We have two tables with polygons stored in geometry type columns. 

I want to fetch the polygons present in one table that are not present in another table. 

As of now I am doing a left outer join and using STAsText(), however that is taking a very long time. 

FYI, we have approximately 120 million polygons in both tables. 

Is there a fast way of comparing geometry type data? Maybe I need to use the spatial indexing, however I am not aware of this.

SELECT newPolygon.* FROM table1 newPolygon LEFT JOIN table2 oldPolygon   ON newPolygon.Shape.STAsText() = oldPolygon.Shape.STAsText() WHERE oldPolygon.Shape IS NULL 

We have two tables which has polygon geometry stored in them. I would like to fetch the polygons present in one table and not present in another table.. As of now I am doing a left outer join and using STAsText() but it is taking lot of time.. FYI,We have 120 million polygons in both the tables. Is there a fast way of fetching? (may be using the spatial indexing, I am not aware of this).

FYI, I am using SQL Server 2012.

Thanks, Balu

We have two tables which has polygon geometry stored in them. I would like to fetch the polygons present in one table and not present in another table.. As of now I am doing a left outer join and using STAsText() but it is taking lot of time.. FYI, we have 120 million polygons in both the tables. Is there a fast way of fetching? (maybe using the spatial indexing, I am not aware of this)

select newPolygon.* from table1 newPolygon left join table2 oldPolygon on newPolygon.Shape.STAsText() = oldPolygon.Shape.STAsText() where oldPolygon.Shape is null