[postgis-users] Unsubscribe
Wayne Seguin
wseguin at gmail.com
Wed Jul 1 12:03:57 PDT 2015
> On Jul 1, 2015, at 3:00 PM, postgis-users-request at lists.osgeo.org wrote:
>
> Send postgis-users mailing list submissions to
> postgis-users at 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 at lists.osgeo.org
>
> You can reach the person managing the list at
> postgis-users-owner at 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 at voodoo-arts.net>
> To: postgis-users at lists.osgeo.org
> Subject: [postgis-users] Fast bounding box intersection query against
> many edges
> Message-ID: <20150701031859.GA30472 at 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 && '01030000800100000005000000CFEF86084A68E4402A04354BEAD4F340000000000019F940CFEF86084A68E4405B4CFD3C0FB6F540000000000019F9407E92D074A83EED405B4CFD3C0FB6F540000000006022F9407E92D074A83EED402A04354BEAD4F340000000006022F940CFEF86084A68E4402A04354BEAD4F340000000000019F940'::geometry)
> Filter: (('01030000800100000005000000CFEF86086A6BE4402A04354B7AD6F34000000000901AF9407E92D074883BED402A04354B7AD6F34000000000D020F9407E92D074883BED405B4CFD3C7FB4F54000000000D020F940CFEF86086A6BE4405B4CFD3C7FB4F54000000000901AF940CFEF86086A6BE4402A04354B7AD6F34000000000901AF940'::geometry && st_expand(edge, 25::double precision)) AND _st_3ddwithin(edge, '01030000800100000005000000CFEF86086A6BE4402A04354B7AD6F34000000000901AF9407E92D074883BED402A04354B7AD6F34000000000D020F9407E92D074883BED405B4CFD3C7FB4F54000000000D020F940CFEF86086A6BE4405B4CFD3C7FB4F54000000000901AF940CFEF86086A6BE4402A04354B7AD6F34000000000901AF940'::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 && '01030000800100000005000000CFEF86084A68E4402A04354BEAD4F340000000000019F940CFEF86084A68E4405B4CFD3C0FB6F540000000000019F9407E92D074A83EED405B4CFD3C0FB6F540000000006022F9407E92D074A83EED402A04354BEAD4F340000000006022F940CFEF86084A68E4402A04354BEAD4F340000000000019F940'::geometry)
> Total runtime: 2507.927 ms
>
>
> ------------------------------
>
> Message: 2
> Date: Wed, 1 Jul 2015 01:37:04 -0700 (PDT)
> From: Dimitra <dimitradimitra at windowslive.com>
> To: postgis-users at lists.osgeo.org
> Subject: Re: [postgis-users] Problem in 3Dcitydatabase
> importer/exporter
> Message-ID: <1435739824425-5008534.post at 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 at keybit.net>
> To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> Subject: Re: [postgis-users] Fast bounding box intersection query
> against many edges
> Message-ID: <20150701090625.GB6966 at 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 at uv.es>
> To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> Subject: Re: [postgis-users] raster, stats conditioned to a set of
> values
> Message-ID:
> <CAD6WO6T8mEdQ0oYQrjJ0JwSRprzciJ1euG0nprAJR4gHQij3MA at 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 at 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 at lists.osgeo.org [mailto:postgis-users-
>>> bounces at 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 at 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 at gmail.com>
> To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> Subject: [postgis-users] Problem in Clip between geometry and raster
> table
> Message-ID:
> <CAKPVhYKPiREZbTTUK0Vcw=tpHh6L3yETtLH_j=C2aMSu0RHXqw at 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 at voodoo-arts.net>
> To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> Subject: Re: [postgis-users] Fast bounding box intersection query
> against many edges
> Message-ID: <20150701114622.GC30472 at 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, '01030000800100000005000000CFEF86086A6BE4402A04354B7AD6F34000000000901AF9407E92D074883BED402A04354B7AD6F34000000000D020F9407E92D074883BED405B4CFD3C7FB4F54000000000D020F940CFEF86086A6BE4405B4CFD3C7FB4F54000000000901AF940CFEF86086A6BE4402A04354B7AD6F34000000000901AF940'::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 at uv.es>
> To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> Subject: Re: [postgis-users] raster, stats conditioned to a set of
> values
> Message-ID:
> <CAD6WO6QZx__3qNtLEYjL5VC+UnXMUpc08wgB1J_6P7hjjtZxWg at 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 at 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 at 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 at lists.osgeo.org [mailto:postgis-users-
>>>> bounces at 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 at 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 at gmx.de>
> To: postgis-users at lists.osgeo.org
> Subject: Re: [postgis-users] Problem in 3Dcitydatabase
> importer/exporter
> Message-ID:
> <trinity-b2be4476-239e-44b0-a520-457e73cc5c86-1435757366888 at 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 at windowslive.com>
> An:?postgis-users at 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 at 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 at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
> End of postgis-users Digest, Vol 161, Issue 1
> *********************************************
More information about the postgis-users
mailing list