CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2geospatialspatial
ace_mccloud
ace_mccloud

Reputation: 507

STcontains() query running slow even with Spatial Index

I have one table called 'Region' which has got 2000 records. In the region table I have 1 geometry column called points. Now I have another table called Bus_Route with geometry column called 'geometry' with 10 million records. Now I want the points which lie in that region. I am using the following query to see weather my point lies in that region or not by using STcontains. But this query is taking long time (around 3 days). I have created Spatial index on my Bus_Route table which has got 10 million records but that didn't help as well.

         Select
              Region.BlockID,
              Bus_Route.geometry
              from 
              Bus_Route,                         
              Region
           where 

              Region.points.STContains(Bus_Route.geometry) = 1

Please help!

Upvotes: 1

Views: 2401

Answers (1)

Ben Thul
Ben Thul

Reputation: 32687

A couple of observations:

  1. Because your bus probably doesn't run through Flatland, use geography instead of geometry.

  2. If you're going to do a cross join (which, your query as stated is doing), be explicit about it by doing something like:

    Select Region.BlockID, Bus_Route.geometry from Bus_Route
    cross join Region

  3. Have you looked at using STIntersects instead of STContains? If I'm interpreting your problem correctly, you're looking for regions that a given route passes through, which means that the intersection between the two geo instances would be non-empty. The query you've written will show routes that are fully enclosed within a region.

Upvotes: 2

Related Questions