giacecco

0 info it worked if it ends with ok
1 verbose cli [ '/usr/bin/node', '/usr/bin/npm', 'install' ]
2 info using npm@2.14.9
3 info using node@v0.12.10
4 verbose node symlink /usr/bin/node
5 verbose readDependencies loading dependencies from /opt/package.json
6 warn package.json dicoim-main-website@0.0.1 No README data
7 verbose install where, deps [ '/opt', [ 'express' ] ]
8 verbose install where, peers [ '/opt', [] ]
9 verbose installManyTop reading for lifecycle /opt/package.json

giacecco

.../database$ psql -d olaf_yr2_paper -f create_crowdflower_init_csv.sql 
psql:create_crowdflower_init_csv.sql:5: \copy: arguments required
psql:create_crowdflower_init_csv.sql:14: ERROR:  syntax error at or near "TO"
LINE 5:   TO '/Users/giacecco/Documents/PhD/GitHub projects/OLAF-yr2...
          ^
.../database$ 

giacecco

SELECT lr_town, ST_Union(mbr) AS geom
FROM os_open_names_with_towns
WHERE type = 'transportNetwork' AND lr_town = 'SOUTHAMPTON'
GROUP BY lr_town

giacecco

DROP VIEW IF EXISTS os_open_names_with_towns;
CREATE VIEW os_open_names_with_towns AS
SELECT a.*, b.town AS lr_town
FROM
(SELECT * FROM os_open_names WHERE type = 'transportNetwork') AS a,
(SELECT town, bounding_box FROM lr_pp_town_definition) AS b
WHERE ST_Contains(b.bounding_box, a.mbr)
UNION
SELECT *, NULL AS lr_town FROM os_open_names WHERE type != 'transportNetwork';

giacecco

DROP VIEW IF EXISTS lr_pp_town_definition;
CREATE VIEW lr_pp_town_definition AS
SELECT a.town, ST_MakeEnvelope(MIN(ST_X(b.geom)), MIN(ST_Y(b.geom)), MAX(ST_X(b.geom)), MAX(ST_Y(b.geom)), 4326) AS bounding_box
FROM
(SELECT DISTINCT town, pcd FROM lr_pp) AS a
LEFT JOIN
(SELECT pcd, geom FROM ons_pd WHERE doterm IS NULL) AS b
ON a.pcd = b.pcd
GROUP BY town;

giacecco

[
  {
    "location" : {
      "lat" : 50.936847,
      "lon" : -1.3963189999999486
    },
    "panoramaId" : "MpgI98E7NEuIe0P0Ypi9-Q",
    "pov" : {
      "heading" : 120.97,
      "pitch" : 13.3,

giacecco

# a few useful functions...
is.even <- function(x) sum(x %% 2 == 0) == length(x)
is.odd <- function(x) sum(x %% 2 != 0) == length(x)

# and finally, the calculation
can_be_inferred <- sum(apply(lrpp_streets_for_inference, 1, function (row) {
    aons <- lrpp_addresses_with_numeric_aon[(lrpp_addresses_with_numeric_aon$street == row[1]) & (lrpp_addresses_with_numeric_aon$pcd == row[2]), "aon"]
    # if all numbers I know are odd or even, I can only infer the missing odd and even numbers,
    # otherwise I can infer all
    # TODO: crazy below: why row[3] and row[4] are not numeric any longer?

giacecco

# find how many opportunities for applying the inference algorithm are available, and min and max 
# aon per street
lrpp_streets_for_inference <- lrpp_addresses_with_numeric_aon %>% group_by(street, pcd) %>% summarise(minAon = min(aon), maxAon = max(aon), how_many = n()) %>% filter(how_many > 1)

giacecco

# read from LRPP all addresses whose street names are not NULL and PAON or SAON are numeric; if both 
# are, take the PAON only the DISTINCT below is important, otherwise down this script the AONs of 
# properties that have been sold many times will weight more than the others
# (we did something similar to this already for 
# http://sociam-olaf.tumblr.com/post/124663267575/how-many-addresses-in-one-town )
lrpp_addresses_with_numeric_aon <- collect(tbl(src_postgres("olaf"), sql(paste0("SELECT DISTINCT street, aon, pcd FROM ((SELECT street, CAST(SUBSTRING(paon, '^([0-9]+)') AS NUMERIC) AS aon, pcd FROM lr_pp WHERE town = '", target_town, "' AND street IS NOT NULL AND paon ~ '^[0-9]+') UNION (SELECT street, CAST(SUBSTRING(saon, '^([0-9]+)') AS NUMERIC) AS aon, pcd FROM lr_pp WHERE town = '", target_town, "' AND street IS NOT NULL AND paon !~ '^[0-9]+' AND saon ~ '^[0-9]+')) AS a", collapse = ""))))

giacecco

# calculate the number of unique properties in the target town recorded in LRPP 
lrpp_addresses_no <- collect(tbl(src_postgres("olaf"), sql(paste0("SELECT COUNT(*) AS no_of_addresses FROM (SELECT DISTINCT paon, saon, street FROM lr_pp WHERE town = '", target_town , "') AS a", collapse = ""))))$no_of_addresses