\u672c\u6587\u7531 \u7b80\u60a6 SimpRead<\/a> \u8f6c\u7801\uff0c \u8f6c\u8f7d\u81ea blog.patricktriest.com<\/a><\/p>\n<\/blockquote>\n A 20-minute guide to building a Node.js API to serve geospatial "Game of Thrones" data from PostgreSQ......<\/p>\n<\/blockquote>\n Have you ever wondered how "Google Maps" might be working in the background?<\/em><\/p>\n Have you watched "Game of Thrones" and been confused about where all of the castles and cities are located in relation to each other?<\/em><\/p>\n Do you not care about "Game of Thrones", but still want a guide to setting up a Node.js server with PostgreSQL and Redis?<\/em><\/p>\n In this 20 minute tutorial, we'll walk through building a Node.js API to serve geospatial"Game of Thrones" data from PostgreSQL (with the PostGIS extension) and Redis.<\/p>\n Part II<\/a> of this series provides a tutorial on building a "Google Maps" style web application to visualize the data from this API.<\/p>\n Check out https:\/\/atlasofthrones.com\/<\/a> for a preview of the final product.<\/p>\n \u200b Before starting, we'll need to install the project dependencies.<\/p>\n The primary datastore for this app is PostgreSQL<\/a>. Postgres is a powerful and modern SQL database, and is a very solid choice for any app that requires storing and querying relational data. We'll also be using the PostGIS<\/a> spatial database extender for Postgres, which will allow us to run advanced queries and operations on geographic datatypes.<\/p>\n This page contains the official download and installation instructions for PostgreSQL - https:\/\/www.postgresql.org\/download\/<\/a><\/p>\n Another good resource for getting started with Postgres can be found here - http:\/\/postgresguide.com\/setup\/install.html<\/a><\/p>\n If you are using a version of PostgreSQL that does not come bundled with PostGIS, you can find installation guides for PostGIS here - We'll be using Redis<\/a> in order to cache API responses. Redis is an in-memory key-value datastore that will enable our API to serve data with single-digit millisecond response times.<\/p>\n Installation instructions for Redis can be found here - https:\/\/redis.io\/topics\/quickstart<\/a><\/p>\n Finally, we'll need Node.js<\/a> v7.6 or above to run our core application server and endpoint handlers, and to interface with the two datastores.<\/p>\n Installation instructions for Node.js can be found here - To keep things simple, we'll be using a pre-built database dump for this project.<\/p>\n The database dump contains polygons and coordinate points for locations in the "Game of Thrones" world, along with their text description data. The geo-data is based on multiple open source contributions, which I've cleaned and combined with text data scraped from A Wiki of Ice and Fire<\/a>, Game of Thrones Wiki<\/a>, and WesterosCraft<\/a>. More detailed attribution can be found here<\/a>.<\/p>\n<\/blockquote>\n In order to load the database locally, first download the database dump.<\/p>\n We'll need to create a user in the Postgres database.<\/p>\n If you already have a Postgres instance with users\/roles set up, feel free to skip this step.<\/p>\n<\/blockquote>\n Run Next, create a new user in Postgres.<\/p>\n In case it wasn't obvious, you should replace Next, create a new database for your project.<\/p>\n Grant query privileges in the new database to your newly created user.<\/p>\n Then connect to this new database, and activate the PostGIS extension.<\/p>\n Run Load the downloaded SQL dump into your newly created database.<\/p>\n If you've had no errors so far, congrats!<\/p>\n Let's enter the Again, substitute "patrick" here with your username.<\/p>\n Once we're in the Postgres shell, we can get a list of available tables with the We can inspect the schema of an individual table by running<\/p>\n Now, let's get a list of all of the kingdoms, with their corresponding names, claimants, and ids.<\/p>\n Nice! If you're familiar with Game of Thrones, these names probably look familiar.<\/p>\n Let's try out one more query, this time on the This query returns a list of available Go ahead and exit the Postgres shell with Run the following commands to clone the starter project and install the dependencies<\/p>\n The starter branch includes a base directory template, with dependencies declared in package.json. It is configured with ESLint<\/a> and JavaScript Standard Style<\/a>.<\/p>\n If the lack of semicolons in this style guide makes you uncomfortable, that's fine, you're welcome to switch the project to another style in the Before starting, we'll need to add a Here's a sample You'll need to change the"patrick" in the DATABASE_URL entry to match your Postgres user credentials. Unless your name is Patrick, that is, in which case it might already be fine.<\/p>\n A very simple This will load the variables defined in Setting authentication credentials and other environment specific configuration using ENV variables is a good, language agnostic way to handle this information. For a tutorial like this it might be considered overkill, but I've encountered quite a few production Node.js servers that are omitting these basic best practices (using hardcoded credentials checked into Git for instance). I imagine these bad practices may have been learned from tutorials which skip these important steps, so I try to focus my tutorial code on providing examples of best practices.<\/p>\n<\/blockquote>\n We'll be using Koa.js<\/a> as an API framework. Koa is a sequel-of-sorts to the wildly popular Express.js<\/a>. It was built by the same team as Express, with a focus on minimalism, clean control flow, and modern conventions.<\/p>\n Open First, import the required dependencies at the top of the file.<\/p>\n Next, we'll initialize our Koa app, and retrieve the API listening port and CORs settings from the local environment variables.<\/p>\n Add the following (below the imports) in Now we'll define two middleware functions with Add the following code to Koa makes heavy use of async\/await for handling the control flow of API request handlers. If you are unclear on how this works, I would recommend reading these resources -<\/p>\n You might notice that we're using Add the following code to Here we're just defining a small logger module using the Now open up the In this step, all we really care about is the Below the imports, initialize a new API router.<\/p>\n Now add a simple "Hello World" endpoint.<\/p>\n Finally, export the router at the bottom of the file.<\/p>\n Now we can mount the endpoint route(s) and start the server.<\/p>\n Add the following at the end of Try starting the server with Now try opening Now that our API server is running, we'll want to connect to our Postgres database in order to actually serve data. In the Try starting the server again with Now let's add a basic query test to make sure that our database and API server are communicating correctly.<\/p>\n In This will perform one the simplest possible queries (besides In Now, we've defined a new endpoint, Run Our end goal is to render our "Game of Thrones" dataset on a map. To do so, we'll need to serve our data in a web-map friendly format: GeoJSON<\/a>. GeoJSON is a JSON specification (RFC 7946<\/a>), which will format geographic coordinates and polygons in a way that can be natively understood by browser-based map rendering tools.<\/p>\n Note - If you want to minimize payload size, you could convert the GeoJSON results to TopoJSON<\/a>, a newer format that is able to represent shapes more efficiently by eliminating redundancy. Our GeoJSON results are not prohibitively large (around 50kb for all of the Kingdom shapes, and less than 5kb for each set of location types), so we won't bother with that in this tutorial.<\/p>\n<\/blockquote>\n In the Here, we are using the Note that in the location query, we are not directly appending the provided type to the query string. Instead, we're using In the Here, we are are executing the corresponding Postgres queries and awaiting each response. We are then mapping over each result row to add the entity metadata as GeoJSON properties.<\/p>\n I've deployed a very simple HTML page here<\/a> to test out the GeoJSON responses using Leaflet<\/a>.<\/p>\n In order to provide a background for the GeoJSON data, the test page loads a sweet "Game of Thrones" basemap produced by Carto<\/a>. This simple HTML page is also included in the starter project, in the Start the server (\n
A Game of Maps<\/h3>\n
![\"\"](\"https:\/\/cdn.patricktriest.com\/blog\/images\/posts\/got_map\/got_map.jpg\")
\u200b<\/p>\nStep 0 - Setup Local Dependencies<\/h3>\n
0.0 - PostgreSQL and PostGIS<\/h5>\n
http:\/\/postgis.net\/install\/<\/a><\/p>\n0.1 - Redis<\/h5>\n
0.2 - Node.js<\/h5>\n
https:\/\/nodejs.org\/en\/download\/<\/a><\/p>\nStep 1 - Getting Started With Postgres<\/h3>\n
1.0 - Download Database Dump<\/h5>\n
\n
wget https:\/\/cdn.patricktriest.com\/atlas-of-thrones\/atlas_of_thrones.sql\n<\/code><\/pre>\n1.1 - Create Postgres User<\/h5>\n
\n
psql -U postgres<\/code>\u200b on the command line to enter the Postgres shell as the default postgres<\/code>\u200b user. You might need to run this command as root (with sudo<\/code>\u200b) or as the Postgres user in the operating system (with sudo -u postgres psql<\/code>\u200b) depending on how Postgres is installed on your machine.<\/p>\npsql -U postgres\n<\/code><\/pre>\nCREATE USER patrick WITH PASSWORD 'the_best_passsword';\n<\/code><\/pre>\npatrick<\/code>\u200b and the_best_passsword<\/code>\u200b in the above command with your desired username and password respectively.<\/p>\n1.2 - Create "atlas_of_thrones" Database<\/h5>\n
CREATE DATABASE atlas_of_thrones;\n<\/code><\/pre>\nGRANT ALL PRIVILEGES ON DATABASE atlas_of_thrones to patrick;\nGRANT SELECT ON ALL TABLES IN SCHEMA public TO patrick;\n<\/code><\/pre>\n\\c atlas_of_thrones\nCREATE EXTENSION postgis;\n<\/code><\/pre>\n\\q<\/code>\u200b to exit the Postgres shell.<\/p>\n1.3 - Import Database Dump<\/h5>\n
psql -d atlas_of_thrones < atlas_of_thrones.sql\n<\/code><\/pre>\n1.4 - List Databse Tables<\/h5>\n
atlas_of_thrones<\/code>\u200b database from the command line.<\/p>\npsql -d atlas_of_thrones -U patrick\n<\/code><\/pre>\n\\dt<\/code>\u200b command.<\/p>\n\\dt\n<\/code><\/pre>\nList of relations\n Schema | Name | Type | Owner \n--------+-----------------+-------+---------\n public | kingdoms | table | patrick\n public | locations | table | patrick\n public | spatial_ref_sys | table | patrick\n(3 rows)\n<\/code><\/pre>\n1.5 - Inspect Table Schema<\/h5>\n
\\d kingdoms\n<\/code><\/pre>\nTable "public.kingdoms"\n Column | Type | Modifiers \n-----------+------------------------------+---------------------------------------------------------\n gid | integer | not null default nextval('political_gid_seq'::regclass)\n name | character varying(80) | \n claimedby | character varying(80) | \n geog | geography(MultiPolygon,4326) | \n summary | text | \n url | text | \nIndexes:\n "political_pkey" PRIMARY KEY, btree (gid)\n "political_geog_idx" gist (geog)\n<\/code><\/pre>\n1.6 - Query All Kingdoms<\/h5>\n
SELECT name, claimedby, gid FROM kingdoms;\n<\/code><\/pre>\nname | claimedby | gid \n------------------+---------------+-----\n The North | Stark | 5\n The Vale | Arryn | 8\n The Westerlands | Lannister | 9\n Riverlands | Tully | 1\n Gift | Night's Watch | 3\n The Iron Islands | Greyjoy | 2\n Dorne | Martell | 6\n Stormlands | Baratheon | 7\n Crownsland | Targaryen | 10\n The Reach | Tyrell | 11\n(10 rows)\n<\/code><\/pre>\n1.7 - Query All Location Types<\/h5>\n
location<\/code>\u200b table.<\/p>\nSELECT DISTINCT type FROM locations;\n<\/code><\/pre>\ntype \n----------\n Landmark\n Ruin\n Castle\n City\n Region\n Town\n(6 rows)\n<\/code><\/pre>\nlocation<\/code>\u200b entity types.<\/p>\n\\q<\/code>\u200b.<\/p>\nStep 2 - Setup NodeJS project<\/h3>\n
2.0 - Clone Starter Repository<\/h5>\n
git clone -b backend-starter https:\/\/github.com\/triestpa\/Atlas-Of-Thrones\ncd Atlas-Of-Thrones\nnpm install\n<\/code><\/pre>\n\n
.eslintrc.js<\/code>\u200b config.<\/p>\n<\/blockquote>\n2.1 - Add .env file<\/h5>\n
.env<\/code>\u200b file to the project root in order to provide environment variables (such as database credentials and CORs configuration) for the Node.js app to use.<\/p>\n.env<\/code>\u200b file with sensible defaults for local development.<\/p>\nPORT=5000\nDATABASE_URL=postgres:\/\/patrick:@localhost:5432\/atlas_of_thrones?ssl=false\nREDIS_HOST=localhost\nREDIS_PORT=6379\nCORS_ORIGIN=http:\/\/localhost:8080\n<\/code><\/pre>\nindex.js<\/code>\u200b file with the following contents is in the project root directory.<\/p>\nrequire('dotenv').config()\nrequire('.\/server')\n<\/code><\/pre>\n.env<\/code>\u200b into the process environment, and will start the app defined in the server<\/code>\u200b directory. Now that everything is setup, we're (finally) ready to actually begin building our app!<\/p>\n\n
Step 3 - Initialize basic Koa server<\/h3>\n
3.0 - Import Dependencies<\/h5>\n
server\/index.js<\/code>\u200b to begin setting up our server.<\/p>\nconst Koa = require('koa')\nconst cors = require('kcors')\nconst log = require('.\/logger')\nconst api = require('.\/api')\n<\/code><\/pre>\n3.1 - Initialize App<\/h5>\n
server\/index.js<\/code>\u200b.<\/p>\n\/\/ Setup Koa app\nconst app = new Koa()\nconst port = process.env.PORT || 5000\n\n\/\/ Apply CORS config\nconst origin = process.env.CORS_ORIGIN | '*'\napp.use(cors({ origin }))\n<\/code><\/pre>\n3.2 - Define Default Middleware<\/h5>\n
app.use<\/code>\u200b. These functions will be applied to every request. The first function will log the response times, and the second will catch any errors that are thrown in the endpoint handlers.<\/p>\nserver\/index.js<\/code>\u200b.<\/p>\n\/\/ Log all requests\napp.use(async (ctx, next) => {\n const start = Date.now()\n await next() \/\/ This will pause this function until the endpoint handler has resolved\n const responseTime = Date.now() - start\n log.info(${ctx.method} ${ctx.status} ${ctx.url} - ${responseTime} ms<\/code>)\n})\n\n\/\/ Error Handler - All uncaught exceptions will percolate up to here\napp.use(async (ctx, next) => {\n try {\n await next()\n } catch (err) {\n ctx.status = err.status || 500\n ctx.body = err.message\n log.error(Request Error ${ctx.url} - ${err.message}<\/code>)\n }\n})\n<\/code><\/pre>\n\n
3.3 - Add Logger Module<\/h5>\n
log.info<\/code>\u200b and log.error<\/code>\u200b instead of console.log<\/code>\u200b in the above code. In Node.js projects, it's really best to avoid using console.log<\/code>\u200b on production servers, since it makes it difficult to monitor and retain application logs. As an alternative, we'll define our own custom logging configuration using winston<\/a>.<\/p>\nserver\/logger.js<\/code>\u200b.<\/p>\nconst winston = require('winston')\nconst path = require('path')\n\n\/\/ Configure custom app-wide logger\nmodule.exports = new winston.Logger({\n transports: [\n new (winston.transports.Console)(),\n new (winston.transports.File)({\n name: 'info-file',\n filename: path.resolve(__dirname, '..\/info.log'),\n level: 'info'\n }),\n new (winston.transports.File)({\n name: 'error-file',\n filename: path.resolve(__dirname, '..\/error.log'),\n level: 'error'\n })\n ]\n})\n<\/code><\/pre>\nwinston<\/code>\u200b package. The configuration will forward our application logs to two locations - the command line and the log files. Having this centralized configuration will allow us to easily modify logging behavior (say, to forward logs to an ELK server) when transitioning from development to production.<\/p>\n3.4 - Define "Hello World" Endpoint<\/h5>\n
server\/api.js<\/code>\u200b file and add the following imports.<\/p>\nconst Router = require('koa-router')\nconst database = require('.\/database')\nconst cache = require('.\/cache')\nconst joi = require('joi')\nconst validate = require('koa-joi-validate')\n<\/code><\/pre>\nkoa-router<\/code>\u200b module.<\/p>\nconst router = new Router()\n<\/code><\/pre>\n\/\/ Hello World Test Endpoint\nrouter.get('\/hello', async ctx => {\n ctx.body = 'Hello World'\n})\n<\/code><\/pre>\nmodule.exports = router\n<\/code><\/pre>\n3.5 - Start Server<\/h5>\n
server\/index.js<\/code>\u200b.<\/p>\n\/\/ Mount routes\napp.use(api.routes(), api.allowedMethods())\n\n\/\/ Start the app\napp.listen(port, () => { log.info(Server listening at port ${port}<\/code>) })\n<\/code><\/pre>\n3.6 - Test The Server<\/h5>\n
npm start<\/code>\u200b. You should see the output Server listening at port 5000<\/code>\u200b.<\/p>\nhttp:\/\/localhost:5000\/hello<\/code>\u200b in your browser. You should see a "Hello World" message in the browser, and a request log on the command line. Great, we now have totally useless API server. Time to add some database queries.<\/p>\nStep 4 - Add Basic Postgres Integraton<\/h3>\n
4.0 - Connect to Postgres<\/h5>\n
server\/database.js<\/code>\u200b file, we'll add the following code to connect to our database based on the defined environment variables.<\/p>\nconst postgres = require('pg')\nconst log = require('.\/logger')\nconst connectionString = process.env.DATABASE_URL\n\n\/\/ Initialize postgres client\nconst client = new postgres.Client({ connectionString })\n\n\/\/ Connect to the DB\nclient.connect().then(() => {\n log.info(Connected To ${client.database} at ${client.host}:${client.port}<\/code>)\n}).catch(log.error)\n<\/code><\/pre>\nnpm start<\/code>\u200b. You should now see an additional line of output.<\/p>\ninfo: Server listening at 5000\ninfo: Connected To atlas_of_thrones at localhost:5432\n<\/code><\/pre>\n4.1 - Add Basic "NOW" Query<\/h5>\n
server\/database.js<\/code>\u200b, add the following code at the bottom -<\/p>\nmodule.exports = {\n \/** Query the current time *\/\n queryTime: async () => {\n const result = await client.query('SELECT NOW() as now')\n return result.rows[0]\n }\n}\n<\/code><\/pre>\nSELECT 1;<\/code>\u200b) on our Postgres database: retrieving the current time.<\/p>\n4.2 - Connect Time Query To An API Route<\/h5>\n
server\/api.js<\/code>\u200b add the following route below our "Hello World" route.<\/p>\n\/\/ Get time from DB\nrouter.get('\/time', async ctx => {\n const result = await database.queryTime()\n ctx.body = result\n})\n<\/code><\/pre>\n\/time<\/code>\u200b, which will call our time Postgres query and return the result.<\/p>\nnpm start<\/code>\u200b and visit http:\/localhost:5000\/time<\/code>\u200b in the browser. You should see a JSON object containing the current UTC time. Ok cool, we're now serving information from Postgres over our API. The server is still a bit boring and useless though, so let's move on to the next step.<\/p>\nStep 5 - Add Geojson Endpoints<\/h3>\n
\n
5.0 - Add GeoJSON Queries<\/h5>\n
server\/database.js<\/code>\u200b file, add the following functions under the queryTime<\/code>\u200b function, inside the module.exports<\/code>\u200b block.<\/p>\n\/** Query the locations as geojson, for a given type *\/\ngetLocations: async (type) => {\n const locationQuery = `\n SELECT ST_AsGeoJSON(geog), name, type, gid\n FROM locations\n WHERE UPPER(type) = UPPER($1);`\n const result = await client.query(locationQuery, [ type ])\n return result.rows\n},\n\n\/** Query the kingdom boundaries *\/\ngetKingdomBoundaries: async () => {\n const boundaryQuery = `\n SELECT ST_AsGeoJSON(geog), name, gid\n FROM kingdoms;`\n const result = await client.query(boundaryQuery)\n return result.rows\n}\n<\/code><\/pre>\nST_AsGeoJSON<\/code>\u200b function from PostGIS in order to convert the polygons and coordinate points to browser-friendly GeoJSON. We are also retrieving the name and id for each entry.<\/p>\n\n
$1<\/code>\u200b as a placeholder in the query string and passing the type as a parameter to the client.query<\/code>\u200b call. This is important since it will allow the Postgres to sanitize the "type" input and prevent SQL injection attacks.<\/p>\n<\/blockquote>\n5.1 - Add GeoJSON Endpoint<\/h5>\n
server\/api.js<\/code>\u200b file, declare the following endpoints.<\/p>\nrouter.get('\/locations\/:type', async ctx => {\n const type = ctx.params.type\n const results = await database.getLocations(type)\n if (results.length === 0) { ctx.throw(404) }\n\n \/\/ Add row metadata as geojson properties\n const locations = results.map((row) => {\n let geojson = JSON.parse(row.st_asgeojson)\n geojson.properties = { name: row.name, type: row.type, id: row.gid }\n return geojson\n })\n\n ctx.body = locations\n})\n\n\/\/ Respond with boundary geojson for all kingdoms\nrouter.get('\/kingdoms', async ctx => {\n const results = await database.getKingdomBoundaries()\n if (results.length === 0) { ctx.throw(404) }\n\n \/\/ Add row metadata as geojson properties\n const boundaries = results.map((row) => {\n let geojson = JSON.parse(row.st_asgeojson)\n geojson.properties = { name: row.name, id: row.gid }\n return geojson\n })\n\n ctx.body = boundaries\n})\n<\/code><\/pre>\n5.2 - Test the GeoJSON Endpoints<\/h5>\n
geojsonpreview<\/code>\u200b directory.<\/p>\nnpm start<\/code>\u200b) and open http:\/\/localhost:5000\/kingdoms<\/code>\u200b in your browser to download the kingdom boundary GeoJSON. Paste the response into the textbox in the "geojsonpreview" web app, and you should see an outline of each kingdom. Clicking on each kingdom will reveal the geojson properties for that polygon.<\/p>\n
![\"\"](\"https:\/\/cdn.patricktriest.com\/blog\/images\/posts\/got_map\/geojson_preview.jpg\")
\u200b<\/p>\n