SQL not intersect when projection is 3857

91
3
01-13-2021 02:00 PM
KurtRadamaker
New Contributor II

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

0 Kudos
3 Replies
DanPatterson
MVP Notable Contributor

Wild guess... web mercator is not recommended for any geometry calculations like distance or area, so it doesn't surprise me that it would be the last choice to use for a projected coordinate system.  Can you use another one ? (eg utm etc)


... sort of retired...
0 Kudos
NathanRaley
New Contributor III
Those are not valid 3857 coordinates. 3857 coordinates are in meters, not degrees.
0 Kudos
NathanRaley
New Contributor III

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.