SQL not intersect when projection is 3857

KurtRadamaker

Using sql when I try to intersect a polygon with coordinates, when the projection is 3857 the intersect does NOT work, when the projection is 4326 wgs it works fine. The only difference between the FCs is the SRID. I projected FC PARCELS_TX 3857 to FC PARCELS_TX_WGS as a new FC and ran the same sql code and it returned a row as expected.

Below code using 4326 the intersect works

SET @lat = 32.288675
SET @long = -98.278444
DECLARE @g geometry = geometry::Point(@long, @lat, 4326)

/****** Script for SelectTopNRows command from SSMS ******/
select [Poly_MPARCELAPN]
,[Pt_STZIP4]
,[Pt_XCOORD]
,[Pt_YCOORD]
,[Shape]
FROM [GISDB].[dbo].[PARCELS_TX_WGS]
where @g.STIntersects(Shape) = 1

 

##################################

3857

Same code over table projected at 3857 does not return a result. It should return the same result as 4326

SET @lat = 32.288675
SET @long = -98.278444
DECLARE @g geometry = geometry::Point(@long, @lat, 3857)

/****** Script for SelectTopNRows command from SSMS ******/
select [Poly_MPARCELAPN]
,[Pt_STZIP4]
,[Pt_XCOORD]
,[Pt_YCOORD]
,[Shape]
FROM [GISDB].[dbo].[PARCELS_TX]
where @g.STIntersects(Shape) = 1

NathanRaley

Some more details now that I am not posting from my phone:

3857 in SQL Server uses Meters as its unit, 4326 uses decimal degrees.  The coordinates you posted are not the same coordinates between the two, you would have to convert the geometry to a 3857 geometry, or convert from the decimal degrees to meters.  Also, any geometry defined as a specific SRID within SQL Server will be excluded from any geospatial query you are performing against it when the geometry is in a different spatial reference.

 

Hope that helps.