python and post gis question

Xristos Xristoou saxri89 at gmail.com
Sat Jun 17 08:20:36 EDT 2017


Τη Παρασκευή, 16 Ιουνίου 2017 - 10:59:10 μ.μ. UTC+3, ο χρήστης Xristos Xristoou έγραψε:
> I have create a python script where use post gis queries to automate some intersection tasks using postgis database.
> 
> in my database I have polygons,points and lines.
> 
> here my snippet code 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 post gis sql query work 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 ?

what you mean where?



More information about the Python-list mailing list