markhneedham/blog huggingface dataset:
Neo4j: Find the midpoint between two lat/longs
Date: 2016-10-31 19:31:46

image::{{<siteurl>}}/uploads/2016/10/2016-10-31_06-06-00.png[2016 10 31 06 06 00,118]

Over the last couple of weekends I've been playing around with some transport data and I wanted to run the A* algorithm to find the quickest route between two stations.

The A* algorithm takes an +++<cite>+++estimateEvaluator+++</cite>+++ as one of its parameters and the evaluator looks at lat/longs of nodes to work out whether a path is worth following or not. I therefore needed to add lat/longs for each station and I found it surprisingly hard to find this location date for all the points in the dataset.

Luckily I tend to have the lat/longs for two points either side of a station so I can work out the midpoint as an approximation for the missing one.

I found http://www.movable-type.co.uk/scripts/latlong.html#midpoint[an article which defines a formula] we can use to do this and there's http://stackoverflow.com/questions/4656802/midpoint-between-two-latitude-and-longitude[a StackOverflow post] which has some Java code that implements the formula.

I wanted to find the midpoint between Surrey Quays station (51.4931963543,-0.0475185810) and a point further south on the train line (51.47908,-0.05393950). I wrote the following Cypher query to calculate this point:

[source,cypher]
----

WITH 51.4931963543 AS lat1, -0.0475185810 AS lon1,
     51.47908 AS lat2 , -0.05393950 AS lon2

WITH radians(lat1) AS rlat1, radians(lon1) AS rlon1,
     radians(lat2) AS rlat2, radians(lon2) AS rlon2,
     radians(lon2 - lon1) AS dLon

WITH rlat1, rlon1, rlat2, rlon2,
     cos(rlat2) * cos(dLon) AS Bx,
     cos(rlat2) * sin(dLon) AS By

WITH atan2(sin(rlat1) + sin(rlat2),
           sqrt( (cos(rlat1) + Bx) * (cos(rlat1) + Bx) + By * By )) AS lat3,
     rlon1 + atan2(By, cos(rlat1) + Bx) AS lon3

RETURN degrees(lat3) AS midLat, degrees(lon3) AS midLon
----

[source,text]
----

╒═════════════════╤═════════════════════╕
│midLat           │midLon               │
╞═════════════════╪═════════════════════╡
│51.48613822097523│-0.050729537454086385│
└─────────────────┴─────────────────────┘
----

The Google Maps screenshot on the right hand side shows the initial points at the top and bottom and the midpoint in between. It's not perfect; ideally I'd like the midpoint to be on the track, but I think it's good enough for the purposes of the algorithm.

Now I need to go and fill in the lat/longs for my location-less stations!
Neo4j: Create dynamic relationship type
Date: 2016-10-30 22:12:50

One of the things I've often found frustrating when importing data using Cypher, Neo4j's query language, is that it's quite difficult to create dynamic relationship types.

Say we have a CSV file structured like this:

[source,cypher]
----

load csv with headers from "file:///people.csv" AS row
RETURN row
----

[source,text]
----

╒═══════════════════════════════════════════════════════╕
│row                                                    │
╞═══════════════════════════════════════════════════════╡
│{node1: Mark, node2: Reshmee, relationship: MARRIED_TO}│
├───────────────────────────────────────────────────────┤
│{node1: Mark, node2: Alistair, relationship: FRIENDS}  │
└───────────────────────────────────────────────────────┘
----

We want to create nodes with the relationship type specified in the file. Unfortunately, in Cypher we can't pass in relationship types so we have to resort to the FOREACH hack to create our relationships:

[source,cypher]
----

load csv with headers from "file:///people.csv" AS row
MERGE (p1:Person {name: row.node1})
MERGE (p2:Person {name: row.node2})

FOREACH(ignoreMe IN CASE WHEN row.relationship = "MARRIED_TO" THEN [1] ELSE [] END |
 MERGE (p1)-[:MARRIED_TO]->(p2))

