Saturday, December 18, 2010

SQL Server Full-Text-Search for Clinical Data

In a recent post, I talked about how SQL Server’s Full-Text-Search might be a rather simple solution to find health care data in progress notes and other “blobs” of semi-structured data. I decided to build a working prototype and this blog will discuss how it was constructed and what our next steps will be.

Fortunately, we have a transcription department so all of our notes are saved as ASCII text. However, for my personal health information at home, I’ve scanned the documents I received from my health providers and saved the images as TIFF format. SQL will use the Windows’ built-in TIFF OCR engine to create an index on the words included in these TIFF (or PDF) documents.

The first small challenge was that our notes are saved in RTF. I wanted to be able to display the notes on a web page and without requiring an ActiveX plug in to display the RTF. So how to convert the RTF to plain text?

I found the easiest (and free) way to do this conversion was to use the Rich-Text-Box that is included with .NET and Visual Studio. I feed the text as rtf into the control and then immediately read it out as text and the control handles the conversion.
RichTextBox rtb = new RichTextBox(); rtb.Rtf = my RTF from the feed; myDatabase = rtp.Text;
Our content still contained some HL7 markup that I needed to “fix” and I needed to change the carriage return and line feeds to their HTML equivalents, but this was all easy to do so that the final results looked very similar to the original text with the rtf.

I next built a single .html web page using jquery for the user interface that will collect the search terms and allow the searcher to limit the results by either Visit Id or MRN.


When they press the Search button, the application will make a web service call to the SQL Server and return a list of MRNs (hidden below), with the size of the Note, and the message time.  A user can hover over the link in the “View Note” column to see the first line of the note; or, press the link to get a dialog pop-up where the search term is highlighted.


So a single html page wired up to a SQL Server with Full Text Search enabled allows our clinicians to search now across over 26 million records.  And with the incredible performance of the Full-Text-Index, the results come back almost instantly.

Sunday, February 21, 2010

Hosting Domains with Dynamic DNS

The folks over at Google do a great job of allowing folks to create simple web sites and I have helped our local homeowner’s association deploy a web site there.  However, for some things such as web services, it is better to host on a server I control and for those I have a small web server running at home. 

I also like to be able to use Remote Desktop Protocol to “remote in” to my home network from work and need the IP address to do so.  Unfortunately, our Internet Provider can and will change the IP address periodically since it is assigned via DHCP and this prevents me from hosting web services or remoting in.

Enter Dynamic DNS or DDNS.  With a DDNS service, you can quickly change the IP address associated with your domain name(s) when your IP address changes at home. 

For several years, I had GoDaddy host my domains and used ZoneEdit for the DDNS.  This worked great but due to a number of issues recently with GoDaddy, I moved my domains over to NameCheap and I’ve been very impressed with them so far.  The cool thing is that NameCheap also provides for DDNS and so I now have everything managed in one location.

At the end of this post, I’ll provide links to the DDNS code I wrote for both ZoneEdit and NameCheap; but, let me first describe the functionality I wanted.
  • I wanted a light-weight service that runs in the background automatically.  To accomplish this, the code runs as a background Windows Service that automatically starts when Windows restarts.  The benefit to me is that when Windows Update automatically cycles my server and it is assigned a new IP address by my ISP on startup, the service will automatically update my DNS settings at NameCheap.
  • I didn’t want to hammer the DDNS server when the IP address didn’t change.  To meet this, I store the current IP address and only send the update requests when it changes.
  • I needed to exclude the private addresses assigned on my network.  To meet this goal, in the ZoneEdit code, I have the exclusion list in the config file.  For the newer code for NameCheap, I hard code the exclusions to all the non-routable private networks and also provide for the ability to exclude certain addresses.
  • I needed to be able to add or remove domains or sub-domains without recompiling the application.  Both apps use configuration files to add or remove target domains.
  • And I needed logging to see if I had problems.  Both tools write to the Windows event log.
The ZoneEdit Code with a compiled version is here.

The NameCheap Code with compiled version is here.

I recommend that you examine and compile the code yourself; but, if you want you can use the pre-compiled version you must agree to do so at your own risk!  If you do, you need to install the service by using the “InstallUtil” utility that ships with Microsoft .NET and I’ve included for convenience.

Hosting small web sites or services at home can work with DDNS and I’ve provided a couple of tools to help you do this; but, if you have more than a few visitors, I strongly recommend that you go with a professional hosting organization and not try to run this at home.