Archive for the ‘ SQL Server Spatial ’ Category

One more SQLSaturday done :) I love SQLSaturdays, it’s always a fun experience for me. Uhm, ok, maybe let me qualify that. It’s always a nervewracking AND fun experience for me – when I’m speaking. I just always want it to be worth people’s time to attend my presentations.

I think there was a good crowd at my session, and I’m quite happy to see they seemed to have fun too (I hope!) …
Btw I have a couple more presentations in the upcoming SQLSaturday#114 – on PowerShell, and on ETL Basics.

Here’s a couple mementos for me to remember this day by. Thank you to everyone who attended, and spent their Saturdays with us …

These are the files. If you have any questions about them, please feel free to contact me.
SQLSaturday108 Redmond – SSRS Beyond the Basics Presentation – Donabel Santos
SQLSaturday108 – Donabel Santos – RDL files

SQLSaturday108 – Donabel Santos – PowerShell Files
SQLSaturday108 – Donabel Santos – jquery point generator

VN:F [1.9.22_1171]
Rating: 10.0/10 (3 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Geocode Locations Using Google Maps v3 API and SSIS

I wanted to do a cool experiment on SSIS that I haven’t tried before. I am really interested in this whole mapping component on SSRS, and I know that most likely the biggest challenge of companies is how to geocode addresses that are already in their databases. So what I wanted to do was to try and retrieve lat/long information from public geocoding web services within SSIS.

Please note that the exercise below is purely for experimentation. Please read the restrictions of each of the geocoding services, and go with whichever is appropriate for your purpose.

Also this is a simplistic code to show you how to get the latitude/longitude information. On your own versions, test thoroughly and add the appropriate exception handlers.

Ok, let’s start.

Step 1 – Look for the appropriate geocoding service.

There are few publicly available ones. Some are limited in content, some are very restricted. Check out the summary here – http://en.wikipedia.org/wiki/Geocoding

For this experiment I am going to use Google Maps v3 API since it’s publicly available, and V3 doesn’t need the Maps API key anymore! Please make sure you read the restrictions of using the Google Maps v3 API before deciding to use it in your applications/systems.

Note that for v3, there are only 2 available output formats: json and xml. Previous version of the API supported other output formats like kml and csv. I am going to stick with XML. (I’ll leave JSON for the next experiment).

Here is an example URL and result: http://maps.googleapis.com/maps/api/geocode/xml?address=Pacific+West+Outlet,Gilroy,California,US&sensor=false

Try to study the structure, it will help later on with the parsing for latitude and longitude.

Step 2 – Prep your package

Create your SSIS package. Drop a data flow task, and add your source. In my case my source is an OLE DB source pointing to my SQL Server table that has address fields already.


Read the rest of this entry »

VN:F [1.9.22_1171]
Rating: 8.9/10 (15 votes cast)
VN:F [1.9.22_1171]
Rating: +4 (from 4 votes)

How to Import Shapefile (.shp) Spatial Data into SQL Server 2008

1. Get your spatial data ready. If you’re just starting to work with spatial data and don’t have a handy .shp file, you can visit some of these sites which provide publicly available shapefiles:
http://www.vdstech.com/map_data.htm
http://wwwn.cdc.gov/epiinfo/script/shapefiles.aspx
http://www.mapcruzin.com/download-free-arcgis-shapefiles.htm
http://www.census.gov/geo/www/cob/bdy_files.html

2. Download the SQL Server Spatial Tools from ArcGIS, which include the Shape2SQL tool.

Read the rest of this entry »

VN:F [1.9.22_1171]
Rating: 8.8/10 (15 votes cast)
VN:F [1.9.22_1171]
Rating: +2 (from 4 votes)
`