0

I'm looking at researching into the impact of particular energy sources, upon house prices. I would like to find information on property values for multiple post codes, in the vicinity of where energy is being produced.

I found the following information which was helpful: HM Land Registry SPARQL query for multiple postcodes and have adjusted the code sightly, so that I am able to obtain more categories of information. However, this only produces results when I use the example post codes specified in the link above. It doesn't return results for the other postcodes that I want. Apologies, if there is a straight forward solution, but I am completely new to SPARQL (and to coding in general). Hence I am not sure what to do next? I've attached the code below. Many thanks.

prefix rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
prefix rdfs: <http://www.w3.org/2000/01/rdf-schema#>
prefix owl: <http://www.w3.org/2002/07/owl#>
prefix xsd: <http://www.w3.org/2001/XMLSchema#>
prefix sr: <http://data.ordnancesurvey.co.uk/ontology/spatialrelations/>
prefix pc: <http://data.ordnancesurvey.co.uk/ontology/postcode/>
prefix ukhpi: <http://landregistry.data.gov.uk/def/ukhpi/>
prefix lrppi: <http://landregistry.data.gov.uk/def/ppi/>
prefix skos: <http://www.w3.org/2004/02/skos/core#>
prefix lrcommon: <http://landregistry.data.gov.uk/def/common/>

