Distance Based Queries with PostgreSQL, PostGIS, and Sequelize

This post assumes you are familar enough with node, express, sequelize, and postgreSQl to spin up a basic express server and connect it to a Postgres database using sequelize.

When I attempted to first create an api that would find users within a certain distance of an alert, I was unable to find any working examples that itegrated Sequelize queries and PostGIS. Below you’ll find an example of how to enable PostGIS and one method of formatting your queries.

Luckily, most common installations of Postgres include PostGIS, but if you are unlucky head on over to postgis.net/install for instructions. FYI, their first instruction will be to install a current version of Postgres.

On the command line use psql to create your database and enable PostGIS

1
2
3
4
psql
CREATE DATABASE geoBase
\connect geoBase
CREATE EXTENSION POSTGIS;

You will now be blessed with spatial functions and object types in your database. Specifically, you can now use the Sequelize.GEOMETRY and GEOGRAPHY data types and the PostGIS functions are available to you.

In our database we have list of users at specific “points”. A Sequelize.GEOMETRY(‘POINT’) is stored in your database by first creating a point object and storing it in your db. Sequelize will convert your point into geometry for you. YAY! (LineStrings and Polygons are also valid geometry types)

Example Model definition:

1
2
3
4
5
6
7
8
9
10
11
const Sequelize = require('sequelize')
const db = require('./_db')
const Alert = db.define('alert', {
  deviceId: Sequelize.STRING,
  position: Sequelize.GEOMETRY('POINT', 4326),
  codename: Sequelize.STRING,
  message: Sequelize.STRING
})
module.exports = Alert

If you get an error that type Geometry does not exist, make sure you go back into psql and enable PostGIS.

We are using the 4326 SRID which will give us distances in degrees. We can then convert to our distance unit of choice.

Creating points in the db:

