PostGIS extension

The PostGIS PostgreSQL extension adds support for storing, indexing, and querying geographic data.

Note: YSQL does not currently support GiST indexes. This is tracked in GitHub issue #1337.

Install PostGIS

macOS

There are two ways to install PostGIS on macOS:

• Download and install Postgres.app

• Or, install with Homebrew:

  brew install postgres postgis
  

Ubuntu

Add the PostgreSQL APT sources. Then, use apt to install:

sudo apt-get install postgresql-11 postgresql-11-postgis-3

CentOS

Get the YUM repository from the PostgreSQL website. Then, use yum or dnf to install:

sudo yum install postgresql11-server postgis31_11 postgis31_11-client

Install the extension

Copy the extension files to your YugabyteDB installation as follows:

cp -v "$(pg_config --pkglibdir)"/*postgis*.so "$(yb_pg_config --pkglibdir)" &&
cp -v "$(pg_config --sharedir)"/extension/*postgis*.sql "$(yb_pg_config --sharedir)"/extension &&
cp -v "$(pg_config --sharedir)"/extension/*postgis*.control "$(yb_pg_config --sharedir)"/extension

On Linux systems, PostGIS libraries have dependencies that must also be installed. Use the extensions option of the post-install tool, available in YugabyteDB 2.3.2 and later, as follows:

./bin/post_install.sh -e

Then, create the extension:

./bin/ysqlsh -c "CREATE EXTENSION postgis;"

This may take a couple of minutes.

Example

1. Get a sample PostGIS dataset:

   wget -O edmonton.zip "https://data.edmonton.ca/api/geospatial/jfvj-x253?method=export&format=Shapefile" && unzip edmonton.zip
   

2. Extract the dataset using the shp2pgsql tool. This should come with your PostgreSQL installation — it is not yet packaged with YSQL.

   shp2pgsql geo_export_*.shp > edmonton.sql
   

3. Edit the generated edmonton.sql for YSQL compatibility.

   • First, inline the PRIMARY KEY declaration for gid as YSQL does not yet support adding primary key constraints after the table creation.
   • Additionally, for simplicity, change the table name (and references to it in the associated INSERT statements) to just geo_export (in other words, remove the UUID postfix).

   The edmonton.sql file should now start as follows:

   SET CLIENT_ENCODING TO UTF8;
   SET STANDARD_CONFORMING_STRINGS TO ON;
   BEGIN;
   CREATE TABLE "geo_export" (gid serial PRIMARY KEY,
     "area_km2" numeric,
     "name" varchar(254),
     "number" numeric);
   SELECT AddGeometryColumn('','geo_export','geom','0','MULTIPOLYGON',2);
   
   INSERT INTO "geo_export" ("area_km2","name","number",geom) VALUES ...
   

4. Load the sample data.

   ./bin/ysqlsh -a -f edmonton.sql
   

5. Run some sample queries. Connect using ysqlsh and run the following:

   SELECT name, area_km2, ST_Area(geom), ST_Area(geom)/area_km2 AS area_ratio FROM "geo_export" LIMIT 10;
   

               name            |     area_km2      |       st_area        |      area_ratio
   ----------------------------+-------------------+----------------------+----------------------
   River Valley Terwillegar   | 3.077820277027079 | 0.000416617423004673 | 0.000135361192501822
   Carleton Square Industrial | 0.410191631391664 | 5.56435079305678e-05 | 0.000135652469899947
   Cy Becker                  | 1.015144841249301 | 0.000137900847258255 | 0.000135843518732308
   Elsinore                   | 0.841471068786406 | 0.000114331091817771 |  0.00013587049639468
   McLeod                     | 0.966538217483227 | 0.000131230296771637 | 0.000135773520796051
   Gainer Industrial          | 0.342464541730177 | 4.63954326887451e-05 | 0.000135475142782225
   Coronet Industrial         | 1.606907195063447 | 0.000217576340986435 | 0.000135400688760899
   Marquis                    | 9.979100854886905 |  0.00135608901739072 | 0.000135892906295924
   South Terwillegar          | 1.742840325820606 | 0.000235695089933611 | 0.000135236192576985
   Carlisle                   | 0.961897333826841 | 0.000130580966739925 | 0.000135753538499185
   (10 rows)
   

   SELECT a.name, b.name FROM "geo_export" AS a, "geo_export" AS b
   WHERE ST_Intersects(a.geom, b.geom) AND a.name LIKE 'University of Alberta';
   

           name          |          name
   -----------------------+-------------------------
   University of Alberta | University of Alberta
   University of Alberta | McKernan
   University of Alberta | Belgravia
   University of Alberta | Garneau
   University of Alberta | River Valley Mayfair
   University of Alberta | River Valley Walterdale
   University of Alberta | Windsor Park
   (7 rows)