Downloading the EU

… or parts of it anyway.

The European Union is generally up to lots of weird and wonderful things, one of the more esoteric being the “Tenders Electronic Daily” (TED) database. Basically, all public procurement above a certain value has to go through this database so that companies from all over the world have a fair chance of winning the contracts. Once a tender is won, the names and addresses of the winning companies are also posted. This, in the hope that Europeans will get more roads for their tax-euros and that less money disappear into the mayors cousins pocket.

The database is hosted at http://ted.europa.eu/. It’s publicly available, but you can’t obtain the data in bulk unless you pay a lot of money. This post describes how data can be scraped en masse through the web interface. I’ve written it partly to brag, partly to help you out on the off chance that you someday need to analyse these contracts (in which case I sincerely hope you stumble on this post).

A typical won tender looks like this (you need to be logged in, registration is free):
http://ted.europa.eu/Exec?DataFlow=N_list_results.dfl&Template=TED/N_result_details_data.htm&Page=1&docnumber=2005238380&StatLang=EN

http://ted.europa.eu/Exec?DataFlow=N_list_results.dfl&Template=TED/N_result_details_curr.htm&Page=1&docnumber=2005238380&StatLang=EN

Notice that there are two interesting pages per contract, one has structured data, the other has unstructured text (the “Document family” stuff is also pretty interesting, but we won’t go into that here). Also note the “docnumber” url parameter. It actually turns out to be composed of two parts, the year (2005 for this particular contract) and an integer (238380). The integer part starts in January from 000001 and is then incremented as contracts accrue through the year. There are very few holes in this sequence, although it seems a few documents are redacted each year. The problem now looks as simple as determining the maximum docnumber for each year and wade through all of them, downloading both data and text parts, right?

The login-requirement will foil you unfortunately — to get at a contract more than a few weeks old, you need to be logged in. Being considered as “logged in” by TED turns out to entail posting a valid JSESSIONID cookie. The browser obviously accomplishes this automatically when you interact with the web site, but replicating it in a scraper proved to be extraordinarily difficult — in fact, I spent the better part of a week looking at WireShark traces before I managed to reverse engineer the process. There are three steps:

  1. Post username and password to http://ted.europa.eu/eLogin
  2. Simulate a search by posting to http://ted.europa.eu/ExecSessionVariables
  3. Simulate browsing the search result by getting http://ted.europa.eu/Exec?DataFlow=ShowPage.dfl&TableName=TED_EN&Template=TED/N_result_list.htm&Page=1&toconf=yes

Any sane login process would obviously stop after the first step, but for some reason you have to built state for your session variable on the server before you are allowed to retrieve random documents. The exact nature of the search being simulated is completely irrelevant, I just use a giant string mined from a random WireShark trace. The web site is built on some arcane Java stack — the convolutedness of which must be pretty amazing (posting incomplete requests will net you a stack trace that also reveals the supplier, who shall go unnamed here). Below, I’ve posted a C# class that will do the right thing. Note the gotcha that HttpWebRequest will not absorb cookies received during posts, so you have to record these yourself.

Once you can get valid session ids, getting all the data is pretty straight forward, although it will probably take a while. I highly recommend requesting gzipped data from the EU server (by including “Accept-Encoding” – “gzip” in the header) as it will drastically cut down on bandwidth usage. You can safely have multiple scrapers hammering the website concurrently, I found 10 to be a good number. Note that the servers are apparently rebooted around midnight CET each day during which time requests tend to fail.

Remember that you can browse the contract information on Google Maps here: http://tedbot.itu.dk/

And the code:

using System;
using System.Text;
using System.Net;
using System.IO;
using System.Net.Security;
using System.Security.Principal;

namespace TED.Scraper
{
    class Authenticator
    {
        static string userid = "username";
        static string password = "password";

        public enum MethodType { Get, Post };

