1 Reply by brianmiller

  • brianmiller
  • Veteran
  • Offline
  • Registered: 10-05-2006
  • Posts: 113

Re: Snapshot tool and ArcGIS

Along similar lines to my previous post I have had problems within ArcGIS saving the Event layer created from the OLE connection to my snapshot database and table. In ArcGIS it would not let me create a feature class from the linked table event layer. After posting the question on the ESRI user forum I was able to pin it down to the fact that my Observation Comments field was not in the correct format. Whether it was not in the right format in SQL or after the linking I am not sure. In the SQL table it was listed as a TEXT field. All other 'Text like' fields were listed as VARCHAR.

When linking to the GIS all the VARCHAR fields were then TEXT fields and the TEXT field (Observation Comments) was a 'BLOB' field. All comments were replaced with the word BLOB. Not very helpful!

After changing the TEXT field in my snapshot table in SQL server to VARCHAR I was able to export the layer to a shapefile in ArcMap and create a feature class from ArcCatalog.

So, is the problem down to the way Recorder has set the Obs Comment field to TEXT (same I assume with the Survey Event Comment) in SQL whilst all others are VARCHAR (or CHAR)? If so is this something that can be changed easily please to help resolve the problem?

The problem is if I can not create a feature class from the layer I can not query the points either by their attributes or spatially.

Has anyone else encountered this problem?

Cheers

Brian

Brian Miller
(Conservation Officer (Buckinghamshire), BBOWT)

brianmiller's Website

2 Reply by charlesroper

  • charlesroper
  • charlesroper
  • Digital Development Manager
  • Offline
  • From: Field Studies Council
  • Registered: 26-03-2006
  • Posts: 906

Re: Snapshot tool and ArcGIS

The TAXON_OCCURRENCE.COMMENT field being set to the TEXT data type could prove to be problematic in future according to this snippet of information I found in books online:

ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. For more information, see Using Large-Value Data Types.

I have had problems with the comment fields before now. I can't remember what they were, but I solved them by CASTing to VARCHAR. Regarding the use of snapshots, I don't ever link the snapshot table(s) to the GIS but instead write a query that massages the data into its final, GIS presentable state. Although it may sound like an irritating extra step, the simplified data model makes the querying a pleasure and makes it much faster too. If you turn your query into a View, then you'll be able to access it from ArcGIS just as if it were a table. Using Views like this is really powerful as it allows you to present the data you bring into GIS in a variety of ways and when the underlying data changes (i.e you run a snapshot update), so does the data in the views.

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

charlesroper's Website

3 Reply by brianmiller

  • brianmiller
  • Veteran
  • Offline
  • Registered: 10-05-2006
  • Posts: 113

Re: Snapshot tool and ArcGIS

It is not just the TAXON_OCCURRENCE.COMMENT field that is set to TEXT, it is the other Comment fields too.

I too have managed to successfully create a view of some data to present in ArcGIS, changing the dates from INT to DATE formats. I also managed to CAST the Obs Comment field to a VARCHAR (8000), although it would have been nicer to use something else perhaps(?) The reason I made it 8000 was because it has those horrible rtf tags, which my little knowledge in SQL coding does not allow me to remove. I looked at the code Ken Thompson produced but again have no idea how to use it and fear it would not work in SQL in anycase. Any ideas?

The problem I have with all this comes back to other staff undertaking the use of the Snapshot tool. They will not want to go in and make these additional views, let alone the fact that they do not have permissions to do so on the SQL server (our membership database is stored on the same server as Recorder 6). Which would leave it down to me to create everyones snapshot as and when they create them... which would not be feasible.

Apart from these two issues (currently found) its a great tool.

Brian

Brian Miller
(Conservation Officer (Buckinghamshire), BBOWT)

brianmiller's Website

4 Reply by johnvanbreda

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

Re: Snapshot tool and ArcGIS

The Snapshot uses the Text datatype since that is SQL Server 2000's only method of declaring an unlimited length string.  This data type is deprecated in SQL 2005 and replaced by the max datatypes, but unfortunately we're still stuck with SQL 2000 for the majority of users.

There is a built in Recorder function that will strip the RTF for you:
dbo.ufn_RtfToPlaintext(fieldname)

A future development idea might be to autocreate views for you with a certain structure?

John van Breda
Biodiverse IT

johnvanbreda's Website

5 Reply by brianmiller

  • brianmiller
  • Veteran
  • Offline
  • Registered: 10-05-2006
  • Posts: 113

Re: Snapshot tool and ArcGIS

johnvanbreda wrote:

A future development idea might be to autocreate views for you with a certain structure?

Sounds like a good idea. You could have it so you pick the available fields you want like in the Report Wizard perhaps.

Cheers

Brian

Brian Miller
(Conservation Officer (Buckinghamshire), BBOWT)

brianmiller's Website

6 Reply by charlesroper

  • charlesroper
  • charlesroper
  • Digital Development Manager
  • Offline
  • From: Field Studies Council
  • Registered: 26-03-2006
  • Posts: 906

Re: Snapshot tool and ArcGIS

Or you could base it on the report wizard filter results, i.e. the auto-created view would mimic what you see at the end of the report wizard?

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

charlesroper's Website

7 Reply by johnvanbreda

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

Re: Snapshot tool and ArcGIS

What I meant was that the views would be created alongside the snapshot and would be views of the snapshot data. The views would contain DateTime (or null if not possible) values instead of vague date integers. We could also strip out RTF formatting on any text fields, and convert Text to VarChar(8000). This way you get the best of both worlds - the raw data in the snapshot and the views giving you massaged data that is even simpler to use.

John van Breda
Biodiverse IT

johnvanbreda's Website

8 Reply by charlesroper

  • charlesroper
  • charlesroper
  • Digital Development Manager
  • Offline
  • From: Field Studies Council
  • Registered: 26-03-2006
  • Posts: 906

Re: Snapshot tool and ArcGIS

It sounds very useful, John, and I'm all for it.

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

charlesroper's Website

9 Reply by brianmiller

  • brianmiller
  • Veteran
  • Offline
  • Registered: 10-05-2006
  • Posts: 113

Re: Snapshot tool and ArcGIS

So too am I all for this functionality, sounds good.

Brian

Brian Miller
(Conservation Officer (Buckinghamshire), BBOWT)

brianmiller's Website