How To Download Postgis For Mac

admin

If you need help using these packages (the installers and buildbot binaries), please ask on our users mailing list.

  1. How To Download Postgis For Mac Download
  2. Postgis Windows Download
  3. Postgresql Postgis Download

Postgres.app is a full featured PostgreSQL installation packaged as a standard Mac app. Postgres.app The easiest way to get started with PostgreSQL on the Mac. If you’re new to Postgres, this is the file you should download. It includes everything you need to get started with PostgreSQL and PostGIS. Postgres.app with PostgreSQL 13. The StackBuilder package manager (used to download and install drivers, tools and applications to complement your PostgreSQL installation). The sections that follow provide information about using the PostgreSQL 12.0 installer. It also provides information about.

The StackBuilder package manager (used to download and install drivers, tools and applications to complement your PostgreSQL installation). The sections that follow provide information about using the PostgreSQL 12.0 installer. It also provides information about. 2) Download the version 9.1.6 of postgresql 3) After the installation, run the Stack Builder 4) Stack Builder: select your postgresql installation and click next 5) Select Categories - Spatial Extensions - PostGIS 1.5 for PostgreSQL 9.1 v1.5.3-1 6) Finish the installation. Perhaps you have to uninstall your previous installation using homebrew.

PostGIS 3.0.2 came out August 15th, 2020. Binaries for versions of PostgreSQL 9.6-12 (64-bit) available in Unreleased PostGIS Versions and OSGeo downloads), installers for 9.6-12 (64-bit) are available on stackbuilder and OSGeo downloads. PostGIS 3.0.2 bundle includes. How to install PostGIS on Mac OS X PostGIS is a powerful extension to the PostgreSQL database that adds support for geometry types and geospatial functions such as point, line, polygon, distance, area, union, intersection, etc. POSTGIS ODBC DRIVERS FOR MAC - Then use the csv dump to import the data. The bit driver is of alpha quality. I'll post new binaries once this issue is resolved. The PostGIS version may.

If you find a bug with the installers or the buildbot packages, please report these on our Ticket Tracker under component: build/upgrade/install with keyword: windows.

The installers are designed to work with EnterpriseDb PostgreSQL distributions.

PostGIS 3.0.2 came out August 15th, 2020. Binaries for versions of PostgreSQL 9.6-12 (64-bit) available in Unreleased PostGIS Versions and OSGeo downloads), installers for 9.6-12 (64-bit) are available on stackbuilder and OSGeo downloads.

PostGIS 3.0.2 bundle includes:

  • PostGIS 3.0.2 with raster, GEOS 3.8.1, PROJ4 5.2.0, SFCGAL support (1.3.8), address_standardizer, topology
  • PostGIS Tiger geocoder extension - Tiger 2019
  • pgRouting 3.1 pgRouting 3.1.0
  • Commandline raster loader (raster2pgsql), shapefile import/export (shp2pgsql,pgsql2shp)
  • Commandline osm2pgrouting 2.3.6 for loading data from .osm files into pgRouting routable format
  • GUI: shp2pgsql-gui which has both import and export support for geometry/geography
  • ogrfdw 1.0.12 - spatial foreign data wrapper forreading both spatial (spatial columns become postgis geometry) and non-spatial dataIMPORT FOREIGN SCHEMA support
  • GDAL 2.4.4 with OpenJPEG 2.3.1 (JPEG 2000), ODBC, Curl, SQLite3 (for GeoPackage and OSM support), excel (XLS) (via FreeXL 1.0.5), libreoffice, XLSX spreadsheet (via expat 2.2.7) (used by both PostGIS raster and ogrfdw)
  • pgpointcloud 1.2.0 (only the 64-bit packages have this)for querying LIDAR point cloud and in/out functions to convert to PostGIS geometry

The simplest way to get PostGIS on Windows for the EnterpriseDb Windows PostgreSQL distribution is using the StackBuilder.Refer to An Almost Idiot’s Guide Installing PostGIS on Windows 1. Download and install PostgreSQL from EnterpriseDB. 2. Run the “StackBuilder” utility and install the PostGIS add-on.

If you want to use the zip, setup, and setup source files directlythey are available:

  • Zip and Installer files The .zip files are just the binaries you can copy intoyour PostgreSQL installation and then enable in each database as usual. The exe are standard windows setup for both 32-bit and 64-bit PostgreSQL.

  • Source Setup files The source setup files are useful if you want to build your own NSIS installer or create a single setup for your own projectthat also installs PostGIS. The Binary zip files should contain all the binary files you need to customize your own setup minus the documentation.You can also just extract the .exe setup file to get the binaries, but the extract sometimes gets mangled leaving out some of the postgis-gui subfolders.

