<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body text="#000000" bgcolor="#FFFFFF">
<p><br>
</p>
<div class="moz-cite-prefix">Em 18/07/2019 10:12, Pierre Racine
escreveu:<br>
</div>
<blockquote type="cite"
cite="mid:c6b1de059f7b4897a20bf627c79640c9@ul-exc-pr-mbx13.ulaval.ca">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<meta name="Generator" content="Microsoft Word 15 (filtered
medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;
color:black;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p
{mso-style-priority:99;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman",serif;
color:black;}
pre
{mso-style-priority:99;
mso-style-link:"Préformaté HTML Car";
margin:0cm;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";
color:black;}
p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
{mso-style-priority:34;
margin-top:0cm;
margin-right:0cm;
margin-bottom:0cm;
margin-left:36.0pt;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;
color:black;}
p.msonormal0, li.msonormal0, div.msonormal0
{mso-style-name:msonormal;
mso-style-priority:99;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman",serif;
color:black;}
span.PrformatHTMLCar
{mso-style-name:"Préformaté HTML Car";
mso-style-priority:99;
mso-style-link:"Préformaté HTML";
font-family:Consolas;}
span.EmailStyle21
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle22
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.EmailStyle25
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 90.0pt 72.0pt 90.0pt;}
div.WordSection1
{page:WordSection1;}
/* List Definitions */
@list l0
{mso-list-id:101149790;
mso-list-type:hybrid;
mso-list-template-ids:-899663072 202113041 202113049 202113051 202113039 202113049 202113051 202113039 202113049 202113051;}
@list l0:level1
{mso-level-text:"%1\)";
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level2
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level3
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l0:level4
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level5
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level6
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l0:level7
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level8
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level9
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
ol
{margin-bottom:0cm;}
ul
{margin-bottom:0cm;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
<div class="WordSection1">
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"
lang="EN-US">What is your goal : to simplify the queries
expression or to optimize the processing time?</span></p>
</div>
</blockquote>
<br>
<p class="MsoNormal"><font size="-2"><span
style="font-family:"Times New Roman",serif;
color:#002060" lang="EN-US">The goal is a bit theoretic,
focused on gainning expressiveness
for query languages, by joinning both query and a map algebra
language
together. so that regions in the raster side could be
identified by algebraic
expressions. Some topological assumptions must be considered
so the
nine-interceptions method can be applied with no format
conversion. I guess
PostGIS can be a platform to go around the issue. </span></font></p>
<blockquote type="cite"
cite="mid:c6b1de059f7b4897a20bf627c79640c9@ul-exc-pr-mbx13.ulaval.ca">
<div class="WordSection1">
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"
lang="EN-US"><o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"
lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"
lang="EN-US">Yes it would work with touch as well.<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"
lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"
lang="EN-US">At some point you have to convert pixels to
something that is comparable with vector. The point is to
lower this conversion to the minimum. The trick in PostGIS
it to tile everything so that only required tiles are
converted. You could certainly have some better algorithms
than what
</span><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"
lang="EN-US">ST_Intersects(d.geom, e.rast) is doing
</span><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"
lang="EN-US">to process at the pixel level instead of at the
tile level.<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"
lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"
lang="EN-US">Pierre<o:p></o:p></span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"
lang="EN-US"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1
1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:windowtext"
lang="FR">De :</span></b><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:windowtext"
lang="FR"> Joao Cordeiro <a class="moz-txt-link-rfc2396E" href="mailto:joao.cordeiro@inpe.br"><joao.cordeiro@inpe.br></a>
<br>
<b>Envoyé :</b> 17 juillet 2019 10:30<br>
<b>À :</b> Pierre Racine
<a class="moz-txt-link-rfc2396E" href="mailto:Pierre.Racine@sbf.ulaval.ca"><Pierre.Racine@sbf.ulaval.ca></a><br>
<b>Objet :</b> Re: [postgis-users] raster regions<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<p class="MsoNormal">Hi, Pierre,<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">Thanks, I appreciate your replies .<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">That is quite a clear and readable
solution to describing an i<b>ntercepting</b> situation.
Should it work with
<b>st_touch </b>as well? Without any raster-vector
conversion for the data? My "consume dream" is to have it
all in a single query expression, in which the SQL parser
delegates to a map algebra parser the evaluation of a kind
of "pertinence criteria" to regions described through "map
algebraic" expressions. I guess, usinga binary
representation to "identify" the regions will allways break
an user question into two! For instance, a question such as
"an specific natural event (such as flooding) touching a
given municipality", would allways demmand for some (tricky)
adequate binary data, prior to being expressed in the query
language.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Regards<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Em 16/07/2019 10:05, Pierre Racine
escreveu:<o:p></o:p></p>
</div>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"
lang="EN-US">Right. To minimize the amount of intermediate
computation you can add a discriminant to the first query:</span><o:p></o:p></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"
lang="EN-US"> </span><o:p></o:p></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"
lang="EN-US">WITH floodareas AS (</span><o:p></o:p></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"
lang="EN-US"> SELECT ST_MapAlgebra(rast, ‘1BB’, ‘CASE
WHEN [rast] < 10 THEN 1 ELSE NULL END’) rast</span><o:p></o:p></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"
lang="EN-US"> FROM elevation e, districts d</span><o:p></o:p></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"
lang="EN-US"> WHERE ST_Intersects(d.geom, e.rast)</span><o:p></o:p></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"
lang="EN-US">)</span><o:p></o:p></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"
lang="EN-US">SELECT d.*</span><o:p></o:p></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"
lang="EN-US">FROM districts d, floodareas f</span><o:p></o:p></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"
lang="EN-US">WHERE ST_Intersects(d.geom, f.rast)</span><o:p></o:p></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"
lang="EN-US"> </span><o:p></o:p></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"
lang="EN-US">Pierre</span><o:p></o:p></p>
<div>
<div style="border:none;border-top:solid #E1E1E1
1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif"
lang="FR">De :</span></b><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif"
lang="FR"> postgis-users
<a href="mailto:postgis-users-bounces@lists.osgeo.org"
moz-do-not-send="true"><postgis-users-bounces@lists.osgeo.org></a>
<b>De la part de</b> <a
href="mailto:joao.cordeiro@inpe.br"
moz-do-not-send="true">joao.cordeiro@inpe.br</a><br>
<b>Envoyé :</b> 15 juillet 2019 14:40<br>
<b>À :</b> <a
href="mailto:postgis-users@lists.osgeo.org"
moz-do-not-send="true">postgis-users@lists.osgeo.org</a><br>
<b>Objet :</b> Re: [postgis-users] raster regions</span><o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"> <o:p></o:p></p>
<p>Thanks, Pierre,<o:p></o:p></p>
<p>It seems ok to model the query the way you showed, however,
that implies the generation of an entire intermediate binary
raster, just to identify the region inside the whole mapped
area. My issue is on avoiding such intermediate data by
incorporating map algebra expression into the same query
expression, Another example, ""... d.geom touch (ndvi2018
< ndvi2019)" where ndvixxxx are raster data.<o:p></o:p></p>
<p>Att.<o:p></o:p></p>
<p>Em 15-07-2019 14:43, Pierre Racine escreveu:<o:p></o:p></p>
<blockquote style="border:none;border-left:solid #1010FF
1.5pt;padding:0cm 0cm 0cm
4.0pt;margin-left:3.75pt;margin-top:5.0pt;margin-bottom:5.0pt">
<div>
<p class="MsoNormal"
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">Hi,</span><o:p></o:p></p>
<p class="MsoNormal"
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"> </span><o:p></o:p></p>
<p class="MsoNormal"
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">That
could translate into something like this in PostGIS:</span><o:p></o:p></p>
<p class="MsoNormal"
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"> </span><o:p></o:p></p>
<p class="MsoNormal"
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">WITH
floodareas AS (</span><o:p></o:p></p>
<p class="MsoNormal"
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">
SELECT ST_MapAlgebra(rast, ‘1BB’, ‘CASE WHEN [rast]
< 10 THEN 1 ELSE NULL END’) rast</span><o:p></o:p></p>
<p class="MsoNormal"
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">
FROM elevation</span><o:p></o:p></p>
<p class="MsoNormal"
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">)</span><o:p></o:p></p>
<p class="MsoNormal"
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">SELECT
d.*</span><o:p></o:p></p>
<p class="MsoNormal"
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">FROM
districts d, floodareas f</span><o:p></o:p></p>
<p class="MsoNormal"
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">WHERE
ST_Intersects(g.geom, f.rast)</span><o:p></o:p></p>
<p class="MsoNormal"
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"> </span><o:p></o:p></p>
<p class="MsoNormal"
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">NULL
becomes nodata values. You could also use ST_Reclass()
instead of ST_Mapalgebra().</span><o:p></o:p></p>
<p class="MsoNormal"
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"> </span><o:p></o:p></p>
<p class="MsoNormal"
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">Pierre</span><o:p></o:p></p>
<p class="MsoNormal"
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"> </span><o:p></o:p></p>
<div>
<div style="border:none;border-top:solid #E1E1E1
1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><strong><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif">De :</span></strong><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif">
postgis-users <<a
href="mailto:postgis-users-bounces@lists.osgeo.org"
moz-do-not-send="true">postgis-users-bounces@lists.osgeo.org</a>>
<strong><span
style="font-family:"Calibri",sans-serif">De
la part de</span></strong> Joao Cordeiro<br>
<strong><span
style="font-family:"Calibri",sans-serif">Envoyé :</span></strong>
15 juillet 2019 10:19<br>
<strong><span
style="font-family:"Calibri",sans-serif">À :</span></strong>
'PostGIS Users Discussion' <<a
href="mailto:postgis-users@lists.osgeo.org"
moz-do-not-send="true">postgis-users@lists.osgeo.org</a>><br>
<strong><span
style="font-family:"Calibri",sans-serif">Objet :</span></strong>
[postgis-users] raster regions</span><o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p>
<p>Hi there,<o:p></o:p></p>
<p>I am not actually a POSTGIS user, or developer,
although I am doing some research of integration of
vector -raster in a dbms context. My main question s
about identifying regions represented in a domain like
the "hybrid raster" so that they can be referred to in a
query. For example, I would like to write something like
"<em><b>select d from districts where d.geometry touches
flooding</b></em>", where "flooding" indicates an
expression intended to decide on the pertinence of
locations to a flooding risk area described, for
instance through map algebra.<o:p></o:p></p>
<p>Thanks for any hints or suggestions.<o:p></o:p></p>
<p>Att.<o:p></o:p></p>
</div>
<p class="MsoNormal"> <o:p></o:p></p>
<pre>_______________________________________________<o:p></o:p></pre>
<pre>postgis-users mailing list<o:p></o:p></pre>
<pre><a href="mailto:postgis-users@lists.osgeo.org" moz-do-not-send="true">postgis-users@lists.osgeo.org</a><o:p></o:p></pre>
<pre><a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" moz-do-not-send="true">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><o:p></o:p></pre>
</blockquote>
<p> <o:p></o:p></p>
<div>
<p class="MsoNormal"> <o:p></o:p></p>
</div>
<p class="MsoNormal"><br>
<br>
<o:p></o:p></p>
<pre>_______________________________________________<o:p></o:p></pre>
<pre>postgis-users mailing list<o:p></o:p></pre>
<pre><a href="mailto:postgis-users@lists.osgeo.org" moz-do-not-send="true">postgis-users@lists.osgeo.org</a><o:p></o:p></pre>
<pre><a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" moz-do-not-send="true">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><o:p></o:p></pre>
</blockquote>
</div>
</blockquote>
</body>
</html>