Postgresql: Intersection between polygons returns lines and points

Asked

Viewed 20 times

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?

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.