5

I'm working with PostgreSQL 14.1 with PostGIS 3.1. I've got two tables:

  1. grid that holds a 10x10 km UTM grid, using field utm as PK.
  2. species that holds fields name (for the species name), utm as a FK, and status (with the status of the species in that particular utm cell).

I've built the following query:

SELECT
 s.name, s.status, g.utm, g.geom
 FROM 
 species s
 JOIN grid g
 ON g.utm = s.utm;

But I need separate tables or views for each species. How could it be achieved?

2 Answers 2

7

You need to use procedural language PL/pgsql(https://www.postgresql.org/docs/12/plpgsql.html), and loop over all your species.

Use format() function (https://www.postgresql.org/docs/current/functions-string.html) to dynamically define table name.

You can start by :

DO $$
DECLARE r record;
BEGIN
    FOR r IN  SELECT DISTINCT name FROM species
    LOOP
        RAISE INFO 'Create table for %', r.name;
        EXECUTE FORMAT ('CREATE TABLE %I AS (SELECT  s.name, s.status, g.utm, g.geom FROM species s JOIN grid g ON g.utm = s.utm WHERE s.name = %L )'
                , 'specie_'||r.name, r.name);
     --If needed, replace CREATE TABLE above by CREATE VIEW
     --Add here for constrains, index, etc..
    END LOOP;
END
$$;

1
  • 1
    It works smooth and fast, creating all the tables with no need to type each species' name.
    – jpinilla
    Commented Feb 11, 2022 at 16:47
6

Simplest thing to do is to literally create a View for each species:

CREATE OR REPLACE VIEW <species_x> AS (
  SELECT
      s.name,
      s.status,
      g.utm,
      g.geom::GEOMETRY(POLYGON, <SRID>)
  FROM
      species AS s
  JOIN
      grid AS g USING (utm)
  WHERE
      s.name = '<species_x>
);

However:

Instead of to automate creation, update and deletion of hundreds of relations, a function to return data for a given species would be more dynamic; here a naïve example:

CREATE OR REPLACE FUNCTION SpeciesStatusMap (
  species TEXT
) RETURNS TABLE AS (
  name   TEXT,
  status TEXT,
  utm    INT,
  geom   GEOMETRY(POLYGON, <SRID>)
) LANGUAGE SQL PARALLEL SAFE STRICT AS
  $$
    SELECT
      s.name,
      s.status,
      g.utm,
      g.geom::GEOMETRY(POLYGON, <SRID>)
    FROM
      species AS s
    JOIN
      grid AS g USING (utm)
    WHERE
      s.name = $1
    ;
  $$
;

and use it as:


SELECT *
FROM   SpeciesStatusMap('<species_x>')
;

Note: replace all text in the query wrapped in <> with your specific values.

The latter approach is by far more versatile and perfectly suitable for medium sized data-sets.


Note that you want to ensure you have an index in place on grid.utm and species.name!

3
  • 1
    The problem with this approach is that one must type each species' name. Having several hundreds of species, it takes sooooo much time. The loop proposed by @JB_D's answer makes the work automatically.
    – jpinilla
    Commented Feb 11, 2022 at 16:59
  • Sure, but you end up with having hundreds of Views to maintain. The function gives you the exact performance of a View, for whatever species you are looking for. Your choice. I don't mind if you like having Views better.
    – geozelot
    Commented Feb 11, 2022 at 18:05
  • Now I understand. Very relevant. Thank you very much for explaining.
    – jpinilla
    Commented Feb 12, 2022 at 10:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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