3

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

  • Can you do a SELECT DISTINCT shape.STSrid FROM zelaya2.dbo.HYDRANTS; and the same for incidents. If they are different then you have an issue with SRIDs SQL Server will not compare geometries or geographies with different SRIDs. It may be worthwhile putting up some test data and table schemas for people to try things out for you. The query you are trying should work, so it is likely to be a data issue. – MickyT Nov 26 '19 at 22:52
  • 1
    If the data type of the column is geometry, then a 1000 degree buffer would be generated, and an invalid geometry would result. – Vince Nov 27 '19 at 23:43
0

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.

0

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.

  • I think you may find it is still a SRID incompatibility. My take is the data being return when the last line(s) are removed is that the cross join is being returned. – MickyT Nov 26 '19 at 22:46
  • Thanks for your response. I confirmed that both layers are SRID 4326. I tried running your query, but as with mine, it ran without error and returned no records. – rzelayac Nov 27 '19 at 22:53
  • Are the shape and geom fieldtypes Geometry or geography? Are you sure the item you have chosen has incidents within the distance specified? If you increase the buffer size does it eventually deliver records? – jport Nov 28 '19 at 12:32
  • Did you ever get to the bottom of this? – jport Dec 4 '19 at 19:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.