0

I have data that contains energy demand information from postcodes(sometimes single and sometimes a range)the column POSTCODE_V and POSTCODE_T mean POSTCODE_FROM and POSTCODE_TO respectively. here's a sample of the data enter image description here

example of the postcode data is as follows

enter image description here

I'm trying to spatially join this data to the postcode shapefile without losing information.

I tried a join based on postcode from and then to merge the data by generating an additional column with sequence of numbers with repeating numbers if its a range of postcodes and dissolving the data based on this column. This is not working very well as some postcodes do not have energy data. Can someone help me figure out the best way to deal with this? In addition, the postcode file is huge. It has 450000 records.

Improve this question
1
  • What do you mean by "spatially join this data"? In order to do that, you would need a geometry for each table and I don't see WKT, WKB, geom or XY (Lat/Lon) in your data above. Are there other tables that contain such information?
    – kttii
    Commented Sep 16, 2016 at 20:46

1 Answer 1

Reset to default
2

You just need to use a BETWEEN criteria to join the energy demand table with its respective postcode data:

SELECT *
FROM energy_demand,postcode_data
WHERE postcode_data.pc6_PC6 BETWEEN energy_demand.POSTCODE_V AND energy_demand.POSTCODE_T
ORDER BY 1,5
Improve this answer
4
  • Can you please expand on how to use that to solve the problem raised in the Question?
    – Midavalo ♦
    Commented Sep 10, 2016 at 3:40
  • Thank you for the reply. the SQL query only joins and creates duplicates for the inbetween postcodes which means it adds additional information in the energy demand column. If analysed in ArcGIS this would yield wrong results.
    – bala
    Commented Sep 12, 2016 at 8:43
  • @bala I guess I misunderstood your question. I created the first 4 energy_demand records and the first 4 postcode_data records and then joined them with the statement I provided. Can you please edit your answer and provide an example output list of the result you are wanting?
    – kttii
    Commented Sep 12, 2016 at 12:36
  • @kttii I found a solution using the same query. After the table is generated i will dissolve the polygons based on postcode_from. This would give me what i need. The only problem now is the query takes a looong time to execute with 450000 rows of data. Do you know a faster workaround with the same query?
    – bala
    Commented Sep 12, 2016 at 13:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.