0

I have a list of about 900 postcodes for each solar farm in England and Wales. I would like to find the house prices for each postcode, to see how house prices may have changed after the solar farms were implemented.

I am new to SPARQL and have no idea how to do a single query for all the postcodes. If anyone can help it would be great.

This is the link to searching via postcode: http://landregistry.data.gov.uk/app/qonsole.

Many thanks.

Stanislav Kralin
  • 11,070
  • 4
  • 35
  • 58
rjo93
  • 15
  • 1
  • 1
    https://stackoverflow.com/questions/17982615/how-to-access-the-land-registry-dwelling-type-from-a-sparql-query might help That has examples of using the land registry data, and suggestions on how to learn to use it. – Joshua Taylor Jul 24 '17 at 21:08

1 Answers1

2

It doesn't look like the same endpoint contains both the house price index and postcodes. However, looks like the resources in different enpoints are linked, so we can use a federated query to combine the information:

SELECT DISTINCT ?regionName ?postcode ?date ?ukhpi ?volume {
  SERVICE <http://data.ordnancesurvey.co.uk/datasets/os-linked-data/apis/sparql> {
    VALUES ?postcode {
      #-- insert postcodes here (two example postcodes below)
      "NP20 5AW"
      "SW1W 0NY"
    }
    ?postcodeUri rdfs:label ?postcode ;
        pc:district ?ordnanceSurveyRegion .
  }

  ?region owl:sameAs ?ordnanceSurveyRegion .
  ?region rdfs:label ?regionName .
  FILTER (langMatches(lang(?regionName), "EN"))

  ?report
    ukhpi:refRegion ?region;
    ukhpi:refMonth ?date;
    ukhpi:housePriceIndex ?ukhpi.

  OPTIONAL { 
    ?report ukhpi:salesVolume ?volume 
  }
} ORDER BY DESC(?date)

Here we query the Ordnance Survey endpoint to get the regions (district) using postcodes, and then get the house price indexes using those regions.

Note that inserting all 900 postcodes at once might be too much for the endpoint to handle.

Try the query in Yasgui.

Stanislav Kralin
  • 11,070
  • 4
  • 35
  • 58
evsheino
  • 2,147
  • 18
  • 20
  • thanks for that it worked really well, i would also like to retrieve every individual transaction in each postcode, how can i do this? – rjo93 Jul 28 '17 at 18:13
  • If you just include all the transactions, not all the transactions would be relevant to each housing index report. I suppose you could join the transactions by date, but how are the housing index report months (`refMonth`) related to the transation dates? – evsheino Jul 31 '17 at 07:35