SQL Spatial - How to get Spatial data. (FREE Maps 'n Demographics)

  • Article

This post lists ways to get spatial maps, some of them are Free. If you are in a hurry jump to the last link, it is so cool. Then check out the Free Map sites.

Overview

There are 3 ways to get the spatial data for your application:-

  1. Buy it
  2. Download it - FREE
  3. Build it Yourself - FREE but costs time

I'd have liked to make this a comprehensive list of every source. But it would huge & I'd never finish it. So mainly I'll walk you thru a couple of options, some links I've found handy & then show you how to get started for Free. 

 

Comparing the options

1a. Buy it (Own the data)

For any large enterprise with a serious need for accurate data, this might be the cheaper than expensive employees wasting time with the "free" stuff. Typically it comes from a Professional Supplier. It is comprehensive & maintained with regular updates, often quarterly or as needed.

There are advantages in having all the map data "on-site". Also buying all your data up front may mean the costs are fixed, so easier to forecast & budget. And you can negotiate a price, perhaps discounted based on the number of users you license.

Note: Not all applications require accurate data. Sometimes, for example, thematic maps, you just need to convey the basic idea of the regions you are trying to represent. State boundaries can be "out by miles" & users will still recognise that area as a state. Occasionally this distortion improves readability eg: Moving Hawaii near California & making it much bigger, may make it easier to see how your Sales per population compare with other USA States. Free data could be good here.

Vendors who sell spatial data

Map Data Sciences (Aust & NZ)
ESRI
Pitney Bowes MapInfo

(Lots of others - I plan to update this post with other vendors in the future.)

1b. Buy it (View the data)

For many solutions Online Suppliers are preferable. They are often cheaper, as you only pay for what you've used. ie: if you occasionally zoom to very high detail (normally very expensive) you didn't need to buy the entire country at the resolution, on the off chance that someone might want to look at somewhere in high detail. You don't have to pay & maintain the systems to store & deliver the data.  It can deliver a richer visual experience by complimenting the maps with a vast library of satellite & aerial photography.

A big bonus is a low entry price. For small businesses & startup projects this means no up-front huge expense. Hopefully your costs grow in line with your revenue.

Vendors who sell online Shape data

Virtual Earth  To use Virtual Earth with SQL see Johannes Kebeck's excellent set of posts

Google also have an offering. It is not obvious to me how to extract data from it or to use it to display data from SQL. As a result I've not worked with it & can't comment on how easy/hard it is to combine with SQL Spatial. Someone at the SQL User group mentioned that Google put ads randomly on the map. When an ad for their map competitor kept displaying on their page, they decided to redevelop on Virtual Earth. I expect this would only happen on the Free version but I don't know for sure.

2. Download it. FREE

There is a ton of stuff available for free. Especially in USA. If all you want is countries &/or states so you can colour them, usually you can find it. The most common format is ESRI Shapefile. There are free utilities that will load Shapefiles into SQL Server 2008. I use Morten Nielsen's Shape2SQL. However I expect that most companies would be better suited by a more robust tool like Safe Software's FME which can convert data to/from any format SQL2008 included.

Free stuff is typically not maintained, but if all you need is a general concept of an area for business reporting, it is often good enough. Outside of USA, if you need real detail; suburbs, postcodes, land parcels & detailed street names, it is unlikely you'll find it for free.
   While I've not included links here you can often get highly detailed information from specialist geological sites for free. So if you want to know where the sandstone & granite layers are, these sites are for you.

Cool sites for Free Map data

Source Comment
gData This site rocks, Maps with a decent level of detail to most places of the world.
Centres for Disease Control & Prevention Also brilliant. Boundaries & State borders of most countries, in a modest amount of detail. Great for thematic maps. As a bonus has heaps of demographic data related to population.
Dundas Map Resources Page Brilliant site with lots of links to other sites.
US Census Bureau 2007 TIGER/Line Shapefiles More detail than most but only on USA
Geography Network (ESRI) Large network of global maps but with a strength on USA
   
GeoData.gov U.S. Maps & Data Another US based set of maps.
FloraMap Detailed maps & data about worlds Plants & other organisms

Sample Level of world detail from gData site.
Sample 1. Major Regions of Australian State of New South Wales
displayed in SQL Server Management Studio (SSMS)

gData_NSW

Sample 2. Same map zoomed in to show Major Local Council Regions in Sydney.

GData_Sydney

Cool sites for Free & Paid Demographic data

Often your data mining &/or mapping insight is enriched by considering additional demographic information. I've found an effective way to find this is to search "< your Country name> Census"

Source Comment
Australian Bureau of Statistics - Census Results form 1996, 2001 & 2006 Census
Statistics New Zealand 1996, 2001 & 2006 Census Data
Statistics Singapore It appears you need to pay for this. But I've not explored completely.

 

3. Build it Yourself - (FREE but costs time)

There are a variety of sources that let you capture &/or build your own data.

A) Encode from a GPS Device

I'll document this better in a future post. The concept is that you could have someone with a Mobile GPS (phone) visit all the locations of interest & slowly build up your sales regions or other areas of interest. This approach is so labour intensive & other easier methods exist so I doubt you'd do it. But it is also very valuable approach just to track your equipment, products & people. Then display that info on map. I've seen some very impressive govt & corporate apps where this is a key part of the business solution.

      Creating Applications that Utilize GPS

      GPS Intermediate Driver Reference

      Trimble's Tutorial on GPS & how it works

      MSN Direct "Send to GPS" API

B) Bulk GeoCode from Address Data

Many Data cleansing providers offer this capability. eg: DQM but you can also write your own utilities that use the MapPoint Web service to do it for you. See Precise Geocoding with MapPoint Web Service and Virtual Earth. 
You can use some of the utilities & sites listed below.

Bulk Geocode providers 

MapPoint Web Service. MapPoint has a Batch geocoder which lets you geocode in bulk for free, but you do need an account. Batch-geocoding can be accomplished either through a Customer Data Service (CDS) API or manually through the Virtual Earth Platform Customer Service Site (CSS). To test these services. You can sign-up for a free developer account here
USC (Uni Sth California) - Free Geocoding Appears to be limited to USA Addresses
Large list of Geocoding Software Heaps of tools listed in this index.
   
Geocoder.ca their web site also lets you Reverse geocode any US or Canadian latitude & longitude - for free. But at 1 point at a time you may need to be patient.

Or use functionality built into many of the professional Geospatial tools. Vendors like ESRI, MapInfo & Manifold

 

B) Encode from the Web

There are a number of map sites where you can get Long Lat info on the point you are looking at. Both Virtual Earth (3D mode) & Google Maps, display Long / Lat / Altitude of any point you hover over. But this would requires a lot of effort to build up any decent set of objects, one point at a time. Sure, easier than driving to each point & using your GPS. But still hard. So ...

My favourite "Create your own GIS Data" solution - This is cool!

Build your own very accurate regions, sales territories, geopolitical boundaries etc & export them directly into SQL 2008. IDV solution have made this brilliant tool

The idvSolutions Spatial wiki site is run by IDV Solutions as a demonstration of the powerful visual experiences that can be created with Silverlight. It uses Virtual Earth for its spatial capability.

It allows you to draw Points, Lines & Polygons anywhere in the world, at extremely high detail, then Export your regions to SQL 2008.

This could be very handy for small businesses to create their own sales territories & other fairly static regions. Then use them to build thematic maps of various parts of your business. Sure this could be labour intensive & thus expensive if you needed to create a lot of shapes. But for a small family business, there is always a school kid somewhere keen to help out.

I’m not sure how long they will continue to provide this free service. You can use it to map entire countries or tiny sections of a house.

NZMap

In this example I've created the region representing a local Shopping Complex, then included the closest Railway & rail stations.

1. Go to the idvSolutions site & see the Virtual Earth background.

IDVAerial1

2. Click "Create New"

3. Draw your Region, Path or Points of Interest on the map. Give each one a name & description.

IDVDrawing2

4. Label & Save Drawing. Draw in Map, Aerial or Hybrid View.

IDVDrawing3 IDVDrawing5

5. Click Export Drawing & Select format "SQL Server 2008"

IDVDrawing6

6. Run the TSQL it creates to insert the spatial objects you created.

IDVDrawing7

Thanks

Please give feedback. Is this type of info useful? Did it save you time? What was good, What could be better?, Notice any errors? What would you like me to cover? All thoughts, comments, suggestions welcome.

 

Technorati Tags: SQL Server,SQL Server Spatial,Spatial,Free,TSQL,Geo Data