[postgis-users] Casting a view

[Tumasgiu Rossini]

Hi,

To test the geometry output type you can also call st_relate to examine the
spatial relations between the two geometries using the DE-9IM model.
http://postgis.net/docs/using_postgis_dbmanagement.html#DE-9IM

Le mar. 29 mai 2018 à 08:22, Marcin Mionskowski <mionskowskimarcin at gmail.com>
a écrit :

> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20180529/ff3276d4/attachment.html>