        static string extSearchq = @"Name=statisticMode%40fulltext_textfield"+
            "%40OJ_textfield%40country_textfield"+
            "%40place_textfield%40contract_textfield"+
            "%40procedure_textfield%40document_textfield"+
            "%40regulation_textfield%40CPV_textfield%40NUTS_textfield"+
            "%40publication_textfield%40docnumber_textfield%40datedoc_textfield"+
            "%40deadline_textfield%40type_author_textfield%40name_author_textfield"+
            "%40heading_textfield%40activity_textfield%40fulltext_textfield_hid"+
            "%40OJ_textfield_hid%40country_textfield_hid%40place_textfield_hid"+
            "%40contract_textfield_hid%40procedure_textfield_hid%40document_textfield_hid"+
            "%40regulation_textfield_hid%40CPV_textfield_hid%40NUTS_textfield_hid"+
            "%40publication_textfield_hid%40docnumber_textfield_hid%40datedoc_textfield_hid"+
            "%40deadline_textfield_hid%40type_author_textfield_hid%40name_author_textfield_hid"+
            "%40heading_textfield_hid%40activity_textfield_hid%40docLang%40maxRow%40SelRetrieval" +
            "%40FTIndex%40SearchFrom%40ExpertQry%40op1%40op2%40Query%40ErrorMes%40AdviceMes&Value=No" +
            "%40null%40null%40null%40null%40null%40null%40null%40null%40null%40null%40null%40null%40null" +
            "%40null%40null%40null%40null%40null%40null%40null%40null%40null%40null%40null%40null%40null" +
            "%40null%40null%40null%40null%40null%40null%40null%40null%40null%40null%40EN%4025%40OJ%2CND%2CTI" +
            "%40TEDINDEX_ARCHIVE2%40extended%40null%40AND%40AND%40cs_type%3Adb+%40null" +
            "%40null&Language=EN&Redirect=Exec%3FDataFlow%3DShowPage.dfl%26TableName%3DTED_EN" +
            "%26Template%3DTED%2FN_result_list.htm%26Page%3D1%26toconf" +
            "%3Dyes&ErrorMes=null&AdviceMes=null&RedirectError=Exec" +
            "%3FDataFlow%3DShowPage.dfl%26Template%3DTED%2Fextended_search%26BANNER%3DEXT";

        public static string GetAuthenticatedSessionId()
        {
            CookieContainer cc = new CookieContainer();

            DoHttpRequest("http://ted.europa.eu/eLogin",
                MethodType.Post,
                true,
                String.Format("USERID={0}&PASSWORD={1}", userid, password),
                cc);

            DoHttpRequest("http://ted.europa.eu/ExecSessionVariables",
                            MethodType.Post,
                            true,
                            extSearchq,
                            cc);

            DoHttpRequest("http://ted.europa.eu/Exec?" +
                "DataFlow=ShowPage.dfl&TableName=TED_EN&Template=TED/N_result_list.htm&Page=1&toconf=yes",
                MethodType.Get,
                false,
                null,
                cc);

            string sessionid = cc.GetCookies(new Uri("http://ted.europa.eu"))["JSESSIONID"].Value;
            return sessionid;
        }

        private static void DoHttpRequest(string url, MethodType mtype,
            bool isFormEncoded, string requestString, CookieContainer cc)
        {
            HttpWebRequest req = (HttpWebRequest)WebRequest.Create(url);
            req.Method = mtype.ToString();
            req.CookieContainer = cc;

            req.AllowAutoRedirect = false;
            req.AllowWriteStreamBuffering = true;
            req.AuthenticationLevel = AuthenticationLevel.None;
            req.ImpersonationLevel = TokenImpersonationLevel.None;
            req.UserAgent =
                "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB; rv:1.9b4) Gecko/2008030714 Firefox/3.0b4";

            req.Proxy = null;

            if (isFormEncoded)
            {
                req.ContentType = "application/x-www-form-urlencoded";
                req.Expect = null;

                byte[] reqData = Encoding.UTF8.GetBytes(requestString);
                req.ContentLength = reqData.Length;
                Stream reqStream = req.GetRequestStream();
                reqStream.Write(reqData, 0, reqData.Length);
                reqStream.Close();
            }

            try
            {
                HttpWebResponse response = (HttpWebResponse)req.GetResponse();
                if (isFormEncoded)
                {
                    // seems that cookies received in post are not saved to collection
                    req.CookieContainer.Add(new Uri("http://ted.europa.eu"), response.Cookies);
                }

                response.Close();
            }
            catch (Exception e)
            {
                throw;
            }
        }

        private static void AddRequestString(HttpWebRequest request, string requeststring)
        {
            byte[] reqData = Encoding.UTF8.GetBytes(requeststring);
            request.ContentLength = reqData.Length;
            Stream reqStream = request.GetRequestStream();
            reqStream.Write(reqData, 0, reqData.Length);
            reqStream.Close();
        }
    }
}

Smuggling exceptions out of try-catches with IEnumerable

