SQL Slow Spatial Data

Question

I have two tables, one called Point, and the other, called Poly i, contains about 400 polygons in the Poly table and about 200 thousand points in the point table. I wanted to find the number of points in a certain polygon. I am using the following query:

select COUNT(*) from point p
inner join Poly pl  on pl.GeoDataID = 101 and p.geopoint.STIntersects(pl.Poly)=1 

I also created spatial indexes for both GeoPoint and Poly Columns, and both columns are a geography type.

Edit: using SQL Server 2008

Question. Above the query returns results from about 1 minute to 40 seconds, which is too slow for my case, because I wanted to show them on Google Maps in real time. Is my approach to this problem right? or is this the best way to achieve this?

Answer 1

. SQL,

CREATE SPATIAL INDEX MyIndexName ON MyTable(GeomFieldName) USING GEOMETRY_GRID
WITH (
BOUNDING_BOX =(-1493907.5664457313, 6128509.51667404, -578861.3521250226, 7703103.135644257),
GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM),
CELLS_PER_OBJECT = 16, PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)

BOUNDING_BOX. . , , , .

, , http://geographika.co.uk/sql-server-spatial-sql - .

, , . WITH (INDEX (MyIndexName)).

SELECT *
FROM MyTable
WITH (INDEX (MyIndexName))
WHERE (geometry::Point(@x,@y,3785).STWithin(MyGeomField) = 1)

, , .

, , . .

Denali SQL Server auto, . -, STWithin STIntersects.