Python PostGIS script add new field on polygons layers

Question

I have create a Python script where use PostGIS queries to automate some intersection tasks using PostGIS database.

In my database I have polygons, points and lines.

Here's​ a snippet of my script :

 try:
        if str(geomtype) == 'point':
            geomtype = 1
        elif str(geomtype) == 'linestring':
            geomtype = 2
        elif str(geomtype) == 'polygon':
            geomtype = 3
        else:
            raise TypeError()
    sql = "\nDROP TABLE IF EXISTS {0}.{1};\nCREATE TABLE {0}.{1} AS (SELECT {4},\n(st_dump(ST_CollectionExtract(st_intersection(dbgis.{2}.shape,dbgis.{3}.shape)::GEOMETRY(GEOMETRY,2345),{5}))).geom AS shape\nFROM  dbgis.{2}, dbgis.{3} WHERE st_intersects(dbgis.{2}.shape,dbgis.{3}.shape) AND {5}=3);\nCREATE INDEX idx_{2}_{3}_{6} ON {0}.{1} USING GIST (shape);\nALTER TABLE {0}.{1} ADD COLUMN id SERIAL;\nALTER TABLE {0}.{1} ADD COLUMN my_area double precision;\nUPDATE {0}.{1} SET my_area = ST_AREA(shape::GEOMETRY);\nALTER TABLE {0}.{1} ADD PRIMARY KEY (id);\nSELECT pop_g_col('{0}.{1}'::REGCLASS);\nGRANT ALL ON {0}.{1} TO GROUP u_cl;\nGRANT ALL ON {0}.{1} TO GROUP my_user;\n-- VACUUM ANALYZE {0}.{1};\n".format(schema, table, tablea, tableb, selects, geomtype, id_gen())
    return sql

This PostGIS SQL query works​ nice, but I need the new field where I want to add my_area to create only for polygons layers.

That code create for all layers (lines, points, polygons) that field my_area if layer is point or line then take value 0 I don't like that I don't need it.

How to change this code to create my_area only in polygons ?

Answer 1

I wasn´t able to test this, but you should be able to use CASE WHEN THEN END in your initial SELECT to add and populate my_area if geomtype = 3, e.g. something along the lines of this:

   CREATE TABLE {0}.{1} AS
     (SELECT subquery.*,
             CASE {3} WHEN 3 THEN (SELECT ST_Area(shape::GEOMETRY))
             END AS my_area
      FROM  (SELECT {4},
                    (st_dump(ST_CollectionExtract(st_intersection(dbgis.{2}.shape,dbgis.{3}.shape)::GEOMETRY(GEOMETRY, 2345), {5}))).geom AS shape
             FROM  dbgis.{2}, dbgis.{3}
             WHERE st_intersects(dbgis.{2}.shape, dbgis.{3}.shape)
               AND {5} = 3) AS subquery);

I wrote this out of my head and might have overseen something but I think, in general, this structure should get you going.

You could, of course, do the conditional selection within your python code, e.g. write two sql strings according to your needs and let your if ... elif ... statement return the one you need based on geomtype.


EDIT:

Important: I had to alter the query above slightly to make it work!


And this would be your sql string, use it as in your code above:

sql = "\nDROP TABLE IF EXISTS {0}.{1};\nCREATE TABLE {0}.{1} AS (SELECT subquery.*, CASE {3} WHEN 3 THEN (SELECT ST_Area(shape::GEOMETRY)) END AS my_area FROM (SELECT {4}, (st_dump(ST_CollectionExtract(st_intersection(dbgis.{2}.shape,dbgis.{3}.shape)::GEOMETRY(GEOMETRY, 2345), {5}))).geom AS shape FROM dbgis.{2}, dbgis.{3} WHERE st_intersects(dbgis.{2}.shape, dbgis.{3}.shape) AND {5} = 3) AS subquery);\nCREATE INDEX idx_{2}_{3}_{6} ON {0}.{1} USING GIST (shape);\nALTER TABLE {0}.{1} ADD COLUMN id SERIAL;\nALTER TABLE {0}.{1} ADD PRIMARY KEY (id);\nSELECT pop_g_col('{0}.{1}'::REGCLASS);\nGRANT ALL ON {0}.{1} TO GROUP u_cl;\nGRANT ALL ON {0}.{1} TO GROUP my_user;\n-- VACUUM ANALYZE {0}.{1};\n".format(schema, table, tablea, tableb, selects, geomtype, id_gen())

If this query does not work, please post your error message.