CODEWITHSUNDEEP
sqlsql-servergeometrygeospatialspatial-index
mobile devteam
mobile devteam

Reputation: 3

Spatial Query taking too long to execute

I have a table that contains geometry column for polygon data along with its location. I am trying to execute a query to find the polygon in which my point resides. The current query that I have been using works but it takes 50 seconds to return the results. Is there a way that I can reduce the execution time.

declare @point geometry =  geometry::Point(ISNULL(-77.6914,0),ISNULL(38.6140,0), 32768)
select @point

SELECT hucname
FROM polygonlocations a 
Where 1=1 AND a.ogr_geometry.MakeValid().STContains(@point) = 1

Also I have tried to add spatial index to the geometry column ogr_geometry but it is taking 52 seconds to load after that. I have tried below query for creating the index:

CREATE SPATIAL INDEX [idxGeometryIndex] ON polygonlocations 
( ogr_geometry )USING  GEOMETRY_GRID 
WITH (
  BOUNDING_BOX =(-180, -90, 180, 90))
GO

Upvotes: 0

Views: 292

Answers (1)

Roger Wolf
Roger Wolf

Reputation: 7692

First of all, it looks like you are using geometry data type for objects which are in fact geographic. If that's the case, then you are doing it the wrong way; there is a separate geography data type. The main difference between them is that geometry "places" all its objects on a flat plane, while geography puts them on a geoid which is an approximation of the Earth's shape.

Second, the index won't work with your query because you call the MakeValid() method, which returns a potentially different polygon, and that polygon is not in the index. I would suggest cleansing your data when you put it into the database. Cleansing, apart from the MakeValid(), should also include an optional ReorientObject() call for polygons with the ring orientation problem. You can do it like this:

update t set
  t.GeoData = t.GeoData.ReorientObject()
from YourTable t
where t.GeoData.EnvelopeAngle() > 179;

Last but not least, in my experiments I've found that the geography_auto_grid somehow outperforms the old geography_grid option. This is an example of a spatial index for polygons I've ended up with in my system:

create spatial index [IX_AreaUnits_GeoData] on dbo.AreaUnits (GeoData)
with (
  cells_per_object = 256
);
go

The optimal value for cells_per_object might be different in your circumstances (and it might also differ from table to table, depending on the nature and size of the polygons you store in each of them). I'd recommend to experiment with several different values and see what works best in your particular scenario.

Upvotes: 4

Related Questions