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.

  • 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 Sep 16 '16 at 20:46
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
  • Can you please expand on how to use that to solve the problem raised in the Question? – Midavalo Sep 10 '16 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 Sep 12 '16 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 Sep 12 '16 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 Sep 12 '16 at 13:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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