question

David 2 1 avatar image
David 2 1 asked

How to Create a UK Geography Dimension

Hi there, Does anyone know of a good script that will create a Geography Dimension based on a UK postcode? TIA
sql-server-2014data-warehousegeographydimension
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Oleg avatar image
Oleg answered
I tried the file freely available for download at [FreeMapTools][1] site and it works just fine. Here is the [path to the zip file][2]. It is a big SQL script file (about 228 MB when unzipped) which inserts 1.74 million rows into the table named postcodelatlng, so I created the table with this name and 3 columns (post code, latitude and longitude). Because the file is so big, SSMS does not open it on my laptop. To cure this problem, I split the file into 10 chunks and then executed them all to insert the data into this table. In order not to even bother with opening the files by hand, I executed the scripts using the openrowset, like this: declare @sql nvarchar(max); select @sql = t.BulkColumn from openrowset ( bulk 'C:\Temp\ukpostcodesmssql_01.sql', single_clob ) t; exec sp_executesql @statement = @sql; Optionally, the CSV file may be downloaded instead, it is smaller and there are no problems with bulk inserting the data from it in just one scoop: bulk insert postcodelatlng from 'C:\Temp\ukpostcodes.csv' with (fieldterminator = ',', firstrow = 2); Once I executed all chunks, I had the table populated with 1.74 million rows. There are so many because they include both "OUT" and "IN". On the website they also have the smaller version of the data with just the "OUT" codes (2,971 rows). In any case, once the latitude and longitude info is available, it can be used to populate the column of geography data type via [geography::Point([Latitude], [Longitude], 4326)][3]. Hope this helps. Oleg [1]: https://www.freemaptools.com/download-uk-postcode-lat-lng.htm [2]: https://www.freemaptools.com/download/full-postcodes/ukpostcodesmssql.zip [3]: https://technet.microsoft.com/en-us/library/bb933811(v=sql.105).aspx
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Oleg firstly my apologies for the slow reply. I asked the question just before I left for the Amalfi coast for a short break and wasn't expecting such a prompt answer. Many thanks again for your invaluable insight. I'll give your suggestion a bash and let you know in due course.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.