If you are using BigSQL distribution use the PostGIS available via the BigSQL distribution.If you are on BigSQL (PostgreSQL 9.5+) and need items not available in BigSQL distribution (e.g. SFCGAL, pgRouting, or development versions of PostGIS),you can use the binaries in the experimental section. Note the path you copy to in BigSQL is a little different (e.g. postgresql/lib instead of lib).

If you need SFCGAL support, you need to use the PostGIS packaged in the zips in the experimental section instead of the BigSQL PostGIS.Also make sure not to overwrite any files that come with BigSQL distribution.

PostgreSql is already the second most loved database according to the 2018 StackOverflow survey and it’s the fastest growing in popularity . It’s billed as the “world’s most advanced open source relational database”, and with its comprehensive adherence to SQL standards compliance, it’s easy to see why users love it. But when you add the spatial SQL support that comes with the PostGIS extension, there truly is nothing else out there like it (at least not for free).

“But I already use GIS why would I bother with a database.”

For those not already convinced that they need a database to do GIS work, here are my top 4 reasons to start using one:

  1. Reproducibility – rather than clicking buttons, you can completely script an analysis with just SQL. Need to redo the analysis and tweak a few things? Update the code and run it again. This can also help automate repetitive tasks and skips laborious menus and forms.
  2. Freedom – speaking of buttons, what if you need to accomplish something across several different layers that isn’t supported by a GUI option? With SQL you get freedom to stitch together the analysis you need.
  3. Less limitations – But what if your data doesn’t fit into the 2GB limit of a shapefile, or the the process is too slow or never finishes in your GIS? Modern databases like Postgres are designed to deal with large datasets, and process millions of records much faster than a traditional GIS, or even scripts with Python or R.
  4. Organization – Have you ever opened your own GIS project, and started crying when you look at the mess of temporary layers that were created in the pursuit of that one perfect map? I can often run a set of several steps with either a single SQL query or SQL script. This saves me time, helps keep me organized and makes it easier to re-run tasks. Layers are also easily organized in schemas or distinct databases, and every table can store metadata (comments) about itself and every column it contains.

If you’re still not convinced, check out this great article all about PostGIS:

So, whether you need local spatial processing for data science or GIS, or you plan to support spatial queries in an application backend, PostgreSql/PostGIS is a worthy tool. In this post, I’ll cover the basics of getting PostgreSql with the PostGIS extension installed on both Mac and Windows for development purposes (in production, we use Ubuntu Linux, which is another post), and look at ways you can also manage your database, import spatial data and see the results of a spatial query. From here on, I will also refer to this combination just as “Postgres”.

Mac Installation

The official documentation for Postgres lists 2 options for Windows installation. For MacOS, it’s six. That can be a barrier for users just getting started with Postgres. The official PostGIS documentation summarizes the options better; if you want a dead simple way to get started, use Postgres.app. For more advanced users, use Homebrew.

It used to be the case that Homebrew was the better option if you needed support for other PostgreSql extensions, like PgRouting or Mapbox Vector Tiles (MVT) support. However, Postgres.app has been adding support for other extensions, and now comes with support for PostGIS, PLV8 and MVT already built in. If you want PgRouting, you can use Homebrew to install the dependencies it needs, and build it from source.

To install Postgres.app, it’s as simple as downloading and moving the application to your Applications folder. The 1-2-3 installation steps on the Postgres.app docs couldn’t be easier to follow:

Make sure to follow step #3, as you will need the aforementioned command line tools to import shapefiles and geojson into PostGIS. By default, Postgres.app will startup automatically at login, and you’ll be able to see this if you click the elephant icon, and select “Open Postgres”:

For Homebrew users I’m going to assume you know what you’re doing, but here’s a nice rundown of what to do:

Make sure to follow all the instructions, including the database creation step. This will ensure you have the same setup as the Postgres.app users. Homebrew users will also have to manually install the PostGIS extension:

Windows Installation

For Windows, the choice seems more clear about which software package to use for installation, as there are only two choices, and only one of those is supported with complete official installers from PostGIS. So we recommend the Enterprise DB installer, which you can download for here:

Take the latest version of Postgres available (11 as of 3/2019). Run the installer and accept all the defaults. Make sure to enter a password that you can remember or is stored appropriately. After the installation completes, you will be prompted about whether you would like to install any additional software with “Stack Driver”. Select the only database in the list and hit next.

Open the Categories -> Spatial Extensions, and check the box for the PostGIS bundle. You can check if the version available here matches the most recent release of PostGIS here:

