Get your PostGIS data ready for Mapbox Studio

This guide will outline two methods for preparing data from your PostGIS database to upload to Mapbox Studio:

Export your data with QGIS — connect QGIS to your PostGIS database and save selected tables as Shapefiles.
Query and export your data with ogr2ogr — query your database and create GeoJSON files.

This guide will use QGIS, a free, open source GIS. Download and install QGIS before getting started.

Export PostGIS data with QGIS

If you prefer interacting with your PostGIS database via a graphical user interface, we recommend using QGIS to connect to your database to export your data. If you're comfortable writing your own queries, skip to the next section.

Connect to PostGIS

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

screenshot of QGIS on how to add PostGIS Layers

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

screenshot of QGIS showing Add PostGIS Table dialogue

Now enter:

A name for this connection.
The host name. The default is localhost.
Port. The default is 5432.
Your PostGIS database name.
The username for the database.

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

screenshot of QGIS showing how to create a New PostGIS connection

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

screenshot of QGIS showing Connect button

Select tables

You should see a label appear in the Schema column. The label in the screenshot below is 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 the Add button on the bottom left of the dialog box.

animated GIF of the process for selecting tables in the Add PostGIS Tables dialogue

View data

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

screenshot of QGIS showing data visualized

Save as Shapefile

Next, export your data as a Shapefile so you can upload it to Mapbox Studio later. Make sure your Layers panel is visible in QGIS by checking Layers under the View > Panels tab.

screenshot of QGIS showing how to make layers panel visible

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

screenshot of QGIS showing how to save as

This will open the Save vector layer as... panel. Make sure that you set the Format to ESRI Shapefile. Next, click the Browse button to navigate to the folder where you wish to save your file locally. Once you've navigated to your desired destination directory, create a new empty folder to house your shapefiles and give it a name. Select your new folder, then click OK.

screenshot of QGIS showing Save vector layer as dialogue

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

screenshot of QGIS successful save message

psql -d natural_earth \
  \dt;

Choose columns

The example code below queries all the columns in tables named ne_10m_minor_islands table.


  \d+ ne_10m_minor_islands;

Reformat and query

With one command you can convert your data into GeoJSON format, re-project from EPSG:3857 to EPSG:4326, save the new GeoJSON file as minor_islands.json, and query 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";