1 Topic by SteveGoddard

  • SteveGoddard
  • SteveGoddard
  • Member
  • Offline
  • From: Brecon
  • Registered: 31-01-2012
  • Posts: 39

Topic: Recorder 6 - Function to convert OSGB36 to WGS84

Hi All,
We dump all of our data from R6 every evening to a PostGIS database in order to do our reporting etc.
I've noticed that one of our systems was using the sample.lat and sample.long for plotting points onto a Bing map. The points were out by a couple of 100 metres due to R6 using OSGB36 rather than WGS84.

So...

As part of the nightly dump, we initially de-normalise the data into a 'denormal' table within the R6 database. Is there a function available within MS SQL Server (either natively or part of R6) to convert between OSGB36 and WGS84? If there's noting built in or part of the R6 install, has anyone written one or know of one?

Many thanks,
Steve

=====================================================
Steve Goddard - IT Officer
Biodiversity Information Service for Powys & Brecon Beacons National Park
T: 01874 610881 - E: steve[at]b-i-s.org - W: www.b-i-s.org

SteveGoddard's Website

2 Reply by charlesroper

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

Re: Recorder 6 - Function to convert OSGB36 to WGS84

There's no reprojection function either in SQL Server or as a custom function as part of Recorder that I know of. However, there is such a function in PostGIS. Could you not do the reprojection in PostGIS? Here's a pointer:

http://gis.stackexchange.com/questions/ … tgis-table

Alternatively, if your denormal table is spatially enabled (which is quite easy to do if you're on a recent version of SQL Server), you could do the conversion using OGR2OGR, which is a freely available conversion tool. This is how you can convert a shapefile from one projection to another:

http://www.mercatorgeosystems.com/blog- … hape-file/

And this is how you connect to a SQL Server spatial database:

http://www.gdal.org/drv_mssqlspatial.html

So it would be a question of combining the two techniques and running the command as part of your nightly run.

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

3 Reply by charliebarnes

  • charliebarnes
  • Veteran
  • Offline
  • From: Greater Lincolnshire
  • Registered: 07-09-2008
  • Posts: 1,396

Re: Recorder 6 - Function to convert OSGB36 to WGS84

Or if your Bing map is HTML based, reproject using javascript: http://www.movable-type.co.uk/scripts/l … oords.html

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

charliebarnes's Website

4 Reply by SteveGoddard

  • SteveGoddard
  • SteveGoddard
  • Member
  • Offline
  • From: Brecon
  • Registered: 31-01-2012
  • Posts: 39

Re: Recorder 6 - Function to convert OSGB36 to WGS84

Thanks Charlie,
That's basically what I do at the  moment - although I do it server side with a PHP script that converts the eastings / northings to OSGB36 and then to WGS84.
It would just be handy to have this processing done 'at source' when we do the extraction.

Steve

=====================================================
Steve Goddard - IT Officer
Biodiversity Information Service for Powys & Brecon Beacons National Park
T: 01874 610881 - E: steve[at]b-i-s.org - W: www.b-i-s.org

SteveGoddard's Website