If the version is recent enough, you can just install the version from Stack Driver, otherwise you’ll need to download the installer directly from the PostGIS page. Accept all the defaults, but select the option to create a “spatial database” and make the name of the database the same as your username. This will ensure that the instructions below can be followed by both the Mac and Windows users.

The initial database you create with this method will already have the PostGIS extension enabled, but in the future, when you create a new database, you will need to manually enable the extension, which is covered in the next section on psql.

Additionally, to make the Postgres/Postgis command line tools available globally, you can run a script that is included with the install. You can add the script to your system-wide shell startup script (not covered here, see the reference for more), but for now, when you open a new command prompt, execute the following:

Note that the quotes are very important here, because “Program Files” has a space in it, the command won’t execute correctly without the quotes. You will need to execute this command every time a new terminal is opened unless you add it to a system-wide shell startup script.

PSQL

MacFor

Once you have Postgres installed (with either method), you will now also have the Postgres command line client installed, which is called “psql”. Mac users will need to activate the PostGIS extension in the sample database that was created (and Windows users will need to know how to do this for additional databases). Psql is not necessarily the easiest way to interact with your databases, but it can do everything you need. In the next step, we’ll install PgAdmin and look at other programs, but for now, it’s good to know that psql at least exists.

For Mac users run:

from the Terminal. You can do this because running just “psql” uses standard defaults, and is the same as running:

If you’re using Postgres.app, you can also double click on the database, and you should automatically connect to the Postgres. If you did not double click on the Postgres.app database, after starting “psql”, you’ll then need to connect to the “database”. This can often be confusing for new users, who think they already have connected to the database. But in reality, we’ve connected to postgres, within which we can create individual databases. For instance, each project you work on might warrant its own database. Run these commands to connect to the sample database:

For Windows users, search for the “SQL Shell (psql)” program (it was also installed by the EnterpriseDb installer), and accept some of the defaults, but supply your username as the database name, and enter the password you created for the postgres user.

Now, that both Windows and Mac users are in the same place, we can execute the final command we need:

Note for reference, these are what the psql commands mean:

  • “l” is the command to list the available databases
  • “c” is used to connect to the database
  • “q” is the command to quit
  • For a complete reference to commands used in psql, see this great psql guide

Loading Spatial Data

The next step is to actually add or “import” some tables with spatial data. We’ll use two layers, a state polygon layer from the US Census, and a point file of wind turbine locations from the USGS. With these layers we can do a classic “point in poly” query, finding all points within a state polygon. To start, download the “cb_2017_us_state_20m.zip” state shapefile from:

”Unzip the file, and then we’ll use shp2pgsql, a command line utility that’s already been installed along with Postgis, regardless of the method you followed. You can find the documentation about this command line tool in the official Postgis documentation:

The command we’ll need for the state shapefile on the Mac is:

For Windows, it’s slightly different

Note that regardless of how the output is displayed, this command does prompt you to enter your password, so enter it and hit enter when prompted. Also note that the “-I” flag is used to automatically create the spatial index we’ll need to properly query this layer (see the postgis link above for all of the available flags).

Projections

How did we know to include the SRID of 4269 (the -s flag from above)? If you don’t know what the SRID is, or how to find it, you might want to review this:

For

To quickly convert from the .prj (projection file) of the shapefile to the necessary SRID, you can use this website:

Or import the file into your favorite GIS (we use QGIS) and look at the projection info.

Convert the Wind Turbines File

The next file we need is the locations of wind turbines which are available from here:

Even though there is a shapefile available, make sure to download the GeoJSON file, since this will force us to explore another tool that is indispensable for working with geographic data. “ogr2ogr” is a command line tool that’s not developed by Postgis, but is installed as a dependency. It’s part of GDAL (Geospatial Data Abstraction Library), which is used by many geospatial software projects, and can also be used directly on the command line. To import the point file on Macs do:

for Windows, you should be able to use the same exact command, but it appears the current version of the EDB Postgres installer comes with a version of ogr2ogr that lacks Postgres support! So, you can either continue by importing the shapefile instead:

or install a different version of ogr2ogr from this well known and loved package:

Note that while the above links should continue to work, the file names of the geojson and shp files may change, so look at the names of the files that get downloaded and update the above “uswtdb_****” file name to match when using these commands.

Administering Your Database (and visualizing our tables!)

Now that we’ve enabled PostGIS in our sample database and imported some tables, let’s install and open PgAdmin, which is as close to an official database administration tool as you can get for Postgres (there is no official one, but knowing how to at least use PgAdmin can come in handy as it’s probably the most popular tool, albeit not the most loved). Windows users will already have this installed. Mac users will need to head over to the download page:

