topic Re: SQL not intersect when projection is 3857 in Developers Questions

SQL not intersect when projection is 3857

KurtRadamaker — Wed, 13 Jan 2021 22:00:40 GMT

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

Re: SQL not intersect when projection is 3857

DanPatterson — Thu, 14 Jan 2021 00:09:03 GMT

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)

Re: SQL not intersect when projection is 3857

NathanRaley — Thu, 14 Jan 2021 01:01:40 GMT

Those are not valid 3857 coordinates. 3857 coordinates are in meters, not degrees.

Re: SQL not intersect when projection is 3857

NathanRaley — Thu, 14 Jan 2021 15:17:41 GMT

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.