1
2
3
4
5
6
7
8
9
10
11
12
router.post('/alerts', (req, res, next) => {
  let point = {
    type: 'Point',
    coordinates: [req.body.long, req.body.lat],
    crs: { type: 'name', properties: { name: 'EPSG:4326'} }
  }
  Alert.create({
    message: req.body.message,
    codename: req.body.message,
    position: point
  })

Sample query to find all within 0.032 deg which is approximately 2 miles.

1
2
3
4
5
6
7
8
9
10
11
12
13
Alert.findAll({
  where: Sequelize.where(
    Sequelize.fn('ST_DWithin',
      Sequelize.col('position'),
      Sequelize.fn('ST_SetSRID',
        Sequelize.fn('ST_MakePoint',
          req.query.long, req.query.lat),
        4326),
      0.032),
    true)
})
.then(alerts => res.send(alerts))
.catch(next)

It is very important to note that Sequelize uses the ST_GeomFromGeoJSON PostGIS method to create points. GeoJSON coordinates require that longitude is first and latitude is second. This is counter intuitive to those of us accustomed to using latitude first, but very important when creating points to measure distance. The sequelize docs and issues history also appear to have confused lat/long. The PostGIS docs and GeoJSON spec are very explicit as to the order.

Setting up an Express server in Node

Before we begin

You will need to have node and npm (node package manager) installed on your machine.

If you are unsure about this, check out the npm blog post on the subject,
How to Install Npm, but npm should be installed with node itself.

Because we are using npm to keep track of our dependencies we will need to initialize npm in our project directory.

1
2
3
mkdir yourProject
cd yourProject
npm init

You will then be walked through creating the package.json file for your project. It will auto populate the name as your directory and ask you for some other settings. Just hitting enter until the end will populate package.json with default values. You can edit these later just by opening the file.

Creating the server

app.js

A standard method to create your express server is to put it in a file called app.js.

1
touch app.js

Requiring Express

Like all of our dependencies, we will have to install express in our node project and “require” express in app.js. Requiring a file in your file gives you access to that file’s module.exports.
In terminal:

1
npm install express --save

In app.js

1
2
const express = require('express');
const app = express();

Notice that since express is an included npm package, we will not need to give it a path. Npm will automatically look in node_modules in the current folder and up the directory chain.
We have assigned the returned value of express() to app and this is what we will use to interact with the node server.

Starting the server

app.listen can be used to start the HTTP server and listen on the specified port.

1
2
3
app.listen('3333', function(){
  console.log('Listening on port 3333');
})

To start the server you would run

1
node app.js

in the terminal.
In this case port 3333 is on our local host so we can access it at http://localhost:3333. If we navigated there currently, there would be no response from the server, since we haven’t set up any routes.

Adding a route

Express adheres to the restful api and makes it easy to receive and respond to HTTP GET, PUT, POST, and DELETE verbs. An exampe of a simple get request would be in app.js:

1
2
3
app.get('/', function (req, res){
  res.send('You\'ve reached the home directory');
});

If you send a get request to http://localhost:3333, such as by opening it in a broswer it will send the response “You’ve reached the home directory!”

Refactoring a Javascript Calculator

Refactoring a simple calculator web app

The Why

Before I started Fullstack Academy, I had gone through most of the Free Code Camp front end curriculum. One thing that I found I was missing is a general best practices. It never occurred to me that a calculator could be an object with methods even though I knew how to create objects with method. Free Code Camp gives you some basic tools, but doesn’t tell you if you’re making something smart or just making it hacky.

Anyway, I’ve decided to refactor the JS, JQuery calculator web app I made for Free Code Camp using a calculator object. If time allows, I will refactor it again as a parser, because… why not?

The original code pen of the original version can be found here: Simple Javascript Calculator

The calculator object

In my original version, on each operation, I was putting the total, operator, and new input into a string and evaluating. I had the text field set up so you could only input by button to prevent errors, so this also would have prevented any hacking being done through the input. But honestly, I didn’t even know at the time that a hacker could enter code in a text input that is then passed into an eval statement.

In the object version I created a calculator object with a total, a next, and methods to add, subtract, multiply, and divide. Other functionality that was in my original calculator include clear all, clear entry, backspace, and +/-.

Clearing the calculator

Using the object method seems have simplified clearing the calculator, I can just make a new calculator.

Adding functionality

Each operation button will need to grab the current input value, run the last operation, clear the next value and save the current operation.

1
2
3
4
5
6
7
8
9
10
$('#plus').click(function(){
  if(calc.lastOperation){
    calc.lastOperation(+calc.next);
  }else{
    calc.setTotal(+calc.next);
  }
  $('#numberIn').val(calc.getTotal());
  calc.next = "";
  calc.lastOperation = calc.add;
})

I then created a runLast function to complete the first 6 lines in the plus click function and used that for minus, divide, multiply, and equals.
All the basic functionality of a calculator is now there.

Next Steps

-I attempted to add the click functions to the number button with a loop but it wasn’t working. I could figure this out.
-I could wrap the entire javascript, not just the calculator, inside an object. This would be a good exercise in ‘this’, binding, and arrow functions.
-When you click the equals button it stops the chaining and you have to start over. This is a little buggy as you may want to do an operation on your result.

-Rewrite the calculator as a parser that can handle parentheses.

Big O and Sorting Algorithms

Introduction

I first encountered Big O notation in my Fullstack technical interview. I was given a problem that could easily be solved by a nested for loop, but I knew an more elegant/efficient solution must have been possible.

The interviewer mentioned Big O notation and how this was an O of n^2 because we were looping through the array twice. I was eventually able (with significant prodding) to come to a solution that had a Big O of n. Cool, I still didn’t get what Big O was, but I knew that the second function was more efficient.

What is Big O?

Big O can be described as how the time it takes for an algorithm to complete grows as the data set that the algorithm processes grows. Big O accounts for the worst case scenario

A Big O of 1 means that the algorithm finishes in constant time. It doesn’t matter if the set has 1 item, or 1,000,000 items, it will take the same amount of time to finish. Examples of this are accessing data at an index of an array or accessing data in an ideal hash table. You can go directly to that item without looping or checking any other items. These examples are very simplified and a Big O of 1, doesn’t mean it will be fast, but only that it is constant.

A Big O of log n indicates that the completion time grows smaller than the data set grows. One example of this is a balanced Binary Search Tree. Since each step in the search allows you to throw out half of the remaining data, doubling the data only increase the completion time by one time step.

A Big O of n means that the time to complete the algorithm increases linearly with the size of a set. An example of this is searching a linked list. In a worst case scenario, you start with the first node in the list and your result is in the last node. You have then checked every single item in the list.

A Big O of n log n generally indicates a two step process where one process requires you to touch every single item, but instead of having to do it n times, there is an optimization that allows you to break the other step into smaller pieces. An example of this is a merge sort. To merge the arrays, you will need to touch each of the elements in the array. But do to splitting each array in half, etc., you will only need to merge the arrays log n times, rather than n times, resulting in the product n log n.

A Big O of n^2 means that you need to touch every single item in the list at least twice. An example is the Bubble Sort search algorithm. At a worst case, you iterate through the n values in the array n times. Each time, bubbling the largest number to the end of the array.

A Big of n! can be found in algorithms like finding all permutations of an array. Here dynamic programming techniques should come in handy.

Fullstack Academy First Impressions

Week 1 Day 2

Thoughts on what we are learning

I am really glad that I started the cs50x course before starting at FS. The first two days
were studying data structures and I made sure to watch the cs50 data structure videos as part of my pre-reading. This really helped me to understand the theory behind the data structures and abstract data types as well as why they are important for speed and memory management in general.

On the other hand, the implementation of data structures in Javascript is a little frustrating since we are simulating the structures. I don’t think I would understand why these data structures are relevant without having learned about pointers in C, but without needing pointers, the implementation seems overly contrived. Sure, having to remember to malloc the space for every node in a list and free the memory when you get rid of it is extra steps, but at least you know exactly where it is and what it does.

It also doesn’t help that I find the imprecision of Javascript to be inconvenient at best, and extremely frustrating at worst, depending on the situation. For example, when I first created a simple calculator web app in Javascript, formatting the output to a specific number of decimal places feels like doing a backflip through a flaming hoop. But Javascript isn’t necessarily the best choice for complex computation or implementing low level data structures, so I’m hoping that when we get to the places where Javscript is the best choice, I learn to love it for its strengths instead of loathing it for its weaknesses. I firmly believe in using the right tool for the job. While an axe can be used as a hammer, an axe, a knife, or a plane, it might not be the most efficient way to turn a tree into a 1/4 inch thick board.

Thoughts on pair programming

I think pair programming is a good way to work through a problem. It’s harder to just try things without understanding how they work because your partner will also need to understand what is happening.

When working remotely and pair programming, the biggest weakness is the time to switch places. Instead of just switching seats, you need to leave the test specs, commit the changes, and push them. Then, your partner still needs to pull the changes, open the file, deal with the conflicts , share their screen, and restart testem. This makes switching quite a process when you are in the middle of a good flow and have limited time to solve the workshops.

My biggest weakness in pair programming is that I feel like I am always taking over, whether I am the driver or the navigator. I will need to figure out how to let the navigator catch up before I start typing. This should be easier after spending years teaching students how to literally navigate, but with students, I know the answer and I am just waiting for them to reach the same conclusion so it is easy to wait. With pair programming, because I want to learn as much as possible, I have a tendency to explain to the navigator what I am thinking/coding rather than waiting for them to come up with the answer so that it is faster and we can continue to move forward.

Some Javascript takeaways

When you want to capture a reference to an object, understand what you are referencing.

1
2
3
4
5
6
7
var obj1 = {value: "first", previous: null, next: obj2};
var obj2 = {value: "second, previous: obj1, next: obj3};
var obj3 = {value: "third", previous: obj2, next: null};
var x = obj3.previous.value
console.log(x) //"second"
obj3.previous = obj1;
console.log(x) //"second"

In the previous example, when we assign x, it is assigned obj2.value, not referencing obj3.previous, so when obj3.previous is changed to obj1, the value of x doesn’t change. Objects are only passed by reference when you are referencing the entire object, not an element of that object.

My first post with hexo

Figuring out how to post and deploy with Hexo. YAY!

Formatting a title

Formatting a subtitle

1
$ hexo new "My New Post"

That’s the command to create a new post.