1 Reply by RobLarge (edited by RobLarge 18-11-2011 12:35:43)

  • RobLarge
  • RobLarge
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 15-07-2008
  • Posts: 812

Re: Lat/Long and Like

Two unrelated questions both pertaining to an XML report I have been working on.

The first relates to latitude and longitude. The main query in my report extracts both sample lat and sample long (as well as a lot of other fields) and the columns section includes statements to output these along with everythinge  else. However the final output doesn't include these two fields (everything else is there and the syntax is identical).

Is there something special about these two fields? I want to use them to send the report output to the Recorder mapping tool (among other things).

The second question is about the use of the LIKE operator in a WHERE clause in my XML report.

If I use the standard SQL wildcard character % to denote a string of any characters in a LIKE statement, the XML report falls over reporting something like "No parameter for the object '%'". Presumably the % has some reserved functionality in XML.

I have created my own workaround, by creating a user function to do the LIKE outside the XML report XML, but am I missing something, is there a way to do LIKE with a wildcard in XML?

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

RobLarge's Website

2 Reply by RobLarge

  • RobLarge
  • RobLarge
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 15-07-2008
  • Posts: 812

Re: Lat/Long and Like

I have now found a partial answer to the first question in the xml help (have to convert lat & long to strings), going to test whether this enables the mapping functionality.

Still interested to hear about the second though.

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

RobLarge's Website

3 Reply by RobLarge

  • RobLarge
  • RobLarge
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 15-07-2008
  • Posts: 812

Re: Lat/Long and Like

OK I have converted the lat & long values to varchar(40), and they now appear in the output, but they appear to have lost a lot of precision, having only 4 or 5 decimal places (presumably the CAST function did this). Don't know if this matters in the grand scheme of things, but it doesn't seem right.

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

RobLarge's Website

4 Reply by MikeWeideli

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

Re: Lat/Long and Like

If you substitute the % with Ampersand#37;  (substitute the word Ampersand with the & character - had to do this way to stop forum displaying as a  % )    the like should work. The % has a reserved meaning within XML and need to be substituted in the same way  as  <  and   >   

To make this clear I have uploaded a simple example of a report which implements the above. It returns a Survey where the word 'Test' is included in the Survey name.

http://forums.nbn.org.uk/uploads.php?file=JNCC_Example_4.zip

Truncation of the Lat/Long will be a problem. The following seems to produce an acceptable result.

SELECT CONVERT(varchar(50), CAST(lat AS decimal(38,15))) AS LATITUDE , CONVERT(varchar(50), CAST(long AS decimal(38,15))) AS LONGITUDE from sample


I have updated my XML help system to reflect  the above and at the same time made a few minor ammendments. The latest version is at http://forums.nbn.org.uk/uploads.php?file=R6XMLHelp.zip

Mike Weideli

MikeWeideli's Website

5 Reply by RobLarge

  • RobLarge
  • RobLarge
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 15-07-2008
  • Posts: 812

Re: Lat/Long and Like

Thanks Mike. I thought the issue with the % would be much as you describe.

Regarding the CONVERT function, I think I will need the final output to be float (or at least a numeric format) but I will play around & see what works best.

One more question. Is it possible to send the output from an xml report to the map, or is this functionality only available with outputs from the wizard? If it is possible, what do I need to do to enable it (I thought Lat, Long and spatial_ref_system would do it but it doesn't seem to).

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

RobLarge's Website

6 Reply by MikeWeideli

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

Re: Lat/Long and Like

Convert takes a parameter which determines the precision of the output, but it outputs in scientific notation. This might be Ok for your needs ?

XML report output can go to the map. It needs lat, long,spatial_ref, spatial_ref_system, and the taxon_list_Item_key

Mike Weideli

MikeWeideli's Website

7 Reply by RobLarge

  • RobLarge
  • RobLarge
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 15-07-2008
  • Posts: 812

Re: Lat/Long and Like

Thanks Mike

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

RobLarge's Website

8 Reply by RobLarge

  • RobLarge
  • RobLarge
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 15-07-2008
  • Posts: 812

Re: Lat/Long and Like

Great, that now goes to the map and apears to display correctly.

However, if I use the tool to find source data for the occurrence it tells me that there are no distribution points at that location. Is there anything I can add to enable that functionality? I have already tried adding Taxon_Occurrence_Key, but that didn't work.

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

RobLarge's Website

9 Reply by RobLarge (edited by RobLarge 22-11-2011 15:49:16)

  • RobLarge
  • RobLarge
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 15-07-2008
  • Posts: 812

Re: Lat/Long and Like

OK Mike that is mysterious. I just implemented the % sign change exactly as you described above, tested it and it worked fine a number of times.

Then I handed the report to someone else to test and it fell over imediately with the same error I was getting in the first place "exception message : No argument for format '%1k'." (the 1k is the start of the string of text I am looking for). I retested it on my machine and now it fails every time.

I have rechecked my code and the text %1k does not appear anywhere, while the sequence ampersand#37;1k (but with a & instead of ampersand) occurs several times.

I am at a loss as to why it should function perfectly at first, then start to fail, but it has. I won't bother sending you a copy of the report, because it will be easier and quicker to return to the way I had it coded before (using a ufn to do the LIKE externally) and in any case the report uses several ufns which you won't have and depends on data in our db which you wont have either.

Bit of an annoyance though.

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

RobLarge's Website

10 Reply by RobLarge

  • RobLarge
  • RobLarge
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 15-07-2008
  • Posts: 812

Re: Lat/Long and Like

Another observation.

Having made extensive use of ufns which return Bit data type (i.e. boolean) I have noticed  that when using these in an xml report, the result is not interpreted as True or False, but only as 1 or 0.

So

SELECT * FROM Tablename
WHERE Userfunction

Returns no fields, even if the result from Userfunction is True, but

SELECT * FROM Tablename
WHERE Userfunction = 1

Does return data.

Is this the expected behaviour? I haven't checked whether you mention this in your xml help file (I am just about to download the lastest version).

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

RobLarge's Website

11 Reply by MikeWeideli

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

Re: Lat/Long and Like

I think this is expected SQL Server  behaviour and not something different in XML reports.

Mike Weideli

MikeWeideli's Website

12 Reply by RobLarge

  • RobLarge
  • RobLarge
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 15-07-2008
  • Posts: 812

Re: Lat/Long and Like

Ah I see that now. How odd, that the implicit logical format works in many other applications of SQL and many programming languages, but not in SQL Server.

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

RobLarge's Website