[postgis-users] ERROR: WKB structure does not match expected size!

[Francisco Salas Rosette]

Hi,

When I try to restore my database ocurr this problems 

 

pg_restore: [archiver (db)] Error while PROCESSING TOC:

pg_restore: [archiver (db)] Error from TOC entry 3737; 0 18356 TABLE DATA
test postgres

pg_restore: [archiver (db)] COPY failed for table "test": ERROR:  WKB
structure does not match expected size!

CONTEXT:  COPY test, line 238554, column the_geom:
"0105A0AB100100010280150140087021D9B853C0AEF7080BA019364
06508A39F..."

pg_restore: executing SEQUENCE SET test_gid_seq

 

I have PostgreSQL 9.2.4.1 y Postgis 2.1.0 

 

Thanks & Regards

Francisco Salas



Anuncie sus servicios en ANDARIEGO… aplicación para celulares sobre mapas de 
toda Cuba.  
Visite el sitio http://andariego.geocuba.cu o http://www.andariego.cu, 
contáctenos por el correo andari...@geosi.geocuba.cu o por el teléfono 8817400. 
Ponga su información al servicio de todos.
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Help with SQL query?

[Darrel Maddy]

Dear Regina,

Many thanks for this suggestion.

I ran the query in this form and for one raster it takes 3372s (~55 mins).  I 
guess that is what I had anticipated from the single tile  exercise I ran with 
the alternate algorithm.

This still seems a little too long however and so I have started to explore 
ways to improve upon this by pre-processing some of the data.  The cells of 
interest represent the ‘main channels’ in a drainage network.  Although in 
future this network may change position from one output timestep to another 
(they are actually 1000l iterations of the model apart) in this particular 
variant the position of these cells is static.  With that in mind I decided to 
create a binary raster where the 1’s represent the channel cells (I had to do 
this in arcgis as QGIS does not appear to have a Con function in the raster 
calculator!)  and then exported this as a point layer. I then deleted the 
points coded zero and saved the shp file in QGIS.  I imported the ntwork shp 
file into postgis (there are about 15500 15 points) and I am now running:

CREATE TABLE mymodel.networkdep AS
SELECT filename, gid, ST_Value(rast, geom) val
FROM mymodel.deposition, mymodel.network
WHERE ST_Intersects(rast, geom)
ORDER BY gid, rid;

I will sum by raster (i.e. filename) using the new table but will settle for 
just having the relevant data for now. This took  2057s(~35 minutes!) to 
complete!

If this is the best way to do it I will explore the OGR library and try and 
hardcode the network point output directly into the model or, more 
realistically, write a short routine to extract this automatically from the 
flow accumulation output rasters without recourse to a GIS.

I am learning a lot through this exercise, so thanks once again to all of you 
who have made suggestions, they are very much appreciated.

Best wishes

Darrel








From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of 
Paragon Corporation
Sent: 26 November 2015 05:08
To: 'PostGIS Users Discussion' 
Subject: Re: [postgis-users] Help with SQL query?

That timing seems much slower than I recall.

FWIW expression based mapalgebra as I recall is slower than using the call back 
function approach.  So you could try wrapping your CASE in a call back function.

However I think something else might be going on here and postgres might be 
repeating work.  I forgot under what conditions it decides to reevaluate a 
function call, I just remember being really surprised by it.

To avoid that, you can try using a CTE, also you don't need that ST_Union call 
which for larger number of rasters is expensive, and you might even generate a 
raster that is too big to compute.

I'm also guessing your rasts are all tiled the same, so you really don't need 
ST_Intersects, just use the same box operator

So try this:

WITH  foo AS (
  SELECT ST_SummaryStats( ST_MapAlgebra(deposition.rast, concentrated.rast, 
'CASE WHEN [rast2] > 0.0 THEN [rast1] ELSE NULL END' ) ) As st
FROM mymodel.deposition INNER JOIN mymodel.concentrated ON ( 
deposition.rast  ~=  concentrated.rast )
WHERE deposition.rid=1

)
SELECT SUM( (st).sum )
FROM foo;