FOREACH(ignoreMe IN CASE WHEN row.relationship = "FRIENDS" THEN [1] ELSE [] END |
 MERGE (p1)-[:FRIENDS]->(p2))
----

This works, but:

. Looks horrendous
. Doesn't scale particularly well when we have multiple relationship types to deal with

As http://www.markhneedham.com/blog/2016/10/27/neo4j-dynamically-add-property/[in my last post] the https://neo4j-contrib.github.io/neo4j-apoc-procedures/[APOC library] comes to the rescue again, this time in the form of the +++<cite>+++apoc.create.relationship+++</cite>+++ procedure.

This procedure allows us to change our initial query to read like this:

[source,cypher]
----

load csv with headers from "file:///people.csv" AS row
MERGE (p1:Person {name: row.node1})
MERGE (p2:Person {name: row.node2})

WITH p1, p2, row
CALL apoc.create.relationship(p1, row.relationship, {}, p2) YIELD rel
RETURN rel
----

Much better!
Python: Scraping elements relative to each other with BeautifulSoup
Date: 2016-07-11 06:01:22

Last week we hosted a Game of Thrones based intro to Cypher at the http://www.meetup.com/Women-Who-Code-London/events/231014802/[Women Who Code London meetup] and in preparation had to scrape http://gameofthrones.wikia.com/wiki[the wiki] to build a dataset.

I've built lots of datasets this way and it's a painless experience as long as the pages make liberal use of CSS classes and/or IDs.

Unfortunately the Game of Thrones wiki doesn't really do that so I had to find another way to extract the data I wanted - extracting elements *based on their position to more prominent elements on the page*.

For example, I wanted to extract http://gameofthrones.wikia.com/wiki/Arya_Stark[Arya Stark]'s allegiances which look like this on the page:

image::{{<siteurl>}}/uploads/2016/07/2016-07-11_06-45-37.png[2016 07 11 06 45 37,596]

We don't have a direct route to her allegiances but we do have an indirect path via the h3 element with the text 'Allegiance'.

The following code gets us the 'Allegiance' element:

[source,python]
----

from bs4 import BeautifulSoup

file_name = "Arya_Stark"
wikia = BeautifulSoup(open("data/wikia/characters/{0}".format(file_name), "r"), "html.parser")
allegiance_element = [tag for tag in wikia.find_all('h3') if tag.text == "Allegiance"]

> print allegiance_element
[<h3 class="pi-data-label pi-secondary-font">Allegiance</h3>]
----

Now we need to work out the relative position of the div containing the houses. It's inside the same parent div so I thought it'd probably be the next sibling:

[source,python]
----

next_element = allegiance_element[0].next_sibling

> print next_element
----

Nope. Nothing! Hmmm, wonder why:

[source,python]
----

> print next_element.name, type(next_element)
None <class 'bs4.element.NavigableString'>
----

Ah, empty string. Maybe it's the one after that?

[source,python]
----

next_element = allegiance_element[0].next_sibling.next_sibling

> print next_element.name, type(next_element)
[<a href="/wiki/House_Stark" title="House Stark">House Stark</a>, <br/>, <a href="/wiki/Faceless_Men" title="Faceless Men">Faceless Men</a>, u' (Formerly)']
----

Hoorah! Afer this it became a case of working out how the text was structure and pulling out what I wanted.

https://github.com/mneedham/neo4j-got/blob/master/scrape_characters.py[The code I ended up with is on github] if you want to recreate it yourself.
Neo4j: Cypher - avoid duplicate calls to NOT patterns
Date: 2016-01-17 12:19:35

I've been reacquainting myself with the https://github.com/neo4j-meetups/modeling-worked-example[meetup.com dataset] ahead of http://www.meetup.com/graphdb-london/[Wednesday's meetup in London] and wanted to write a collaborative filtering type query to work out which groups people in my groups were in.

This started simple enough:

[source,cypher]
----