It installs as a regular program, but when you open it, the current version opens in your default web browser (yes, yuck, we know, we’ll look at alternatives later). The official instructions for adding a server in PgAdmin are here:

But they are a little vague. Windows users will already have their local server added, but the instructions are useful to both users. To start, you will need to create a new connection or “server”. If you click into the “Server Dialog” section of the instructions, you’ll see this:

You’ll want to enter a name for this connection, I typically like the format:

So in this case I would use localhost10_chrismarx. Next, keep following the instructions and click the “Connection tab” . Here you will want to add localhost as the host name, your username for username, and I prefer to never click the save password dialog. Typically passwords saved in this manner are not secure.

Then click Save. Opening up the new connection you’ve added, you should be able to see this. Note that what we’re interested in are the tables we’ve added, which by default, will show up several levels down:

Maps (finally!) of your table data

If you open a new query tab in PgAdmin ( by clicking Tools > Query Tool), and enter and run (click the lightning bolt button or press F5) the following query:

and then look in the “Data Output” pane, and horizontally scroll to last column named “geom”, and click the “eye” icon in the column header (which activates the spatial viewer), you should be able to see your states table with all the US states! (If everything was done correctly…)

We can do the same for points:

Or even combine the states and points

Spatial SQL (finally!)

But we’re here to do spatial SQL! Now we can run the point in polygon query, finding out which states have the most wind turbines. You can use this query:

Which yields the following results:

How To Download Postgis For Mac Download

State | Turbine Count
TX 13396
CA 8676
IA 4445
OK 3865
KS 2951

For visualizing the results as a choropleth, we can use QGIS, which gives us this:

Use QGIS to visualize PostGIS data (and make the map above)

Getting started with QGIS is another blog post, but as far as working with Postgres is concerned, the setup is very similar to any DB admin tool. In fact, QGIS has a “DB Manager” tool for working with databases. However, lamentably, the one thing you can’t do from that particular window is actually add a DB connection 🙁

So, to get started and add a DB connection (at least as of QGIS 3.6), the easy way is to use the “Browser” tab, which makes sense, since this is the panel where you can explore sources of data to add to your project. To add a connection, right click on the “PostGIS” entry:

That will open the window for adding a new PostGIS connection:

This should look familiar after having to add a connection in PGAdmin, and you’ll need the same information. I also tend to check the “Also list tables with no geometry” option, since sometimes I need to look at or join information from tables that don’t store geometry data. The next step is to open the “DB Manager”, which you can do from the main menu:

If you open up the PostGIS item in the Providers list, you will see the same table info that you could see in PGAdmin. You can visualize the entire table by right clicking the table and selecting “Add to Canvas”. But the real fun is loading the results of a custom query. Click the “Sql Window” button, which is the table & wrench button (second from the left). To make sure that the new SQL window is opened with a connection to the correct database, make sure you database or one of the entities within your database is highlighted in the “Providers” panel.

Now you can enter the query we used earlier to find the states with the most wind turbines, with a few modifications to ensure we can actually visualize the results.

The extra outer query takes the records we got for the counts of wind turbines in each state, and joins it back with the state layer in order to also include the “geom” column in the results of our query. This is the column that actually stores the geometry associated with each record. It also uses a “right join” so that we get all the states in the final query (since we still want to see the states that have no wind turbines). For these states, we also want to return a 0 rather than a null (so we can visualize it), so we use the “Coalesce” operator to either return the “turbine_cnt” or a 0 (only when that column’s value is null).

To load the results of that query, first click “Execute” button to see/verify the tabular results, then click the “Load as new layer” checkbox to show the new layer dialog, enter a layer name, and then click the “Load” button.

Now you should see your state layer. Right click on the layer, in the “Layers” panel, select “Properties”, and then from the “Symbology” section you can apply the “Graduated” classification. To see more about this process, check out the QGIS manual :

Postgis Windows Download

Wrap Up

Phew, that was a lot of work just to run one query, but now that you’ve got the basics setup, you can unleash all of the spatial SQL power on your big datasets.

Lastly, I’ll put in a plug for DBeaver (the successor to SQuirreL-SQL), a multi-platform database IDE. Given that it can be used to administer just about any database (Postgres, MYSQL, Oracle, etc.), it’s a great tool to have in your toolbox. It’s also open source, and has 11k+ stars on GitHub. It can be downloaded from here:

Postgresql Postgis Download

I use it for my SQL development, and it has a spatial data viewer for doing quick visualizations: