1 Reply by atu

  • atu
  • Member
  • Offline
  • From: Luxembourg
  • Registered: 06-10-2008
  • Posts: 26

Re: need help

Hi,
I need some help for some problems with the following xml report:
http://forums.nbn.org.uk/uploads.php?fi … d_taxa.xml
:/

There are 2 problems:

1. The ORDER BY is ignored. Also i tried the xml equivalent  Orderby tag, But it dosent work either:
<Orderby  name="by ..." SQL="Order By Taxon_Branch, Taxon_Name" />

2. How to optimize the big fat part at the beginning of the query to get the taxon branch?

Thanks,
Armand

atu's Website

2 Reply by atu

  • atu
  • Member
  • Offline
  • From: Luxembourg
  • Registered: 06-10-2008
  • Posts: 26

Re: need help

There is an other problem.
When recorder has finished the report it isnt possible to export a kml (google earth) file. I guess there are some fields missing in the report. But which fields?

atu's Website

3 Reply by johnvanbreda

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

Re: need help

Hi Armand,

That's some query! It's a bit hard to work through every line, but here's an illustration of how I would get the "branch" for a taxon. It only goes up 4 levels to make it clearer, but it can easily be extended. Perhaps you could create a view for this to make the report code much cleaner?

SELECT ITG1.Item_Level,
    ISNULL(ITN4.Actual_Name + '\', '') +
    ISNULL(ITN3.Actual_Name + '\', '') +
    ISNULL(ITN2.Actual_Name + '\', '') +
    ITN1.Actual_Name
FROM Index_Taxon_Name ITN1
LEFT JOIN (Index_Taxon_Group ITG1 
    INNER JOIN Index_Taxon_Name ITN2 ON ITN2.Taxon_List_Item_Key=ITG1.Taxon_List_Item_Key
) ON ITG1.Contained_List_Item_Key=ITN1.Taxon_List_Item_Key AND ITG1.Item_Level=1
LEFT JOIN (Index_Taxon_Group ITG2
    INNER JOIN Index_Taxon_Name ITN3 ON ITN3.Taxon_List_Item_Key=ITG2.Taxon_List_Item_Key
) ON ITG2.Contained_List_Item_Key=ITN1.Taxon_List_Item_Key AND ITG2.Item_Level=2
LEFT JOIN (Index_Taxon_Group ITG3
    INNER JOIN Index_Taxon_Name ITN4 ON ITN4.Taxon_List_Item_Key=ITG3.Taxon_List_Item_Key
) ON ITG3.Contained_List_Item_Key=ITN1.Taxon_List_Item_Key AND ITG3.Item_Level=3
WHERE ITN1.Taxon_List_Item_Key='***'

The sorting problem I think, unfortunately, is a problem in the way XML Reports are run in Recorder rather than within your XML file.

To export to KML, you need to include a spatial_ref, spatial_ref_system, lat and long column. Because your report renames the spatial_ref and spatial_ref_system column Recorder cannot detect that this is spatial data. Rather than renaming the columns in the SQL query, just use the caption of the <column> element to change the display caption.

Cheers

John van Breda
Biodiverse IT

johnvanbreda's Website

4 Reply by atu (edited by atu 21-01-2009 14:00:55)

  • atu
  • Member
  • Offline
  • From: Luxembourg
  • Registered: 06-10-2008
  • Posts: 26

Re: need help

Thanks John,
The taxon branch query works well and it's much cleaner. I created a view and now the query is even much cleaner. But performance :( I'm not a specialist in query optimizing but I think there is some effort to do with the whole query. I try to create a new table taxon_branch and schedule a stored procedure which regular update this table with taxon branch data. May the performance increase instead of using a view. What do you think?

Armand

atu's Website

5 Reply by johnvanbreda

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

Re: need help

Hi Armand

Making a table purely for optimising your queries is certainly not a bad idea. I think that as the taxon dictionary only changes occasionally, you will not have to update it very often so you could do it only when required rather than regularly scheduled.

As to optimising the queries, the biggest culprit is all the left joins. Here's an approach you could try which builds the branch into a temp table, avoiding left joins. I'm not sure if it is quicker though! The example will pull in everything in your DB which starts with 'b', in my case that seemed to run pretty quickly:

IF EXISTS(SELECT 1 FROM tempdb.dbo.sysobjects WHERE Name LIKE '#Branch_%' AND Type='U')
    DROP TABLE #Branch

SELECT Taxon_List_Item_Key, Taxon_List_Item_Key AS Original_TLI_Key, CAST(Preferred_Name AS VARCHAR(2000)) AS Branch
INTO #Branch
FROM Index_Taxon_Name
WHERE Actual_Name LIKE 'B%'

WHILE 1=1
BEGIN
    UPDATE B
    SET B.Taxon_List_Item_Key=ITG.Taxon_List_Item_Key,
        B.Branch = ITN.Preferred_Name + '\' + B.Branch
    FROM #Branch B
    INNER JOIN Index_Taxon_Group ITG 
        ON ITG.Contained_List_Item_Key=B.Taxon_List_Item_Key
        AND ITG.Taxon_List_Item_Key<>B.Taxon_List_Item_Key
    INNER JOIN Index_Taxon_Name ITN ON ITN.Taxon_List_Item_Key=ITG.Taxon_List_Item_Key

    IF @@ROWCOUNT=0
        BREAK
END

SELECT * FROM #Branch

Good luck!

John van Breda
Biodiverse IT

johnvanbreda's Website

6 Reply by atu (edited by atu 03-02-2009 07:51:32)

  • atu
  • Member
  • Offline
  • From: Luxembourg
  • Registered: 06-10-2008
  • Posts: 26

Re: need help

johnvanbreda wrote:

To export to KML, you need to include a spatial_ref, spatial_ref_system, lat and long column. Because your report renames the spatial_ref and spatial_ref_system column Recorder cannot detect that this is spatial data. Rather than renaming the columns in the SQL query, just use the caption of the <column> element to change the display caption.

I'm able to export to google earth kml file, since i included the spatial_ref, spatial_ref_system, lat and long column. But lat and long dont appear in the report. Why?

http://forums.nbn.org.uk/uploads.php?fi … d_5_km.xml

atu's Website