1 Reply by davec (edited by davec 01-06-2009 12:38:16)

  • davec
  • Veteran
  • Offline
  • From: Brecon, Powys, Wales
  • Registered: 11-05-2006
  • Posts: 108

Re: Serious errors betwen Lat/Lon and SPATIAL_REF

This does not look good. :(

I can see errors in a some records in our Recorder 6 database whilst I was mapping out some 10Km data.

By comparing the data in the LAT/LONG and SPATIAL_REFERENCE fields for those records which look in error I obtain these results (The external tool used Recorder's LAT/LONG values to return OSGB).

 
LAT       LONG        SPATIAL_REF AS REPORTED IN RECORDER     SPATIAL_REF IN EXTERNAL TOOL
===================================================================================
52.265830   -3.314651  SN87                                        SO10295 63932
52.265830   -3.314651  SO15                                        SO10295 63932
51.964272   -4.016153  SN63                                        SN61500 31500
52.335033   -3.892049  SN77                                        SN71000 72500

Note that the first two are the same LAT/LONG but different grid refs. :/

For comparison,  LAT/LONG reported for SN60000 30000 and SN70000 70000 by the external tool is;

SN63   51.950417  -4.037362
SN77   52.312308  -3.907219

I haven't looked at records at other spatial resolution, but I will be doing so. So my question is - has anyone else come across this? I hope this is just limited to 10Km, but I will now need to investigate this.

The trouble is - will the right grid ref please stand up! As far as I know, we have never imported records with LAT/LONG, everything has been provided in OSGB grid refs. But then, if it's a problem with Recorder's conversion, why is it only on some 10Km records and not others?

I have no Idea at the moment....watch this space.

Dave Cope,
Biodiversity Technology Officer,
Biodiversity Information Service for Powys and Brecon Beacons National Park.

davec's Website

2 Reply by davec

  • davec
  • Veteran
  • Offline
  • From: Brecon, Powys, Wales
  • Registered: 11-05-2006
  • Posts: 108

Re: Serious errors betwen Lat/Lon and SPATIAL_REF

This is a case of mia culpa. :rolleyes:

I'm just checking the way I've handled the data in our reporting system. LOCATION.LAT and LOCATION.LONG has been used where there is a valid location key but not LOCATION.SPATIAL_REF. That still points to SAMPLE.SPATIAL_REFS.

Ooops. Cue sound of self flagellation with broken Recorder 6 CDs. :o

Dave Cope,
Biodiversity Technology Officer,
Biodiversity Information Service for Powys and Brecon Beacons National Park.

davec's Website

3 Reply by johnvanbreda

  • johnvanbreda
  • Administrator
  • Offline
  • From: UK
  • Registered: 27-04-2006
  • Posts: 1,237

Re: Serious errors betwen Lat/Lon and SPATIAL_REF

Hi Dave

One thing to note is the slight differences you probably see between the external tool and Recorder's conversion is because lat/long is not a fixed thing. There are many variations calculation used to convert between a point on the approximate sphere of the earth and a lat long value. Recorder is using the OSGB 1936 form of calculation for your OSGB grid references, but the World Geographic Survey 1984 (WGS84) form of calculation is more widespread because it is used by GPS. I hope that makes some sense!

Cheers

John van Breda
Biodiverse IT

johnvanbreda's Website

4 Reply by davec

  • davec
  • Veteran
  • Offline
  • From: Brecon, Powys, Wales
  • Registered: 11-05-2006
  • Posts: 108

Re: Serious errors betwen Lat/Lon and SPATIAL_REF

Hi John,

Yes it makes sense thanks. As far as I can see I was using the correct projection throughout. I use a transformation to generate point geometries from Recorder data: 

Transform( GeomFromText( 'POINT(X Y)',4277),27700)

Where X = SAMPLE.LONG, Y=SAMPLE.LAT. 4277 and 27700 are SRIDs.

The problem has been solved in that I can see that the fields in postgresql were not populated correctly as I noted before.

Just to clarify for any onlookers this is NOT a Recorder 6 issue.

Cheers.

Dave Cope,
Biodiversity Technology Officer,
Biodiversity Information Service for Powys and Brecon Beacons National Park.

davec's Website