1

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 ?

2

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.

  • @ThingumaBobthat look great but can you show me an example with my code in sql where I use ?I talk that because I am very new and confused – jessie jes Jun 17 '17 at 23:01
  • @jessiejes do you still need help? look closely, I just altered the table creation part in your code: in 'sql', replace that first part with above query, and near the end of your 'sql' remove both the part where you add the column 'my_area' and where you set it to st_area. – ThingumaBob Jun 18 '17 at 21:03
  • yes maybe more because firs time to use POSTGIS and i am more confused because i use and python....show me if you can complete example with my code to can test it?...thnx you btw you are very good i know undestard where i need but i ant to execute yet – jessie jes Jun 19 '17 at 13:58
  • @jessiejes okay I edited my answer. – ThingumaBob Jun 20 '17 at 12:06
  • realy thnx you i will test it now and i ping you if tesi is success or not thnx you again – jessie jes Jun 20 '17 at 15:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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