Search results matching tags 't-sql tuesday', 'SQL Server 2008', and 'programming'

T-SQL Tuesday #006: Tiger/Line Spatial Data

Mike C — Tue, 11 May 2010 23:11:00 GMT

This month’s T-SQL Tuesday post is about LOB data http://sqlblog.com/blogs/michael_coles/archive/2010/05/03/t-sql-tuesday-006-what-about-blob.aspx.

For this one I decided to post a sample Tiger/Line SQL database I use all the time in live demos. For those who aren't familiar with it, Tiger/Line data is a dataset published by the U.S. Census Bureau. Tiger/Line has a lot of nice detailed geospatial data down to a very detailed level. It actually goes from the U.S. state level all the way down to street, feature and landmark level. Tiger/Line data is very complete and detailed--but the best part is it's FREE. There are lots of applications for Tiger/Line, like national [U.S.] and local mapping and geocoding applications [applications that convert street addresses to (latitude, longitude) coordinates]. All this great data is distributed in the form of a ton of ESRI shapefiles.

A shapefile is basically a file format that contains shape objects like points, lines and polygons. SQL Server doesn’t natively understand ESRI shapefiles, but it also stores geospatial objects like lines, points and polygons. There some handy utilities out there for loading these files into SQL Server. Morten Nielsen has a great utility for loading shapefiles into Geometry and Geography data types at http://www.sharpgis.net/page/Shape2SQL.aspx. Because of the volume of data involved in this project (I loaded hundreds of shapefiles) I decided to roll my own small set of SSIS custom components that read ESRI shapefiles and convert them to SQL Server spatial data types [keep an eye out -- these components are scheduled to be published with source code by SQL Server Standard magazine in the near future].

The sample database can be downloaded from http://www.sqlkings.com/downloads/Tiger_Sample.zip

Download the ZIP file from http://www.sqlkings.com/downloads/Tiger_Sample.zip.
Extract the database backup file and restore it to a SQL Server 2008 instance.

In the future I’ll be sharing some code samples on the blog to demonstrate Tiger/Line data (as well as spatial data from other sources) based on this database.

Here are a couple of quick queries you can run against this sample database to view the spatial data in SSMS 2008.

SELECT *

FROM Tiger.State;

SELECT c.*

FROM Tiger.State s

INNER JOIN Tiger.County c

ON s.STATEFP = c.STATEFP

WHERE s.STUSPS = 'TX';

SELECT *

FROM Tiger.ZCTA z

WHERE z.ZCTA5CE00 LIKE '0[7-8]%';

This last one works because all of the ZIP Code tabulation areas for the state of New Jersey start with '07' and '08'. There are similar relationships between other ZCTA prefixes and their states.

Next time we'll look at using SQL Server-based spatial data with online mapping programs like Bing maps.

*For more information about Tiger/Line data visit http://www.census.gov/geo/www/tiger/tgrshp2009/tgrshp2009.html.

T-SQL Tuesday #006: "What About BLOB?"

Mike C — Mon, 03 May 2010 23:29:00 GMT

Invitation for T-SQL Tuesday #006: "What About BLOB?"

It's getting warm outside just in time for the May T-SQL Tuesday blog party. I’ll be your host this month--and the secret word for this T-SQL Tuesday is "Large Object (LOB) Data".

What’s T-SQL Tuesday?

About 6 months ago Adam Machanic (Twitter: @AdamMachanic) decided to throw a worldwide blog party. Every month Adam picks a host to post the topic and the rules. Everyone who wants to participate publishes a blog entry on the topic of the day, all during the same 24 hour time period.

Here’s the shortlist of the roundups from the first 5 T-SQL Tuesdays:

#001 Adam Machanic asked for your favorite Date/Time tricks
#002 Adam followed up by asking for your favorite Puzzling Situations
#003 Rob Farley made the commitment with his topic: Relationships
#004 Mike Walsh kicked asked for your input on IO
#005 Aaron Nelson asked us to report on "Reporting"

And that brings us up-to-date for #006: LOB Data. Anyone can participate - just post a blog entry related to the topic and link it back to this page.

To participate your post must go live between 00:00:00 GMT on Tuesday the 11th of May and 00:00:00 GMT on Wednesday the 12th. See the table below for some quick conversions to other time zones.

Time Zone	Start	End
GMT	2010-05-11 00:00:00	2010-05-12 00:00:00
EST	2010-05-10 20:00:00	2010-05-11 20:00:00
PST	2010-05-10 17:00:00	2010-05-11 17:00:00

What Is LOB Data?

MSDN conveniently defines Large Object ("LOB") data types for us: "LOB data types are those that exceed the maximum row size of 8 kilobytes (KB)."

There have been a several improvements in LOB data functionality in SQL Server 2008 (there were even some in SQL Server 2005). In 2008 the XML, GEOMETRY, GEOGRAPHY data types can all hold 2.1 GB of data. CLR data types can also hold up to 2.1 GB of data. So the question of the day is how do you use LOB data? Here are a few possible starting points:

LOB data storage, optimization, limitations, "under-the-hood"
Indexing, querying, optimization, tricks, tips, performance tuning of LOB data
Interesting uses/projects for LOB data types:

The MAX data types (VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX))
XML
GEOMETRY/GEOGRAPHY (spatial)
CLR data types

FILESTREAM hints, tips, tricks, .NET SqlFileStream Class

The only rule is that your topic has to involve SQL Server's LOB data types in some form. If you want to demonstrate handling LOB data in .NET, for instance, go for it. If you want to demonstrate Oracle LOB data handling, this might not be the place to do it (although a comparison of the two might be interesting...) :)

Here Come the Lawyers...

Once again, please note the time is in GMT (aka UTC). As other hosts have mentioned, feel free to write your post ahead of time and schedule it for publication. The rules are based on when the post goes live, not when it is written. The rules for T-SQL Tuesday haven’t changed, but I’ll cover them again here:

Your post must go live between 00:00:00 GMT on Tuesday the 11th of May and 00:00:00 GMT on Wednesday the 12th (see my handy-dandy time zone conversion table above for more info). If your post doesn’t go live in the time limits, it won’t be included in the round-up post.
Your post must link back to this post (by trackback or comment).
"T-SQL Tuesday #006" MUST be in the title of the post.
It is your responsibility to verify the trackback or comment appears here on this post. If you don’t see your trackback add your own comment with a link back to your T-SQL Tuesday post and it will be included in the roundup.

Note that some bloggers have reported an issue that their blog sites don’t properly generate a trackback when they preschedule a post for publication. After you publish your post, be sure to check here to see if a trackback was generated. Feel free to post a comment here after your post goes live if you just want to play it safe.

Twitter

A lot of the T-SQL Tuesday bloggers and bloggees are on Twitter. Follow the hashtag #TSQL2sDay and when your post goes live, tweet a link to it with that tag.

Want To Host?

It's easy to become a host (hey, even I did it!):

1. Participate in at least two events.

2. Let Adam Machanic know you want to host.

You can tweet Adam or leave a comment on his blog, as described in his first T-SQL Tuesday Invitation.

If you have any questions, concerns or general comments, leave me a message on this post. I'll respond to your post and/or make updates to this post as required. Have fun thinking of a topic and I’m looking forward to all the great posts!

Sponsors

This episode of T-SQL Tuesday was brought to you by the letters "M", "A", "X", and the number 2,147,483,647.