topic Re: STX Sql Server Geometry Query returns Longitude of 278?? in Geodatabase Questions

STX Sql Server Geometry Query returns Longitude of 278??

ScottMinter — Wed, 03 Jul 2024 22:29:04 GMT

I have a query on table that contains a geometry type named shape:

select shape.STX from Vehicle;

The return value I get is: 278.440065912902

Any ideas how I could end up with a Geometry column in a SQL table with such a result? I get the data from Geoevent which comes from Verizon. Verizon passes me a shape (geometry) and also a lat and long column. The lat and long columns are correct. However, when I try to use a select statement to see the longitude using STX, I get this weird result. If I use shape.STX - 360 I get the correct longitude. I'm puzzled.

I need to be able to use the shape column in an STDistance() query to see how far away my vehicles are from a work order task on another object on our map. Obviously, with a longitude value of 278... the STDistance is giving me erroneous information.

Note: when querying for shape.STY I get the correct latitude. So only the longitude is wrong in the shape column??

Re: STX Sql Server Geometry Query returns Longitude of 278??

VinceAngelo — Thu, 04 Jul 2024 18:15:07 GMT

While latitude has hard limits on values ([-90,90]), longitude does not. pi radians is equivalent in every way to 3pi radians and 2pi, 0, and -2pi are equivalent as well. There may be limitations in storage of coordinate values in SQL Server, which, like Esri's ST_Geometry, uses integer internal representation, but values between -360 degrees and +360 degrees ought to be safe.

STDistance(), if done correctly for geodesic values, should not find any distance at all between 278.440065912902 and -81.559934087098 with the same latitude. Measuring in Cartesian degrees is of course useless for all purposes.

So it seems your problem is not with the longitude values but with distance measurement. You would need to edit or reply to your own post with a more specific description of that issue to get a useful answer.

- V

Re: STX Sql Server Geometry Query returns Longitude of 278??

ScottMinter — Tue, 09 Jul 2024 12:48:54 GMT

Attached is an image of the query I'm running. The important fact is that the Longitude that is passed from Verizon as a column is accurate.

The longitude when using the STX function is different and causing the STDistance to give an inaccurate result.

Query:

SELECT v.Latitude as VehicleLatPassedFromVerizon, v.Longitude as VehicleLongPassedFromVerizon, v.shape.STX as VehicleLongUsingTsqlNeededForDistance, v.shape.STY as VehicleLatUsingTsql, v.shape.STAsText() VehicleShapeText,geoshape.STAsText() WOGeoshapeText, v.shape as VehicleShape, geoshape as WOGeoshape,geoshape.STX as WOLatFromShape, geoshape.STY as WOLongFromShape, geoshape.STSrid WOSrid, shape.STSrid VehicleSrid, geoshape.STDistance(v.SHAPE) [Distance between WO Task and Vehicle] FROM [Elements].[dbo].[WM_ServiceOrders_WorkItems_Tasks] t, [ArcSDE].dbo.VEHICLE v where taskid in (404565,407063) and (v.Label like '%Patrick%' or v.Label like '%Jasen%')

So in this example, Verizon is sending me a column from the GPS device named longitude with a value = -82.00294200. However, when I use the SQL Server STX function on the vehicle shape Verizon is sending I get something different and I don't understand why. v.shape.STX = 277.997153412551.

If I take v.Shape.STX - 360 it equals -81.00294200. I noticed this, but don't know what it means. I know if I plot the vehicles location on Google Maps using the longitude of 277.997153412551 instead of -81.00294200 the point will show up in a different place on the earth vs using the longitude of -81. So the STDistance is giving me an incorrect value.

Re: STX Sql Server Geometry Query returns Longitude of 278??

ScottMinter — Tue, 09 Jul 2024 17:36:08 GMT

When using the Geography data type the distance results are returned in meters while with the Geometry data type the results are reported in decimal degrees.

This fact was unknown to me and was the root of my issue. I'm a software engineer learning about GIS....still lots to learn.

Casting the geometry given from Verizon into a Geography type was the solution.

The backstory is our WO system vendor changed their coordinate system to always use WKID 4326. This led to many changes and finally got me to the stored procedure that calculated the distance between our WO and the vehicle. Had to change the coordinate system of the vehicle layer to use Srid 4326 first because STDistance will return 0 every time if the coordinate systems are different.

Solution query by recasting to a geography type:

SELECT round(geography::Point(geoshape.STY,geoshape.STX,4326).STDistance(geography::Point(v.Shape.STY,v.Shape.STX,4326))/1609.34,1) as MilesFromWO FROM [Elements].[dbo].[WM_ServiceOrders_WorkItems_Tasks] t, [ArcSDE].dbo.VEHICLE v where taskid in (404565) and v.Latitude is not null

Re: STX Sql Server Geometry Query returns Longitude of 278??

ScottMinter — Tue, 09 Jul 2024 17:38:49 GMT

A helpful link: https://www.benjaminspaulding.com/2012/10/29/stdistance-spatial-sql/