Problem in using STIntersects in SQL Server 2008

Question

Using this Spatial Query I am trying to get all the country information which intersects the point 78,22. Expected result is information of "India", but this query is returning no rows.

select * from countryspatial
where
geom.STIntersects((geometry::STGeomFromText('POINT (78 22)', 4326)))>0;

This is the table definition:

CREATE TABLE [dbo].[CountrySpatial](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [ObjectID] [bigint] NULL,
 [FIPS_CNTRY] [nvarchar](255) NULL,
 [GMI_CNTRY] [nvarchar](255) NULL,
 [ISO_2DIGIT] [nvarchar](255) NULL,
 [ISO_3DIGIT] [nvarchar](255) NULL,
 [ISO_NUM] [int] NULL,
 [CNTRY_NAME] [nvarchar](255) NULL,
 [LONG_NAME] [nvarchar](255) NULL,
 [ISOSHRTNAM] [nvarchar](255) NULL,
 [UNSHRTNAM] [nvarchar](255) NULL,
 [LOCSHRTNAM] [nvarchar](255) NULL,
 [LOCLNGNAM] [nvarchar](255) NULL,
 [STATUS] [nvarchar](255) NULL,
 [POP2005] [bigint] NULL,
 [SQKM] [float] NULL,
 [SQMI] [float] NULL,
 [COLORMAP] [smallint] NULL,
 [geom] [geometry] NULL,
PRIMARY KEY CLUSTERED 
(
 [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[CountrySpatial]  WITH CHECK ADD  CONSTRAINT [enforce_srid_geometry_CountrySpatial] CHECK  (([geom].[STSrid]=(0)))
GO

ALTER TABLE [dbo].[CountrySpatial] CHECK CONSTRAINT [enforce_srid_geometry_CountrySpatial]
GO

Answer 1

The first thing to comment is that earth surface points should be stored using Geography, not Geometry. There are differences to the storage and how the functions work (even if similarly named)

Here is a working example:

Simplified table:

CREATE TABLE CountrySpatial(
 ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
 geog geography NULL)
GO

Insert something that resembles a diamond around India

INSERT INTO CountrySpatial(geog)
VALUES (geography::STGeomFromText('POLYGON((' +
 '77.22702 28.67613, ' + -- new delhi (top)
 '72.566071 23.059516, ' + -- ahmedabad (left)
 '77.593689 13.005227, ' + -- bengaluru (bottom)
 '88.374023 22.614011, ' + -- kolkata (right)
 '77.22702 28.67613))', 4326));

Find the match. It is UNION-ed to the Point being sought. STBuffer increases the point to a 100km radius, so that it will show up when viewed together with the Geography record found (switch to Spatial tab in the output)

select geog
from countryspatial
where geog.STIntersects(geography::STGeomFromText('POINT (78 22)', 4326))>0
union all
select geography::STGeomFromText('POINT (78 22)', 4326).STBuffer(100000)