MATCH (member:Member {name: "Mark Needham"})-[:MEMBER_OF]->(group:Group)<-[:MEMBER_OF]-(other:Member)-[:MEMBER_OF]->(otherGroup:Group)
RETURN otherGroup, COUNT(*) AS commonMembers
ORDER BY commonMembers DESC
LIMIT 5
----

And doesn't take too long to run:

[source,text]
----

Cypher version: CYPHER 2.3, planner: COST. 1084378 total db hits in 1103 ms.
----

However, it was showing up several groups that I'm already a member of so I added in a "WHERE NOT" clause to sort that out:

[source,cypher]
----

MATCH (member:Member {name: "Mark Needham"})-[:MEMBER_OF]->(group:Group)<-[:MEMBER_OF]-(other:Member)-[:MEMBER_OF]->(otherGroup:Group)
WHERE NOT (member)-[:MEMBER_OF]->(otherGroup)
RETURN otherGroup, COUNT(*) AS commonMembers
ORDER BY commonMembers DESC
LIMIT 5
----

Unfortunately when I ran this the amount of db hits increased by 14x and it now took 3x as long to run:

[source,text]
----

Cypher version: CYPHER 2.3, planner: COST. 14061442 total db hits in 3364 ms.
----

The problem is that we're making lots of duplicate calls to +++<cite>+++NOT (member)-[:MEMBER_OF]\->(otherGroup)+++</cite>+++ because each group shows up lots of times.

This is the 'reduce cardinality of work in progress' tip from http://neo4j.com/blog/neo4j-2-2-query-tuning/[Michael Hunger's blog post]:

____
Bonus Query Tuning Tip: Reduce Cardinality of Work in Progress When following longer paths, you'll encounter duplicates. If you're not interested in *all the possible paths* -- but just distinct information from stages of the path -- make sure that you eagerly eliminate duplicates, so that later matches don't have to be executed many multiple times.
____

We can reduce the WIP in our query by doing the counting of common members first and then filtering out the groups we're already a member of:

[source,cypher]
----

MATCH (member:Member {name: "Mark Needham"})-[:MEMBER_OF]->(group:Group)<-[:MEMBER_OF]-(other:Member)-[:MEMBER_OF]->(otherGroup:Group)
WITH otherGroup, member, COUNT(*) AS commonMembers
WHERE NOT (member)-[:MEMBER_OF]->(otherGroup)
RETURN otherGroup, commonMembers
ORDER BY commonMembers DESC
LIMIT 5
----

This gets us back down to something closer to the running time/db hits of our initial query:

[source,text]
----

Cypher version: CYPHER 2.3, planner: COST. 1097114 total db hits in 1004 ms.
----
Clojure: First steps with reducers
Date: 2016-01-24 22:01:43

I've been playing around with Clojure a bit today in preparation for https://skillsmatter.com/meetups/7794-using-clojure-neo4j-to-build-a-meetup-recommendation-engine[a talk I'm giving next week] and found myself writing the following code to apply the same function to three different scores:

[source,clojure]
----

(defn log2 [n]
  (/ (Math/log n) (Math/log 2)))

(defn score-item [n]
  (if (= n 0) 0 (log2 n)))

(+ (score-item 12) (score-item 13) (score-item 5)) 9.60733031374961
----

I'd forgotten about folding over a collection but quickly remembered that I could achieve the same result with the following code:

[source,clojure]
----