SELECT DISTINCT ?regionName ?postcode ?date ?ukhpi ?volume ?ukhpi_averagePrice ?ukhpi_averagePriceCash ?ukhpi_averagePriceDetached ?ukhpi_averagePriceExistingProperty ?ukhpi_averagePriceFirstTimeBuyer ?ukhpi_averagePriceFlatMaisonette ?ukhpi_averagePriceFormerOwnerOccupier ?ukhpi_averagePriceMortgage ?ukhpi_averagePriceNewBuild ?ukhpi_averagePriceSA ?ukhpi_averagePriceSemiDetached ?ukhpi_averagePriceTerraced ?ukhpi_housePriceIndex ?ukhpi_housePriceIndexCash ?ukhpi_housePriceIndexDetached ?ukhpi_housePriceIndexExistingProperty ?ukhpi_housePriceIndexFirstTimeBuyer ?ukhpi_housePriceIndexFlatMaisonette ?ukhpi_housePriceIndexFormerOwnerOccupier ?ukhpi_housePriceIndexMortgage ?ukhpi_housePriceIndexNewBuild ?ukhpi_housePriceIndexSA ?ukhpi_housePriceIndexSemiDetached ?ukhpi_housePriceIndexTerraced ?ukhpi_percentageAnnualChange ?ukhpi_percentageAnnualChangeCash ?ukhpi_percentageAnnualChangeDetached ?ukhpi_percentageAnnualChangeExistingProperty ?ukhpi_percentageAnnualChangeFirstTimeBuyer ?ukhpi_percentageAnnualChangeFlatMaisonette ?ukhpi_percentageAnnualChangeFormerOwnerOccupier ?ukhpi_percentageAnnualChangeMortgage ?ukhpi_percentageAnnualChangeNewBuild ?ukhpi_percentageAnnualChangeSemiDetached ?ukhpi_percentageAnnualChangeTerraced ?ukhpi_percentageChange ?ukhpi_percentageChangeCash ?ukhpi_percentageChangeDetached ?ukhpi_percentageChangeExistingProperty ?ukhpi_percentageChangeFirstTimeBuyer ?ukhpi_percentageChangeFlatMaisonette ?ukhpi_percentageChangeFormerOwnerOccupier ?ukhpi_percentageChangeMortgage ?ukhpi_percentageChangeNewBuild ?ukhpi_percentageChangeSemiDetached ?ukhpi_percentageChangeTerraced ?ukhpi_refPeriodDuration ?ukhpi_refPeriodStart ?ukhpi_salesVolume ?ukhpi_salesVolumeCash ?ukhpi_salesVolumeExistingProperty ?ukhpi_salesVolumeMortgage ?ukhpi_salesVolumeNewBuild{
  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
           } OPTIONAL
      { ?report  ukhpi:averagePrice  ?ukhpi_averagePrice }
    OPTIONAL
      { ?report  ukhpi:averagePriceCash  ?ukhpi_averagePriceCash }
    OPTIONAL
      { ?report  ukhpi:averagePriceDetached  ?ukhpi_averagePriceDetached }
    OPTIONAL
      { ?report  ukhpi:averagePriceExistingProperty  ?ukhpi_averagePriceExistingProperty }
    OPTIONAL
      { ?report  ukhpi:averagePriceFirstTimeBuyer  ?ukhpi_averagePriceFirstTimeBuyer }
    OPTIONAL
      { ?report  ukhpi:averagePriceFlatMaisonette  ?ukhpi_averagePriceFlatMaisonette }
    OPTIONAL
      { ?report  ukhpi:averagePriceFormerOwnerOccupier  ?ukhpi_averagePriceFormerOwnerOccupier }
    OPTIONAL
      { ?report  ukhpi:averagePriceMortgage  ?ukhpi_averagePriceMortgage }
    OPTIONAL
      { ?report  ukhpi:averagePriceNewBuild  ?ukhpi_averagePriceNewBuild }
    OPTIONAL
      { ?report  ukhpi:averagePriceSA  ?ukhpi_averagePriceSA }
    OPTIONAL
      { ?report  ukhpi:averagePriceSemiDetached  ?ukhpi_averagePriceSemiDetached }
    OPTIONAL
      { ?report  ukhpi:averagePriceTerraced  ?ukhpi_averagePriceTerraced }
    OPTIONAL
      { ?report ukhpi:housePriceIndex  ?ukhpi_housePriceIndex }
    OPTIONAL
      { ?report  ukhpi:housePriceIndexCash  ?ukhpi_housePriceIndexCash }
    OPTIONAL
      { ?report  ukhpi:housePriceIndexDetached  ?ukhpi_housePriceIndexDetached }
    OPTIONAL
      { ?report  ukhpi:housePriceIndexExistingProperty  ?ukhpi_housePriceIndexExistingProperty }
    OPTIONAL
      { ?report  ukhpi:housePriceIndexFirstTimeBuyer  ?ukhpi_housePriceIndexFirstTimeBuyer }
    OPTIONAL
      { ?report  ukhpi:housePriceIndexFlatMaisonette  ?ukhpi_housePriceIndexFlatMaisonette }
    OPTIONAL
      { ?report  ukhpi:housePriceIndexFormerOwnerOccupier  ?ukhpi_housePriceIndexFormerOwnerOccupier }
    OPTIONAL
      { ?report ukhpi:housePriceIndexMortgage  ?ukhpi_housePriceIndexMortgage }
    OPTIONAL
      { ?report  ukhpi:housePriceIndexNewBuild  ?ukhpi_housePriceIndexNewBuild }
    OPTIONAL
      { ?report  ukhpi:housePriceIndexSA  ?ukhpi_housePriceIndexSA }
    OPTIONAL
      { ?report  ukhpi:housePriceIndexSemiDetached  ?ukhpi_housePriceIndexSemiDetached }
    OPTIONAL
      { ?report  ukhpi:housePriceIndexTerraced  ?ukhpi_housePriceIndexTerraced }
    OPTIONAL
      { ?report  ukhpi:percentageAnnualChange  ?ukhpi_percentageAnnualChange }
    OPTIONAL
      { ?report  ukhpi:percentageAnnualChangeCash  ?ukhpi_percentageAnnualChangeCash }
    OPTIONAL
      { ?report  ukhpi:percentageAnnualChangeDetached  ?ukhpi_percentageAnnualChangeDetached }
    OPTIONAL
      { ?report  ukhpi:percentageAnnualChangeExistingProperty  ?ukhpi_percentageAnnualChangeExistingProperty }
    OPTIONAL
      { ?report  ukhpi:percentageAnnualChangeFirstTimeBuyer  ?ukhpi_percentageAnnualChangeFirstTimeBuyer }
    OPTIONAL
      { ?report  ukhpi:percentageAnnualChangeFlatMaisonette  ?ukhpi_percentageAnnualChangeFlatMaisonette }
    OPTIONAL
      { ?report  ukhpi:percentageAnnualChangeFormerOwnerOccupier  ?ukhpi_percentageAnnualChangeFormerOwnerOccupier }
    OPTIONAL
      { ?report  ukhpi:percentageAnnualChangeMortgage  ?ukhpi_percentageAnnualChangeMortgage }
    OPTIONAL
      { ?report  ukhpi:percentageAnnualChangeNewBuild  ?ukhpi_percentageAnnualChangeNewBuild }
    OPTIONAL
      { ?report  ukhpi:percentageAnnualChangeSemiDetached  ?ukhpi_percentageAnnualChangeSemiDetached }
    OPTIONAL
      { ?report  ukhpi:percentageAnnualChangeTerraced  ?ukhpi_percentageAnnualChangeTerraced }
    OPTIONAL
      { ?report ukhpi:percentageChange  ?ukhpi_percentageChange }
    OPTIONAL
      { ?report  ukhpi:percentageChangeCash  ?ukhpi_percentageChangeCash }
    OPTIONAL
      { ?report  ukhpi:percentageChangeDetached  ?ukhpi_percentageChangeDetached }
    OPTIONAL
      { ?report ukhpi:percentageChangeExistingProperty  ?ukhpi_percentageChangeExistingProperty }
    OPTIONAL
      { ?report  ukhpi:percentageChangeFirstTimeBuyer  ?ukhpi_percentageChangeFirstTimeBuyer }
    OPTIONAL
      { ?report  ukhpi:percentageChangeFlatMaisonette  ?ukhpi_percentageChangeFlatMaisonette }
    OPTIONAL
      { ?report  ukhpi:percentageChangeFormerOwnerOccupier  ?ukhpi_percentageChangeFormerOwnerOccupier }
    OPTIONAL
      { ?report  ukhpi:percentageChangeMortgage  ?ukhpi_percentageChangeMortgage }
    OPTIONAL
      { ?report  ukhpi:percentageChangeNewBuild  ?ukhpi_percentageChangeNewBuild }
    OPTIONAL
      { ?report  ukhpi:percentageChangeSemiDetached  ?ukhpi_percentageChangeSemiDetached }
    OPTIONAL
      { ?report ukhpi:percentageChangeTerraced  ?ukhpi_percentageChangeTerraced }
    OPTIONAL
      { ?report  ukhpi:refPeriodDuration  ?ukhpi_refPeriodDuration }
    OPTIONAL
      { ?report  ukhpi:refPeriodStart  ?ukhpi_refPeriodStart }
    OPTIONAL
      { ?report  ukhpi:salesVolume  ?ukhpi_salesVolume }
    OPTIONAL
      { ?report  ukhpi:salesVolumeCash  ?ukhpi_salesVolumeCash }
    OPTIONAL
      { ?report  ukhpi:salesVolumeExistingProperty  ?ukhpi_salesVolumeExistingProperty }
    OPTIONAL
      { ?report  ukhpi:salesVolumeMortgage  ?ukhpi_salesVolumeMortgage }
    OPTIONAL
      { ?report  ukhpi:salesVolumeNewBuild  ?ukhpi_salesVolumeNewBuild }

} ORDER BY ?ukhpi_refMonth
econ23
  • 1
  • 1
  • 1
    and now the big question: for which postcodes that you expect it should work does it not work? I mean you posted here a working query and said for other postcodes it does not - but how can anybody here without knowing which postcodes you're talking about? – UninformedUser Jul 12 '19 at 03:46
  • Looks like there is no mapping between both datasets through `owl:sameAs` e.g. `?region owl:sameAs ?ordnanceSurveyRegion` do not produce solutions for districts that are not mapped. I took a closer look at the service and it seems that some other regions are related also with `rdfs:seeAlso` predicate so if you replace the above pattern with `?region (owl:sameAs|rdfs:seeAlso) ?ordnanceSurveyRegion .` it will produce more solutions for other postcodes ... – Damyan Ognyanov Jul 12 '19 at 06:33
  • and even then, data for some postcodes might be missing or at just region URIs not match. Would be still nice to know some postcodes that failed, such that we could say what definitely is missing in resp. between the datasets – UninformedUser Jul 12 '19 at 07:29
  • Thanks to everybody responding. Perfect! That worked brilliantly, thank you ever so much! Apologies for not being clearer, just to clarify, the original post codes which worked were "NP20 5AW "SW1W 0NY". The postcodes from my first site, e.g "LE67 4AF","LE67 4AH","LE67 4QP","LE67 4AG" all failed. I then used a random postcode generator to obtain post codes to try and work out if it was a general problem, none of these worked either. Thanks again for your help. – econ23 Jul 12 '19 at 14:32

0 Answers0