locked
Geometry column performance issue RRS feed

  • Question

  • I have a table with with 500K records and with 2 spatual columns. one geography and another goemetry both stores the lat/long cordinates of some objects in the table.All the objects are in North America only

    I want to search the objects within a shape say, a polygon and I use STPolyFromText methode for this.The query against geography columns returns within 6 seconds while the query against geometry takes near 3 minutes.

    the result is same in both the cases. Due to some issues with frond end application it cannot use the geography column and I have to use the geometry colum instead.

    How do I improve the query against geometry? Specifying a bouding box which just cover the shape(polygon in this example) will improve the query? But I could not get it working.

    Please help...

    The two queries are

    select * from tbl_Spatial where geographyCol.STIntersects(geography::STPolyFromText('POLYGON((
    -115.18560 36.302949,
    -115.08624 36.263500,
    -115.17735 36.304881,
    -115.18560 36.302949
    ))',4326)) = 1
    
    

    --completed within 6 seconds

    select * from tbl_Spatial where geometryCol.STIntersects(geometry::STPolyFromText('POLYGON((
    -115.18560 36.302949,
    -115.08624 36.263500,
    -115.17735 36.304881,
    -115.18560 36.302949
    ))',4326)) = 1
    
    

    --takes more than 3 minutes

    and I created the geoMetry index as

    Create Spatial Index SpatIndex_Geom ON tbl_Spatial(geometryCol)
     WITH (BOUNDING_BOX = (-94,38,-71.5,50))
    
    

    Update:  I manged to add a bounding box filter but still it takes more or less the same time as before.

    Declare @BoundingBox geometry;
    Set @BoundingBox= geometry::STLineFromText('LINESTRING (-115.18560 36.302949,-115.08624 36.263500,-115.17735 36.304881,-115.18560 36.302949)',4326).STEnvelope()
    
    
    Select *
    from tbl_Spatial 
    where 
    	@BoundingBox.STContains(geometryCol)=1 AND
    	geometryCol.STIntersects(geometry::STPolyFromText('POLYGON((
    	-115.18560 36.302949,
    	-115.08624 36.263500,
    	-115.17735 36.304881,
    	-115.18560 36.302949
    	))',4326)) = 1  
    	

     




    Tuesday, April 5, 2011 9:52 AM

Answers

  • I think you need to change the bounding box of your spatial index so that it covers your data and query sample (your polygon). Your bounding box at (-94,38,-71.5,50)) won't do that.

    Cheers, Bob Beauchemin, SQLskills

    • Marked as answer by sqlblr Thursday, April 7, 2011 5:44 AM
    Tuesday, April 5, 2011 5:52 PM

All replies

  • I think you need to change the bounding box of your spatial index so that it covers your data and query sample (your polygon). Your bounding box at (-94,38,-71.5,50)) won't do that.

    Cheers, Bob Beauchemin, SQLskills

    • Marked as answer by sqlblr Thursday, April 7, 2011 5:44 AM
    Tuesday, April 5, 2011 5:52 PM
  • Thanks Bob

    Yes. That was the issue. I had to change it to -126.562500,28.613459,-57.128906,49.496675

    Thursday, April 7, 2011 5:45 AM