(reduce #(+ %1 (score-item %2)) 0 [12 13 5]) 9.60733031374961
----

The added advantage here is that if I want to add a 4th score to the mix all I need to do is append it to the end of the vector:

[source,clojure]
----

(reduce #(+ %1 (score-item %2)) 0 [12 13 5 6]) 12.192292814470767
----

However, while Googling to remind myself of the order of the arguments to reduce I kept coming across http://clojure.org/reference/reducers[articles and documentation about reducers] which I'd heard about but never used.

As I understand they're used to http://ianrumford.github.io/blog/2013/08/25/some-trivial-examples-of-using-clojure-reducers/[achieve performance gains and easier composition of functions over collections] so I'm not sure how useful they'll be to me but I thought I'd give them a try.

Our first step is to bring the namespace into scope:

[source,clojure]
----

(require '[clojure.core.reducers :as r])
----

Now we can compute the same result using the +++<cite>+++reduce+++</cite>+++ function:

[source,clojure]
----

(r/reduce #(+ %1 (score-item %2)) 0 [12 13 5 6]) 12.192292814470767
----

So far, so identical. If we wanted to calculate individual scores and then filter out those below a certain threshold the code would behave a little differently:

[source,clojure]
----

(->>[12 13 5 6]
    (map score-item)
    (filter #(> % 3))) (3.5849625007211565 3.700439718141092)

(->> [12 13 5 6]
     (r/map score-item)
     (r/filter #(> % 3))) #object[clojure.core.reducers$folder$reify__19192 0x5d0edf21 "clojure.core.reducers$folder$reify__19192@5d0edf21"]
----

Instead of giving us a vector of scores the reducers version returns a reducer which can pass into +++<cite>+++reduce+++</cite>+++ or +++<cite>+++fold+++</cite>+++ if we want an accumulated result or +++<cite>+++into+++</cite>+++ if we want to output a collection. In this case we want the latter:

[source,clojure]
----

(->> [12 13 5 6]
     (r/map score-item)
     (r/filter #(> % 3))
     (into [])) (3.5849625007211565 3.700439718141092)
----

With a measly 4 item collection I don't think the reducers are going to provide much speed improvement here but we'd need to use the +++<cite>+++http://clojure.org/reference/reducers#_using_reducers[fold]+++</cite>+++ function if we want processing of the collection to be done in parallel.

One for next time!
Unix: Split string using separator
Date: 2016-06-19 07:22:57

I recently found myself needing to iterate over a bunch of '/' separated strings on the command line and extract just the text after the last '/'.

e.g. an example of one of the strings

[source,text]
----

A/B/C
----

I wanted to write some code that could split on '/' and then pick the 3rd item in the resulting collection.

One way of doing this is to echo the string and then pipe it through cut:

[source,bash]
----

$ string="A/B/C"
$ echo ${string} | cut -d"/" -f3
C
----

or awk:

[source,bash]
----

$ echo ${string} | awk -F"/" '{ print $3}'
C
----

I don't like having to echo the string - it feels a bit odd so I wanted to see if there was a way to do the parsing more 'inline'.

I came across http://stackoverflow.com/questions/918886/how-do-i-split-a-string-on-a-delimiter-in-bash[this post] which explains how to change the internal field separator (IFS) on the shell and then parse the string into an array using http://ss64.com/bash/read.html[read]. I gave it a try:

[source,bash]
----

$ IFS="/" read -ra ADDR <<< "${string}"; echo ${ADDR[2]}
C
----

Works! We can even refer to the last item in the array using -1 instead of it's absolute position:

[source,bash]
----

$ IFS="/" read -ra ADDR <<< "${string}"; echo ${ADDR[-1]}
C
----

I'd not come across this use of the 'read' function before. The key is the '-a' parameter. From the man page:

____
-a aname The words are assigned to sequential indices of the array variable aname, starting at 0. All elements are removed from aname before the assignment. Other name arguments are ignored.
____

So we're resetting the internal field separator and then reading the string into another variable as an array split on the '/' character.

Pretty neat although now it's longer than the original command and I'm sure I'll forget the syntax.

Further down the page is another suggestion which seems even harder to remember but is much shorter:

[source,bash]
----

$ echo ${string##*/}
C
----

This drops from the beginning of the string up until the last occurrence of '/' which is exactly what we want.

This way is the nicest and doesn't require any echoing if we just want to assign the result to a variable. The echo is only used here to see the output.
Unix: Find all text below string in a file
Date: 2016-06-19 08:36:46

I recently wanted to parse some text out of a bunch of files so that I could do some sentiment analysis on it. Luckily the text I want is at the end of the file and doesn't have anything after it but there is text before it that I want to get rid.

The files look like this:

[source,text]
----

# text I don't care about

= Heading of the bit I care about

# text I care about
----

In other words I want to find the line that contains the Heading and then get all the text after that point.

I figured sed was the tool for the job but my knowledge of the syntax was a bit rusty. Luckily http://stackoverflow.com/questions/7103531/how-to-get-the-part-of-file-after-the-line-that-matches-grep-expression-first[this post] served as a refresher.

Effectively what we want to do is delete from the beginning of the file up until the line after the heading. We can do this with the following command:

[source,bash]
----

$ cat /tmp/foo.txt
# text I don't care about

= Heading of the bit I care about

# text I care about
----

[source,bash]
----

$ cat /tmp/foo.txt | sed '1,/Heading of the bit I care about/d'

# text I care about
----

That still leaves an extra empty line after the heading which is a bit annoying but easy enough to get rid of by passing another command to sed that strips empty lines:

[source,bash]
----

$ cat /tmp/foo.txt | sed -e '1,/Heading of the bit I care about/d' -e '/^\s*$/d'
# text I care about
----

The only difference here is that we're now passing the '-e' flag to allow us to specify multiple commands. If we just pass them sequentially then the 2nd one will be interpreted as the name of a file.
Python: BeautifulSoup - Insert tag
Date: 2016-06-30 21:28:35

I've been scraping the Game of Thrones wiki in preparation http://www.meetup.com/Women-Who-Code-London/events/231014802/[for a meetup at Women Who Code next week] and while attempting to extract character allegiances I wanted to insert missing line breaks to separate different allegiances.

I initially tried http://stackoverflow.com/questions/14652706/python-beautifulsoup-add-tags-around-found-keyword[creating a line break] like this:

[source,python]
----

>>> from bs4 import BeautifulSoup
>>> tag = BeautifulSoup("<br />", "html.parser")
>>> tag
<br/>
----

It looks like it should work but later on in my script I check the 'name' attribute to work out whether I've got a line break and it doesn't return the value I expected it to:

[source,python]
----

>>> tag.name
u'[document]'
----

My script assumes it's going to return the string 'br' so I needed another way of creating the tag. The following does the trick:

[source,python]
----

>>> from bs4 import Tag
>>> tag = Tag(name = "br")
>>> tag
<br></br>
----

[source,python]
----

>>> tag.name
'br'
----

That's all for now, back to scraping for me!
Unix parallel: Populating all the USB sticks
Date: 2016-06-01 05:53:38

The day before Graph Connect Europe 2016 we needed to create a bunch of USB sticks containing Neo4j and the training materials and eventually iterated our way to a half decent approach which made use of the https://www.gnu.org/software/parallel/parallel_tutorial.html[GNU parallel command] which I've always wanted to use!

But first I needed to get a USB hub so I could do lots of them at the same time. I bought the https://www.amazon.co.uk/gp/product/B00ID270ZU/ref=oh_aui_detailpage_o08_s00?ie=UTF8&psc=1[EasyAcc USB 3.0] but there are lots of other ones that do the same job.

Next I mouunted all the USB sticks and then renamed the volumes to be NEO4J1 \-> NEO4J7:

[source,bash]
----

for i in 1 2 3 4 5 6 7; do diskutil renameVolume "USB DISK" NEO4J${i}; done
----

I then created a bash function called 'duplicate' to do the copying work:

[source,bash]
----

function duplicate() {
  i=${1}
  echo ${i}
  time rsync -avP --size-only --delete --exclude '.*' --omit-dir-times /Users/markneedham/Downloads/graph-connect-europe-2016/ /Volumes/NEO4J${i}/
}
----

We can now call this function in parallel like so:

[source,bash]
----

seq 1 7 | parallel duplicate
----

And that's it. We didn't get a 7x improvement in the throughput of USB creation from doing 7 in parallel but it took ~ 9 minutes to complete 7 compared to 5 minutes each. Presumably there's still some part of the copying that is sequential further down - https://en.wikipedia.org/wiki/Amdahl%27s_law[Amdahl's law] #ftw.

I want to go and find other things that I can use pipe into parallel now!
Docker: Unknown - Unable to query docker version: x509: certificate is valid for
Date: 2016-12-21 07:11:50

I was playing around with Docker locally and somehow ended up with this error when I tried to list my https://docs.docker.com/machine/overview/[docker machines]:

[source,bash]
----

$ docker-machine ls
NAME      ACTIVE   DRIVER       STATE     URL                         SWARM   DOCKER    ERRORS
default   -        virtualbox   Running   tcp://192.168.99.101:2376           Unknown   Unable to query docker version: Get https://192.168.99.101:2376/v1.15/version: x509: certificate is valid for 192.168.99.100, not 192.168.99.101
----

My Google Fu was weak I couldn't find any suggestions for what this might mean so I tried shutting it down and starting it again! </>

On the restart I actually got some helpful advice: ~~~bash $ docker-machine stop Stopping "default"\... Machine "default" was stopped. ~~~ ~~~bash $ docker-machine start Starting "default"\... (default) Check network to re-create if needed\... (default) Waiting for an IP\... Machine "default" was started. Waiting for SSH to be available\... Detecting the provisioner\... Started machines may have new IP addresses. You may need to re-run the `docker-machine env` command. ~~~

So I tried that: ~~~bash $ docker-machine env Error checking TLS connection: Error checking and/or regenerating the certs: There was an error validating certificates for host "192.168.99.101:2376": x509: certificate is valid for 192.168.99.100, not 192.168.99.101 You can attempt to regenerate them using 'docker-machine regenerate-certs [name]'. Be advised that this will trigger a Docker daemon restart which will stop running containers. ~~~

And then regenerates my certificates: ~~~bash $ docker-machine regenerate-certs Regenerate TLS machine certs? Warning: this is irreversible. (y/n): y Regenerating TLS certificates Waiting for SSH to be available\... Detecting the provisioner\... Copying certs to the local machine directory\... Copying certs to the remote machine\... Setting Docker configuration on the remote daemon\... ~~~

And now everything is happy again! ~~~bash $ docker-machine ls NAME ACTIVE DRIVER STATE URL SWARM DOCKER ERRORS default - virtualbox Running tcp://192.168.99.101:2376 v1.9.0 ~~~
Go: cannot execute binary file: Exec format error
Date: 2016-12-23 18:24:12

In an earlier blog post I mentioned that I'd been http://www.markhneedham.com/blog/2016/12/23/go-templating-with-the-gin-web-framework/[building an internal application] to learn a bit of Go and I wanted to deploy it to AWS.

Since the application was only going to live for a couple of days I didn't want to spend a long time build up anything fancy so my plan was just to build the executable, SSH it to my AWS instance, and then run it.

My initial (somewhat naive) approach was to just build the project on my Mac and upload and run it:

[source,bash]
----

$ go build

$ scp myapp ubuntu@aws...

$ ssh ubuntu@aws...

$ ./myapp
-bash: ./myapp: cannot execute binary file: Exec format error
----

That didn't go so well! By reading http://askubuntu.com/questions/648555/bash-program-cannot-execute-binary-file-exec-format-error[Ask Ubuntu] and https://dave.cheney.net/2015/03/03/cross-compilation-just-got-a-whole-lot-better-in-go-1-5[Dave Cheney's blog post on cross compilation] I realised that I just needed to set the appropriate environment variables before running +++<cite>+++go build+++</cite>+++.

The following did the trick:

[source,bash]
----

env GOOS=linux GOARCH=amd64 GOARM=7 go build
----

And that's it! I'm sure there's more sophisticated ways of doing this that I'll come to learn about but for now this worked for me.