1 Topic by charlesroper

  • charlesroper
  • charlesroper
  • Digital Development Manager
  • Offline
  • From: Field Studies Council
  • Registered: 26-03-2006
  • Posts: 906

Topic: Recorder's Lat/Long coordinates are inaccurate?

I've been loading some data into SQL Server 2008 as spatial Point data using the following method:

geography::Point(sa.LAT, sa.LONG, 4326)

Where sa is the SAMPLE table and 4326 is the SRID (WGS 84). Trouble is, the points are not plotting in the correct position. I thought this was a projection error at first, but on inspecting the LAT and LONG values in the SAMPLE table I see that they don't correspond to the SPATIAL_REFERENCE. For example:

A sample with spatial ref of TQ217137 has a lat/long in Recorder of 50.9093715524171, -0.268780988607304.

If I use Where's the Path to locate TQ217137, it indicates that the lat/long should be 50.909945, -0.270343.

Here's a screenshot: [img]http://i.imgur.com/gxrgfC4.png[/img]. You can see that Recorder's generated lat/long is closer to TQ218136. It's ~120m out. What is going wrong here?

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

charlesroper's Website

2 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,627

Re: Recorder's Lat/Long coordinates are inaccurate?

Recorder 6 uses OSG 36 for Lat/Long. There is some informatioin on using SQL 2008 spatial fields somehwere which I worked out a while ago   - trying to track it down.

Mike Weideli

MikeWeideli's Website

3 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,627

Re: Recorder's Lat/Long coordinates are inaccurate?

USE [NBNData]
GO
/****** Object:  UserDefinedFunction [dbo].[LCSpatial_OSGB_Geom]    Script Date: 04/30/2011 19:52:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[LCSpatial_OSGB_Geom]
(@Spatial_Ref varchar(30), @SRS varchar(4), @Indicator int )
RETURNS Geometry
--
--    DESCRIPTION
--    Function to return a Geometry as a point from a Spatial Reference (OSBG only)
--   
--
--    PARAMETERS
--    NAME                DESCRIPTION
--   
--
--
--    AUTHOR:    Mike Weideli
--    CREATED: 28 April 2011
--

AS
BEGIN

--****************************************************************************************************
DECLARE @ReturnGeometry Geometry
DECLARE @WKTString varchar(100)
DECLARE @EastingStr varchar(20)
DECLARE @NorthingStr varchar(20)


If @SRS = 'OSGB'
BEGIN
  Set @NorthingStr = dbo.LCReturnNorthingsV2(@spatial_ref,'OSGB',@Indicator)
  Set @EastingStr = dbo.LCReturnEastingsV2(@spatial_ref,'OSGB',@Indicator)
  SET @WKTString = 'POINT(' + @EastingStr + ' ' + @NorthingStr + ')'
  Set @ReturnGeometry = geometry::STPointFromText(@WKTString,27700)
   




END

--****************************************************************************************************
RETURN @ReturnGeometry
END

Also R6 V6.18 has a UDF which converty WSG84 into OSGB 36.

Mike Weideli

MikeWeideli's Website