Converting data from parliamentary constituency code/county code to a mappable format?












1















I have a spreadsheet detailing fuel poverty in the UK, against a) individual parliamentary constituencies b) county and c) local authority. This can be found here if anyone needs to look: https://www.gov.uk/government/statistics/sub-regional-fuel-poverty-data-2017



I want to map this in QGIS (preferably as symbols of different size, rather than shapes denoting the actual shape) but the location information is based on codes (e.g. Darlington is E14000658 as the Parliamentary Constituency).



Is there a way to convert this to say co-ordinates for mapping? There are many hundreds of fields so I am not really able to do this manually...










share|improve this question









New contributor




Wiredchild is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

























    1















    I have a spreadsheet detailing fuel poverty in the UK, against a) individual parliamentary constituencies b) county and c) local authority. This can be found here if anyone needs to look: https://www.gov.uk/government/statistics/sub-regional-fuel-poverty-data-2017



    I want to map this in QGIS (preferably as symbols of different size, rather than shapes denoting the actual shape) but the location information is based on codes (e.g. Darlington is E14000658 as the Parliamentary Constituency).



    Is there a way to convert this to say co-ordinates for mapping? There are many hundreds of fields so I am not really able to do this manually...










    share|improve this question









    New contributor




    Wiredchild is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
    Check out our Code of Conduct.























      1












      1








      1








      I have a spreadsheet detailing fuel poverty in the UK, against a) individual parliamentary constituencies b) county and c) local authority. This can be found here if anyone needs to look: https://www.gov.uk/government/statistics/sub-regional-fuel-poverty-data-2017



      I want to map this in QGIS (preferably as symbols of different size, rather than shapes denoting the actual shape) but the location information is based on codes (e.g. Darlington is E14000658 as the Parliamentary Constituency).



      Is there a way to convert this to say co-ordinates for mapping? There are many hundreds of fields so I am not really able to do this manually...










      share|improve this question









      New contributor




      Wiredchild is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.












      I have a spreadsheet detailing fuel poverty in the UK, against a) individual parliamentary constituencies b) county and c) local authority. This can be found here if anyone needs to look: https://www.gov.uk/government/statistics/sub-regional-fuel-poverty-data-2017



      I want to map this in QGIS (preferably as symbols of different size, rather than shapes denoting the actual shape) but the location information is based on codes (e.g. Darlington is E14000658 as the Parliamentary Constituency).



      Is there a way to convert this to say co-ordinates for mapping? There are many hundreds of fields so I am not really able to do this manually...







      qgis data convert coordinates






      share|improve this question









      New contributor




      Wiredchild is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.











      share|improve this question









      New contributor




      Wiredchild is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.









      share|improve this question




      share|improve this question








      edited 2 days ago









      Vince

      14.4k32747




      14.4k32747






      New contributor




      Wiredchild is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.









      asked 2 days ago









      WiredchildWiredchild

      335




      335




      New contributor




      Wiredchild is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.





      New contributor





      Wiredchild is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.






      Wiredchild is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.






















          1 Answer
          1






          active

          oldest

          votes


















          4














          First you need the parliamentary borders, which you can download as a shapefile from



          http://geoportal.statistics.gov.uk/datasets/westminster-parliamentary-constituencies-december-2015-full-extent-boundaries-in-great-britain



          You can also get the data generalised or clipped to the coastline - search for "Westminster" seems to get most of the options:



          http://geoportal.statistics.gov.uk/datasets?q=westminster%20parliamentary%20constituencies%20(december%202015)%20full%20extent%20boundaries%20in%20great%20britain



          this shapefile loads into QGIS, and has a pcon15cd column, which is the 2015 parliamentary constituency code, which starts with E14.



          From the spreadsheet with E14 codes, save as a CSV and load into QGIS, which will appear as a layer with no geometry. Then use QGIS' JOIN too relate the data from the CSV to the polygons in the shapefile. You can then use a QGIS style setting to map the centroid points instead of the boundaries, and colour according to the linked value from the CSV.



          There should be a few blogs on this process, or even in the QGIS documentation.



          But quite quickly, and with a few "gotchas" (like stuff on the start and end of the spreadsheet), I produced this:



          enter image description here



          which is a point map of the centroids of electoral constituencies coloured by one of the variables in the fuel poverty spreadsheet.






          share|improve this answer





















          • 2





            The OS OpenData Boundary-Line dataset also provides polygons for GB administrative boundaries and the shape file for Westminster constituencies (westminster_const_region.shp) includes the constituency codes you're looking for. ordnancesurvey.co.uk/business-and-government/products/… As a quick check I joined the shapefile and the Excel file using the 'Spreadsheet Layers' plug-in, but I think the suggestion of converting just the required parts of the Excel file to CSV is a good one.

            – nhopton
            2 days ago













          • That's great thank you very much!

            – Wiredchild
            yesterday











          Your Answer








          StackExchange.ready(function() {
          var channelOptions = {
          tags: "".split(" "),
          id: "79"
          };
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function() {
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled) {
          StackExchange.using("snippets", function() {
          createEditor();
          });
          }
          else {
          createEditor();
          }
          });

          function createEditor() {
          StackExchange.prepareEditor({
          heartbeatType: 'answer',
          autoActivateHeartbeat: false,
          convertImagesToLinks: false,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: null,
          bindNavPrevention: true,
          postfix: "",
          imageUploader: {
          brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
          contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
          allowUrls: true
          },
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          });


          }
          });






          Wiredchild is a new contributor. Be nice, and check out our Code of Conduct.










          draft saved

          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fgis.stackexchange.com%2fquestions%2f308471%2fconverting-data-from-parliamentary-constituency-code-county-code-to-a-mappable-f%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown

























          1 Answer
          1






          active

          oldest

          votes








          1 Answer
          1






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes









          4














          First you need the parliamentary borders, which you can download as a shapefile from



          http://geoportal.statistics.gov.uk/datasets/westminster-parliamentary-constituencies-december-2015-full-extent-boundaries-in-great-britain



          You can also get the data generalised or clipped to the coastline - search for "Westminster" seems to get most of the options:



          http://geoportal.statistics.gov.uk/datasets?q=westminster%20parliamentary%20constituencies%20(december%202015)%20full%20extent%20boundaries%20in%20great%20britain



          this shapefile loads into QGIS, and has a pcon15cd column, which is the 2015 parliamentary constituency code, which starts with E14.



          From the spreadsheet with E14 codes, save as a CSV and load into QGIS, which will appear as a layer with no geometry. Then use QGIS' JOIN too relate the data from the CSV to the polygons in the shapefile. You can then use a QGIS style setting to map the centroid points instead of the boundaries, and colour according to the linked value from the CSV.



          There should be a few blogs on this process, or even in the QGIS documentation.



          But quite quickly, and with a few "gotchas" (like stuff on the start and end of the spreadsheet), I produced this:



          enter image description here



          which is a point map of the centroids of electoral constituencies coloured by one of the variables in the fuel poverty spreadsheet.






          share|improve this answer





















          • 2





            The OS OpenData Boundary-Line dataset also provides polygons for GB administrative boundaries and the shape file for Westminster constituencies (westminster_const_region.shp) includes the constituency codes you're looking for. ordnancesurvey.co.uk/business-and-government/products/… As a quick check I joined the shapefile and the Excel file using the 'Spreadsheet Layers' plug-in, but I think the suggestion of converting just the required parts of the Excel file to CSV is a good one.

            – nhopton
            2 days ago













          • That's great thank you very much!

            – Wiredchild
            yesterday
















          4














          First you need the parliamentary borders, which you can download as a shapefile from



          http://geoportal.statistics.gov.uk/datasets/westminster-parliamentary-constituencies-december-2015-full-extent-boundaries-in-great-britain



          You can also get the data generalised or clipped to the coastline - search for "Westminster" seems to get most of the options:



          http://geoportal.statistics.gov.uk/datasets?q=westminster%20parliamentary%20constituencies%20(december%202015)%20full%20extent%20boundaries%20in%20great%20britain



          this shapefile loads into QGIS, and has a pcon15cd column, which is the 2015 parliamentary constituency code, which starts with E14.



          From the spreadsheet with E14 codes, save as a CSV and load into QGIS, which will appear as a layer with no geometry. Then use QGIS' JOIN too relate the data from the CSV to the polygons in the shapefile. You can then use a QGIS style setting to map the centroid points instead of the boundaries, and colour according to the linked value from the CSV.



          There should be a few blogs on this process, or even in the QGIS documentation.



          But quite quickly, and with a few "gotchas" (like stuff on the start and end of the spreadsheet), I produced this:



          enter image description here



          which is a point map of the centroids of electoral constituencies coloured by one of the variables in the fuel poverty spreadsheet.






          share|improve this answer





















          • 2





            The OS OpenData Boundary-Line dataset also provides polygons for GB administrative boundaries and the shape file for Westminster constituencies (westminster_const_region.shp) includes the constituency codes you're looking for. ordnancesurvey.co.uk/business-and-government/products/… As a quick check I joined the shapefile and the Excel file using the 'Spreadsheet Layers' plug-in, but I think the suggestion of converting just the required parts of the Excel file to CSV is a good one.

            – nhopton
            2 days ago













          • That's great thank you very much!

            – Wiredchild
            yesterday














          4












          4








          4







          First you need the parliamentary borders, which you can download as a shapefile from



          http://geoportal.statistics.gov.uk/datasets/westminster-parliamentary-constituencies-december-2015-full-extent-boundaries-in-great-britain



          You can also get the data generalised or clipped to the coastline - search for "Westminster" seems to get most of the options:



          http://geoportal.statistics.gov.uk/datasets?q=westminster%20parliamentary%20constituencies%20(december%202015)%20full%20extent%20boundaries%20in%20great%20britain



          this shapefile loads into QGIS, and has a pcon15cd column, which is the 2015 parliamentary constituency code, which starts with E14.



          From the spreadsheet with E14 codes, save as a CSV and load into QGIS, which will appear as a layer with no geometry. Then use QGIS' JOIN too relate the data from the CSV to the polygons in the shapefile. You can then use a QGIS style setting to map the centroid points instead of the boundaries, and colour according to the linked value from the CSV.



          There should be a few blogs on this process, or even in the QGIS documentation.



          But quite quickly, and with a few "gotchas" (like stuff on the start and end of the spreadsheet), I produced this:



          enter image description here



          which is a point map of the centroids of electoral constituencies coloured by one of the variables in the fuel poverty spreadsheet.






          share|improve this answer















          First you need the parliamentary borders, which you can download as a shapefile from



          http://geoportal.statistics.gov.uk/datasets/westminster-parliamentary-constituencies-december-2015-full-extent-boundaries-in-great-britain



          You can also get the data generalised or clipped to the coastline - search for "Westminster" seems to get most of the options:



          http://geoportal.statistics.gov.uk/datasets?q=westminster%20parliamentary%20constituencies%20(december%202015)%20full%20extent%20boundaries%20in%20great%20britain



          this shapefile loads into QGIS, and has a pcon15cd column, which is the 2015 parliamentary constituency code, which starts with E14.



          From the spreadsheet with E14 codes, save as a CSV and load into QGIS, which will appear as a layer with no geometry. Then use QGIS' JOIN too relate the data from the CSV to the polygons in the shapefile. You can then use a QGIS style setting to map the centroid points instead of the boundaries, and colour according to the linked value from the CSV.



          There should be a few blogs on this process, or even in the QGIS documentation.



          But quite quickly, and with a few "gotchas" (like stuff on the start and end of the spreadsheet), I produced this:



          enter image description here



          which is a point map of the centroids of electoral constituencies coloured by one of the variables in the fuel poverty spreadsheet.







          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited 2 days ago

























          answered 2 days ago









          SpacedmanSpacedman

          22.8k23449




          22.8k23449








          • 2





            The OS OpenData Boundary-Line dataset also provides polygons for GB administrative boundaries and the shape file for Westminster constituencies (westminster_const_region.shp) includes the constituency codes you're looking for. ordnancesurvey.co.uk/business-and-government/products/… As a quick check I joined the shapefile and the Excel file using the 'Spreadsheet Layers' plug-in, but I think the suggestion of converting just the required parts of the Excel file to CSV is a good one.

            – nhopton
            2 days ago













          • That's great thank you very much!

            – Wiredchild
            yesterday














          • 2





            The OS OpenData Boundary-Line dataset also provides polygons for GB administrative boundaries and the shape file for Westminster constituencies (westminster_const_region.shp) includes the constituency codes you're looking for. ordnancesurvey.co.uk/business-and-government/products/… As a quick check I joined the shapefile and the Excel file using the 'Spreadsheet Layers' plug-in, but I think the suggestion of converting just the required parts of the Excel file to CSV is a good one.

            – nhopton
            2 days ago













          • That's great thank you very much!

            – Wiredchild
            yesterday








          2




          2





          The OS OpenData Boundary-Line dataset also provides polygons for GB administrative boundaries and the shape file for Westminster constituencies (westminster_const_region.shp) includes the constituency codes you're looking for. ordnancesurvey.co.uk/business-and-government/products/… As a quick check I joined the shapefile and the Excel file using the 'Spreadsheet Layers' plug-in, but I think the suggestion of converting just the required parts of the Excel file to CSV is a good one.

          – nhopton
          2 days ago







          The OS OpenData Boundary-Line dataset also provides polygons for GB administrative boundaries and the shape file for Westminster constituencies (westminster_const_region.shp) includes the constituency codes you're looking for. ordnancesurvey.co.uk/business-and-government/products/… As a quick check I joined the shapefile and the Excel file using the 'Spreadsheet Layers' plug-in, but I think the suggestion of converting just the required parts of the Excel file to CSV is a good one.

          – nhopton
          2 days ago















          That's great thank you very much!

          – Wiredchild
          yesterday





          That's great thank you very much!

          – Wiredchild
          yesterday










          Wiredchild is a new contributor. Be nice, and check out our Code of Conduct.










          draft saved

          draft discarded


















          Wiredchild is a new contributor. Be nice, and check out our Code of Conduct.













          Wiredchild is a new contributor. Be nice, and check out our Code of Conduct.












          Wiredchild is a new contributor. Be nice, and check out our Code of Conduct.
















          Thanks for contributing an answer to Geographic Information Systems Stack Exchange!


          • Please be sure to answer the question. Provide details and share your research!

          But avoid



          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.


          To learn more, see our tips on writing great answers.




          draft saved


          draft discarded














          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fgis.stackexchange.com%2fquestions%2f308471%2fconverting-data-from-parliamentary-constituency-code-county-code-to-a-mappable-f%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown





















































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown

































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown







          Popular posts from this blog

          "Incorrect syntax near the keyword 'ON'. (on update cascade, on delete cascade,)

          Alcedinidae

          Origin of the phrase “under your belt”?