Using the new version of Tweet-SQL you can consume data from the Twitter Search API. The data in Twitter Search is constantly updated with new tweets so anything you grab out of the API is near real-time. To perform a query with Tweet-SQL run the following T-SQL;

EXEC dbo.tweet_src_search 'MC Frontalot', null, null;

Data from Twitter can be dealt with as an xml resultset, regular resultsets and with output parameters.

Twitter Search Results in Tweet-SQL

The procedure supports the full range of optional parameters offered by the api. Here’s a few examples;

lang - Restricts tweets to the given language, given by an ISO 639-1 code.

Search for Tweets containing ‘Paris’ in the French language only.

EXEC dbo.tweet_src_search 'Paris', 'lang=fr', null;

French tweets containing Paris

rpp - The number of tweets to return per page, up to a max of 100.

Search for Tweets mentioning “Swine Flu” and return up to 100 results.

EXEC dbo.tweet_src_search 'swine flu', 'rpp=100', null;

Request a maximum number of rows to return with Tweet-SQL

geocode   - Returns tweets by users located within a given radius of the given latitude/longitude, where the user’s location is taken.

Search for my favourite pub in Farringdon by users within 10 miles of EC1N 8FH.

DECLARE @geocode VARCHAR(100) = 'geocode=51.521935,-0.106859,10mi';
-- Need to url encode the geocode
SET @geocode = dbo.tweet_fnc_urlEncode(@geocode);

EXEC dbo.tweet_src_search 'Castle EC1', @geocode, null;

Tweet-SQL geocode search

These, of course, can be chained together to procedure powerful Twitter searches. The example below will search for tweets containing ‘Fabric’, within 1 mile of EC1N 8FH, returning up to 100 results.

DECLARE @optional VARCHAR(100),
		@geocode VARCHAR(50) = 'geocode=51.521935,-0.106859,1mi';
-- Need to url encode the geocode
SET @geocode = dbo.tweet_fnc_urlEncode(@geocode);
-- Combine the rpp and geocode parameters in the optional variable
SET @optional = 'rpp=100&' + @geocode;

EXEC dbo.tweet_src_search 'Fabric', @optional, null;

Using Tweet-SQL you could;

  • Archive Tweets containing certain terms and perform analytics.
  • Monitor Twitter for mentions of certain terms.
  • Auto-follow people mentioning specific subjects.

Detailed examples to follow soon.