locked
Spatial questions from a spatial beginner! RRS feed

  • Question

  • Hi all

    I am just starting with my first mapping report in SQL 2008 R2 Express Advanced Services. I have a list of UK sites with postcodes in a table. I am extracting the letters from the outcode of the postcode, which is being compared with a list of postcode areas from wiki here:

    http://en.wikipedia.org/wiki/List_of_postcode_areas_in_the_United_Kingdom

    This then gives my the postcode area that a site is in. I need to now map all the UK postcode areas, and show the number of sites by postcode area. This is where I am stuck. I dont know how to show this information. I have tried to find a shapefile (I think that is what I need??) of UK postcode areas, but cannot find one anywhere. I then tried using the Ordnance Survey Boundary-Line shapefiles:

    https://www.ordnancesurvey.co.uk/opendatadownload/products.html

    but they dont match the postcode areas that I have, eg I cant find 'Stoke-on-Trent' in any of the shapefiles from the OS. Can someone help please?

    Also, I am using
    Shape2SQL to upload the shape files to SQL Server, but not sure what to enter for the SRID, can someone help with that too please?

    Cheers
    Naz

    Tuesday, August 16, 2011 11:12 AM

All replies

  • Are you just trying to plot the locations of UK addresses based on their postcode? If so, I wouldn't bother with trying to extract the postal sector and cross-referencing to a shapefile - just look up the coordinates of each individual postcode unit from the Codepoint Open dataset (a different OS product on the same page you linked to).

    CodePoint Open gives the easting/northing coordinates of every postcode in the UK measured in the EPSG:27700 SRID (OSGB National Grid)


    twitter: @alastaira blog: http://alastaira.wordpress.com/
    Tuesday, August 16, 2011 3:37 PM
    Answerer
  • Hi tanoshimi

    I have downloaded this CodePoint dataset, but it contains 120 csv files - not sure how to get them into a SQL database quickly - any ideas? I havent been using SQL for very long, its not even my main role at work so I am just learning as I go along - any help with this would be appreciated.

    Also, we measure how many sites we have by postcode sector, and I need to create a map in SQL which shows exactly that - the number of sites within each postcode sector/area, as per the list from Wiki I linked above. Once I have managed to import all the postcodes from the Codepoint dataset into SQL, will I still be able to do this somehow?

    I noticed that the csv filenames in the Codepoint dataset match exactly the list of postcode sectors from wiki, maybe I can use that somehow? Is it possible to create a map of all the postcode sectors using the Codepoint dataset, and then number each sector with how many site postcodes fall within it?

    Cheers

    Wednesday, August 17, 2011 9:39 AM
  • I suggest you start by reading up about the UK postcode format. Wikipedia has a great article, here: http://en.wikipedia.org/wiki/Postcodes_in_the_United_Kingdom

    The CSV files in the CodePoint Open dataset are named according to postcode area, as you point out.

    To load CSV files into SQL Server easily, use the Import/Export wizard (Right-click on the name of a database in SQL Server Management Studio, then select Tasks -> Import Data). If you have SSIS installed, you can use this to easily loop through each of the files in the directory (they all have the same schema). You could also use BCP, OPENROWSET or several other techniques to get the data in.

    To find out the number of sites in each postcode sector, you don't need a spatial query at all - what you need is to extract the postcode sector from the full postcode. This includes the whole outward portion of the code (i.e. NR1) together with the first numeric digit of the inward portion of the code (i.e. NR1 3). You can do this by trimming the length of each postcode to exclude the final two characters (the postcode unit) using SUBSTRING and then grouping the results by the sector and counting the number of records in each.

    In terms of creating a map of the results, the only tool SQL Server provides to create a graphical output of spatial data is the SQL Server Reporting Services map component (not including the SSMS spatial results tab, which is no use for exporting images) - are you planning creating a reporting services report? If not, you'll have to use a third-party tool to connect to SQL Server and draw the results.


    twitter: @alastaira blog: http://alastaira.wordpress.com/
    Wednesday, August 17, 2011 10:27 AM
    Answerer
  • Hi tanoshimi

    I have read the UK postcode setup on wiki but not sure it helps me with what I need to do?.

    Yes I am going to use reporting services. I need to create a report which shows a map of the UK, divided into postcode sectors. Within each sector needs to be a number written on the map, which is the number of sites we have in that sector.

    I am going to read up on one of those other techniques you suggested to import the Codepoint data into SQL, we dont have Integration Services, so any idea which would be easiest for me to use as a complete beginner?

    Cheers

    Wednesday, August 17, 2011 11:12 AM
  • I should also add that I already have created a report which gives me a list of postcode sectors with number of sites within each. What I need to do is to now show this data on a map.

    Cheers

    Wednesday, August 17, 2011 11:13 AM
  • So the problem is that you don't know the "shape" of each postcode sector to plot on the map?

    The thing is, postcode sectors and areas don't truly have shapes of their own. They are simply aggregations of postcode units (which are placed at specific point locations) defined by the Royal Mail for the purposes of delivering to addresses. Although you can get maps that show polygonal areas representing "NR1", "NR2", etc., these areas are created artificially from the raw point data and are quite tricky to define (the only way I can think of doing so would be to create a voronoi cell around each postcode and then merge those cells that shared common postsectors) - they are not really geographic entities in their own right.

    Would it be sufficient to work out the "average" location of each postcode unit in a given postal sector and plot the number of sites in that sector at that location?


    twitter: @alastaira blog: http://alastaira.wordpress.com/
    Wednesday, August 17, 2011 1:55 PM
    Answerer
  • Hi, what I am trying to produce is a map exactly like the one here:

    http://www.direct-marketing-lists.co.uk/postcode_map.htm

    This map shows all postcode areas in the UK. The only change I would have is to add the number of sites into each area, and maybe change the colours to suit our needs.

    I am just now trying to import all those postcode csv files into a table using the Import Wizard, but having a few difficulties - I really need an idiots guide to do it. Do you happen to know of any simple guide to import multiple csv files into SQL? I *think* I need to use the Flat File Source to import them - is that correct? Also, I wont be needing all the columns, can I exclude certain columns from the import?

    Sorry for the simple questions, and cheers for the help

    Naz

    Wednesday, August 17, 2011 3:26 PM
  • I have just read your last reply, sorry I dont really understand. Each csv file in the Codepoint dataset represents a postcode area. So therefore wouldnt it be possible to create a postcode area map using the sum of the shapes for each postcode? Or is that stoopid?
    Wednesday, August 17, 2011 3:30 PM
  • Every postcode (i.e. NR2 4TE) in each of those codepoint CSV files is not a "shape" - it's a distinct point. So, if you add up all the points of the postcodes that begin NR2 ... you won't get a polygon shape - what you'll get is a set of points. (That's what I was trying to explain in my previous post, but obviously badly!).

    So, the question is, how do you get from a bunch of points to a polygon shape? If you knew that every postcode region were convex, you could use STConvexHull, but that isn't the case. There's actually a bit of tricky logic involved, which explains why, even though the codepoint dataset is itself free, companies (such as this one: http://www.map-logic.co.uk/digital-maps/postcode-boundary-data.html) will sell the post area and postsector boundary shape information derived from that data.

    I've got a Voronoi generator script lying around somewhere. If I find it I'll have a go at deriving those shapes myself from the codepoint open dataset and seeing how they compare to the map you linked to.


    twitter: @alastaira blog: http://alastaira.wordpress.com/
    Wednesday, August 17, 2011 5:56 PM
    Answerer
  • Check out this link:

     

    http://www.adathedev.co.uk/2011/01/gb-post-code-geographic-data-load-to.html

     

    It helped me get data in and converted into Lat/Lon etc.

    Monday, August 22, 2011 9:01 AM
  • Hi, any idea how I install the .exe file that I am supposed to be referencing in the C prompt window? I downloaded all the files from the Github website but there is no .exe file in there? How did you manage to do it?

    Cheers

    Naz

    Monday, August 22, 2011 10:45 AM
  • There are definitely only 120 postcode areas in the UK, 121 including Northern Ireland (postcode area BT). I have looked everywhere to see if someone has shapefiles for these postcode areas but they dont exist. I have a bad feeling that I will have to give up...
    Monday, August 22, 2011 10:57 AM
  • Check out this link:

     

    http://www.adathedev.co.uk/2011/01/gb-post-code-geographic-data-load-to.html

     

    It helped me get data in and converted into Lat/Lon etc.


    Not sure how to use this, when I run this command I get an error:

    'OSCodePointDataImport.exe' is not recognized as an internal or external command, operable program or batch file.

    Not sure what to do with this now??

    Monday, August 22, 2011 2:38 PM
  • Either I misunderstood your requirements, or the code at http://www.adathedev.co.uk/2011/01/gb-post-code-geographic-data-load-to.html is not going to help you do what you want to do (plot postcode areas on a map) - it's just the CodePoint Open dataset that you've already got converted to WGS84 coordinate system.

    The shapefiles of postcode areas do exist - I already provided a link to one source of them in my previous post - http://www.map-logic.co.uk/digital-maps/postcode-boundary-data.html. I'm sure there are other sources available as well. But, if you want to create those areas yourself from CodePoint Open data you need to do some processing. I tried my Voronoi method last night and it worked with up to about 1,000,000 points, but couldn't cope with the full 1.7 million rows needed to properly calculate the full set of postcode areas... suspect I ran out of memory.


    twitter: @alastaira blog: http://alastaira.wordpress.com/
    Monday, August 22, 2011 6:57 PM
    Answerer
  • Yes you were completely correct, I am looking for postcode area shapes on a map. I just assumed that because TwoLeggedHorse had replied, it was to achieve this goal. Got confused, sorry. So me converting the CodePoint data to WGS84 wont help me to create these shapes then, damn. So is it better to have the postcode data in WGS84 or as it is? I have read up on WGS84 quickly but dont see what the point is in converting to it from what I have?

    I was hoping this kind of shape file for postcode areas would just be available FOC somewhere, seeing as all the postcodes are available to download, as are shapes for boundaries, districts, admin units, etc etc. Guess nobody else has found it FOC then?

    Cheers
    Naz

    Tuesday, August 23, 2011 9:25 AM
  • *Bump*

    Still hoping someone has found a shapefile of postcode areas FOC somewhere, or can help me generate it from the OS data somehow?

    If you are still here tanoshimi, maybe you can tell me how to use this voronoi script to create these areas here on our server, using the OS data? Dont think my PC could handle it, but am a little worried about putting too much stress on the SQL server, what do you think?

    Tuesday, September 13, 2011 8:48 AM