March 13, 2012 at 11:27 am
I am tasked with building a report to show sales per region in the UK, but do not know how to match my data with the shp file
I have only experimented with the US supplied maps up until now, so I am sure my task is easy.
I have a table of UK outer postcodes and their Latitude, Longitude and can link that to my sales table no problem to get meanigful stats.
The part of the query to convert the Lat,Lng is this
SELECT geography::STPointFromText('POINT('
+ CAST([LNG] as VARCHAR(10)) + ' ' + CAST([LAT] AS VARCHAR(10))
+ ')', 4326) AS GEODATA
where LNG and LAT are my fields.
For example in SSMS for postcode AL10 this gives me a GEODATA value of 0xE6100000010CEE5A423EE8E1494053245F09A4C4CEBF
When using the map wizard, each county has a unique number per county and a shape_length and shape_area field, so I cannot see how to map my GEODATA field (SSRS shows as POINT(-2.12248 57.1311) to the spatial dataset fields
Also, I guess another option (maybe the right one) would be to use extract the unique no. per county in the shp file such as 13938 for Bedfordshire into a table, so I can match on county, but I am not sure how to do that, and I should be able to use the lat/lng points somehow surely.
Thanks
March 14, 2012 at 4:24 am
can you zip and attach your shp and dbf files as I have only ever done maps by embedding the geo data as a spatial query passing a bing map underlayer for the actual map
also can you supply some lats and longs for a few points, as -2.12248 and 57.1311 come out as somewhere in Scotland. If we could have some for multiple points around the UK can get some sample points on the map
March 14, 2012 at 8:16 am
Hi Anthony,
The list of postcodes and their lat/long I got from http://www.freemaptools.com/download-uk-postcode-lat-lng.htm
The shp files I got from http://www.diva-gis.org/
I have now used Shape2SQL to convert the shp file to a table, and then perform a join to total per county name of the sales document against the county name in the shp/sql table which does seem to answer 1 of my 2 issues.
The other being how do I actually plot the total sales per postcode onto the map, with or without bing maps involvment.
Thanks
March 14, 2012 at 8:22 am
if you have to number of sales in the same row as the geo data in the data set, then at the point layer properties, you can specify a label text column, passing in the number here will but the pointer with the number at the point on the map
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply