1 Topic by atheta01

  • atheta01
  • Veteran
  • Offline
  • From: YNU Coleoptera recorder
  • Registered: 30-10-2009
  • Posts: 325

Topic: How to Report distance of record from centroid grid ref in Circle rpt

Currently when I produce output files in response to data search enquiries I am not able to send out information in GIS format and I rely entirely on Recorder 6 output for this. I am increasingly being asked in response to a data search about a centroid for information which gives distance of record from the centroid. I appreciate that this can be done in GIS. Would it be possible to introduce a field in the circle report or via the Report Wizard which gives this information?

Bob Marsh
Doncaster LRC
Coleoptera recorder for YNU

atheta01's Website

2 Reply by MikeWeideli

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

Re: How to Report distance of record from centroid grid ref in Circle rpt

Should be fairly easy to do, but will require a udf to calculate the distance.  Also change to the circle reports.

Mike Weideli

MikeWeideli's Website

3 Reply by TeresaF

  • TeresaF
  • Veteran
  • Offline
  • From: BTO
  • Registered: 12-05-2008
  • Posts: 172

Re: How to Report distance of record from centroid grid ref in Circle rpt

Not as convenient as being in the R6 reports, but if you want a quick solution CBDC use my spreadsheet macro to add this information when processing the output in Excel (you have to type the site centroid at the top in B1).

It gives centre-centre and also the max and min distances based on the resolution of the grid references - CBDC supplies all three to enquirers, as min distance of 0 means the grid square of the record overlapped with the site centroid. It was a popular addition with consultants.

https://www.dropbox.com/s/078x63n04ann8 … y.xls?dl=0

-----------------
Teresa Frost | Wetland Bird Survey National Organiser | BTO
Other hat  | National Forum for Biological Recording Council
(Old hats  | NBN Board, ALERC Board, CBDC, KMBRC)

4 Reply by MikeWeideli

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

Re: How to Report distance of record from centroid grid ref in Circle rpt

Hi

I have had a go at this. Attached is the SQL to add a new UDF to calculate the distance, plus a new Circle Report Ci4 which is the same as Ci1, but returns the distance. Before proceeding further could you please give this a try and let me know if there are any problems. To make use of this feature your  Circle reports need to be adapted, but this will be easier once we have one report correct.

Post's attachments

JNCC_Ci4.xml 6.18 kb, 8 downloads since 2015-12-10 

SqLReturnDistance.txt 4.73 kb, 12 downloads since 2015-12-10 

You don't have the permssions to download the attachments of this post.
Mike Weideli

MikeWeideli's Website

5 Reply by atheta01

  • atheta01
  • Veteran
  • Offline
  • From: YNU Coleoptera recorder
  • Registered: 30-10-2009
  • Posts: 325

Re: How to Report distance of record from centroid grid ref in Circle rpt

Thanks Teresa and Mike for quick responses.....as soon as I can I'll have a look at this probably in next couple of days and I'll get back to you.

Bob Marsh
Doncaster LRC
Coleoptera recorder for YNU

atheta01's Website

6 Reply by atheta01

  • atheta01
  • Veteran
  • Offline
  • From: YNU Coleoptera recorder
  • Registered: 30-10-2009
  • Posts: 325

Re: How to Report distance of record from centroid grid ref in Circle rpt

Hi Mike - I've just tried to run JNCC_Ci4.xml

In the Parameter Entry window I complete entries in all the available fields

When I run the report I get the following message:...."There is an error in the SQL code. The error message is: 'Cannot find either column "dbo" or the user-defined function or aggregate "dbo.LCReturnDistanceCircle, or the name is ambiguous' "

Bob Marsh
Doncaster LRC
Coleoptera recorder for YNU

atheta01's Website

7 Reply by atheta01

  • atheta01
  • Veteran
  • Offline
  • From: YNU Coleoptera recorder
  • Registered: 30-10-2009
  • Posts: 325

Re: How to Report distance of record from centroid grid ref in Circle rpt

Hi Mike - just running Ci4 as it stands isn't enough is it? I've got to do something with the SQL to add the new UDF to calculate the distance but I'm not sure what.

Bob Marsh
Doncaster LRC
Coleoptera recorder for YNU

atheta01's Website

8 Reply by MikeWeideli

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

Re: How to Report distance of record from centroid grid ref in Circle rpt

Have you got SQL Management Studio installed and do you have access to it ?

Mike Weideli

MikeWeideli's Website

9 Reply by atheta01

  • atheta01
  • Veteran
  • Offline
  • From: YNU Coleoptera recorder
  • Registered: 30-10-2009
  • Posts: 325

Re: How to Report distance of record from centroid grid ref in Circle rpt

Not here at the LRC but I have on my laptop at home. I'll drop  a post on the Forum when I've got set up.

Bob Marsh
Doncaster LRC
Coleoptera recorder for YNU

atheta01's Website

10 Reply by atheta01

  • atheta01
  • Veteran
  • Offline
  • From: YNU Coleoptera recorder
  • Registered: 30-10-2009
  • Posts: 325

Re: How to Report distance of record from centroid grid ref in Circle rpt

Hi Mike - I've got SQL Server 2008 R2 Management Studio. All I need to know is what buttons to press................I've downloaded the the SQL to add a new UDF to calculate the distance, and the circle report Ci4 to my laptop.

Bob Marsh
Doncaster LRC
Coleoptera recorder for YNU

atheta01's Website

11 Reply by atheta01

  • atheta01
  • Veteran
  • Offline
  • From: YNU Coleoptera recorder
  • Registered: 30-10-2009
  • Posts: 325

Re: How to Report distance of record from centroid grid ref in Circle rpt

Thanks for the help on that Mike - it works fine on my home R6 installation. All I've got to do now is get our corporate IT people to help me run this into my networked version of R6 at the LRC, because I don't have direct access to the R6 server or Management Studio.

Thanks also Teresa - I've been having a play with your spreadsheet macro (I'm impressed) - this will provide a kind of solution to what I want. I can run the existing Circle Report in R6, and in the Excel created from that paste in the distance-from-centroid data column from your sheet into my final spreadsheet. A bit faffy but should be an interim solution.

Bob Marsh
Doncaster LRC
Coleoptera recorder for YNU

atheta01's Website