The Power of PostgreSQL With Leaflet and Nodejs/Express (Part 2)
This article will show the guidelines on how to set up a PostgreSQL database with the PostGIS extension. The database created will let the user add a point, a line or a polygon geometry into the database.
To be able to follow this tutorial, all you have to do is installing PostgreSQL and pgAdmin4 on your computer. Don’t worry it’s completely free. You can find it on this link and it’s available for windows, mac and Linux users: https://www.postgresql.org/download/
Don’t forget to install the PostGIS bundle during the installation of PostgreSQL.
PostgreSQL with PostGIS
I think that everybody agree that PostgreSQL is a powerful database. What makes it more interesting for GIS development are the spatial capabilities that comes with it. In our example, we use it to store geometry data, but it can also be used to create complete routing system with multiple algorithms like Dijkstra that is used to calculate the shortest path for example.
pgAdmin4
After opening pgAdmin4, create a database with the name of your choice or use one that is already created. Then you can navigate into the table:
After this step, you just have to right click on ‘Tables’ and then on ‘Query Tool…’ . In the ‘Query editor’ at your right, insert this SQL statement and execute those queries:
In the previous step, we added these extensions to be able to use the spatial capability of PostGIS.
Next, right click on ‘Tables’ and then on ‘Query Tool…’ . In the ‘Query editor’ at your right, insert this SQL statement and execute this query:
You should receive a message saying something like ‘Query returned successfully’. As you can notice the table created is in sync with the form that we created in the part 1, cause we want to send the submitted data from the form to this database right?
Also, we added to the ‘coordinates’ column the data type ‘geography’ to be able to add spatial data to it. As you can see, we added ‘SERIAL’ that will auto increments a unique identifier for the id column. Alright, we got our database setup that’s super cool!!!
Adding geometry data to the database
Let’s say we want to add a polygon geometry to our table. Let’s use this query:
As you can see, we write the type of geometry that we want to add (polygon in this query) and just after the coordinates, we add a map projection. In this case 4326 that represent EPSG:4326 wich stands for World Geodetic System 1984 or WGS84.
If you want to add point or line geometry, check the PostGIS documentation here:
Now that we have added the polygon geometry into the table, we just have to right click on table (pointtable in this case) and click on ‘View/Edit Data’ and then on ‘All Rows’ so we can see all the data.
A little Bonus just for you
When you see a data with spatial properties on pgAdmin 4, you can visualise this data on a map. Sounds too good to be true? Well, it’s not, all you have to do is click on this button when you query the data of your table:
Enjoy!!!
Get ready for the final part where we will integrate the backend with Nodejs and Express:
