I am interested in learning how to add a PostgreSQL database to a web map so I can do functions like select by attributes or locations. I am using Leaflet API to make the web map. What are the necessary steps to integrate a PostgreSQL database into a Leaflet?
You can't talk directly from a web browser to a database, because a browser runs code on a client's computer, and that doesn't (and shouldn't) connect directly to your database.
What you need is a piece of middleware that converts a request from your code to a Postgresql query, and returns data that Leaflet can understand.
Or you can grab something bigger, like GeoServer, which already has it's API developed (it's called WFS, and is an open standard) and knows how to talk to PostGIS, including doing parameterised queries and views.
Well I just made up an API server that queries the DB (PostgreSQL or MySQL) and returns GeoJSON on-the-fly. I have tested with the Fetch API and Axios that they can be used to GET the GeoJSON (from an URL like
localhost:5000/postgres/api/<table-name> and add it as a layer to Leaflet.
You can clone the repo from Github and set some variables in
server.js to start the server. The URL changes to
localhost:5000/mysql/api/<table-name>?fields=<field-1>,<field-2>...and so on if you decide to use MySQL.
You can also filter by attributes by specifying which attributes to filter by and the filter keyword (for instance,
localhost:5000/postgres/api/test?fields=name&filter=ABC provided that you have an existing table with
test table name and have attribute
name in the table and an feature with attribute
name equal to
Demo is hosted at https://sql2geojson.herokuapp.com/