The code in this post is from a talk by Erik Meijer, I claim no credit.

Take a look at this screenshot:

Exception thrown from try-catch

Pretty wacky huh? Visual Studio has broken into debug mode even though the division is clearly wrapped in a try-catch. Take a guess at what’s going on before reading on.

Here’s the full code listing:

static void Main(string[] args)
{
    var xs = new[] { 1, 2, 3, 0 };
    IEnumerable q = null;

    try
    {
        q = from x in xs select 1 / x;
    }
    catch { }

    foreach (var z in q)
    {
        Console.WriteLine(z);
    }
}

The obvious expectation is that division-by-zero will be caught by the try-catch. Alas, the division does not happen in the scope of the try statement. Instead, due to the deferred execution paradigm of IEnumerable, it happens on a by-need basis in the for-loop. From there the exception can propogate unopposed to the top af the stack. Haskell, where lazy evaluation dominates, avoids exceptions (and other messy side effects) alltogether, partly for reasons demonstrated by this example.

UPDATE: As Wayne points out, fixing this is as easy as extending the try to cover the enumeration. The point of this post was mainly to poke fun at the Visual Studio debugger and to point out that lazy evaluation can bite you if you’re not careful.

CRMMetal and LINQtoCRM

As you may know, Microsoft introduced many-to-many relationships in version 4.0 of Dynamics CRM. Unfortunately, querying these relationships is not supported through the general web service entities as the intermediary entities are not exposed. Fetch XML works fine however (and don’t forget this great hack to generate FetchXML). The missing entities meant that LINQtoCRM didn’t support many-to-many relationships either, even though the underlying query-engine generates Fetch XML.

To remedy this deficiency, a simple tool is now bundled with experimental versions of LINQtoCRM. In the tradition of the other major query providers, it’s called “CRMMetal”. It works by asking the metadata web service for all the metadata, including relationships, and then filtering out the many-to-many ones:

RetrieveAllEntitiesRequest allEntitiesRequest = new RetrieveAllEntitiesRequest();
allEntitiesRequest.RetrieveAsIfPublished = false;
allEntitiesRequest.MetadataItems = MetadataItems.IncludeRelationships;

RetrieveAllEntitiesResponse allEntitiesResponse = 
	(RetrieveAllEntitiesResponse)service.Execute(allEntitiesRequest);

var mtom = allEntitiesResponse.CrmMetadata.OfType().
	SelectMany(e => e.ManyToManyRelationships, (e, d) =>
		new
		{
			intersectname = d.IntersectEntityName,
			schemaname = d.SchemaName.Replace("_association",""),
			ent1Name = d.Entity1LogicalName,
			ent2Name = d.Entity2LogicalName,
			ent1Att = d.Entity1IntersectAttribute,
			ent2Att = d.Entity2IntersectAttribute
		}
		).Distinct().OrderBy(s => s.schemaname);

It then uses the CodeDOM API to generate classes similar to the web service ones, although the property bodies are empty and there’s no XML serialization attributes. It seems CodeDOM has not been updated with recent .Net releases: You can’t generate automatic properties and the API doesn’t support declarative composition of code, a great shame I think. Other than that, generating the code is pretty straightforward:

CodeCompileUnit targetUnit = new CodeCompileUnit();
string nameSpace = "LinqtoCRMApplication.CRM";

CodeNamespace ns = new CodeNamespace(nameSpace);
ns.Imports.Add(new CodeNamespaceImport("System"));

// The stupid codedom API doesn't properly support declarative DOM building, fail.
CodeTypeDeclarationCollection classes = new CodeTypeDeclarationCollection(
	mtom.Select(_ =>
		new CodeTypeDeclaration()
		{
			//Name = _.name,
			Name = _.schemaname,
			IsClass = true,
			IsPartial = true,
		}
	)
	.ToArray());

