[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: [postgis-users] Unsubscribe
From: Wayne Seguin <wseguin () gmail ! com>
Date: 2015-07-01 19:03:57
Message-ID: 6FED8747-AC7D-4B5E-A105-26F9ABE56132 () gmail ! com
[Download RAW message or body]
> On Jul 1, 2015, at 3:00 PM, postgis-users-request@lists.osgeo.org wrote:
>
> Send postgis-users mailing list submissions to
> postgis-users@lists.osgeo.org
>
> To subscribe or unsubscribe via the World Wide Web, visit
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> or, via email, send a message with subject or body 'help' to
> postgis-users-request@lists.osgeo.org
>
> You can reach the person managing the list at
> postgis-users-owner@lists.osgeo.org
>
> When replying, please edit your Subject line so it is more specific
> than "Re: Contents of postgis-users digest..."
>
>
> Today's Topics:
>
> 1. Fast bounding box intersection query against many edges
> (Tom Kazimiers)
> 2. Re: Problem in 3Dcitydatabase importer/exporter (Dimitra)
> 3. Re: Fast bounding box intersection query against many edges
> (Sandro Santilli)
> 4. Re: raster, stats conditioned to a set of values (juli g. pausas)
> 5. Problem in Clip between geometry and raster table
> (Marcello Benigno)
> 6. Re: Fast bounding box intersection query against many edges
> (Tom Kazimiers)
> 7. Re: raster, stats conditioned to a set of values (juli g. pausas)
> 8. Re: Problem in 3Dcitydatabase importer/exporter (Felix Kunde)
>
>
> ----------------------------------------------------------------------
>
> Message: 1
> Date: Tue, 30 Jun 2015 23:18:59 -0400
> From: Tom Kazimiers <tom@voodoo-arts.net>
> To: postgis-users@lists.osgeo.org
> Subject: [postgis-users] Fast bounding box intersection query against
> many edges
> Message-ID: <20150701031859.GA30472@redberry.lan>
> Content-Type: text/plain; charset=us-ascii
>
> Hello everyone,
>
> I use Postgres 9.4 and PostGIS 2.1 to represent about 13.000.000
> vertices in a 3D space (growing). Many points are connected in tree
> structures of varying size, often around 5000 nodes per tree. Therefore,
> we use single edges to represent them in our table. My aim is to have
> very fast queries to get all edges that intersect an arbitrary axis
> aligned bounding box.
>
> From what I understand, one option (1) would be the &&& operator to get
> all all edges with (axis aligned, I assume) bounding boxes that
> intersect with my query bounding box. Or alternatively, (2) use
> ST_3DDWithin to get all edges that are within a distance of half my
> bounding box height to a polygon in Z that cuts my query bounding box in
> half.
>
> Are there other options that I am unaware of? I need to find also edges
> that are really within the query bounding box, that do not intersect
> with its surface.
>
> I tested both approaches and attached one example query each at the end
> of this mail, where I also show the table layout plus indices as well as
> the query plans. Is there something I could improve on?
>
> Option (1) is already pretty quick, but I get some false positives (due
> to intersecting bounding boxes of edges, not edges themself) that I
> would need to remove later (which is okay), but of course it would be
> nice to not have them in the first place. But there as well, better
> speed would be welcome.
>
> Thanks,
> Tom
>
>
> Table layout: for (1) a n-D index and for (2) a 2-D index was needed
> ============
>
> Table "public.treenode_edge"
> Column | Type | Modifiers
> ------------+-----------------------+-----------
> id | bigint | not null
> project_id | integer | not null
> edge | geometry(LineStringZ) |
> Indexes:
> "treenode_edge_pkey" PRIMARY KEY, btree (id)
> "treenode_edge_gix" gist (edge gist_geometry_ops_nd)
> "treenode_edge_gix_2d" gist (edge)
> "treenode_edge_project_id_index" btree (project_id)
>
>
> Option 1: &&&
> =============
>
> -- Returned Nodes: 1327
> -- Time: 105 ms (repeated call: 60 ms)
> -- Region: 41819.31354090536 81255.64336110713 102850 to 59868.26425961124 \
> 88903.95239000155 102900
> SELECT te.id
> FROM treenode_edge te
> WHERE te.edge &&& 'LINESTRINGZ(41819.31354090536 81255.64336110713 102850, \
> 59868.26425961124 88903.95239000155 102900)'
> QUERY PLAN \
>
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------
> Bitmap Heap Scan on treenode_edge te (cost=19.71..1666.64 rows=425 width=8) \
> (actual time=56.202..57.276 rows=1327 loops=1) Recheck Cond: (edge &&& \
> '010200008002000000CFEF86086A6BE4402A04354B7AD6F34000000000201CF9407E92D074883BED405B4CFD3C7FB4F54000000000401FF940'::geometry)
>
> -> Bitmap Index Scan on treenode_edge_gix (cost=0.00..19.61 rows=425 width=0) \
> (actual time=56.063..56.063 rows=1327 loops=1) Index Cond: (edge &&& \
> '010200008002000000CFEF86086A6BE4402A04354B7AD6F34000000000201CF9407E92D074883BED405B4CFD3C7FB4F54000000000401FF940'::geometry)
> Total runtime: 57.365 ms
>
>
> Option 2: ST_3DDWithin
> ======================
>
> -- Returned Nodes: 885
> -- Time: 3282 ms (repeated call: 2462 ms)
> -- Region: 41819.31354090536 81255.64336110713 102850 to 59868.26425961124 \
> 88903.95239000155 102900
> SELECT te.id
> FROM treenode_edge te
> WHERE ST_3DDWithin(te.edge, ST_MakePolygon(ST_GeomFromText('LINESTRING(
> 41819.31354090536 81255.64336110713 102825,
> 59868.26425961124 81255.64336110713 102925,
> 59868.26425961124 88903.95239000155 102925,
> 41819.31354090536 88903.95239000155 102825,
> 41819.31354090536 81255.64336110713 102825)')), 25);
>
> QUERY PLAN \
>
> ------------------------------------------------------------------------------------ \
> ------------------------------------------------------------------------------------ \
> ------------------------------------------------------------------------------------ \
> ------------------------------------------------------------------------------------ \
> ------------------------------------------------------------------------------------ \
> ------------------------------------------------------------------------------------ \
> -------------------------------------------------------------------------------------------------------------------------------------------------------------
> Bitmap Heap Scan on treenode_edge te (cost=56821.84..583286.09 rows=80687 \
> width=8) (actual time=205.092..2507.810 rows=885 loops=1) Recheck Cond: (edge && \
> '01030000800100000005000000CFEF86084A68E4402A04354BEAD4F340000000000019F940CFEF86084 \
> A68E4405B4CFD3C0FB6F540000000000019F9407E92D074A83EED405B4CFD3C0FB6F540000000006022F \
> 9407E92D074A83EED402A04354BEAD4F340000000006022F940CFEF86084A68E4402A04354BEAD4F340000000000019F940'::geometry)
>
> Filter: (('01030000800100000005000000CFEF86086A6BE4402A04354B7AD6F34000000000901AF94 \
> 07E92D074883BED402A04354B7AD6F34000000000D020F9407E92D074883BED405B4CFD3C7FB4F540000 \
> 00000D020F940CFEF86086A6BE4405B4CFD3C7FB4F54000000000901AF940CFEF86086A6BE4402A04354B7AD6F34000000000901AF940'::geometry \
> && st_expand(edge, 25::double precision)) AND _st_3ddwithin(edge, \
> '01030000800100000005000000CFEF86086A6BE4402A04354B7AD6F34000000000901AF9407E92D0748 \
> 83BED402A04354B7AD6F34000000000D020F9407E92D074883BED405B4CFD3C7FB4F54000000000D020F \
> 940CFEF86086A6BE4405B4CFD3C7FB4F54000000000901AF940CFEF86086A6BE4402A04354B7AD6F34000000000901AF940'::geometry, \
> 25::double precision)) Rows Removed by Filter: 1224193
> -> Bitmap Index Scan on treenode_edge_gix_2d (cost=0.00..56801.67 rows=1210300 \
> width=0) (actual time=176.023..176.023 rows=1225078 loops=1) Index Cond: (edge && \
> '01030000800100000005000000CFEF86084A68E4402A04354BEAD4F340000000000019F940CFEF86084 \
> A68E4405B4CFD3C0FB6F540000000000019F9407E92D074A83EED405B4CFD3C0FB6F540000000006022F \
> 9407E92D074A83EED402A04354BEAD4F340000000006022F940CFEF86084A68E4402A04354BEAD4F340000000000019F940'::geometry)
> Total runtime: 2507.927 ms
>
>
> ------------------------------
>
> Message: 2
> Date: Wed, 1 Jul 2015 01:37:04 -0700 (PDT)
> From: Dimitra <dimitradimitra@windowslive.com>
> To: postgis-users@lists.osgeo.org
> Subject: Re: [postgis-users] Problem in 3Dcitydatabase
> importer/exporter
> Message-ID: <1435739824425-5008534.post@n6.nabble.com>
> Content-Type: text/plain; charset=us-ascii
>
> Hi Felix,
>
> First of all, i want to thank you once again for the time you spend trying
> to help me! I was really surprised seeing the whole building in Google Earth
> from 3dCityDataBase. Obviously, i am making mistakes during the import gml
> or export kml file. The export process takes a lot of time to be completed,
> more than 4 hours, fact that concerns me and make me unable to do more
> attempts.. So i want your help one more time. Could you please make
> screenshots of the three templates (import, database, export kml/collada) in
> order to see which options you have selected?
>
>
>
> --
> View this message in context: \
> http://postgis.17.x6.nabble.com/Problem-in-3Dcitydatabase-importer-exporter-tp5008524p5008534.html
> Sent from the PostGIS - User mailing list archive at Nabble.com.
>
>
> ------------------------------
>
> Message: 3
> Date: Wed, 1 Jul 2015 11:06:25 +0200
> From: Sandro Santilli <strk@keybit.net>
> To: PostGIS Users Discussion <postgis-users@lists.osgeo.org>
> Subject: Re: [postgis-users] Fast bounding box intersection query
> against many edges
> Message-ID: <20150701090625.GB6966@localhost>
> Content-Type: text/plain; charset=us-ascii
>
> > On Tue, Jun 30, 2015 at 11:18:59PM -0400, Tom Kazimiers wrote:
> > Hello everyone,
> >
> > I use Postgres 9.4 and PostGIS 2.1 to represent about 13.000.000
> > vertices in a 3D space (growing). Many points are connected in tree
> > structures of varying size, often around 5000 nodes per tree. Therefore,
> > we use single edges to represent them in our table. My aim is to have
> > very fast queries to get all edges that intersect an arbitrary axis
> > aligned bounding box.
> >
> > From what I understand, one option (1) would be the &&& operator to get
> > all all edges with (axis aligned, I assume) bounding boxes that
> > intersect with my query bounding box. Or alternatively, (2) use
> > ST_3DDWithin to get all edges that are within a distance of half my
> > bounding box height to a polygon in Z that cuts my query bounding box in
> > half.
>
> [...]
>
> > Option (1) is already pretty quick, but I get some false positives (due
> > to intersecting bounding boxes of edges, not edges themself) that I
> > would need to remove later (which is okay), but of course it would be
> > nice to not have them in the first place. But there as well, better
> > speed would be welcome.
>
> The ST_3DDWithin function is currently _not_ using &&& operator.
> It may actually be a good idea to make it do so.
> In any case, you can manually mix the two. What speed do you get then ?
>
> The query should then be:
>
> SELECT te.id
> FROM treenode_edge te
> WHERE te.edge &&& 'LINESTRINGZ(41819.31354090536 81255.64336110713 102850, \
> 59868.26425961124 88903.95239000155 102900) AND _st_3ddwithin(te.edge, \
> ST_MakePolygon(ST_GeomFromText('LINESTRING( 41819.31354090536 81255.64336110713 \
> 102825, 59868.26425961124 81255.64336110713 102925,
> 59868.26425961124 88903.95239000155 102925,
> 41819.31354090536 88903.95239000155 102825,
> 41819.31354090536 81255.64336110713 102825)')), 25);
>
> --strk;
>
>
> ------------------------------
>
> Message: 4
> Date: Wed, 1 Jul 2015 11:34:01 +0200
> From: "juli g. pausas" <juli.g.pausas@uv.es>
> To: PostGIS Users Discussion <postgis-users@lists.osgeo.org>
> Subject: Re: [postgis-users] raster, stats conditioned to a set of
> values
> Message-ID:
> <CAD6WO6T8mEdQ0oYQrjJ0JwSRprzciJ1euG0nprAJR4gHQij3MA@mail.gmail.com>
> Content-Type: text/plain; charset="utf-8"
>
> Thanks for this.
> But it didn't work for me.
>
> These queries work
>
> SELECT (ST_SummaryStats(ST_MapAlgebra(rast, 1, '16BSI'::text, '[rast]+1'),
> 1)).* FROM rastertmp.ndvitmp
> SELECT (ST_SummaryStats(ST_MapAlgebra(rast, 1, NULL, '[rast]+1'), 1)).*
> FROM rastertmp.ndvitmp
>
> But not when I use the condition [rast] > 0
>
> SELECT (ST_SummaryStats(ST_MapAlgebra(rast, 1, NULL, '[rast] > 0'), 1)).*
> FROM rastertmp.ndvitmp
>
> ERROR: cannot cast type boolean to double precision
> LINE 1: SELECT ($1 > 0)::double precision
>
> Replacing NULL for '16BSI'::text, or '16BSI'::smallint, '16BSI'::double
> precision doen't solve the problem
>
> Any other suggestion?
> Thanks
>
>
>
>
>
>
> Juli
> --
> *CIDE, CSIC* | www.uv.es/jgpausas |
>
>
> On Tue, Jun 30, 2015 at 5:29 PM, Pierre Racine <Pierre.Racine@sbf.ulaval.ca>
> wrote:
>
> > You can select pixels fulfilling an expression using the one-raster
> > variant of ST_MapAlgebra
> >
> > http://postgis.net/docs/RT_ST_MapAlgebra_expr.html
> >
> > So just do something like:
> >
> > ST_MapAlgebra(rast, '8BUI'::text, '[rast] > 0')
> >
> > before computing stats.
> >
> > To count the number of pixels of a certain value you can use
> > ST_ValueCount().
> >
> > Pierre
> >
> > > -----Original Message-----
> > > From: postgis-users-bounces@lists.osgeo.org [mailto:postgis-users-
> > > bounces@lists.osgeo.org] On Behalf Of juli g. pausas
> > > Sent: Monday, June 29, 2015 12:49 PM
> > > To: PostGIS Users Discussion
> > > Subject: [postgis-users] raster, stats conditioned to a set of values
> > >
> > > Hi all
> > >
> > > I'm just starting to discover postgis, it is really useful.
> > >
> > > I have a raster file with different bands, that I have imported to
> > postgres
> > > (raster2pgsql, without the -R option, i.e., insite the database). I
> > would like to
> > > extract information from band 1, but filtering the data using only
> > >
> > > 1) pixels with positive values (in that band, band 1)
> > >
> > > 2) and pixels in which band 2 is equal to a given value, e.g. 1
> > >
> > >
> > > The type of analysis I'd like to do is descriptive stats but also
> > intersecting
> > > with a vector map. If my raster is rastertmp.ndvitmp, two examples are:
> > >
> > >
> > > SELECT (ST_SummaryStats(rast, 1)).* FROM rastertmp.ndvitmp WHERE rid
> > > = 1
> > >
> > >
> > >
> > > SELECT p.region_cod, ST_ValueCount(ST_Clip(r.rast,1, p.geom, true)) AS
> > res
> > > FROM gis_wd.wd_regiones AS p, rastertmp.ndvitmp AS r
> > > WHERE ST_Intersects(r.rast, p.geom)
> > > AND p.region_cod = 'PA1214';
> > >
> > >
> > >
> > > This works perfectly, but how can I compute the stats only for pixels
> > with
> > > positive values and with a given value in another band? The idea would
> > be
> > > something like: WHERE ST_Values(rast, 1)>0 AND ST_Values(rast, 2)=1
> > >
> > >
> > > Thanks for any help!
> > >
> > >
> > > Juli
> > > --
> > > CIDE, CSIC | www.uv.es/jgpausas |
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users@lists.osgeo.org
> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150701/2ef81737/attachment-0001.html>
>
> ------------------------------
>
> Message: 5
> Date: Wed, 1 Jul 2015 08:12:17 -0300
> From: Marcello Benigno <benigno.marcello@gmail.com>
> To: PostGIS Users Discussion <postgis-users@lists.osgeo.org>
> Subject: [postgis-users] Problem in Clip between geometry and raster
> table
> Message-ID:
> <CAKPVhYKPiREZbTTUK0Vcw=tpHh6L3yETtLH_j=C2aMSu0RHXqw@mail.gmail.com>
> Content-Type: text/plain; charset="utf-8"
>
> Hello guys,
>
> When attempting to perform a clip from a polygon and a raster table , the
> following error appeared:
>
> ERROR: column "nan" does not exist
> > LINE 1: SELECT (NaN)::double precision
> > ^
> > QUERY: SELECT (NaN)::double precision
> > CONTEXT: PL/pgSQL function st_clip(raster,integer,geometry,double
> > precision[],boolean) line 42 at assignment
> > SQL function "st_clip" statement 1
> > (execution time: 250 ms; total time: 485 ms)
> > SELECT (ST_DumpAsPolygons(ST_Clip(c.geom, ST_Buffer(f.geometria, 0.01),
> > TRUE))).val AS dn,
> > (ST_DumpAsPolygons(ST_Clip(c.geom, ST_Buffer(f.geometria, 0.01),
> > TRUE))).geom AS geom
> > FROM public.propriedade_geometria f, dados.edr c
> > WHERE ST_Intersects(f.geometria, c.geom)
> > AND f.tipo = 2 AND f.propriedade_id = 6063 AND c.id_uf = 2 AND c.cidade_id
> > = 4681;
>
>
> I can't understand what may be happening. Can I perform a casting to solve
> this problem ?
>
> --
> *Marcello Benigno B. de Barros Filho*
> Prof. do Curso Superior de Tecnologia em Geoprocessamento - IFPB
> Mestre em Ci?ncias Geod?sicas e Tecnologias da Geoinforma??o - UFPE
> Doutorando em Tecnologia Ambiental e Recursos H?dricos - UFPE
> http://profmarcello.blogspot.com
> http://about.me/marcello.benigno
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150701/f8996e3a/attachment-0001.html>
>
> ------------------------------
>
> Message: 6
> Date: Wed, 1 Jul 2015 07:46:22 -0400
> From: Tom Kazimiers <tom@voodoo-arts.net>
> To: PostGIS Users Discussion <postgis-users@lists.osgeo.org>
> Subject: Re: [postgis-users] Fast bounding box intersection query
> against many edges
> Message-ID: <20150701114622.GC30472@redberry.lan>
> Content-Type: text/plain; charset="us-ascii"
>
> Hi Sandro,
>
> Thanks for your input!
>
> > On Wed, Jul 01, 2015 at 11:06:25AM +0200, Sandro Santilli wrote:
> > > On Tue, Jun 30, 2015 at 11:18:59PM -0400, Tom Kazimiers wrote:
> > > Option (1) is already pretty quick, but I get some false positives (due
> > > to intersecting bounding boxes of edges, not edges themself) that I
> > > would need to remove later (which is okay), but of course it would be
> > > nice to not have them in the first place. But there as well, better
> > > speed would be welcome.
> >
> > The ST_3DDWithin function is currently _not_ using &&& operator.
> > It may actually be a good idea to make it do so.
> > In any case, you can manually mix the two. What speed do you get then ?
> >
> > The query should then be:
> >
> > SELECT te.id
> > FROM treenode_edge te
> > WHERE te.edge &&& 'LINESTRINGZ(41819.31354090536 81255.64336110713 102850, \
> > 59868.26425961124 88903.95239000155 102900) AND _st_3ddwithin(te.edge, \
> > ST_MakePolygon(ST_GeomFromText('LINESTRING( 41819.31354090536 81255.64336110713 \
> > 102825, 59868.26425961124 81255.64336110713 102925,
> > 59868.26425961124 88903.95239000155 102925,
> > 41819.31354090536 88903.95239000155 102825,
> > 41819.31354090536 81255.64336110713 102825)')), 25);
>
> I just tried your suggestion and the timing is the same as the &&&
> query, with fewer nodes returned (a good thing). So it is indeed the
> best of both my initial queries combined. With your query (there is a '
> missing at the end of the first WHERE line), I see these results:
>
> Nodes: 884 (compared to &&& alone: 1327, ST_3DDWithin alone: 885)
> Time: 106 ms (61 ms) (compared to &&& alone: 105 ms (60 ms),
> ST_3DDWithin alone: 3282 ms (2462 ms))
>
> The query plan is attached to the end of this mail. It looks much
> better, without any nested loops, using the n-d index only (I get the
> same results with the 2D index dropped). I'll have to test with other
> typical queries, but this already is quite an improvement---thanks!
>
> Still, I'd be happy to hear more suggestions if there is more I could
> change to improve query time.
>
> Best,
> Tom
>
>
> The query plan of Sandro's query on my server:
> QUERY PLAN \
>
> ------------------------------------------------------------------------------------ \
> ------------------------------------------------------------------------------------ \
> ------------------------------------------------------------------------------------ \
> --------------------------------------------------------------------------------- \
> Bitmap Heap Scan on treenode_edge te (cost=19.64..1772.82 rows=142 width=8) \
> (actual time=56.753..59.307 rows=884 loops=1) Recheck Cond: (edge &&& \
> '010200008002000000CFEF86086A6BE4402A04354B7AD6F34000000000201CF9407E92D074883BED405B4CFD3C7FB4F54000000000401FF940'::geometry)
>
> Filter: _st_3ddwithin(edge, \
> '01030000800100000005000000CFEF86086A6BE4402A04354B7AD6F34000000000901AF9407E92D0748 \
> 83BED402A04354B7AD6F34000000000D020F9407E92D074883BED405B4CFD3C7FB4F54000000000D020F \
> 940CFEF86086A6BE4405B4CFD3C7FB4F54000000000901AF940CFEF86086A6BE4402A04354B7AD6F34000000000901AF940'::geometry, \
> 25::double precision) Rows Removed by Filter: 443
> -> Bitmap Index Scan on treenode_edge_gix (cost=0.00..19.61 rows=425 width=0) \
> (actual time=56.579..56.579 rows=1327 loops=1) Index Cond: (edge &&& \
> '010200008002000000CFEF86086A6BE4402A04354B7AD6F34000000000201CF9407E92D074883BED405B4CFD3C7FB4F54000000000401FF940'::geometry)
> Total runtime: 59.370 ms
>
>
> -------------- next part --------------
> A non-text attachment was scrubbed...
> Name: not available
> Type: application/pgp-signature
> Size: 819 bytes
> Desc: not available
> URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150701/b32cf47c/attachment-0001.pgp>
>
> ------------------------------
>
> Message: 7
> Date: Wed, 1 Jul 2015 14:08:28 +0200
> From: "juli g. pausas" <juli.g.pausas@uv.es>
> To: PostGIS Users Discussion <postgis-users@lists.osgeo.org>
> Subject: Re: [postgis-users] raster, stats conditioned to a set of
> values
> Message-ID:
> <CAD6WO6QZx__3qNtLEYjL5VC+UnXMUpc08wgB1J_6P7hjjtZxWg@mail.gmail.com>
> Content-Type: text/plain; charset="utf-8"
>
> Hi
> I found a way to discard the negative values at least when computing
> ValueCount:
>
> SELECT region_cod, (res).*
> FROM
> (SELECT p.region_cod, ST_ValueCount(ST_Clip(r.rast,1, p.geom, true)) AS
> res
> FROM gis_wd.wd_regiones AS p, rastertmp.ndvitmp AS r
> WHERE ST_Intersects(r.rast, p.geom)
> AND p.region_cod = 'PA1214'
> ) AS foo WHERE (res).value > 0;
>
>
> Although this is not useful for computing the mean and SD, ie. I still do
> not know how to perform ST_SummaryStats excluding negative values in the
> raster. One possibility could perhaps be to reclassify negative values to
> NULL (assuming that NULL values are not considered in ST_SummaryStats,
> which I'm not sure), but my attempts were unsuccessful.
>
> But my main problem is that I would like to do this (e.g., the query
> above), but only for the pixels in which Band2 = 0. Any idea? any clue?
>
> Thanks
>
>
> Juli
> --
> *CIDE, CSIC* | www.uv.es/jgpausas |
>
>
> > On Wed, Jul 1, 2015 at 11:34 AM, juli g. pausas <juli.g.pausas@uv.es> wrote:
> >
> > Thanks for this.
> > But it didn't work for me.
> >
> > These queries work
> >
> > SELECT (ST_SummaryStats(ST_MapAlgebra(rast, 1, '16BSI'::text, '[rast]+1'),
> > 1)).* FROM rastertmp.ndvitmp
> > SELECT (ST_SummaryStats(ST_MapAlgebra(rast, 1, NULL, '[rast]+1'), 1)).*
> > FROM rastertmp.ndvitmp
> >
> > But not when I use the condition [rast] > 0
> >
> > SELECT (ST_SummaryStats(ST_MapAlgebra(rast, 1, NULL, '[rast] > 0'), 1)).*
> > FROM rastertmp.ndvitmp
> >
> > ERROR: cannot cast type boolean to double precision
> > LINE 1: SELECT ($1 > 0)::double precision
> >
> > Replacing NULL for '16BSI'::text, or '16BSI'::smallint, '16BSI'::double
> > precision doen't solve the problem
> >
> > Any other suggestion?
> > Thanks
> >
> >
> >
> >
> >
> >
> > Juli
> > --
> > *CIDE, CSIC* | www.uv.es/jgpausas |
> >
> >
> > On Tue, Jun 30, 2015 at 5:29 PM, Pierre Racine <
> > Pierre.Racine@sbf.ulaval.ca> wrote:
> >
> > > You can select pixels fulfilling an expression using the one-raster
> > > variant of ST_MapAlgebra
> > >
> > > http://postgis.net/docs/RT_ST_MapAlgebra_expr.html
> > >
> > > So just do something like:
> > >
> > > ST_MapAlgebra(rast, '8BUI'::text, '[rast] > 0')
> > >
> > > before computing stats.
> > >
> > > To count the number of pixels of a certain value you can use
> > > ST_ValueCount().
> > >
> > > Pierre
> > >
> > > > -----Original Message-----
> > > > From: postgis-users-bounces@lists.osgeo.org [mailto:postgis-users-
> > > > bounces@lists.osgeo.org] On Behalf Of juli g. pausas
> > > > Sent: Monday, June 29, 2015 12:49 PM
> > > > To: PostGIS Users Discussion
> > > > Subject: [postgis-users] raster, stats conditioned to a set of values
> > > >
> > > > Hi all
> > > >
> > > > I'm just starting to discover postgis, it is really useful.
> > > >
> > > > I have a raster file with different bands, that I have imported to
> > > postgres
> > > > (raster2pgsql, without the -R option, i.e., insite the database). I
> > > would like to
> > > > extract information from band 1, but filtering the data using only
> > > >
> > > > 1) pixels with positive values (in that band, band 1)
> > > >
> > > > 2) and pixels in which band 2 is equal to a given value, e.g. 1
> > > >
> > > >
> > > > The type of analysis I'd like to do is descriptive stats but also
> > > intersecting
> > > > with a vector map. If my raster is rastertmp.ndvitmp, two examples are:
> > > >
> > > >
> > > > SELECT (ST_SummaryStats(rast, 1)).* FROM rastertmp.ndvitmp WHERE rid
> > > > = 1
> > > >
> > > >
> > > >
> > > > SELECT p.region_cod, ST_ValueCount(ST_Clip(r.rast,1, p.geom, true)) AS
> > > res
> > > > FROM gis_wd.wd_regiones AS p, rastertmp.ndvitmp AS r
> > > > WHERE ST_Intersects(r.rast, p.geom)
> > > > AND p.region_cod = 'PA1214';
> > > >
> > > >
> > > >
> > > > This works perfectly, but how can I compute the stats only for pixels
> > > with
> > > > positive values and with a given value in another band? The idea would
> > > be
> > > > something like: WHERE ST_Values(rast, 1)>0 AND ST_Values(rast, 2)=1
> > > >
> > > >
> > > > Thanks for any help!
> > > >
> > > >
> > > > Juli
> > > > --
> > > > CIDE, CSIC | www.uv.es/jgpausas |
> > >
> > > _______________________________________________
> > > postgis-users mailing list
> > > postgis-users@lists.osgeo.org
> > > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150701/4adb1669/attachment-0001.html>
>
> ------------------------------
>
> Message: 8
> Date: Wed, 1 Jul 2015 15:29:27 +0200
> From: "Felix Kunde" <felix-kunde@gmx.de>
> To: postgis-users@lists.osgeo.org
> Subject: Re: [postgis-users] Problem in 3Dcitydatabase
> importer/exporter
> Message-ID:
> <trinity-b2be4476-239e-44b0-a520-457e73cc5c86-1435757366888@3capp-gmx-bs51>
>
> Content-Type: text/plain; charset="utf-8"
>
> 4h?! Holy moly! On my machine it took me about 3 minutes.
> Are you using a bigger dataset than the one you've send to me?
>
> First, you really have to get the data structure right. Change your FME workbench.
> You should have LoD4 CityGML buildings with thousands of polyons and not thousands \
> of buildings with just one polygon.
> Then you should have a look at the coordinates.
> The position by using EPSG 2100 does not seem correct.
>
> I'll send you my project file. You can upload it in the Importer/Exporter to use my \
> settings. No need to do screenshots.
>
> One useful hint for testing:
> If you want to remove everthing use the SQL command:
> SELECT cleanup_schema();
> (only takes some seconds to empty all tables)
>
>
>
> Gesendet:?Mittwoch, 01. Juli 2015 um 10:37 Uhr
> Von:?Dimitra <dimitradimitra@windowslive.com>
> An:?postgis-users@lists.osgeo.org
> Betreff:?Re: [postgis-users] Problem in 3Dcitydatabase importer/exporter
> Hi Felix,
>
> First of all, i want to thank you once again for the time you spend trying
> to help me! I was really surprised seeing the whole building in Google Earth
> from 3dCityDataBase. Obviously, i am making mistakes during the import gml
> or export kml file. The export process takes a lot of time to be completed,
> more than 4 hours, fact that concerns me and make me unable to do more
> attempts.. So i want your help one more time. Could you please make
> screenshots of the three templates (import, database, export kml/collada) in
> order to see which options you have selected?
>
>
>
> --
> View this message in context: \
> http://postgis.17.x6.nabble.com/Problem-in-3Dcitydatabase-importer-exporter-tp5008524p5008534.html
> Sent from the PostGIS - User mailing list archive at Nabble.com.
> _______________________________________________
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users[http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users]
>
> -------------- next part --------------
> A non-text attachment was scrubbed...
> Name: testing_lod4_building.zip
> Type: application/zip
> Size: 4457 bytes
> Desc: not available
> URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150701/b2370e8c/attachment-0001.zip>
>
> ------------------------------
>
> _______________________________________________
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
> End of postgis-users Digest, Vol 161, Issue 1
> *********************************************
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic