[postgis-users] Casting a view

[Marcin Mionskowski]

1. To cast resulting geom to linestring try "st_intersection(adoquin.geom,pol.geom)::geometry(Linestring,32614)" in your SELECT clause.

2. && operator will check if bounding boxes of your geoms intersects. Your check on ST_IsEmpty(..) is basicaly the same as ST_Intersects(...) check, and the second does not involve ST_Intersection(...). I think, that the query should perform better with && combined with ST_Intersects (in my testing it is ~25% faster).
If your intersections can be a multilinestrings or points, then IMO you need WHERE condition "and geometrytype(st_intersection(adoquin.geom,pol.geom))='LINESTRING'". If you add this to your query, then try its performance with ST_IsEmpty() and ST_Intersects() checks.

Regards,
Marcin


On Fri, 25 May 2018 21:42:54 +0200, Nahum Castro <nahumcastro at gmail.com> wrote:

> Hello all.
>
> Can you help me on the next:
>
> I can create a materialized view like this to extract data from other
> tables within a polygon,
>
> query:
> create materialized view vm_adoquin as
> select adoquin.gid,adoquin.id,adoquin.control,st_intersection(adoquin.geom,
> pol.geom) as geom
> from  adoquin, municipio pol
> where pol.municipio = 23
> and adoquin.geom && pol.geom
> and ST_IsEmpty(ST_Intersection(adoquin.geom, pol.geom)) != TRUE;
>
> the resulting materialized view has geom type GEOMETRY but I need the geom
> type as LINESTRING
>
> How I can cast the view if I know that
> geometrytype(st_intersection(autopista.geom, pol.geom))=LINESTRING
>
> but can't use
>
> st_intersection(autopista.geom,
> pol.geom)::geometry(geometrytype(st_intersection(autopista.geom,
> pol.geom)), 32614) to cast because can't use functions in type conversions.
>
>
>
> Thanks in advance.
> Nahum