1
I am working on a spatial query and have two tables with features of Geometrytype ONLY Polygon or Multipolygon:
ZONING TABLE:
select ST_GeometryType (geom), count (*)
from
legislacao_urbanistica_municipal.prod_lu_zon_zoneamento
group by ST_GeometryType (geom)
This query returns to me:
"St_multipolygon" "13"
"St_polygon" "606"
SQUARE TABLE
select ST_GeometryType (geom), count (*)
from
cadastro_tecnico_municipal.prod_ct_ctm_quadra_ctm
group by ST_GeometryType (geom)
This query returns to me:
"St_multipolygon" "110"
"St_polygon" "17661"
Note that there is only Polygon or Multipolygon.
However, when I give a St_intersection, my return contains points, lines and polygons and multis.... Of all! And it’s getting in my way:
This is the query that makes the intersection:
create materialized view legislacao_urbanistica_municipal.lu_lei_7166_zoneamento_quadra_vw as
select row_number() over (order by id_tipo_zoneamento) as id,
ST_Intersection(ST_Multi(quadra.geom),ST_Multi(z_7166.geom)) as geom,
z_7166.id_tipo_zoneamento
from legislacao_urbanistica_municipal.prod_lu_zon_zoneamento as z_7166,
cadastro_tecnico_municipal.prod_ct_ctm_quadra_ctm as quadra
where ST_Intersects (z_7166.geom , quadra.geom)
And the return of her geometry types:
select ST_GeometryType (geom), count (*)
from
legislacao_urbanistica_municipal.lu_lei_7166_zoneamento_quadra_vw
group by ST_GeometryType (geom)
"St_geometrycollection" "195"
"St_linestring" "3"
"St_multilinestring""41"
"St_multipoint" "2"
"St_multipolygon" "783"
"St_point" "13"
"St_polygon" "18782"
The problem is that from this table I make another query of Intersection and, as it has Geometrycollection in it, Pgadmin gives me error:
SELECT h.id_zona_homogenea_tgc,
z.id_tipo_zoneamento,
z.sigla_zoneamento
FROM tributario.prod_tr_lim_zona_homogenea_tgc as h,
(
select a.id_tipo_zoneamento,
b.sigla_zoneamento,
a.geom
from legislacao_urbanistica_municipal.lu_lei_7166_zoneamento_quadra_vw a,
legislacao_urbanistica_municipal.prod_lu_zon_tipo_zoneamento b
where a.id_tipo_zoneamento = b.id_tipo_zoneamento
) as z
where ST_Intersects(h.geom,z.geom)
group by h.id_zona_homogenea_tgc, z.id_tipo_zoneamento, z.sigla_zoneamento
ORDER BY id_zona_homogenea_tgc
ERROR: ERROR: Report Operation called with a LWGEOMCOLLECTION type. This is Unsupported. HINT: Change argument 2: 'GEOMETRYCOLLECTION(POINT(612590.299990058 7794557.73136217),POLYGON((612588.6... SQL state: XX000
I’ve tried some solutions (Dump, cast, Multi), but it’s not rolling. Someone knows the reason and some solution?