[postgis-users] Casting a view

Marcin Mionskowski mionskowskimarcin at gmail.com
Mon May 28 23:20:08 PDT 2018

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.


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

More information about the postgis-users mailing list