1 Reply by Janet Simkin

  • Janet Simkin
  • Janet Simkin
  • Veteran
  • Offline
  • From: Northumberland
  • Registered: 10-05-2006
  • Posts: 260

Re: Reducing grid refs to 6 digits

Our database includes a lot of 8 and even 10 digit grid refs to locate particular trees on which rare lichens grow. We need to be able to reduce these to 6 digit grid refs (100m precision) for certain reports and for the NBN Gateway. Does anyone have a neat bit of SQL to do this?

Janet

Janet Simkin
British Lichen Society

Janet Simkin's Website

2 Reply by MikeWeideli

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

Re: Reducing grid refs to 6 digits

Janet

There is already a UDF in Recorder which does this.

dbo.LCReturnHectad(SpatialRef, SrType )

Takes the saptial ref and spatial ref system. Returns blank if grid ref is lower precision than 100m. You might need to adapt it slightly if you want to return the actual grid ref. if  the precision is less than 100m.


There is also a function which returns 1km tetrad and 10km precision.

Mike

Mike Weideli

MikeWeideli's Website

3 Reply by Janet Simkin

  • Janet Simkin
  • Janet Simkin
  • Veteran
  • Offline
  • From: Northumberland
  • Registered: 10-05-2006
  • Posts: 260

Re: Reducing grid refs to 6 digits

I thought there would be, thanks.

Janet

Janet Simkin
British Lichen Society

Janet Simkin's Website

4 Reply by charliebarnes

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

Re: Reducing grid refs to 6 digits

I can see dbo.LCReturnTetrad which returns a 2km x 2km (SK97U) and dbo.LCReturnHectad which returns 10km x 10km (SK97) but I can't see the one to return 100m x 100m or 1km x 1km?

What can dbo.FormatGridRef return (I thought there was a post about this somewhere, but maybe not - Sally says in http://forums.nbn.org.uk/viewtopic.php?id=318 it can return 1km using dbo.FormatGridRef(SAMPLE.SPATIAL_REF, SAMPLE.SPATIAL_REF_SYSTEM, 1))?

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

charliebarnes's Website

5 Reply by MikeWeideli

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

Re: Reducing grid refs to 6 digits

You are right. There is no function which is returning 100m.

LCRectifyGR calls other UDFs  to return 10km, 2k m(Tetrad) and  1km depending on the parameters. It uses  dbo.FormatGridRef to return the 1 km (parameter 1) or 10km (parameter 0). However, there is nothing inbuilt which does the 100m

I have adapted the Hectad script and loaded the script for a new function at http://forums.nbn.org.uk/uploads.php?file=SQLForReturn100mSq.zip

Please run some checks on results before using.

Takes  spatial_ref, spatial_ref_system and a parameter of 0 or 1.
If this last parameter is set to 1 then if the grid ref is of a lower precision that 100m the actual grid ref is returned. If set to 0 then a blank is returned in these cases.

 

.

Mike Weideli

MikeWeideli's Website