Combining ST Buffer and Intersect with Where clause

Question

I'm trying to create a buffer around a single point in a spatial entity and finding all the points from a different entity that fall within that buffer. I tried two approaches. The code below runs without error but returns no records, even though I know from looking at the data in ArcMap that there should records should come up (increasing the buffer to a huge number also didn't return any records). Removing the last line seems to create a buffer around every point in the incidents entity and finds every point in the hydrants entity. I want to figure out how to do this around just one point in the hydrants entity.

SELECT incidents.id_incident, hydrants.OBJECTID_1
FROM zelaya2.dbo.incidents, zelaya2.dbo.HYDRANTS
WHERE Shape.STBuffer(1000).STIntersects(incidents.geog) = 1 
AND hydrants.OBJECTID_1 = 177619;

Also tried setting a temporary variable as the buffer, but again got no records returns and no error message.

DECLARE @g geography;
Set @g = 
(SELECT hydrants.Shape.STBuffer(1000)
FROM zelaya2.dbo.hydrants
WHERE OBJECTID_1 = 177619)

SELECT *
FROM zelaya2.dbo.incidents
WHERE @g.STIntersects(incidents.geog) = 1

EDIT: I confirmed that both layers are SRID 4326

Answer 1

In ArcMap datalayers with projection info defined are "projected on the fly" so that regardless of their projection info they will seem conincident in the software. My guess is that your hydrants and incident data are not in the exact same projection (SRID in sql server).

Run this and find out if that's your problem.

Select shape.STSrid()
from hydrants 

Select shape.STSrid()
from Incidents

If they don't report the same you would need to project one so they are the same and try your query again.

Answer 2

I originally thought it was an SRID incompatability but realize that if you are getting some records back than they must be the same. So try this instead

select hydrants.Objectid_1, shape.STBuffer(1000) as buffshape
into #buffers
from zelaya2.dbo.HYDRANTS

select 
from zelaya2.dbo.incidents a inner join #buffers b
on a.geog.STIntersects(b.buffshape) = 1
WHERE OBJECTID_1 = 177619

This should do the trick but you may have to create the #buffers temp table around the hydrants. It may be possible to do this without the temp table but it makes it easier in two steps with the on clause rather than the geography info in the where clause.