SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 bulk loading large amount of spatial data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Flowing Fount of Yak Knowledge

2886 Posts

Posted - 03/17/2013 :  00:31:02  Show Profile  Visit jezemine's Homepage  Reply with Quote
hi folks! been a while since I asked or answered a question! glad to be back! :)

I need advice on loading a largish amount of spatial data in to SQL 2008 R2.

I have 13605 individual tab-delimited text files with data like this:

43.00000	-115.00000	1082
43.00000	-114.99917	1062
43.00000	-114.99833	1054
43.00000	-114.99750	1065
43.00000	-114.99667	1083
43.00000	-114.99584	1092
43.00000	-114.99500	1090
43.00000	-114.99417	1089
43.00000	-114.99334	1085
43.00000	-114.99251	1081
43.00000	-114.99167	1067
43.00000	-114.99084	1067
43.00000	-114.99001	1073
43.00000	-114.98918	1077
43.00000	-114.98834	1077
43.00000	-114.98751	1056

each file has 1442401 rows in it. the columns are: Latitude, Longitude, ElevationMeters. Basically this is elevation data for the whole planet, at about 90m resolution in x-y.

So the total number of rows is 19.6 billion. btw if you want this data it's freely available from USGS. It came from a satellite radar survey called SRTM.

I want to load this all into a table using a geography column so I can index it and do spatial queries against it.

My question is about how to load this data most efficiently. My plan was to do this:

1. create two tables:

-- for importing the text files
create table Elevation_stage(Latitude float not null, Longitude float not null, ElevationMeters smallint not null)

-- for storing the final result
create table Elevation(Point geography not null)

2. using a batch script, import files one at a time using bulk insert, then import from stage to the "real" table using insert/select. the body of the loop over files would be something like this:

truncate table Elevation_stage

bulk insert Elevation_stage from '%file%' with (BATCHSIZE=100000,TABLOCK)

insert Elevation
select geography::STPointFromText('POINT('+convert(varchar,Longitude)+' '+convert(varchar,Latitude)+' '+convert(varchar,ElevationMeters)+')', 4326)
from Elevation_stage

The problem is the insert/select takes a relatively long time, about 50 sec per file. It's 2x faster if I use geography::Point() instead of geography::STPointFromText(), but then I can't bake in the elevation value into the point because the Point() constructor does not take Z as a param. It only takes X,Y.

It's extremely stupid that Point ctor doesn't take Z as an arg but that's the way it is. STPointFromText() takes it, Point() does not. Lame.

here's Point, only takes lat/lon: http://technet.microsoft.com/en-us/library/bb933811.aspx (side note: extra lame also that Point takes args in lat,lon order, which is opposite the order STPointFromText wants...)

So if I go the Point() route, I'd have to add an ElevationMeters column to my Elevation table, like this:

create table Elevation(Point geography not null, ElevationMeters smallint not null)

then I could do the insert/select like this which is 2x faster.

insert Elevation
select geography::Point(Latitude, Longitude, 4326), ElevationMeters
from Elevation_stage

If you are loading 10^10 rows, factors of 2 matter. :)

Right now I am leaning towards the Point() route. The only real downside is the table will be about 20% bigger on disk when it's fully loaded, due to having the extra ElevationMeters column.

Any advice much appreciated! Is there a better way to load this data?



Flowing Fount of Yak Knowledge

2886 Posts

Posted - 03/17/2013 :  01:54:18  Show Profile  Visit jezemine's Homepage  Reply with Quote
I created a bug on connect for the deficiency of the Point() ctor. vote for it if you care :)


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000