Pierre Racine | 2 Jul 16:03 2015
Picon

Re: raster, stats conditioned to a set of values

> 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?

1) isolate the pixels with 0. For this you can use ST_MapAlgebra() or ST_Reclass(). I use ST_MapAlgebra()
in the following query
2) compute the intersection with band 1
3) compute the stats as you did

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;

WITH bands AS ( -- reclass the second band to 0 and 1
SELECT ST_MapAlgebra(ST_Band(rast, 2), '16BSI'::text, 'CASE WHEN [rast] < 0 or [rast] > 0 THEN NULL ELSE 1
END') band2,
             ST_Band(rast, 1) band1
) rastintersect AS ( -- compute the intersection of band 1 and band 2
  SELECT ST_Intersection(band1, band2, 'BAND1') rast FROM bands
)
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, rastintersect AS r
    WHERE ST_Intersects(r.rast, p.geom) 
    AND p.region_cod = 'PA1214'
   ) AS foo;
(Continue reading)

Marcello Benigno | 1 Jul 13:12 2015
Picon

Problem in Clip between geometry and raster table

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
_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Tom Kazimiers | 1 Jul 05:18 2015
Picon

Fast bounding box intersection query against many edges

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
dimitra dimitra | 30 Jun 13:52 2015

Re: Problem in 3Dcitydatabase importer/exporter



From: dimitradimitra <at> windowslive.com
To: postgis-users <at> lists.osgeo.org
Subject: RE: [postgis-users] Problem in 3Dcitydatabase importer/exporter
Date: Tue, 30 Jun 2015 14:46:34 +0300

<!-- .ExternalClass .ecxhmmessage P { padding:0px; } .ExternalClass body.ecxhmmessage { font-size:12pt; font-family:Calibri; } -->
Hello Felix,

Thank you for your reply. I am using the newest version of 3Dcitydatabase and during the import, there are no errors. I have been experimenting with the coordinate system but with no result. However, the main problem is that all building parts and textures do not been displayed. I am sending to you  the gml file, if you want to check it out. Thank you anyway for your interest.

> From: felix-kunde <at> gmx.de
> To: postgis-users <at> lists.osgeo.org
> CC: postgis-users <at> lists.osgeo.org
> Date: Mon, 29 Jun 2015 17:49:10 +0200
> Subject: Re: [postgis-users] Problem in 3Dcitydatabase importer/exporter
>
> Hey Dimitra
>
> Did you receive any errors during the import?
> Are you sure you are using the correct reference system in the DB?
> Are you using the newest version of the 3DCityDB?
>
> You could send me the model and I could check it out myself, if you want.
>
> Felix
>  
>  
>
> Gesendet: Montag, 29. Juni 2015 um 15:30 Uhr
> Von: Dimitra <dimitradimitra <at> windowslive.com>
> An: postgis-users <at> lists.osgeo.org
> Betreff: [postgis-users] Problem in 3Dcitydatabase importer/exporter
> Hello,
>
> I create a building model(LOD 3) in sketch up pro and then i transformed it
> into gml format via FME. Despite the fact that, in FME Inspector and FZK
> Viewer the building model is displayed fine, when i import it in 3D
> citydatabase importer/exporter and export it in kml/collada file something
> goes wrong. Specifically, the model is not displayed in the right position,
> some building parts are missing and the textures are lost. Do you know why
> this happens? I am waiting for your reply!! Thanks in advance!!!
>
>
>
>
> --
> View this message in context: http://postgis.17.x6.nabble.com/Problem-in-3Dcitydatabase-importer-exporter-tp5008524.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]
> _______________________________________________
> postgis-users mailing list
> postgis-users <at> lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
juli g. pausas | 29 Jun 18:48 2015
Picon

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
Dimitra | 29 Jun 15:30 2015

Problem in 3Dcitydatabase importer/exporter

Hello, 

I create a building model(LOD 3) in sketch up pro and then i transformed it
into gml format via FME. Despite the fact that, in FME Inspector and FZK
Viewer the building model is displayed fine, when i import it in 3D
citydatabase importer/exporter and export it in kml/collada file something
goes wrong. Specifically, the model is not displayed in the right position,
some building parts are missing and the textures are lost. Do you know why
this happens? I am waiting for your reply!! Thanks in advance!!!

--
View this message in context: http://postgis.17.x6.nabble.com/Problem-in-3Dcitydatabase-importer-exporter-tp5008524.html
Sent from the PostGIS - User mailing list archive at Nabble.com.
Jonathan Moules | 26 Jun 13:54 2015

