[postgis-users] Casting a view

[Nahum Castro]

Thanks.

It worrks now with good performance.

Again, thanks.
Nahum.

2018-05-29 1:20 GMT-05:00 Marcin Mionskowski <mionskowskimarcin at gmail.com>:

> 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(a
> doquin.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
>>
>


-- 
*Nahum Castro González*
Blvd. Perdigón 214, Brisas del Lago.
CP 37207
León, Guanajuato, México
Tel (477)1950304
Cel (477)1274694
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20180530/d0de4d3e/attachment.html>