Get your PostGIS data ready for Mapbox Studio

Are you working with custom data in a PostGIS database? This workflow will show you methods for getting your data ready for Mapbox Studio. We will cover two methods:

  1. Exporting your data with QGIS — connect QGIS to your PostGIS database and save selected tables as Shapefiles.
  2. Querying and exporting your data with ogr2ogr — query your database and create GeoJSON files.

This guide will use QGIS a free, open source geographic information system. Download and install QGIS and let’s get started.

Export PostGIS data with QGIS

First, you will use QGIS to connect to your database to export your data.

Connect to PostGIS

Launch QGIS and select Layer > Add PostGIS Layers from the toolbar.

After the Add PostGIS Table(s) dialog pops up, click the New button to establish your database connection.

Now enter:

  • a name for this connection
  • the host name (default is localhost)
  • port (default is 5432)
  • your PostGIS database name
  • and the username for the database

Click the Test Connection button to make sure you are synced up, then click OK.

Back in the Add PostGIS Table(s) dialog box, click the Connect button to connect to your local database.

Select tables

You should see a label appear in the Schema column, for this example we see public. Click the arrow next to the label to expand the nested table values. Next, click to select the tables you want to show in your project.

When you’re finished click Add (located on the bottom left).

View data

You should now be able to see your data visualized in QGIS.

Save as Shapefile

Next, you will export your data as a Shapefile, a file format compatible with Mapbox Studio. Make sure your Layers panel is visible in QGIS by checking Layers under the View > Panels tab.

From the Layers panel, right-click on your data layer and then click Save As...

This will open the Save vector layer as… panel. Make sure the Format is set to ESRI Shapefile. Next, click the Browse button to move into the folder where you wish to save your file locally and click OK.

In QGIS, a light blue bar will quickly flash to indicate that your save is completed. You can now navigate to that folder on your local machine to retrieve your data.

Zip the Shapefiles

Navigate to the folder with your saved Shapefiles (.shp). Right-click on the folder containing all the files that are part of your Shapefile and Compress or zip this entire folder.

To upload Shapefiles to Mapbox Studio they must be zipped and cannot be larger than 270MB.

Query and export PostGIS data with ogr2ogr

If prefer using the command line instead of QGIS, you can use GDAL’s ogr2ogr utility to convert your data format. Using ogr2ogr and some SQL, you can also query from your database.

Choose tables

Decide what data you wish to display on your map. In this example, the PostGIS database is loaded with Natural Earth data. With the command psql -d natural_earth, I’ve entered my database and with \dt I can view the tables contained within my database.

psql -d natural_earth \

Choose columns

For this example, I’m going to query all the columns in the ne_10m_minor_islands and ne_10m_minor_islands_coastline tables. I can look at all the columns available while in my database with \d+ <tablename>.

  \d+ ne_10m_minor_islands;

Reformat and query

With one command I’ll be converting the data to the GeoJSON format, re-projecting from EPSG:3857 to EPSG:4326, saving the new GeoJSON file as minor_islands.json and minor_islands_coastline.json respectively, and querying specific tables with SQL.

ogr2ogr -f GeoJSON \
  -t_srs EPSG:4326 -s_srs EPSG:3857 \
  minor_islands.json \
  "PG:host=localhost dbname=natural_earth user=postgres" \
  -sql "select * from ne_10m_minor_islands";

ogr2ogr -f GeoJSON \
  -t_srs EPSG:4326 -s_srs EPSG:3857 \
  minor_islands_coastline.json \
  "PG:host=localhost dbname=natural_earth user=postgres" \
  -sql "select * from ne_10m_minor_islands_coastline";

GeoJSON format

I now have my tables in GeoJSON format and ready to upload to Mapbox Studio.

Next steps

Upload your data

You are now ready to upload your data! Read our article on Uploading data to you Mapbox account.

Working with large data files

If you’ve exported global data files or data that may exceed the 270 MB limit for uploading into Mapbox Studio, read our articles on Preparing large data files for Mapbox Studio.

Next article:

Manage large data files for Mapbox Studio with Tippecanoe

Manage big data with Tippecanoe.

Additional questions? Ask our support team or learn more about How Mapbox Works.