7

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?

  • Please specify which database. Postgres, Oracle, MS SQL Server, MySQL..... – GeoSpatialEarth.in Mar 19 '17 at 11:51
  • Welcome to GIS SE! As a new user please take the tour to learn about our focused Q&A format. Which database and which web map are you referring to? Without specifics your question is too broad for this format. Please edit your question to include more specific information about what software you're using, what you've tried, and where you are stuck. – Midavalo Mar 19 '17 at 15:14
8

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.

Depending on how complex your data and what you know how to write code in, you could write something basic in a server programming language, like Python or JavaScript and run it in a light framework, like Flask or Feathers.

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.

2

Here's a useful script by Bryan McBride. It's a PHP script that returns geojson from PostgreSQL-PostGIS database.

p.s. also, check out his bootleaf template.

0

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 ABC.

Demo is hosted at https://sql2geojson.herokuapp.com/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.