Hope that helps,
Regina
http://www.postgis.us
http://postgis.net


From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of 
Darrel Maddy
Sent: Wednesday, November 25, 2015 5:06 PM
To: PostGIS Users Discussion 
>; Brent 
Wood >
Subject: Re: [postgis-users] Help with SQL query?

Dear Brent,

I must confess that my attempts to do this are so far proving very unsuccessful

If  I run the following query:

SELECT  (ST_SummaryStats(ST_Union(rast))).sum AS sum
FROM  (SELECT ST_MapAlgebra(deposition.rast, concentrated.rast, 'CASE WHEN 
[rast2] > 0.0 THEN [rast1] ELSE NULL END' ) As rast
FROM mymodel.deposition, mymodel.concentrated
WHERE ST_Intersects(deposition.rast, concentrated.rast) AND 
deposition.rid=1 ) foo ;

It takes around 30 seconds to complete as I assume it is only looking at one 
tile(they are 256x256 pixels) i.e. rid 1. It is not easy to check the sum – for 
that I need one complete raster.

For the record this was marginally faster than
SELECT (ST_SummaryStats(ST_Union(rast))).sum AS sum
FROM (SELECT ST_MapAlgebra(deposition.rast, concentrated.rast, 'CASE WHEN 
[rast2] > 0.0 THEN [rast1] ELSE NULL END' ) As rast
FROM mymodel.deposition, mymodel.concentrated
WHERE mymodel.deposition.filename='10_depo.tif' AND 
ST_UpperleftX(mymodel.deposition.rast) = 
ST_UpperleftX(mymodel.concentrated.rast) AND
 ST_UpperleftY(mymodel.deposition.rast) = 
ST_UpperleftY(mymodel.deposition.rast) ) foo ;
Even after I built indexes for the clauses after the WHERE.

Now there are 144 tiles in each of the rasters I want to perform this operation 
on.  Logic would 

Re: [postgis-users] Help with SQL query?

[Darrel Maddy]

Dear Regina,

Yes, I should have been clearer. To be honest I did not fully understand what I 
was trying to do at the outset ☺

The binary raster to point conversion  was an afterthought as I had already 
been extracting data along short transects using a point shape file.  For the 
record arc has a function in the raster calculator which works as a conditional 
such that
CON(raster, true, false, condition) which in my case was simply CON(FA, 1, 0 
“value > 600”).  This produces a raster which I then had to convert to points 
and eliminate the zeros.  I’m sure there is a way to do this in QGIS but the 
current raster calculator does not allow that directly.  FYI my rasters have 
only one band as they are being used to store numerical model matrix outputs so 
that I can readily visualise them in order to allow me to see structures I 
would not recognise hidden within the 5 million cell datasets.

Anyhow I will experiment with doing more of this in postgis (it would be great 
if I could script an end-to-end solution). Once I am happy with the numerical 
model I will have the model write the data directly into postgis.

I have made some progress this week  thanks to your help. Hopefully I am 
beginning to see how best to use this tool for my intended purpose.

Thanks again

Darrel



From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of 
Paragon Corporation
Sent: 27 November 2015 20:02
To: 'PostGIS Users Discussion' 
Subject: Re: [postgis-users] Help with SQL query?

Darrel,

Oh that's what you are trying to do sorry I didn't recognize that whole CASE 
thing as a binary check operation until you described the purpose.

For the bit operation type stuff it is much faster to define that 0/1 as a 
geometry (which it looks like you've done, but I don't know if you just have 
one pixel cell per or what or details of how you do it in ArcGIS.  I suspect 
that logic can be recreated easily in PostGIS with something like below:

If your network raster is just a set of 0s and 1s (is it a 1BB) or you just 
want to treat 0 as no-date (which is essentially what you case statement was 
trying to do I think) then you could just convert it to a geometry with these 
functions
http://postgis.net/docs/manual-2.2/RT_ST_Polygon.html, 
http://postgis.net/docs/manual-2.2/RT_ST_SetBandNoDataValue.html

and this SQL Statement

CREATE TABLE mymodel.network AS
SELECT rid As gid, ST_Polygon(ST_SetBandNoDataValue(rast,1, 0) ) As geom
FROM mymodel.concentrated ;


Once you have that concentrated as a network channel as a geometry, then you 
can use ST_Clip and that should be pretty fast and give you the same results.
http://postgis.net/docs/manual-2.2/RT_ST_Clip.html


So your query would look something like

WITH  foo AS (
  SELECT  mymodel.deposition.rid,   ST_SummaryStats( ST_Clip(rast, geom) ) As st
FROM mymodel.deposition INNER JOIN mymodel.network ON ( 
ST_Intersects(rast,geom) )

)
SELECT SUM( (st).sum )
FROM foo;

Hope that helps,
Regina
http://www.postgis.us
http://postgis.net


From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of 
Darrel Maddy
Sent: Friday, November 27, 2015 12:42 PM
To: PostGIS Users Discussion 
>
Subject: Re: [postgis-users] Help with SQL query?

Dear Regina,

Many thanks for this suggestion.

I ran the query in this form and for one raster it takes 3372s (~55 mins).  I 
guess that is what I had anticipated from the single tile  exercise I ran with 
the alternate algorithm.

This still seems a little too long however and so I have started to explore 
ways to improve upon this by pre-processing some of the data.  The cells of 
interest represent the ‘main channels’ in a drainage network.  Although in 
future this network may change position from one output timestep to another 
(they are actually 1000l iterations of the model apart) in this particular 
variant the position of these cells is static.  With that in mind I decided to 
create a binary raster where the 1’s represent the channel cells (I had to do 
this in arcgis as QGIS does not appear to have a Con function in the raster 
calculator!)  and then exported this as a point layer. I then deleted the 
points coded zero and saved the shp file in QGIS.  I imported the ntwork shp 
file into postgis (there are about 15500 15 points) and I am now running:

CREATE TABLE mymodel.networkdep AS
SELECT filename, gid, ST_Value(rast, geom) val
FROM mymodel.deposition, mymodel.network
WHERE ST_Intersects(rast, geom)
ORDER BY gid, rid;

I will sum by raster (i.e. filename) using the new table but will settle for 
just having the relevant data for now. This took  2057s(~35 minutes!) to 
complete!

If this is the best way to do it I will explore the OGR library and try and 
hardcode the network point output directly into the model or, more 
realistically, write a short routine to extract this automatically 

Re: [postgis-users] Can we limit the search range of geocode() function in Tiger Geocoder?

[Paragon Corporation]

Dracodoc,

Nice writeup.  I'll respond on your stack exchange question.

That might be a good enhancement option to focus on for PostGIS 2.3. Haven't
thought of how that filtering would work.
Perhaps another geocode function that takes in a filter of desired states.

Anyrate yah the geometry filter is not really optimized and was more
designed for random polygon areas where you absolutely know an address has
to fall, and it probably needs some performance work since I didn't spend
too much time creating it.

Thanks,
Regina
http://postgis.net
http://www.postgis.us


--- ORIGINA MESSAGE --
Hi all, I asked this question in gis stackexchange here already
.
I'm also posting the question in the mailing list to make sure the experts
can see the question.

I found the server with only 2 states data loaded is much faster than the
server with all states loaded. My theory is bad formatted address that
don't have a exact hit at first will cost much more time when the geocoder
checked all states. With only 2 states this search is limited and stopped
much early.

There is a restrict_region parameter in geocode function looks promising if
it can limit the search range, since I have enough information or reason to
believe the state information in my addresses input are correct.

I wrote a query trying to use one state's geometry as the limiting
parameter:

SELECT geocode('501 Fairmount DR , Annapolis, MD 20137', 1, the_geom)
FROM tiger.state WHERE statefp = '24';


and compared the performance with the simple version

SELECT geocode('501 Fairmount DR , Annapolis, MD 20137',1);


I didn't find performance gain with the parameter. Instead it lost the
performance gain from caching, which usually came from running same query
immediately again because all the needed data have been cached in RAM.

Maybe my usage is not proper, or this parameter is not intended to work as
I expected.

However if the search range can be limited, the performance gain could be
substantial, since it's the bad formatted addresses took the most time to
geocode, and they also often mess up the already cached data because the
geocoder need to search for states, even all my input are in one state and
all data can be cached in RAM.

Thanks!

By the way, I wrote about my system setup
 and work flow
 in my blog. Wish it
can help other novices in geocoding.


___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Help with SQL query?

[Darrel Maddy]

Dear Brent,

Yes that works for me too !

Sometimes I look for what is familiar and overlook the obvious – a little 
embarrassing, but I know now .

Many thanks

Darrel

From: Brent Wood [mailto:pcr...@yahoo.com]
Sent: 27 November 2015 21:53
To: PostGIS Users Discussion ; Darrel Maddy 

Subject: Re: [postgis-users] Help with SQL query?


Darrel

You can use the logical operators in the QGIS raster calculator as a binary 
conditional :

eg: raster a is used to generate a new raster with default 0, but pixel value=1 
 where pixels in a have a value >300...   use the following operation

(a > 300.0)

Save the result as a grid rather than a image. It works for me
Brent

___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Help with SQL query?

[Paragon Corporation]

Darrel,

 

Oh that's what you are trying to do sorry I didn't recognize that whole CASE 
thing as a binary check operation until you described the purpose.

 

For the bit operation type stuff it is much faster to define that 0/1 as a 
geometry (which it looks like you've done, but I don't know if you just have 
one pixel cell per or what or details of how you do it in ArcGIS.  I suspect 
that logic can be recreated easily in PostGIS with something like below:

 

If your network raster is just a set of 0s and 1s (is it a 1BB) or you just 
want to treat 0 as no-date (which is essentially what you case statement was 
trying to do I think) then you could just convert it to a geometry with these 
functions

http://postgis.net/docs/manual-2.2/RT_ST_Polygon.html, 
http://postgis.net/docs/manual-2.2/RT_ST_SetBandNoDataValue.html 

 

and this SQL Statement

 

CREATE TABLE mymodel.network AS

SELECT rid As gid, ST_Polygon(ST_SetBandNoDataValue(rast,1, 0) ) As geom

FROM mymodel.concentrated ;

 

 

Once you have that concentrated as a network channel as a geometry, then you 
can use ST_Clip and that should be pretty fast and give you the same results.

http://postgis.net/docs/manual-2.2/RT_ST_Clip.html

 

 

So your query would look something like

 

WITH  foo AS (

  SELECT  mymodel.deposition.rid,   ST_SummaryStats( ST_Clip(rast, geom) ) As st

FROM mymodel.deposition INNER JOIN mymodel.network ON ( 
ST_Intersects(rast,geom) )

   

)

SELECT SUM( (st).sum )

FROM foo;

 

Hope that helps,

Regina

http://www.postgis.us

http://postgis.net

 

 

From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of 
Darrel Maddy
Sent: Friday, November 27, 2015 12:42 PM
To: PostGIS Users Discussion 
Subject: Re: [postgis-users] Help with SQL query?

 

Dear Regina,

 

Many thanks for this suggestion.

 

I ran the query in this form and for one raster it takes 3372s (~55 mins).  I 
guess that is what I had anticipated from the single tile  exercise I ran with 
the alternate algorithm.

 

This still seems a little too long however and so I have started to explore 
ways to improve upon this by pre-processing some of the data.  The cells of 
interest represent the ‘main channels’ in a drainage network.  Although in 
future this network may change position from one output timestep to another 
(they are actually 1000l iterations of the model apart) in this particular 
variant the position of these cells is static.  With that in mind I decided to 
create a binary raster where the 1’s represent the channel cells (I had to do 
this in arcgis as QGIS does not appear to have a Con function in the raster 
calculator!)  and then exported this as a point layer. I then deleted the 
points coded zero and saved the shp file in QGIS.  I imported the ntwork shp 
file into postgis (there are about 15500 15 points) and I am now running:

 

CREATE TABLE mymodel.networkdep AS

SELECT filename, gid, ST_Value(rast, geom) val

FROM mymodel.deposition, mymodel.network

WHERE ST_Intersects(rast, geom) 

ORDER BY gid, rid;

 

I will sum by raster (i.e. filename) using the new table but will settle for 
just having the relevant data for now. This took  2057s(~35 minutes!) to 
complete! 

 

If this is the best way to do it I will explore the OGR library and try and 
hardcode the network point output directly into the model or, more 
realistically, write a short routine to extract this automatically from the 
flow accumulation output rasters without recourse to a GIS.

 

I am learning a lot through this exercise, so thanks once again to all of you 
who have made suggestions, they are very much appreciated.

 

Best wishes

 

Darrel 

 

 

 

 

 

 

 

 

From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of 
Paragon Corporation
Sent: 26 November 2015 05:08
To: 'PostGIS Users Discussion'  >
Subject: Re: [postgis-users] Help with SQL query?

 

That timing seems much slower than I recall.

 

FWIW expression based mapalgebra as I recall is slower than using the call back 
function approach.  So you could try wrapping your CASE in a call back function.

 

However I think something else might be going on here and postgres might be 
repeating work.  I forgot under what conditions it decides to reevaluate a 
function call, I just remember being really surprised by it.

 

To avoid that, you can try using a CTE, also you don't need that ST_Union call 
which for larger number of rasters is expensive, and you might even generate a 
raster that is too big to compute.

 

I'm also guessing your rasts are all tiled the same, so you really don't need 
ST_Intersects, just use the same box operator

 

So try this:

 

WITH  foo AS (

  SELECT ST_SummaryStats( ST_MapAlgebra(deposition.rast, concentrated.rast, 
'CASE WHEN [rast2] > 0.0 THEN [rast1] ELSE NULL END' ) ) As st

Re: [postgis-users] Help with SQL query?

[Paragon Corporation]

Darrel,

 

I think the equivalent in PostGIS terminology would be ST_Reclass - 
http://postgis.net/docs/manual-2.2/RT_ST_Reclass.html

 

And that's a fairly fast operation as I recall.

 

Hope that helps,

Regina

 

 

 

From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of 
Darrel Maddy
Sent: Friday, November 27, 2015 4:09 PM
To: PostGIS Users Discussion 
Subject: Re: [postgis-users] Help with SQL query?

 

Dear Regina,

 

Yes, I should have been clearer. To be honest I did not fully understand what I 
was trying to do at the outset :)

 

The binary raster to point conversion  was an afterthought as I had already 
been extracting data along short transects using a point shape file.  For the 
record arc has a function in the raster calculator which works as a conditional 
such that 
CON(raster, true, false, condition) which in my case was simply CON(FA, 1, 0 
“value > 600”).  This produces a raster which I then had to convert to points 
and eliminate the zeros.  I’m sure there is a way to do this in QGIS but the 
current raster calculator does not allow that directly.  FYI my rasters have 
only one band as they are being used to store numerical model matrix outputs so 
that I can readily visualise them in order to allow me to see structures I 
would not recognise hidden within the 5 million cell datasets.

 

Anyhow I will experiment with doing more of this in postgis (it would be great 
if I could script an end-to-end solution). Once I am happy with the numerical 
model I will have the model write the data directly into postgis. 

 

I have made some progress this week  thanks to your help. Hopefully I am 
beginning to see how best to use this tool for my intended purpose.

 

Thanks again

 

Darrel

 

 

 

From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of 
Paragon Corporation
Sent: 27 November 2015 20:02
To: 'PostGIS Users Discussion'  >
Subject: Re: [postgis-users] Help with SQL query?

 

Darrel,

 

Oh that's what you are trying to do sorry I didn't recognize that whole CASE 
thing as a binary check operation until you described the purpose.

 

For the bit operation type stuff it is much faster to define that 0/1 as a 
geometry (which it looks like you've done, but I don't know if you just have 
one pixel cell per or what or details of how you do it in ArcGIS.  I suspect 
that logic can be recreated easily in PostGIS with something like below:

 

If your network raster is just a set of 0s and 1s (is it a 1BB) or you just 
want to treat 0 as no-date (which is essentially what you case statement was 
trying to do I think) then you could just convert it to a geometry with these 
functions

http://postgis.net/docs/manual-2.2/RT_ST_Polygon.html, 
http://postgis.net/docs/manual-2.2/RT_ST_SetBandNoDataValue.html 

 

and this SQL Statement

 

CREATE TABLE mymodel.network AS

SELECT rid As gid, ST_Polygon(ST_SetBandNoDataValue(rast,1, 0) ) As geom

FROM mymodel.concentrated ;

 

 

Once you have that concentrated as a network channel as a geometry, then you 
can use ST_Clip and that should be pretty fast and give you the same results.

http://postgis.net/docs/manual-2.2/RT_ST_Clip.html

 

 

So your query would look something like

 

WITH  foo AS (

  SELECT  mymodel.deposition.rid,   ST_SummaryStats( ST_Clip(rast, geom) ) As st

FROM mymodel.deposition INNER JOIN mymodel.network ON ( 
ST_Intersects(rast,geom) )

   

)

SELECT SUM( (st).sum )

FROM foo;

 

Hope that helps,

Regina

http://www.postgis.us

http://postgis.net

 

 

From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of 
Darrel Maddy
Sent: Friday, November 27, 2015 12:42 PM
To: PostGIS Users Discussion  >
Subject: Re: [postgis-users] Help with SQL query?

 

Dear Regina,

 

Many thanks for this suggestion.

 

I ran the query in this form and for one raster it takes 3372s (~55 mins).  I 
guess that is what I had anticipated from the single tile  exercise I ran with 
the alternate algorithm.

 

This still seems a little too long however and so I have started to explore 
ways to improve upon this by pre-processing some of the data.  The cells of 
interest represent the ‘main channels’ in a drainage network.  Although in 
future this network may change position from one output timestep to another 
(they are actually 1000l iterations of the model apart) in this particular 
variant the position of these cells is static.  With that in mind I decided to 
create a binary raster where the 1’s represent the channel cells (I had to do 
this in arcgis as QGIS does not appear to have a Con function in the raster 
calculator!)  and then exported this as a point layer. I then deleted the 
points coded zero and saved the shp file in QGIS.  I imported the ntwork shp 
file 

[postgis-users] Can we limit the search range of geocode() function in Tiger Geocoder?

[draco doc]

Hi all, I asked this question in gis stackexchange here already
.
I'm also posting the question in the mailing list to make sure the experts
can see the question.

I found the server with only 2 states data loaded is much faster than the
server with all states loaded. My theory is bad formatted address that
don't have a exact hit at first will cost much more time when the geocoder
checked all states. With only 2 states this search is limited and stopped
much early.

There is a restrict_region parameter in geocode function looks promising if
it can limit the search range, since I have enough information or reason to
believe the state information in my addresses input are correct.

I wrote a query trying to use one state's geometry as the limiting
parameter:

SELECT geocode('501 Fairmount DR , Annapolis, MD 20137', 1, the_geom)
FROM tiger.state WHERE statefp = '24';


and compared the performance with the simple version

SELECT geocode('501 Fairmount DR , Annapolis, MD 20137',1);


I didn't find performance gain with the parameter. Instead it lost the
performance gain from caching, which usually came from running same query
immediately again because all the needed data have been cached in RAM.

Maybe my usage is not proper, or this parameter is not intended to work as
I expected.

However if the search range can be limited, the performance gain could be
substantial, since it's the bad formatted addresses took the most time to
geocode, and they also often mess up the already cached data because the
geocoder need to search for states, even all my input are in one state and
all data can be cached in RAM.

Thanks!

By the way, I wrote about my system setup
 and work flow
 in my blog. Wish it
can help other novices in geocoding.
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] Help with SQL query?

[Paragon Corporation]

For completeness, your vector query would then look like

 

CREATE TABLE mymodel.network AS

SELECT rid As gid, ST_Polygon(ST_Reclass(rast,  1, '[0-600):0, 
[600-1]:1','1BB', 0) ) As geom

FROM mymodel.concentrated ;

 

 

 

I think you can put in –number in there like - -1000-600.  There was an issue 
way back with negatives but I thnk that was fixed in PostGIS 2.1 something so 
should work

 

So what that basically does is create a new raster from original setting of 
pixel values from 0 to < 600 to 0 and from >= 600 to 1 to 1 and making that 
a 1BB (1-bit booleanl raster), and then definiing 0 as no data so that when you 
convert it to a multipolygon you'll only vectorize the 600-1 range.

 

I think ST_polygon against a 1BB is faster than larger band pixel types.

 

Hope that helps,

Regina

 

 

From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of 
Paragon Corporation
Sent: Friday, November 27, 2015 10:04 PM
To: 'PostGIS Users Discussion' 
Subject: Re: [postgis-users] Help with SQL query?

 

Darrel,

 

I think the equivalent in PostGIS terminology would be ST_Reclass -  
 
http://postgis.net/docs/manual-2.2/RT_ST_Reclass.html

 

And that's a fairly fast operation as I recall.

 

Hope that helps,

Regina

 

 

 

From: postgis-users [  
mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of Darrel Maddy
Sent: Friday, November 27, 2015 4:09 PM
To: PostGIS Users Discussion <  
postgis-users@lists.osgeo.org>
Subject: Re: [postgis-users] Help with SQL query?

 

Dear Regina,

 

Yes, I should have been clearer. To be honest I did not fully understand what I 
was trying to do at the outset :)

 

The binary raster to point conversion  was an afterthought as I had already 
been extracting data along short transects using a point shape file.  For the 
record arc has a function in the raster calculator which works as a conditional 
such that 
CON(raster, true, false, condition) which in my case was simply CON(FA, 1, 0 
“value > 600”).  This produces a raster which I then had to convert to points 
and eliminate the zeros.  I’m sure there is a way to do this in QGIS but the 
current raster calculator does not allow that directly.  FYI my rasters have 
only one band as they are being used to store numerical model matrix outputs so 
that I can readily visualise them in order to allow me to see structures I 
would not recognise hidden within the 5 million cell datasets.

 

Anyhow I will experiment with doing more of this in postgis (it would be great 
if I could script an end-to-end solution). Once I am happy with the numerical 
model I will have the model write the data directly into postgis. 

 

I have made some progress this week  thanks to your help. Hopefully I am 
beginning to see how best to use this tool for my intended purpose.

 

Thanks again

 

Darrel

 

 

 

From: postgis-users [  
mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of Paragon Corporation
Sent: 27 November 2015 20:02
To: 'PostGIS Users Discussion' <  
postgis-users@lists.osgeo.org>
Subject: Re: [postgis-users] Help with SQL query?

 

Darrel,

 

Oh that's what you are trying to do sorry I didn't recognize that whole CASE 
thing as a binary check operation until you described the purpose.

 

For the bit operation type stuff it is much faster to define that 0/1 as a 
geometry (which it looks like you've done, but I don't know if you just have 
one pixel cell per or what or details of how you do it in ArcGIS.  I suspect 
that logic can be recreated easily in PostGIS with something like below:

 

If your network raster is just a set of 0s and 1s (is it a 1BB) or you just 
want to treat 0 as no-date (which is essentially what you case statement was 
trying to do I think) then you could just convert it to a geometry with these 
functions

  
http://postgis.net/docs/manual-2.2/RT_ST_Polygon.html,  
 
http://postgis.net/docs/manual-2.2/RT_ST_SetBandNoDataValue.html 

 

and this SQL Statement

 

CREATE TABLE mymodel.network AS

SELECT rid As gid, ST_Polygon(ST_SetBandNoDataValue(rast,1, 0) ) As geom

FROM mymodel.concentrated ;

 

 

Once you have that concentrated as a network channel as a geometry, then you 
can use ST_Clip and that should be pretty fast and give you the same results.

  
http://postgis.net/docs/manual-2.2/RT_ST_Clip.html

 

 

So your query would look something like

 

WITH  foo AS (

  SELECT  mymodel.deposition.rid,   ST_SummaryStats( ST_Clip(rast, geom) ) As st

FROM mymodel.deposition INNER JOIN mymodel.network ON (