foreach (CodeTypeDeclaration c in classes)
{
	int count = mtom.Where(_ => _.schemaname == c.Name).Count();
	if (count > 1)
	{
		Console.WriteLine("Ignoring {0} due to duplicality", c.Name);
		continue; // bad one, multiple with same name
	}
	
	c.Members.AddRange(new CodeTypeMember[]
		{
			new CodeMemberProperty() 
			{ 
				Name = mtom.Single(_ => _.schemaname == c.Name).ent1Att,
				Type = new CodeTypeReference(nameSpace + ".Key"),
				HasGet = true,
				HasSet = true,
				Attributes = MemberAttributes.Public,
			},
			new CodeMemberProperty() 
			{ 
				Name = mtom.Single(_ => _.schemaname == c.Name).ent2Att,
				Type = new CodeTypeReference(nameSpace + ".Key"),
				HasGet = true,
				HasSet = true,
				Attributes = MemberAttributes.Public,
				
			}
		}
	);

	// goddam codedom doesn't support automatic properties, 
	// have to add something to getters. fail.
	foreach (CodeMemberProperty p in c.Members)
	{
		// just have it return null
		p.GetStatements.Add(
			new CodeMethodReturnStatement(
				new CodePrimitiveExpression(null)));
	}
}

ns.Types.AddRange(classes);
targetUnit.Namespaces.Add(ns);

CodeDomProvider provider = CodeDomProvider.CreateProvider("CSharp");
CodeGeneratorOptions options = new CodeGeneratorOptions();
options.BracingStyle = "C";
using (StreamWriter sourceWriter = new StreamWriter("ManyToManyClasses.cs"))
{
	provider.GenerateCodeFromCompileUnit(
		targetUnit, sourceWriter, options);
}

Go get the code and give it a whirl. LINQtoCRM still has a few large wharts, the ugliest probably being the very limited selectors permitted. I’ll try to lift the selector implementation from LINQtoSharePoint soon.

Showing maps and borders in Processing

A few days ago, I posted a video showing public procurement expanding geopraphically with the EU enlargements in the ’00s. There weren’t any borders, but you could sort of see the outline of Europe and how the dots spread east with time.

Adding actual borders to the map proved very frustrating. The Geographical Information System (GIS) space seems plagued by obtuse binary formats, stodgy desktop applications and a profusion of coordinate systems. I tried many avenues, one of the more promising being a smoothed map from MapShaper passed through TatukGIS and exported to KML. The coordinates from MapShaper turned out to be incompatible with the latitude/longitude ones I had from the Google Maps web services however.

After much searching, I found a KML-file in the Google Maps group (worldcountriesKML.zip) with the borders of all the worlds countries. It’s not smoothed in any way, so the haggard coastline of a country like Norway looks too thick when zoomed out. The result is better than the original though:

I implemented a simple Processing helper-class to parse and draw the KML. You instantiate it like this: helper = new XMLHelper(new XMLElement(this, "world.kml"));. It has a Init() method that takes a String[] of the countries you need and String denoting the XML path to the line coordinates, e.g. "Polygon/outerBoundaryIs/LinearRing/coordinates". After initialization you can ask it for its maximum and minimum coordinates using min_x, max_x, min_y, max_y. The Draw() method draws the map on the sceen, scaled to fit the size.

class XMLHelper
{
  float coordscale = 1;
  XMLElement borders;
  Line[] lines = new Line[0];
  public float max_y =0, min_y = 70, max_x = 0, min_x = 0;
  
  public XMLHelper(XMLElement xe)
  {
    borders = xe;
  }
  
  public void Init(String[] wantedcountries, String coordpath)
  {
    XMLElement[] filecountries = borders.getChildren("Folder/Placemark");
    for(int i = 0; i 

Processing and SQL server on Windows

Tim Regan has a comprehensive description of how he got SQL Server 2008 running with processing. I found some steps to be superfluous while others were missing (it was still an invaluable guide though), here’s how I did it:

  1. Get the SQL Server JDBC driver, be sure to get the Windows version
  2. Unpack it somewhere and copy sqljdbc.jar to libraries\sqljdbc\library in your Processing folder
  3. Copy qljdbc_auth.dll from the enu\auth\x86 folder of jdbc to C:\Windows\System32
  4. In your sketch, do Sketch->Import Library->sqljdbc
  5. You can now use the helper class from Tim’s post (which is inspired by the one in Ben Fry’s book)

Note that it is apparantly not necessary to muck around with the CLASSPATH. Happy querying!

Video of procuring authorities in the EU

I did a video showing the geographical spread over time of authorities buying stuff through the EU public procurement system. We managed to hijack all the contracts some time ago and the addresses of the authorities and the winning contractors have now all been geocoded. You can also explore the data on the TEDBot website.

The animation starts in january 2003 and ends at the end of 2007. You can actually see the EU expansion happening in May 2004 with dots spreading east. 10 14 day intervals are shown each second. The animation was generated with Processing.