Distance between two furthest points of a group

Hi List,

I have sets of points (up to 250,000 in a set) and I want to get the furthest distance between any of them.

 

In theory the simplest way is to use ST_MinimumBoundingCircle(ST_Collect(geography) and then get the diameter of that.

 

The problem is – I don’t seem to be able to get the diameter of that circle (which would give me the distance I want).

 

Does anyone have any thoughts on this? Is there a good way to get the diameter? Or some other way of getting the distance I desire.

 

Thanks,

Jonathan


HR Wallingford and its subsidiaries uses faxes and emails for confidential and legally privileged business communications. They do not of themselves create legal commitments. Disclosure to parties other than addressees requires our specific consent. We are not liable for unauthorised disclosures nor reliance upon them.
If you have received this message in error please advise us immediately and destroy all copies of it.

HR Wallingford Limited
Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom
Registered in England No. 02562099

_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Graeme B. Bell | 26 Jun 04:07 2015
Picon

par_psql - Parallel ‘psql’ for PostGIS workflows.

par_psql v0.1: Parallel ‘psql’, for queries and workflows in PostgreSQL/PostGIS.
================================================

Hi everyone

http://github.com/gbb/par_psql/

I’ve written a tool (par_psql) which makes parallelisation easier for PostgreSQL/PostGIS users, by
providing a new piece of syntax.

With —-& inline, it runs queries or groups of queries in parallel.
Without —-&, it synchronises parallel work then runs subsequent code normally.
This allows easy control of parallelism and synchronisation inline within your SQL script.

The tool is backwards compatible with existing psql scripts, and par_psql scripts are backwards
compatible with psql. It should work with any version of PostgreSQL. The only dependencies are bash and psql.

Benchmark and example code is provided at http://github.com/gbb/par_psql.

Quick example
=============

create table a as ...
create table a1 as ...  —-&
create table a2 as ...  —-&
create table c ...

Some cool uses
==============

1. GIS and any other discipline where you prepare diverse source datasets in a multi-stage workflow before
integrating them together.
2. Where you have CPU-intensive queries, split the work via one field (e.g. ID) and create parallel temp
tables. UNION the results.
3. Add “Preview runs”, that complete progressively using subsets of the data without delaying the
main task.
4. Create scripts where several tasks run at fixed times after the script begins (use pg_sleep() and run
them in parallel).

It’s available under the postgresql open source license. It's a 'quick hack' and version 0.1, so please
be kind with any criticism/bug reports. That said, it works well for me. Enjoy! :-)

Graeme Bell

http://github.com/gbb/par_psql/

ps. I am grateful to the Norwegian Forest and Landscape Institute (soon to be integrated into the Norwegian
NIBIO Institute) for supporting and open sourcing this and other scripts as a contribution to this
year’s FOSS4G Europe Open Source Mapping conference.
Jonatan Malaver | 25 Jun 19:59 2015

reverse linestring direction

Hello,

 

   Say I have 2 points (start and end). How could change the direction of linestring going from start point to end point?

 

Thanks,

 

Jon

 

_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
David Jantzen | 24 Jun 22:13 2015

Uniqueness constraint on geometry columns

Hello,

I've encountered a violation of a uniqueness constraint that is surprising, and I'm hoping for an explanation. Below are repro steps: 

create table duplicate_points (the_geom geometry(PointZM, 4326));
create unique index on duplicate_points (the_geom);
insert into duplicate_points (the_geom) 
values ('01010000E0E610000019FF3EE3C2955EC068942EFD4BCA474000000000000000000000000000000000'),('01010000E0E610000087E2C8E2C2955EC01BDC94FA4BCA474000000000000000000000000000000000');

If I cast the point column to text then the uniqueness constraint works as expected:

create unique index on duplicate_points (cast(the_geom as text));

Is there some kind of precision limit being encountered here?

Thanks,
David
_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Ruth Simm | 24 Jun 20:05 2015
Picon

Query to create 2-pt linestrings from Multilinestrings running very very very slow

Hi all,

I began running this query on a table with 19,763 rows...and it is taking forever (up to 14 hours and still running). I was wondering if there was any other way to optimize this... (note: without creating edges with Topology - which is equally slow)? 

Thank you for your time.

Specs:
PostgreSQL 9.2.4
PostGis 2.1.0

SELECT row_number() OVER() new_id, geom
INTO linestrings FROM
  (SELECT
    (ST_Dump(ST_Node(ST_Union(geom)))).geom geom
  FROM multlinestrings) a;

_______________________________________________
postgis-users mailing list
postgis-users <at> lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Gmane