Re: [postgis-users] [EXTERNAL] Re: ST_Resample function question.

[Bborie Park]

I think the problem here is that the code treats a raster with no SRS
differently than a raster with SRS where reprojection will NOT take place
(essentially, same Cartesian plane for source raster and destination
raster).

The key offending(?) line is
https://github.com/postgis/postgis/blob/svn-trunk/raster/rt_core/rt_warp.c#L273

I'll see if removing that line breaks any existing behavior per unit tests.
If no issues, your use case should just-work. I'll probably be able to
back-patch this into prior releases (2.1+) as not much has happened in the
GDAL Warp wrapper for a few years.

-bborie

On Tue, Jun 12, 2018 at 7:47 AM Birkett, Scott 
wrote:

> The comment that an unset SRID forcing a transformation to happen makes
> sense.
>
> You stated that the following would fail because 
> GDALCreateGenImgProjTransformer2
> would just return null, and null can’t be transformed into a raster.
>
>
>
> So the following fails, we have seen that.
>
> *SELECT *
>
> *ST_Resample(*
>
> *ST_SetSRID(*
>
>
> *'010200F03FF0BF0200020008080100010001000100'::raster,*
>
> *4326),*
>
> *2, 2)*
>
>
>
> But the following still returns the same *error*, when a height and width
> change is requested.
>
> *SELECT *
>
> *ST_Resample(*
>
> *ST_SetSRID(*
>
>
> *'010200F03FF0BF0200020008080100010001000100'::raster,*
>
> *4326),*
>
> *4, 4)*
>
>
>
> While this works
>
> *SELECT *
>
> *ST_Resample(*
>
>
> *'010200F03FF0BF0200020008080100010001000100'::raster,*
>
> *4, 4)*
>
>
>
> Should a resample that changes the height and width still create an error?
>
>
>
> Thanks for your help so far.
>
>
>
> *From: *postgis-users  on behalf
> of Regina Obe 
> *Reply-To: *PostGIS Users Discussion 
> *Date: *Monday, June 11, 2018 at 4:33 PM
> *To: *'PostGIS Users Discussion' 
> *Cc: *"Gruca, Justin" , "Conrad, Gabe" <
> gabe.con...@pioneer.com>
> *Subject: *[EXTERNAL] Re: [postgis-users] ST_Resample function question.
>
>
>
> That error happens when gdal GDALCreateGenImgProjTransformer2 returns a
> NULL transform which I'm guessing happens if that data is already at that
> sampling.
>
> I know other conditions for that are if GDAL can't load the proj library
> or GDAL_DATA path is not set.
>
>
>
> Your data already has a width of 2,2 so I assume that if its already at
> the desired sampling it may return NULL.
>
>
>
> For example:
>
> SELECT ST_Resample(ST_ASRaster(
>
>'SRID=4326;POLYGON((0 0,2 0,2 -2,0 -2,0 0))'::geometry, 2,
> 2),2,2);
>
>
>
> Yields the error you describe:
>
> ERROR: rt_raster_gdal_warp: Could not create GDAL transformation object
> for output dataset creation
>
>
>
> But if my original dimensions were width / height 4/4 and I request 2/2,
> then this works
>
>
>
> SELECT ST_Resample(ST_ASRaster(
>
>'SRID=4326;POLYGON((0 0,2 0,2 -2,0 -2,0 0))'::geometry, 4,
> 4),2,2);
>
>
>
> But that doesn't explain why this doesn't work:
>
>
>
> SELECT ST_Resample(ST_ASRaster(
>
>'SRID=4326;POLYGON((0 0,2 0,2 -2,0 -2,0 0))'::geometry, 4,
> 4),2,2);
>
>
>
>
>
> As far as why it works when you have no SRID, the raster code uses a
> hard-coded geotransform matrix when it's an unknown raster so it always
> gets a geotransform back it can use to apply.
>
>
>
> Hope that helps,
>
> Regina
>
>
>
>
>
>
>
> *From:* postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] *On
> Behalf Of *Birkett, Scott
> *Sent:* Monday, June 11, 2018 4:27 PM
> *To:* postgis-users@lists.osgeo.org
> *Cc:* Gruca, Justin ; Conrad, Gabe <
> gabe.con...@pioneer.com>
> *Subject:* [postgis-users] ST_Resample function question.
>
>
>
> "PostgreSQL 9.5.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3
> 20140911 (Red Hat 4.8.3-9), 64-bit POSTGIS="2.2.5 r15298"
> GEOS="3.5.1-CAPI-1.9.1 r4246" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL
> 2.0.3, released 2016/07/01" LIBXML="2.9.1" LIBJSON="0 (...)"
>
>
>
> The raster created by …
>
> SELECT
> '010200F03FF0BF0200020008080100010001000100'::raster,
> is a two band raster with zeros in the first band and ones in the second
> band.
>
>
>
> The following Resample works.
>
> //
>
>
>
> *SELECT
> 

Re: [postgis-users] add raster constraint exceeds array size limit

[Bborie Park]

Wow... that's new. I'm guessing that the array that collects the individual
tiles' extents is getting too big. Can you file a ticket?

On Fri, Mar 11, 2016 at 12:08 AM, Caitríona Smith 
wrote:

> Hi All
>
> I’ve a number of largish rasters that I’m wanting to tile to relatively
> small tiles, but I’m hitting the array limit on the add maximum extent
> constraint.
>
> If the tile_size is 32x32 or 50x50 I get the error, but a tile size of
> 64x64 completes.
>
> At 50x50 there are 13M rows and at 64x64 there are 8M
>
> I’m wanting to use fairly small tiles as I am intending to version control
> the data at the tile level, and would prefer to keep the area that doesn’t
> change under control to a minimum.
>
> I’m thinking that I might be able to get away with a parent child
> relationship, and that I may have to do that anyway, for performance, but
> I’m not sure what the effect of that will be on the extent constraint.
>
> Is there something else that I am doing wrong, or is there another way
> around this (I’ve not really looked at the component functions to see what
> they do)
>
>
>
> I’m running postgis 2.2.1 on postgres 9.5.1 on SL7.2 for my sins
>
>
>
>
> select version();
>  version
>
>
>
>
> 
> -
>  PostgreSQL 9.5.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
> 20150623 (
> Red Hat 4.8.5-4), 64-bit
> (1 row)
>
>
>
>
> select postgis_full_version();
>
>   post
> gis_full_version
>
>
>
>
> 
>
> 
> 
>  POSTGIS="2.2.1 r14555" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.8.0, 6
> March
> 2012" GDAL="GDAL 1.11.4, released 2016/01/25" LIBXML="2.9.1"
> LIBJSON="0.11" TOPO
> LOGY RASTER
> (1 row)
>
>
>
> hmdb=> select count(*) from dtm2015_50x50;
>   count
> --
>  13360464
> (1 row)
>
> hmdb=> select count(*) from dtm2015;
>   count
> -
>  8152320
> (1 row)
>
>
>
> hmdb=> select addrasterconstraints('dtm2015_50x50'::name, 'rast'::name);
> NOTICE:  Adding SRID constraint
> CONTEXT:  PL/pgSQL function
> addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean)
> line 53 at RETURN
> NOTICE:  Adding scale-X constraint
> CONTEXT:  PL/pgSQL function
> addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean)
> line 53 at RETURN
> NOTICE:  Adding scale-Y constraint
> CONTEXT:  PL/pgSQL function
> addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean)
> line 53 at RETURN
> NOTICE:  Adding blocksize-X constraint
> CONTEXT:  PL/pgSQL function
> addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean)
> line 53 at RETURN
> NOTICE:  Adding blocksize-Y constraint
> CONTEXT:  PL/pgSQL function
> addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean)
> line 53 at RETURN
> NOTICE:  Adding alignment constraint
> CONTEXT:  PL/pgSQL function
> addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean)
> line 53 at RETURN
> NOTICE:  Adding number of bands constraint
> CONTEXT:  PL/pgSQL function
> addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean)
> line 53 at RETURN
> NOTICE:  Adding pixel type constraint
> CONTEXT:  PL/pgSQL function
> addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean)
> line 53 at RETURN
> NOTICE:  Adding nodata value constraint
> CONTEXT:  PL/pgSQL function
> addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean)
> line 53 at RETURN
> NOTICE:  Adding out-of-database constraint
> CONTEXT:  PL/pgSQL function
> addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean)
> line 53 at RETURN
> NOTICE:  Adding maximum extent constraint
> CONTEXT:  PL/pgSQL function
> addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean)
> line 53 at RETURN
> ERROR:  array size exceeds the maximum allowed (1073741823)
> CONTEXT:  SQL statement "SELECT
> st_ashexewkb(st_envelope(st_collect(st_envelope(rast FROM
> cjsmith.dtm2015_50x50"
> PL/pgSQL function _add_raster_constraint_extent(name,name,name) line 20 at
> EXECUTE
> PL/pgSQL 

Re: [postgis-users] restore problem

[Bborie Park]

This problem arises because PostgreSQL sets the search path during the
restore process. Unfortunately, the search path is quite restrictive.

You should be able to do your restore in three sections using pg_restore's
--section flag.

The error looks like it has to be the post-data section, which you should
output to a text file, change the "SET search_path" statements and then run
through psql.

-bborie

On Tue, Nov 10, 2015 at 1:11 PM, Darrel Maddy 
wrote:

> Dear all,
>
>
>
> Like many here I suspect I want to keep a copy of my main databases held
> on my workstation, on my laptop. I used PgAdmin to backup the databases
> concerned (the backup files look about the size I was expecting)  but
> restore on my laptop did not successfully complete.  All my shp file tables
> restored without issues but my raster tables (these are tiled rasters)
> would not. Looking at the error in the restore window of PgAdmin this
> appears to be a problem with a function/constraint.
>
>
>
> pg_restore: processing data for table "dems"
>
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
>
> pg_restore: [archiver (db)] Error from TOC entry 3459; 0 94054 TABLE DATA
> dems postgres
>
> pg_restore: [archiver (db)] COPY failed for table "dems": ERROR:  function
> st_bandmetadata(public.raster, integer[]) does not exist
>
> LINE 1:  SELECT array_agg(pixeltype)::text[] FROM st_bandmetadata($1...
>
>   ^
>
> HINT:  No function matches the given name and argument types. You might
> need to add explicit type casts.
>
> QUERY:   SELECT array_agg(pixeltype)::text[] FROM st_bandmetadata($1,
> ARRAY[]::int[]);
>
> CONTEXT:  SQL function "_raster_constraint_pixel_types" during inlining
>
> COPY dems, line 1: "1
> 010100344034C0E0CCB1D51741806692F80C41..."
>
>
>
>
>
> I did a quick search and found some old chatter on this issue but from the
> messages I read I would have expected this issue to have been cleared up in
> postgis 2 .
>
>
>
> Obviously I cannot contemplate having mission critical data in a database
> which does not backup/restore correctly so I am assuming there is a fix
> which avoids this issue or there is another way to make a copy of the
> database for transfer elsewhere?
>
>
>
> Once again any help would be gratefully received.
>
>
>
> Best wishes
>
>
>
> Darrel
>
>
>
> ps.   Apologies for the string of questions I have asked lately – I will
> go silent once more shortly as I must move on to other things.
>
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
>
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] restore problem

[Bborie Park]

Given that the core problem has to do with the restore process'
search_path, no amount of advice will help you get around the restrictions
in place with the restore process without jumping into that process.

In pgAdmin, you can check the boxes "Pre-data" and "Data" of the "Restore
Options #1" tab. This won't restore the post-data portion of the backup as
restoring post-data should be causing your error.

You could also try unchecking the box "Exit On Error" of the "Restore
Options #2" tab.

-bborie

On Tue, Nov 10, 2015 at 1:29 PM, Darrel Maddy <darrel.ma...@newcastle.ac.uk>
wrote:

> Dear Bborie,
>
>
>
> Yikes – that makes little sense to me.  I am new to both postgres and
> postgis and would have hoped that backup and restore would have been simple
> push-button exercise!
>
>
>
> As I am using PgAdmin I have no idea how to follow your instructions L
>
>
>
> Anyone care to give me an idiots guide?
>
>
>
> Sorry
>
>
>
> Darrel
>
>
>
>
>
> *From:* postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] *On
> Behalf Of *Bborie Park
> *Sent:* 10 November 2015 21:22
> *To:* PostGIS Users Discussion <postgis-users@lists.osgeo.org>
> *Subject:* Re: [postgis-users] restore problem
>
>
>
> This problem arises because PostgreSQL sets the search path during the
> restore process. Unfortunately, the search path is quite restrictive.
>
>
>
> You should be able to do your restore in three sections using pg_restore's
> --section flag.
>
>
>
> The error looks like it has to be the post-data section, which you should
> output to a text file, change the "SET search_path" statements and then run
> through psql.
>
>
>
> -bborie
>
>
>
> On Tue, Nov 10, 2015 at 1:11 PM, Darrel Maddy <
> darrel.ma...@newcastle.ac.uk> wrote:
>
> Dear all,
>
>
>
> Like many here I suspect I want to keep a copy of my main databases held
> on my workstation, on my laptop. I used PgAdmin to backup the databases
> concerned (the backup files look about the size I was expecting)  but
> restore on my laptop did not successfully complete.  All my shp file tables
> restored without issues but my raster tables (these are tiled rasters)
> would not. Looking at the error in the restore window of PgAdmin this
> appears to be a problem with a function/constraint.
>
>
>
> pg_restore: processing data for table "dems"
>
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
>
> pg_restore: [archiver (db)] Error from TOC entry 3459; 0 94054 TABLE DATA
> dems postgres
>
> pg_restore: [archiver (db)] COPY failed for table "dems": ERROR:  function
> st_bandmetadata(public.raster, integer[]) does not exist
>
> LINE 1:  SELECT array_agg(pixeltype)::text[] FROM st_bandmetadata($1...
>
>   ^
>
> HINT:  No function matches the given name and argument types. You might
> need to add explicit type casts.
>
> QUERY:   SELECT array_agg(pixeltype)::text[] FROM st_bandmetadata($1,
> ARRAY[]::int[]);
>
> CONTEXT:  SQL function "_raster_constraint_pixel_types" during inlining
>
> COPY dems, line 1: "1
> 010100344034C0E0CCB1D51741806692F80C41..."
>
>
>
>
>
> I did a quick search and found some old chatter on this issue but from the
> messages I read I would have expected this issue to have been cleared up in
> postgis 2 .
>
>
>
> Obviously I cannot contemplate having mission critical data in a database
> which does not backup/restore correctly so I am assuming there is a fix
> which avoids this issue or there is another way to make a copy of the
> database for transfer elsewhere?
>
>
>
> Once again any help would be gratefully received.
>
>
>
> Best wishes
>
>
>
> Darrel
>
>
>
> ps.   Apologies for the string of questions I have asked lately – I will
> go silent once more shortly as I must move on to other things.
>
>
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
>
> ___
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
>
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

Re: [postgis-users] restore problem

[Bborie Park]

Odd. Looking at your error message again, it looks like the constraint is
already in place by the time the data is getting loaded...

What are you restoring from? Tar, custom or text?

On Tue, Nov 10, 2015 at 2:16 PM, Darrel Maddy <darrel.ma...@newcastle.ac.uk>
wrote:

> Dear Bborie,
>
>
>
> Apologies, I was obviously not clear – I was not asking about the problem
> itself, I can accept this is a difficult problem to resolve. I was simply
> asking for instruction on how to use your work around. This you kindly
> provided. Alas, however, even with the boxes checked, I get the same error
> and the raster table (dems) is empty.
>
>
>
> Darrel
>
>
>
> *From:* postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] *On
> Behalf Of *Bborie Park
> *Sent:* 10 November 2015 21:57
>
> *To:* PostGIS Users Discussion <postgis-users@lists.osgeo.org>
> *Subject:* Re: [postgis-users] restore problem
>
>
>
> Given that the core problem has to do with the restore process'
> search_path, no amount of advice will help you get around the restrictions
> in place with the restore process without jumping into that process.
>
>
>
> In pgAdmin, you can check the boxes "Pre-data" and "Data" of the "Restore
> Options #1" tab. This won't restore the post-data portion of the backup as
> restoring post-data should be causing your error.
>
>
>
> You could also try unchecking the box "Exit On Error" of the "Restore
> Options #2" tab.
>
>
>
> -bborie
>
>
>
> On Tue, Nov 10, 2015 at 1:29 PM, Darrel Maddy <
> darrel.ma...@newcastle.ac.uk> wrote:
>
> Dear Bborie,
>
>
>
> Yikes – that makes little sense to me.  I am new to both postgres and
> postgis and would have hoped that backup and restore would have been simple
> push-button exercise!
>
>
>
> As I am using PgAdmin I have no idea how to follow your instructions L
>
>
>
> Anyone care to give me an idiots guide?
>
>
>
> Sorry
>
>
>
> Darrel
>
>
>
>
>
> *From:* postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] *On
> Behalf Of *Bborie Park
> *Sent:* 10 November 2015 21:22
> *To:* PostGIS Users Discussion <postgis-users@lists.osgeo.org>
> *Subject:* Re: [postgis-users] restore problem
>
>
>
> This problem arises because PostgreSQL sets the search path during the
> restore process. Unfortunately, the search path is quite restrictive.
>
>
>
> You should be able to do your restore in three sections using pg_restore's
> --section flag.
>
>
>
> The error looks like it has to be the post-data section, which you should
> output to a text file, change the "SET search_path" statements and then run
> through psql.
>
>
>
> -bborie
>
>
>
> On Tue, Nov 10, 2015 at 1:11 PM, Darrel Maddy <
> darrel.ma...@newcastle.ac.uk> wrote:
>
> Dear all,
>
>
>
> Like many here I suspect I want to keep a copy of my main databases held
> on my workstation, on my laptop. I used PgAdmin to backup the databases
> concerned (the backup files look about the size I was expecting)  but
> restore on my laptop did not successfully complete.  All my shp file tables
> restored without issues but my raster tables (these are tiled rasters)
> would not. Looking at the error in the restore window of PgAdmin this
> appears to be a problem with a function/constraint.
>
>
>
> pg_restore: processing data for table "dems"
>
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
>
> pg_restore: [archiver (db)] Error from TOC entry 3459; 0 94054 TABLE DATA
> dems postgres
>
> pg_restore: [archiver (db)] COPY failed for table "dems": ERROR:  function
> st_bandmetadata(public.raster, integer[]) does not exist
>
> LINE 1:  SELECT array_agg(pixeltype)::text[] FROM st_bandmetadata($1...
>
>   ^
>
> HINT:  No function matches the given name and argument types. You might
> need to add explicit type casts.
>
> QUERY:   SELECT array_agg(pixeltype)::text[] FROM st_bandmetadata($1,
> ARRAY[]::int[]);
>
> CONTEXT:  SQL function "_raster_constraint_pixel_types" during inlining
>
> COPY dems, line 1: "1
> 010100344034C0E0CCB1D51741806692F80C41..."
>
>
>
>
>
> I did a quick search and found some old chatter on this issue but from the
> messages I read I would have expected this issue to have been cleared up in
> postgis 2 .
>
>
>
> Obviously I cannot contemplate having mission critical data 

Re: [postgis-users] Queries on partitioned table not behaving as expected

[Bborie Park]

That check constraint is not going to help you as it is too complicated for
the partitioning task. I don't believe the partitioning constraint can be
functional but needs to be simpler and built upon a basic data type...

Something like...

CREATE TABLE max_extent AS (
  id integer PRIMARY KEY,
  geom geometry(POLYGON)
);

And then the parent/partition tables had a reference to pc_extent...

CREATE TABLE ahn3_pointcloud.ahn3_all
(
  id integer NOT NULL,
  pa pcpatch(7),
  max_extent_id integer
);

Then your queries become...

WITH max_extents AS (
SELECT
  id
FROM max_extent
WHERE PC_Intersects(geom, ST_MakeEnvelope(120740,486076,121074,486292,
28992))
)
SELECT
*
FROM ahn3_all
JOIN max_extents
  ON ahn3_all.max_extent_id = max_extents.id

-bborie

On Mon, Nov 2, 2015 at 8:45 AM, Rubio Vaughan 
wrote:

> Dear all,
>
> We are trying to load a large LIDAR pointcloud dataset into multiple
> tables in PostGIS, using the point cloud extension. Eventually the whole
> data set will consist of 1400+ tables, containing about 1 million records
> each. Using a union view on all these tables would result in terrible
> performance, which is why we are trying to optimize query performance using
> partitioned tables. According to the documentation, the use of partitioned
> tables with CHECK constraints should cause the query planner to only scan
> those tables for which the CHECK constraint matches. Excerpt from the
> documentation:
>
> 
> *18.7.4. Other Planner Options*
> constraint_exclusion (enum): Controls the query planner's use of table
> constraints to optimize queries. The allowed values of constraint_exclusion
> are on (examine constraints for all tables), off (never examine
> constraints), and partition (examine constraints only for inheritance child
> tables and UNION ALL subqueries). partition is the default setting. It is
> often used with inheritance and partitioned tables to improve performance.
> When this parameter allows it for a particular table, the planner compares
> query conditions with the table's CHECK constraints, and omits scanning
> tables for which the conditions contradict the constraints.
>
> 
>
> However, as you can see from the example query below, the indexes for all
> child tables are still scanned. I would expect the query planner to only
> scan table c_25gn1, which contains the queried region. Does anyone here
> have experience with partitioned tables? I would be delighted to get some
> pointers for figuring out this problem.
>
> Thanks in advance for any help!
>
> Best regards,
> Rubio Vaughan
>
> *General info:*
> -
> ---
> SELECT postgis_full_version();
> "POSTGIS="2.2.0 r14208" GEOS="3.5.0-CAPI-1.9.0 r4084" SFCGAL="1.2.0"
> PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 2.0.1, released 2015/09/15"
> LIBXML="2.9.1" LIBJSON="0.11.99" TOPOLOGY (topology procs from "2.1.4
> r12966" need upgrade) RASTER"
>
> SHOW "constraint_exclusion";
> "partition"
> -
> ---
>
> *The parent table:*
> -
> ---
> CREATE TABLE ahn3_pointcloud.ahn3_all
> (
>   id integer NOT NULL,
>   pa pcpatch(7)
> )
> -
> ---
>
> *One of the child tables:*
> -
> ---
> CREATE TABLE ahn3_pointcloud.c_25dn2
> (
>   id serial NOT NULL,
>   pa pcpatch(7),
>   CONSTRAINT c_25dn2_pkey PRIMARY KEY (id),
>   CONSTRAINT check_extent CHECK (pc_intersects(pa,
> st_geomfromtext('POLYGON((115000 487500,12 487500,12 481250,115000
> 481250,115000 487500))'::text, 28992)))
> )
> INHERITS (ahn3_pointcloud.ahn3_all)
> -
> ---
>
> *Example query:*
> -
> ---
> EXPLAIN SELECT COUNT(pa) FROM ahn3_pointcloud.ahn3_all
> WHERE PC_Intersects(pa, ST_MakeEnvelope(120740,486076,121074,486292,
> 28992))
>
> "Aggregate  (cost=301989.36..301989.37 rows=1 width=32)"
> "  ->  Append  (cost=0.00..301248.82 rows=296216 width=32)"
> "->  Seq Scan on ahn3_all  (cost=0.00..0.00 rows=1 width=32)"
> "  Filter:
> (('010320407101000500407AFD40
> "->  Bitmap Heap Scan on c_25gn1  (cost=6064.85..59546.09
> rows=58574 width=32)"
> "  Recheck Cond:
> ('010320407101000500407AFD4
> "  Filter:
> _st_intersects('010320407101000500407
> "  ->  Bitmap Index Scan on c_25gn1_idx  (cost=0.00..6050.20
> rows=175722 

Re: [postgis-users] HSR Benchmark - 2015

[Bborie Park]

+1. I'll take a look when I can. I'm all for systematic and longitudinal
benchmarks...

-bborie

On Wed, Jul 29, 2015 at 6:13 PM, Brian M Hamlin mapl...@light42.com wrote:

 Hi All -

   last year, I updated and ran a benchmark for spatial search across
 several geo platforms [1]
 This year, I am thinking of updating this the newest crop of versions..
 and perhaps a few new platforms, too..

   I rewrote a couple of the PostGIS parts --  the responses are quite
 good..
 If anyone would like to check that code before proceeding, I am interested
 in feedback.

   I can answer any questions you might have about the setup.. however of
 you want a complete step-by-step
 install and are not able to do that yourself, please wait until the new
 benchmark is done for advice and questions...

   thanks and best regards from foggy Berkeley, California

 [1]  https://github.com/darkblue-b/HSR-TX-Geo-Benchmark

 --
 Brian M Hamlin
 OSGeo California Chapter
 blog.light42.com

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

Re: [postgis-users] memory usage of st_clip/st_intersection in a session

[Bborie Park]

Based upon your description, I can't guess as to the cause of the memory
leak(?). Can you post the query and provide some information (spatial
extent, # of features) about the rasters (tile size, # of bands, in-db or
out-db) and the geometries (same SRID of raster, maximum # of vertices per
polygon)?

-bborie


On Tue, Jun 2, 2015 at 11:39 AM, Bergenroth, Brandon bbergenr...@rti.org
wrote:

 I have thousands of polygons that I clip/intersect with various rasters
 and calculate area-weighted values, etc.

 I have created a function which returns a table of attributes and it works
 like a charm when calling it for a specific polygon and raster.  However
 when I call this function over and over again in a single session, the
 memory used by the PostgreSQL process continues to climb and instead of the
 function returning in the few seconds it normally does, it starts to take
 longer and longer and will just churn on one polygon for a long time to the
 point of never returning.  Now if I open another session and run that
 particular polygon it comes right back so I know it is not the data or the
 function.

 If I batch up the polygons and execute them in a new connection each time
 then they all finish in a timely manner.

 It would be hard for me to produce a test case given the amount of data
 I'm working with, but is there anything I am obviously missing here?  It is
 not such a burden to do each one in a new connection but that is obviously
 not ideal.

 Thanks for any suggestions.

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

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

Re: [postgis-users] raster2pgsql problem - SSL SYSCALL error EOF detected

[Bborie Park]

How strange. I'm betting some hardware limitation (you didn't specify the
amount of memory or disk space) or default PostgreSQL config setting is
causing this behavior. I don't know which exact PostgreSQL setting but
that's because I always reconfig my servers. For good performance, I
strongly urge you to reconfigure that server. At the very least, take a
read through...

https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

-bborie

On Tue, Jun 2, 2015 at 4:04 PM, weston mccarron quantumpsychot...@gmail.com
 wrote:

 The only thing showing up in the log was the following:

 LOG:  checkpoints are occurring too frequently (9 seconds apart)
 HINT:  Consider increasing the configuration parameter
 checkpoint_segments.

 I'm suspicious it had something to do with trying to do too much in one
 transaction, so I've now got raster2pgsql running with the -e flag (so
 there is no transaction and each insert proceeds separately). So far it's
 been running for 15 minutes, I'm up to about 800k records in the table, and
 there are no complaints in the log.



 On Tue, Jun 2, 2015 at 3:17 PM, Bborie Park dustym...@gmail.com wrote:
 
  Are you tailing the postgresql server logs? There typically is more
 information in there about what's causing the error.
 

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

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

Re: [postgis-users] raster2pgsql problem - SSL SYSCALL error EOF detected

[Bborie Park]

Are you tailing the postgresql server logs? There typically is more
information in there about what's causing the error.

On Tue, Jun 2, 2015 at 2:13 PM, weston mccarron quantumpsychot...@gmail.com
 wrote:

 Well let's see. I'm running it on a VPS provided by a2hosting. OS is
 Ubuntu 14.04. Not much else going on on the VPS except postgres and a few
 python scripts run nightly by cron (the scripts download flat file data and
 load it into the database).

 I used apt-get to install everything from the postgres repository.
 Previously (on the 9.3 install) I had messed a little with the memory
 settings in the conf file, but I reverted to completely vanilla
 installation (9.4) last night and still have the exact issue. Like I
 mentioned, I disabled ssl in the conf today and had the same behavior with
 a slightly different error message.

 The database is nothing too special. UTF8 encoding, but otherwise default.
 Last night before trying the raster load again, I reimported one schema
 with three polygon tables (previously imported from shapefiles). Otherwise
 the database is empty. Just adminpack, postgis and postgis-topology
 extensions loaded. I'm trying to load the raster into a new table in a
 completely empty schema.

 From the output, it appears as if it completes every command in the sql
 file generated by raster2pgsql. It finishes the inserts, creates the index,
 does an ANALYZE, then adds all the constraints one-by-one. Finally, it must
 be when it tries to commit the transaction that it croaks.



 On Tue, Jun 2, 2015 at 2:58 PM, Bborie Park dustym...@gmail.com wrote:

 As you indicated, the problem has to do with psql or more specifically
 your database. You'll need to provide more info, such as your postgres
 configuration and hardware specs, for anyone to provide any useful
 suggestions.

 -bborie

 On Tue, Jun 2, 2015 at 1:43 PM, weston mccarron 
 quantumpsychot...@gmail.com wrote:

 Hey folks. I've done some searching on this, and I can't seem to find
 evidence of anyone else having this problem, but I can very reliably
 duplicate it.

 Several months ago, I loaded a bunch of raster data into my 9.3 Postgres
 database with PostGIS 2.1 (I think it was 2.1.2 at the time). I used the
 following commands to simultaneously import 4 tif files (continental US,
 AK, PR and HI):

 raster2pgsql -d -I -C -M -R /path/to/gmted75*.tif -F -t 100x100
 trn.gmted75 | psql -Uadmin --password -hlocalhost mydatabase

 Everything appeared to work great, except that access was too slow for
 it to be practical for my intended use. I wondered if shrinking my tile
 size would help, since I'm only ever pulling single point values (with
 ST_Value). So yesterday, I tried the following:

 raster2pgsql -d -I -C -M -R /path/to/gmted75*.tif -t 10x10 trn.gmted75 |
 psql -Uadmin --password -hlocalhost mydatabase

 The only things I changed were the tile size, and I figured I probably
 didn't need the filename saved in each record (so I got rid of the -F flag).

 But the process went for a long time and then said my connection was
 lost. I tried again, first sending the raster2pgsql output to a file, then
 loading the file with psql. The file output completed just fine, but when I
 tried loading it in psql, it would go for almost an hour, gobble up my hard
 drive space, and then right at the end, give me the following error:

 SSL SYSCALL error: EOF detected

 I've now tried it a bunch of different ways. One tif instead of all four
 at once, logging in and using \i vs. just running it from the shell with
 the -f flag on psql...

 Each time I run the command I lose about 1 GB of hard drive space that I
 can't seem to recover. It's being used by postgres's data directory, but
 VACUUMing FULL, restarting the service, rebooting the server, etc, don't
 seem to recover it. And there's nothing showing in my database schema that
 indicates anything there hogging all that space.

 I finally deleted my database (which freed the disk space), uninstalled
 postgres 9.3, and did a fresh install of 9.4 with PostGIS 2.1.7. I set up a
 fresh database and tried again. Same thing. I edited my conf file to turn
 ssl off and tried again. It behaved exactly the same except now the error
 message was:

 psql:gmted75_US_cont.sql:4866054: server closed the connection
 unexpectedly
 This probably means the server terminated abnormally
 before or while processing the request.
 psql:gmted75_US_cont.sql:4866054: connection to server was lost


 Any ideas? Is the transaction maybe just too large to do at once?

 Am I even on the right track to try smaller tiles to speed up my
 ST_Value access?

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



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

Re: [postgis-users] Slow ST_Intersects and Materialized Views

[Bborie Park]

Can you redo that EXPLAIN with EXPLAIN ANALYZE VERBOSE? That'll give use
what the database actually does...

On Thu, May 28, 2015 at 8:09 AM, Alexander W. Rolek a.ro...@gmail.com
wrote:

 I appreciate the quick responses. Here's the EXPLAIN for the following
 query:

 EXPLAIN
 SELECT DISTINCT
 parcel.apn
 FROM
 gis.parcels as parcel,
 gis.layers as layer
 WHERE
 layer.id = 339 AND
 ST_Intersects(layer.geom_4326, parcel.geom_4326);

 Output:

 HashAggregate  (cost=543.84..805.23 rows=26139 width=11)
   Group Key: parcel.apn
   -  Nested Loop  (cost=5.40..478.49 rows=26139 width=11)
 -  Index Scan using layers_pkey on layers layer
  (cost=0.27..8.29 rows=1 width=56409)
   Index Cond: (id = 339)
 -  Bitmap Heap Scan on parcels parcel  (cost=5.13..469.83
 rows=37 width=1018)
   Recheck Cond: (layer.geom_4326  geom_4326)
   Filter: _st_intersects(layer.geom_4326, geom_4326)
   -  Bitmap Index Scan on parcels_geom_4326
  (cost=0.00..5.12 rows=111 width=0)
 Index Cond: (layer.geom_4326  geom_4326)


 @Steve.Toutant, I tried your approach, but I'm still getting really slow
 queries(minutes). Any other ideas?

 Alexander Rolek

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

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

Re: [postgis-users] raster2pgsql: -t TILE_SIZE

[Bborie Park]

Ramon,

It really depends on whether the rasters are stored in the database (in-db)
or out of the database (out-db)

If out-db, I recommend using the block size returned when running gdalinfo
on the raster...

# gdalinfo raster/test/regress/loader/testraster.tif
Driver: GTiff/GeoTIFF
Files: raster/test/regress/loader/testraster.tif
Size is 90, 50
Coordinate System is `'
Image Structure Metadata:
  INTERLEAVE=PIXEL
Corner Coordinates:
Upper Left  (0.0,0.0)
Lower Left  (0.0,   50.0)
Upper Right (   90.0,0.0)
Lower Right (   90.0,   50.0)
Center  (   45.0,   25.0)
Band 1 *Block=90x30* Type=Byte, ColorInterp=Red
Band 2 Block=90x30 Type=Byte, ColorInterp=Green
Band 3 Block=90x30 Type=Byte, ColorInterp=Blue

I've bolded the important bit.

If you're storing the raster in the database, I have no specific
suggestions. The reason for no suggestion is that it really depends on the
characteristics of the raster (number of bands, pixel type of each band)
and the server environment.

Having said that, I personally use the output from gdalinfo even for in-db
rasters.

-bborie


On Mon, Mar 16, 2015 at 8:23 AM, Stephen Mather step...@smathermather.com
wrote:

 Hi Ramon,

 I am not an expert on this by any measure. Better to check with the
 postgis user group (cc'd). I know the better size choices have changed a
 lot since Bborie optimized / rewrote in C.

 Best,

 Steve



 On Mar 16, 2015 4:24 AM, Ramon de Leon r.a.deleo...@gmail.com wrote:


 Hi Steve,

 Hope you are doing well.

 Just wondering if there is a good rule-of-thumb thing for choosing
 tile_size in importing raster datasets via raster2pgsql.

 I usually use 100x100 or 128x128 just because it is most that I see on
 examples. Sometimes I use the auto but I don't know if that helps in
 optimization when the table gets into Postgres.

 I want to test importing some fairly large rasters to the DB like
 Globcover (
 http://www.un-spider.org/links-and-resources/data-sources/globcover-esa)
 but I still lack knowledge on the import process good practices
 and optimization. Would appreciate general tips in importing rasters
 (specially larger datasets)!

 Thanks!


 - Ramon


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

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

Re: [postgis-users] POSTGIS_ENABLE_OUTDB_RASTERS=1: PostGIS won't start (RHEL 6.5)

[Bborie Park]

The env variables need to be within the environment of the postgres
process. How are you starting postgres?

Shell script? Then you should be able to add the variables to that script.

Direct invocation of posrgres on the command line? You need to have the
variables before the command

VAR=1 postgres ...

-bborie
On Feb 25, 2015 11:40 AM, Phil Hurvitz phurv...@uw.edu wrote:

 Thanks Bborie, I built from the tarball rather than using an rpm; does
 that still mean I should be adding the environment variables to

 /etc/sysconfig/pgsql/postgresql

 So for overkill I added the env vars to that file as well as to the init
 script, restarted PostgreSQL, and it seems I still cannot access the out-db
 raster.

 I added a slope raster using:

 raster2pgsql -I -C -e -Y -F -d -R -s 2926  ./slope/slope_ps.tif gis.slope
 | psql osm_test

 I can get metadata (sorry for the ugly text wrapping):

 select rid, (foo.md).* from (select rid, st_Metadata(rast)  as md from
 slope) as foo;
  rid |upperleftx|upperlefty| width | height | scalex
 |  scaley   | skewx | skewy | srid | numbands
 -+--+--+---+
 +--+---+---+---+--+--
1 | 835161.301005914 | 758483.868026069 | 31935 |  34649 |
 32.80833 | -32.80833 | 0 | 0 | 2926 |1

 But cannot access values:

 select st_summarystats(rast) from slope;
 ERROR:  rt_raster_load_offline_data: Access to offline bands disabled
 CONTEXT:  SQL function st_summarystats statement 1

 select st_value(rast, 1, 1, 1) from slope;
 ERROR:  rt_raster_load_offline_data: Access to offline bands disabled

 -P.

 **
 Philip M. Hurvitz, PhD | Research Assistant Professor | UW-CBE
 Urban Form Lab  | 1107 NE 45th Street, Suite 535  | Box 354802
 University of Washington, Seattle, Washington  98195-4802, USA
 phurv...@u.washington.edu | http://gis.washington.edu/phurvitz
 What is essential is invisible to the eye. -de Saint-Exupéry
 **


  Bborie Park dustym...@gmail.com
 Wed Feb 25 10:46:59 PST 2015

 

 Philip,

 POSTGIS_ENABLE_OUTDB_RASTERS=1 is an environment variable not to be in
 postgresql.conf.

 The same is true for POSTGIS_GDAL_ENABLED_DRIVERS=ENABLE_ALL

 https://wiki.postgresql.org/wiki/PostgreSQL_on_RedHat_Linux

 Based upon the above, it looks like you should add

 POSTGIS_ENABLE_OUTDB_RASTERS=1
 POSTGIS_GDAL_ENABLED_DRIVERS=ENABLE_ALL

 to  /etc/sysconfig/pgsql/postgresql

 The above assumes you're using the packages provided by PostgreSQL.

 http://www.postgresql.org/download/linux/redhat/

 -bborie



 On 2/25/2015 10:10, Phil Hurvitz wrote:

 Hi all, I am having trouble starting PostGIS with out-db raster support

 In my /usr/local/pgsql/data/postgresql.conf file I include the line

 POSTGIS_ENABLE_OUTDB_RASTERS=1

 after which PostGIS won't start (service postgresql start).

 Software is

 postgis_full_version
 ---
   POSTGIS=2.1.3 r12547 GEOS=3.4.2-CAPI-1.8.2 r3921 PROJ=Rel. 4.7.1,
 23 September 2009 GDAL=GDAL 1.11.2, released 2015/02/10
 LIBXML=2.7.6 TOPOLOGY RASTER


 Also PostGIS won't start when I specify

 POSTGIS_GDAL_ENABLED_DRIVERS=ENABLE_ALL

 Any help would be appreciated!


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

Re: [postgis-users] POSTGIS_ENABLE_OUTDB_RASTERS=1: PostGIS won't start (RHEL 6.5)

[Bborie Park]

Philip,

POSTGIS_ENABLE_OUTDB_RASTERS=1 is an environment variable not to be in
postgresql.conf.

The same is true for POSTGIS_GDAL_ENABLED_DRIVERS=ENABLE_ALL

https://wiki.postgresql.org/wiki/PostgreSQL_on_RedHat_Linux

Based upon the above, it looks like you should add

POSTGIS_ENABLE_OUTDB_RASTERS=1
POSTGIS_GDAL_ENABLED_DRIVERS=ENABLE_ALL

to  /etc/sysconfig/pgsql/postgresql

The above assumes you're using the packages provided by PostgreSQL.

http://www.postgresql.org/download/linux/redhat/

-bborie

On Wed, Feb 25, 2015 at 10:10 AM, Phil Hurvitz phurv...@uw.edu wrote:

 Hi all, I am having trouble starting PostGIS with out-db raster support

 In my /usr/local/pgsql/data/postgresql.conf file I include the line

 POSTGIS_ENABLE_OUTDB_RASTERS=1

 after which PostGIS won't start (service postgresql start).

 Software is

 postgis_full_version
 ---
  POSTGIS=2.1.3 r12547 GEOS=3.4.2-CAPI-1.8.2 r3921 PROJ=Rel. 4.7.1, 23
 September 2009 GDAL=GDAL 1.11.2, released 2015/02/10 LIBXML=2.7.6
 TOPOLOGY RASTER


 Also PostGIS won't start when I specify

 POSTGIS_GDAL_ENABLED_DRIVERS=ENABLE_ALL

 Any help would be appreciated!

 --
 -P.

 **
 Philip M. Hurvitz, PhD | Research Assistant Professor | UW-CBE
 Urban Form Lab  | 1107 NE 45th Street, Suite 535  | Box 354802
 University of Washington, Seattle, Washington  98195-4802, USA
 phurv...@u.washington.edu | http://gis.washington.edu/phurvitz
 What is essential is invisible to the eye. -de Saint-Exupéry
 **
 ___
 postgis-users mailing list
 postgis-users@lists.osgeo.org
 http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] POSTGIS_ENABLE_OUTDB_RASTERS=1: PostGIS won't start (RHEL 6.5)

[Bborie Park]

Can you post the shell script?



On Wed, Feb 25, 2015 at 11:50 AM, Phil Hurvitz phurv...@uw.edu wrote:

 Thanks Bborie, I am starting with a shell script, and have added the env
 vars to that script, but am still unable to access the out-db rasters.

 -P.

 **
 Philip M. Hurvitz, PhD | Research Assistant Professor | UW-CBE
 Urban Form Lab  | 1107 NE 45th Street, Suite 535  | Box 354802
 University of Washington, Seattle, Washington  98195-4802, USA
 phurv...@u.washington.edu | http://gis.washington.edu/phurvitz
 What is essential is invisible to the eye. -de Saint-Exupéry
 **

 On 2/25/2015 11:45, Bborie Park wrote:

 The env variables need to be within the environment of the postgres
 process. How are you starting postgres?

 Shell script? Then you should be able to add the variables to that script.

 Direct invocation of posrgres on the command line? You need to have the
 variables before the command

 VAR=1 postgres ...

 -bborie

 On Feb 25, 2015 11:40 AM, Phil Hurvitz phurv...@uw.edu
 mailto:phurv...@uw.edu wrote:

 Thanks Bborie, I built from the tarball rather than using an rpm;
 does that still mean I should be adding the environment variables to

 /etc/sysconfig/pgsql/__postgresql

 So for overkill I added the env vars to that file as well as to the
 init script, restarted PostgreSQL, and it seems I still cannot
 access the out-db raster.

 I added a slope raster using:

 raster2pgsql -I -C -e -Y -F -d -R -s 2926  ./slope/slope_ps.tif
 gis.slope | psql osm_test

 I can get metadata (sorry for the ugly text wrapping):

 select rid, (foo.md http://foo.md).* from (select rid,
 st_Metadata(rast)  as md from slope) as foo;
   rid |upperleftx|upperlefty| width | height |
 scalex  |  scaley   | skewx | skewy | srid | numbands
 -+--+-__-+---+--
 --__+--+--__-+---+--
 -+__--+--
 1 | 835161.301005914 | 758483.868026069 | 31935 |  34649 |
 32.80833 | -32.80833 | 0 | 0 | 2926 |1

 But cannot access values:

 select st_summarystats(rast) from slope;
 ERROR:  rt_raster_load_offline_data: Access to offline bands disabled
 CONTEXT:  SQL function st_summarystats statement 1

 select st_value(rast, 1, 1, 1) from slope;
 ERROR:  rt_raster_load_offline_data: Access to offline bands disabled

 -P.

 **__**__**
 Philip M. Hurvitz, PhD | Research Assistant Professor | UW-CBE
 Urban Form Lab  | 1107 NE 45th Street, Suite 535  | Box 354802
 University of Washington, Seattle, Washington  98195-4802, USA
 phurv...@u.washington.edu mailto:phurv...@u.washington.edu |
 http://gis.washington.edu/__phurvitz
 http://gis.washington.edu/phurvitz
 What is essential is invisible to the eye. -de Saint-Exupéry
 **__**__**


 Bborie Park dustym...@gmail.com mailto:dustym...@gmail.com
 Wed Feb 25 10:46:59 PST 2015

  

 Philip,

 POSTGIS_ENABLE_OUTDB_RASTERS=1 is an environment variable not to
 be in
 postgresql.conf.

 The same is true for POSTGIS_GDAL_ENABLED_DRIVERS=__ENABLE_ALL

 https://wiki.postgresql.org/__wiki/PostgreSQL_on_RedHat___Linux
 https://wiki.postgresql.org/wiki/PostgreSQL_on_RedHat_Linux

 Based upon the above, it looks like you should add

 POSTGIS_ENABLE_OUTDB_RASTERS=1
 POSTGIS_GDAL_ENABLED_DRIVERS=__ENABLE_ALL

 to  /etc/sysconfig/pgsql/__postgresql

 The above assumes you're using the packages provided by
 PostgreSQL.

 http://www.postgresql.org/__download/linux/redhat/
 http://www.postgresql.org/download/linux/redhat/

 -bborie



 On 2/25/2015 10:10, Phil Hurvitz wrote:

 Hi all, I am having trouble starting PostGIS with out-db raster
 support

 In my /usr/local/pgsql/data/__postgresql.conf file I include the
 line

 POSTGIS_ENABLE_OUTDB_RASTERS=1

 after which PostGIS won't start (service postgresql start).

 Software is

 postgis_full_version
 --__
 --__---
POSTGIS=2.1.3 r12547 GEOS=3.4.2-CAPI-1.8.2 r3921
 PROJ=Rel. 4.7.1,
 23 September 2009 GDAL=GDAL 1.11.2, released 2015/02/10
 LIBXML=2.7.6 TOPOLOGY RASTER


 Also PostGIS won't start when I specify

 POSTGIS_GDAL_ENABLED_DRIVERS=__ENABLE_ALL

 Any help would be appreciated!


___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin

Re: [postgis-users] ST_Clip on a raster without band crashes the server

[Bborie Park]

File a bug. It shouldn't work for an empty raster.

On Tue, Feb 24, 2015 at 6:50 AM, Pierre Racine pierre.rac...@sbf.ulaval.ca
wrote:

 Hi,

 I have:

 POSTGIS=2.1.5 r13152 GEOS=3.4.2-CAPI-1.8.2 r0 PROJ=Rel. 4.8.0, 6
 March 2012 GDAL=GDAL 1.11.1, released 2014/09/24 LIBXML=2.7.8
 LIBJSON=UNKNOWN RASTER

 and

 SELECT ST_Clip(ST_MakeEmptyRaster(42, 42, 0, 0, 1.0, 1.0, 0, 0, 4269),
 ST_MakeEnvelope(0, 0, 20, 20, 4269));

 crashed my server.

 Someone can confirm the bug?

 Thanks,

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

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

Re: [postgis-users] Can I change the path of an out-of-db raster?

[Bborie Park]

Hey Guido,

ST_SetBandPath doesn't exist but sounds like a worthwhile addition. Can you
file a ticket for that?

http://trac.osgeo.org/postgis/

In the meantime, a workaround is to use symbolic links or mount points.

-bborie

On Tue, Feb 10, 2015 at 2:34 AM, guido lemoine 
guido.lemo...@jrc.ec.europa.eu wrote:

 Hmm, any thoughts on this? Just to expand my use case: I have a time
 series of images that are all spatially aligned, i.e. same size,
 geo-location, pixel spacing, projection.
 Thus, raster2pgsql -R would always create the same tiled raster entries
 for each image where only the (binary) file name would be different.
 If one would simply be able to change the filename, a single set of tile
 records would be needed. That would be a neat concept for time series
 (other than creating massive multi-band images).

 Something like ST_SetBandPath(rast, filepath) could be useful, although
 only in the aligned context.

 GL


 On 02/06/15, *guido lemoine * guido.lemo...@jrc.ec.europa.eu wrote:

 Dear List,

 I have stored some rasters out-of-db (using raster2pgsql with the -R
 option). I have to move these rasters
 to another disk and wonder if there is a simple way to update the BandPath
 to the new location, other
 than dropping the tables and re-load with the new path. I see there is
 ST_BandPath, but not a corresponding
 ST_SetBandPath().

 Thanks!

 Guido






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

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

Re: [postgis-users] [Raster] Finding pixels intersecting an extent (polygon)

[Bborie Park]

ST_Intersection(rast, geom) does vectorize the raster before intersecting
with the polygon. ST_Clip(rast, geom) rasterizes the polygon before
intersecting with the raster.

Can you provide more information? Such as the query and the version of
PostGIS?

-bborie

On Thu, Feb 5, 2015 at 2:05 PM, Jean Marchal jean.d.marc...@gmail.com
wrote:

 Hi list,

 I am trying to return all the pixels in a raster that intersect (not just
 touch) an extent (say a rectangle). I tried ST_Clip and
 ST_Intersection(raster, geom) but they don't return all the pixels that
 intersect my extent polygon. Do I have to vectorize the raster first using
 ST_PixelAsPolygons or there is a better / more efficient way to proceed?

 Ultimately the goal is to fetch the resulting raster in R.

 Thanks,

 Jean

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

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

Re: [postgis-users] [Raster] Finding pixels intersecting an extent (polygon)

[Bborie Park]

Try something like:

WITH foo AS (
SELECT
ST_AsRaster(
ST_GeomFromText('POLYGON ((-52.54178994517749 46.99199259385565,
-52.54178994517749 46.996897959881, -52.53436080387823 46.996897959881,
-52.53436080387823 46.99199259385565, -52.54178994517749
46.99199259385565))', 4269),
rast,
'8BUI',
touched := True
) AS rast
FROM elev
LIMIT 1
)
SELECT
ST_Intersection(
elev.rast,
foo.rast
) AS rast
FROM elev
JOIN foo
ON ST_Intersects(elev.rast, foo.rast)

The idea is to explicitly convert the geometry into a raster using one of
the elev rasters as a reference. The touched := True slightly changes the
behavior of the rasterization.

From the docs:

The optional touched parameter defaults to false and maps to the GDAL
ALL_TOUCHED rasterization option, which determines if pixels touched by
lines or polygons will be burned.

On Thu, Feb 5, 2015 at 2:45 PM, Jean Marchal jean.d.marc...@gmail.com
wrote:

 Bborie,

 I am running PostGIS 2.1.5. Here is the output of the
 postgis_full_version():

 POSTGIS=2.1.5 r13152 GEOS=3.3.3-CAPI-1.7.4 PROJ=Rel. 4.7.1, 23
 September 2009 GDAL=GDAL 1.9.0, released 2011/12/29 LIBXML=2.8.0
 LIBJSON=UNKNOWN RASTER

 My query looks like this:

 SELECT ST_Intersection(ST_GeomFromText('POLYGON ((-52.54178994517749
 46.99199259385565, -52.54178994517749 46.996897959881, -52.53436080387823
 46.996897959881, -52.53436080387823 46.99199259385565, -52.54178994517749
 46.99199259385565))', 4269), rast) as rast
 FROM elev

 David,

 This query does not returns all the polygons that intersect my polygon. I
 think it returns only those where the centroid is inside the polygon or is
 it covered based? (like 50% of the pixel intersect with the polygon).

 Thanks for your rapid answers!

 Jean

 2015-02-05 14:31 GMT-08:00 David Haynes hayne...@gmail.com:

 select ST_Clip(r.rast,p.geom) as rast
 from polygon p inner join raster r on ST_intersects(r.rast, p.geom)

 This returns a raster which has all pixels inside the polygon

 On Thu, Feb 5, 2015 at 4:05 PM, Jean Marchal jean.d.marc...@gmail.com
 wrote:

 Hi list,

 I am trying to return all the pixels in a raster that intersect (not
 just touch) an extent (say a rectangle). I tried ST_Clip and
 ST_Intersection(raster, geom) but they don't return all the pixels that
 intersect my extent polygon. Do I have to vectorize the raster first using
 ST_PixelAsPolygons or there is a better / more efficient way to proceed?

 Ultimately the goal is to fetch the resulting raster in R.

 Thanks,

 Jean

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



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



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

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

Re: [postgis-users] ST_BandPath() returns null or ô on out-db raster

[Bborie Park]

I'm not aware that loading out-db rasters using Windows network path names
will work. What happens if you add that network path as a network drive?

Can you try something like the following? All I've done is replace the
backslash with a forward slash...

raster2pgsql -R -a -F  fileserver/raster/419/napoleonien/*.tif catalog

I'm wondering if some escaping rules are getting in the way.

On Thu, Jan 29, 2015 at 4:42 AM, Tumasgiu Rossini rossin...@gmail.com
wrote:

 Hi List,

 I am experiencing a problem with outdb geotiff importation.

 I am using the raster2pgsql utility :

 raster2pgsql -R -a -F  \\fileserver\raster\419\napoleonien\*.tif catalog

 On a particular set of geotiff ( wich are generated with the same routine
 as the others ), the command seems to work fine
 but St_BandPath() output null when I'm trying to register the tiffs on a
 local server, and it output the character  'ô' on the production server.

 On others set of raster, the band path is correctly stored.

 What am I doing wrong ?



 My configuration is the following :
 Both Local and server -
PostgreSQL 9.3.5 installed with EntrepriseDB
Postgis 2.1 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
Win 7 64bit
 French_France.1252

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

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

Re: [postgis-users] FileGDB curves

[Bborie Park]

If I remember correctly, curve geometries are not currently supported in
GDAL/OGR. There is a GDAL RFC making the rounds for adding curve support...

http://trac.osgeo.org/gdal/wiki/rfc49_curve_geometries

-bborie

On Wed, Nov 12, 2014 at 7:44 AM, Andy Colson a...@squeakycode.net wrote:

 Shoot.  I changed the command to:

 ogr2ogr
   -f PostgreSQL
   -t_srs EPSG:3857
   'PG:dbname=gis'
   WCWebDataCC.gdb
   -lco FID=gid
   -lco SPATIAL_INDEX=OFF
   -lco GEOMETRY_NAME=the_geom
   -lco SCHEMA=washingtonmn
   -lco PRECISION=NO
   streets


 (removing the -nlt) and I still get a MULTILINESTRING, and it still looks
 the same.

 The ogr2ogr is part of a perl script, and the first command I posted was
 my guess at what it generated, which was a little off.  The command above
 is correct.

 I'm not sure what else to try.


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

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

Re: [postgis-users] Questions related to some tests I did in PostGIS

[Bborie Park]

 So, my questions are:
 Why counting pixels was not faster in the second subset with pixels of 5x5
 meters?


I cannot tell based upon your queries. I can't distinguish which query
times are for the 5x5 vs 1x1. Have you tried doing outdb? It may be more
performant for a source raster of that size.


 How ST_Count works? Does it uses the ST_Value function in each pixel of the
 pixel matrix row by row in each tile to keep track of the nodata value
 pixels?


No. ST_Count goes a different pathway. It does need to check each pixel for
the NODATA value though. I suspect ST_Count is just returning the count
value of ST_SummaryStats as the general cost of counting pixels in a raster
is the majority required for summary stats.


 Is the raster support in postgis, in general, only suitable to get pixel
 statistics of some region in the raster?


Nope. That is the general use case but I know of places where modeling
surfaces is done in-database.


 Whats the advantages of using postgis raster to analyze rasters compared to
 some of the arcgis tools (for example)? I ask that because is much more
 easier to visualize the data in arcgis compared to postgis tables in QGIS
 plugin (wkt raster)


Depends on how big your dataset is. If you're working on longitudinal
datasets (over space and time) the database is better prepared for work on
these kinds of datasets.

One thing that I realize is that ST_Area could be faster at least in the
case of no skew by not going to geometry.

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

Re: [postgis-users] ST_AddBand

[Bborie Park]

You're probably better served using the first variant which gives you full
control over each raster and band to be added.

All the other variants of ST_AddBand are wrappers around the first variant.

-bborie


On Wed, Aug 13, 2014 at 6:37 AM, David Haynes hayne...@gmail.com wrote:

 I have a question about this function, regarding the fifth variant.
 Why does the function assume that all of the rasters input in the array
 have their information stored at the same band? The variant will only allow
 for 1 band number to be specified for all rasters.

 raster *ST_AddBand*(raster torast, raster[] fromrasts, integer fromband=1,
 integer torastindex=at_end);

 This is will not work

 ST_AddBand(ST_MakeEmptyraster(layers.stack_rast),ARRAY[layers.stack_rast,
 layers.stack_rast], ARRAY[1,3]) as rast

 This does, but assumes that I want band 1 both times.
 ST_AddBand(ST_MakeEmptyraster(layers.stack_rast),ARRAY[layers.stack_rast,
 layers.stack_rast], 1) as rast

 Shouldn't there be a variant that allows that uses an array integer that
 allows for you to individual specifications of bands.

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

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

Re: [postgis-users] Problem with postgis raster

[Bborie Park]

Double-checking the error you provided from QGIS, it looks like you haven't
specified a value for host...

-bborie


On Mon, May 19, 2014 at 4:15 PM, dandrigo
lcel...@latitude-geosystems.comwrote:

 Hello,

 below, i paste my DOS interface showing gdalinfo for the srtm file :

 SS_gdalinfo.JPG
 http://postgis.17.x6.nabble.com/file/n5006356/SS_gdalinfo.JPG


 below the information with the postgres query : SELECT
 (ST_SummaryStats(rast)).* FROM public.srtm_test


 SS_query_postgres_srtm.JPG
 http://postgis.17.x6.nabble.com/file/n5006356/SS_query_postgres_srtm.JPG



 I'm really surprised by the error message appearing in qgis. What's the
 meaning of this connexion problem? i succeed without problem in connecting
 and loading postgis layer vector data in qgis. My problem concerns only pg
 raster data...



 --
 View this message in context:
 http://postgis.17.x6.nabble.com/Problem-with-postgis-raster-tp5006344p5006356.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

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

Re: [postgis-users] Raster ST_Union memory limit

[Bborie Park]

RAM available.

-bborie


On Tue, May 6, 2014 at 6:35 AM, Pierre Racine
pierre.rac...@sbf.ulaval.cawrote:

 Hi,

 What maximum size the result of a ST_Union(rast) can be if it is embedded
 in a ST_Tile() call?

 SELECT ST_Tile(ST_Union(rast), 100, 100) rast FROM ...

 The RAM available to PostgreSQL or the maximum size of a PostgreSQL field
 (1GB)?

 Thanks,

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

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

Re: [postgis-users] Raster ST_Union memory limit

[Bborie Park]

I think your query will explode...


On Tue, May 6, 2014 at 7:01 AM, Pierre Racine
pierre.rac...@sbf.ulaval.cawrote:

 Thanks,

 The RAM limit is per row or per query? In other word, if a query produce
 fields smaller than my available RAM but the whole resulting table is
 bigger than my available RAM, are each resulting row flushed to the disk so
 that my whole query does not explode?

 Pierre

  -Original Message-
  From: postgis-users-boun...@lists.osgeo.org [mailto:postgis-users-
  boun...@lists.osgeo.org] On Behalf Of Bborie Park
  Sent: Tuesday, May 06, 2014 9:52 AM
  To: PostGIS Users Discussion
  Subject: Re: [postgis-users] Raster ST_Union memory limit
 
  RAM available.
 
  -bborie
 
 
  On Tue, May 6, 2014 at 6:35 AM, Pierre Racine
  pierre.rac...@sbf.ulaval.ca wrote:
 
 
Hi,
 
What maximum size the result of a ST_Union(rast) can be if it is
  embedded in a ST_Tile() call?
 
SELECT ST_Tile(ST_Union(rast), 100, 100) rast FROM ...
 
The RAM available to PostgreSQL or the maximum size of a
  PostgreSQL field (1GB)?
 
Thanks,
 
Pierre
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
 
 

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

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

Re: [postgis-users] ArcGIS issue - specified coordinate exceeds the valid coordinate range

[Bborie Park]

Mark,

You'll need to provide a lot more information about your layer. Saying that
the layer works in one application but not another does not help much.

You should inspect the geometries of your layer to see if one of them has a
coordinate exceeding the valid range. For example, if your geometries were
in WGS84, we would expect all coordinates to be between -180 - 180 and -90
- 90. If a coordinate was -181, 54, that would throw the range error.

-bborie


On Fri, Apr 18, 2014 at 5:53 AM, Mark Volz markv...@co.lyon.mn.us wrote:

  Hello,



 I am able to access PostGIS layers on AutoCAD map.  However, ArcGIS is
 reporting an error “The selected object failed to draw
 database.public.layer:  The specified coordinate exceeds the valid
 coordinate range.  What steps would I need to do to fix this?







 Sincerely,

 Mark Volz





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

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

Re: [postgis-users] Accessing lots of missing outdb/offline rasters appear to cause PostgreSQL to crash

[Bborie Park]

So, you were hitting two separate problems. Can we see the query that
you're actually trying to run? The assert itself indicates that somehow
rt_raster_from_band received a raster from which bands are to be extracted
but the raster is NULL.

You've going to have to examine your query and the dataset the query is
being run upon.

Without knowing more about your query and your dataset, I can't say what
exactly is causing rt_raster_from_band to receive a NULL raster.

I'll do some poking to see how that function could be receiving a NULL
raster.

-bborie


On Mon, Mar 31, 2014 at 8:01 PM, Robert Nix rob...@urban4m.com wrote:

 Upgraded:

 POSTGIS=2.1.2 r12389 GEOS=3.3.8-CAPI-1.7.8 PROJ=Rel. 4.8.0, 6 March
 2012 GDAL=GDAL 1.10.0, released 2013/04/24 LIBXML=2.8.0 (core procs
 from 2.1.1 r12113 need upgrade) TOPOLOGY (topology procs from 2.1.1
 r12113 need upgrade) RASTER (raster procs from 2.1.1 r12113 need upgrade)


 Unfortunately, *the problem persists* though the scenario is slightly
 different. Specifically, it appears to be unrelated to inaccessible offline
 rasters since the issue is now occurring even while all offline rasters are
 accessible. But it may be due to any SQL exception occurring during the
 processing of SQL that contains raster functions. In other words, I am
 doing an insert that contains calls to functions that execute SQL like that
 in my original post. In some cases, an exception (duplicate key) is
 occurring and almost immediately following that error is the 
 rt_raster_from_band:
 Assertion. And it's consistent. If the duplicate key error occurs, the
 assertion follows. The only other times i see the assertion seems to be
 related to recovery attempts.

 If the assertion occurs more than a few dozen times, the database goes
 into a permanent recovery mode outputting nothing else to the log except
 the recovery message several times a second until i can stop the postgresql
 service.



 On Monday, March 31, 2014 12:44:00 PM UTC-4, Robert Nix wrote:

 After 1001 of these errors:

 2014-03-31 15:50:51 UTC ERROR:  rt_band_load_offline_data: Cannot open
 offline raster: /...


 I get:

 SELECT: rt_api.c:8659: rt_raster_from_band: Assertion `((void *)0) !=
 raster' failed.
 2014-03-31 15:50:52 UTC LOG:  server process (PID 8385) was terminated by
 signal 6: Aborted


 Eventually resulting in:

 2014-03-31 15:50:52 UTC FATAL:  the database system is in recovery mode


 Requiring bouncing the postgres service.

 I'm not sure if this is an issue with PostGIS or PostgreSQL itself.

 I'm accessing the rasters from within a sql function, essentially:

 st_setbandnodatavalue(
   st_snaptogrid(
 st_setsrid(_rast,4326),
 gridx:=st_upperleftx(_rast),
 gridy:=st_upperlefty(_rast)
   ),
   0.0
 )



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

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

Re: [postgis-users] more memory issues with raster2pgsql

[Bborie Park]

I think you can just connect to the database with psql and issue the
following:

postgres=# SHOW client_encoding;

It should return something like...

 client_encoding
-
 UTF8
(1 row)

If your database doesn't return UTF8, you can forcefully set client_encoding

ALTER DATABASE mydb
  SET client_encoding = UTF8

-bborie


On Fri, Mar 28, 2014 at 10:43 PM, georgew gws...@hotmail.com wrote:

 Thanks bborie, this is what I did, but no improvement:

 Microsoft Windows [Version 6.3.9600]
 (c) 2013 Microsoft Corporation. All rights reserved.

 C:\WINDOWS\system32set PGCLIENTENCODING=UTF8

 C:\WINDOWS\system32psql -U postgres -d NZTPU -f c:\cb09.sql
 BEGIN
 psql:c:/cb09.sql:2: NOTICE:  table cb09_3 does not exist, skipping
 DROP TABLE
 CREATE TABLE
 psql:c:/cb09.sql:4: ERROR:  could not load library C:/Program
 Files/PostgreSQL/
 9.3/lib/rtpostgis-2.1.dll: unknown error 998
 LINE 1: INSERT INTO cb09_3 (rast,filename) VALUES ('010100...
  ^
 psql:c:/cb09.sql:5: ERROR:  current transaction is aborted, commands
 ignored
 unt
 il end of transaction block
 psql:c:/cb09.sql:6: ERROR:  current transaction is aborted, commands
 ignored
 unt
 il end of transaction block
 psql:c:/cb09.sql:7: ERROR:  current transaction is aborted, commands
 ignored
 unt
 il end of transaction block
 ROLLBACK
 psql:c:/cb09.sql:9: ERROR:  relation cb09_3 does not exist
 
 I tried again and the second time got the out of memory error.
 What can I try next? I have searched everywhere but have not found a
 suitable answer.
 But as a start how can I determine what the default character set encoding
 is for the server and for the client?



 --
 View this message in context:
 http://postgis.17.x6.nabble.com/more-memory-issues-with-raster2pgsql-tp5006000p5006008.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

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

Re: [postgis-users] more memory issues with raster2pgsql

[Bborie Park]

You should tell raster2pgsql to tile the raster into smaller bite-size
chucks, 256x256 is a safe value. It sounds like the raster is too big for
what memory is available.

-bborie


On Fri, Mar 28, 2014 at 5:29 PM, georgew gws...@hotmail.com wrote:

 Hi, I am trying to convert a raster from SAGA GIS to Postgis using
 raster2pgsql.The command line and its output is:
 C:\\Progra~1\\PostgreSQL\\9.3\\bin\\raster2pgsql.exe -F -d -
 I -C -M -s 2193 I:\cb09\cb09_3dem.sdat cb09_3 | psql -d NZTPU

 Processing 1/1: I:\cb09\cb09_3dem.sdat

 BEGIN
 NOTICE:  table cb09_3 does not exist, skipping
 DROP TABLE
 CREATE TABLE
 ERROR:  could not load library C:/Program
 Files/PostgreSQL/9.3/lib/rtpostgis-2.1.dll: unknown error 998
 LINE 1: INSERT INTO cb09_3 (rast,filename) VALUES ('010100...
  ^
 ERROR:  current transaction is aborted, commands ignored until end of
 transactio
 n block
 .

 I then ran again but just to create the output sql and dumped the first few
 lines of the output as follows:

 C:\\Progra~1\\PostgreSQL\\9.3\\bin\\raster2pgsql.exe -F -d -
 I -C -M -s 2193 I:\cb09\cb09_3dem.sdat cb09_3  c:\cb09.sql
 Processing 1/1: I:\cb09\cb09_3dem.sdat

 C:\WINDOWS\system32more  c:\cb09.sql
 BEGIN;
 DROP TABLE IF EXISTS cb09_3;
 CREATE TABLE cb09_3 (rid serial PRIMARY KEY,rast raster,filename
 text);
 INSERT INTO cb09_3 (rast,filename) VALUES
 ('0101001440

 14C000801D5F324100A0BC3F53419108

 C112211C4A804FC3C788A5B3446B9CB3440C9BB34412AAB3447FFFB3449DB9B444D679B54413

 06B6446859B6441C52B6441037B6446788B64468C9B64401CCB644EDAAB644797FB64458BCB64417

 95B744E348B844A0C6B8447C1CB944A13AB944DE0EB944D3E0B8445E14B9445B90B9442619BA443D
 .

 Finally executed the sql:

 C:\WINDOWS\system32psql -U postgres -d NZTPU -f c:\cb09.sql
 BEGIN
 psql:c:/cb09.sql:2: NOTICE:  table cb09_3 does not exist, skipping
 DROP TABLE
 CREATE TABLE
 psql:c:/cb09.sql:4: ERROR:  out of memory
 DETAIL:  String of 276576218 bytes is too long for encoding conversion.
 psql:c:/cb09.sql:5: ERROR:  current transaction is aborted, commands
 ignored
 unt
 il end of transaction block
 
 I assume the DLL not found is due to the lack of memory (because the file
 is
 where it should be)
 Is that assumption correct?

 All my databases and templates are UTF8 as shown here:

 http://postgis.17.x6.nabble.com/file/n5006000/postgres.png

 So why is the encoding conversion necessary?
 And more importantly how do I get around this problem?
 I am on Windows 8.1 Pro, and the very latest versions of Postgres (9.3) and
 Postgis (2.1.1)
 Many thanks for your help




 --
 View this message in context:
 http://postgis.17.x6.nabble.com/more-memory-issues-with-raster2pgsql-tp5006000.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

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

Re: [postgis-users] more memory issues with raster2pgsql

[Bborie Park]

How big is the original? Numbers of pixels width and height? Number of
bands? Pixel types of the bands? I just want to know so that I can tell you
if its even possible to store that raster as one database value.

Utf-8 shouldn't be an issue as the raster data itself isn't in any
character set encoding. They're almost always numerical values.

All my production and dev postgresql clusters are in utf8 and I've never
seen a raster encoding issue.

-bborie
On Mar 28, 2014 6:36 PM, georgew gws...@hotmail.com wrote:

 Thanks bborie, however from the error message I received the problem seems
 due to the need to convert the original raster to utf-8. But the original
 raster is in utf-8 already.
 If I follow your suggestion (and after this mystery conversion is done)
 will
 I be able to recombine the tiles to create a single raster, to be just like
 the original?




 --
 View this message in context:
 http://postgis.17.x6.nabble.com/more-memory-issues-with-raster2pgsql-tp5006000p5006002.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

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

Re: [postgis-users] more memory issues with raster2pgsql

[Bborie Park]

Wait a minute. I wonder what your psql's client encoding is. This seems
familiar...

-bborie
On Mar 28, 2014 5:30 PM, georgew gws...@hotmail.com wrote:

 Hi, I am trying to convert a raster from SAGA GIS to Postgis using
 raster2pgsql.The command line and its output is:
 C:\\Progra~1\\PostgreSQL\\9.3\\bin\\raster2pgsql.exe -F -d -
 I -C -M -s 2193 I:\cb09\cb09_3dem.sdat cb09_3 | psql -d NZTPU

 Processing 1/1: I:\cb09\cb09_3dem.sdat

 BEGIN
 NOTICE:  table cb09_3 does not exist, skipping
 DROP TABLE
 CREATE TABLE
 ERROR:  could not load library C:/Program
 Files/PostgreSQL/9.3/lib/rtpostgis-2.1.dll: unknown error 998
 LINE 1: INSERT INTO cb09_3 (rast,filename) VALUES ('010100...
  ^
 ERROR:  current transaction is aborted, commands ignored until end of
 transactio
 n block
 .

 I then ran again but just to create the output sql and dumped the first few
 lines of the output as follows:

 C:\\Progra~1\\PostgreSQL\\9.3\\bin\\raster2pgsql.exe -F -d -
 I -C -M -s 2193 I:\cb09\cb09_3dem.sdat cb09_3  c:\cb09.sql
 Processing 1/1: I:\cb09\cb09_3dem.sdat

 C:\WINDOWS\system32more  c:\cb09.sql
 BEGIN;
 DROP TABLE IF EXISTS cb09_3;
 CREATE TABLE cb09_3 (rid serial PRIMARY KEY,rast raster,filename
 text);
 INSERT INTO cb09_3 (rast,filename) VALUES
 ('0101001440

 14C000801D5F324100A0BC3F53419108

 C112211C4A804FC3C788A5B3446B9CB3440C9BB34412AAB3447FFFB3449DB9B444D679B54413

 06B6446859B6441C52B6441037B6446788B64468C9B64401CCB644EDAAB644797FB64458BCB64417

 95B744E348B844A0C6B8447C1CB944A13AB944DE0EB944D3E0B8445E14B9445B90B9442619BA443D
 .

 Finally executed the sql:

 C:\WINDOWS\system32psql -U postgres -d NZTPU -f c:\cb09.sql
 BEGIN
 psql:c:/cb09.sql:2: NOTICE:  table cb09_3 does not exist, skipping
 DROP TABLE
 CREATE TABLE
 psql:c:/cb09.sql:4: ERROR:  out of memory
 DETAIL:  String of 276576218 bytes is too long for encoding conversion.
 psql:c:/cb09.sql:5: ERROR:  current transaction is aborted, commands
 ignored
 unt
 il end of transaction block
 
 I assume the DLL not found is due to the lack of memory (because the file
 is
 where it should be)
 Is that assumption correct?

 All my databases and templates are UTF8 as shown here:

 http://postgis.17.x6.nabble.com/file/n5006000/postgres.png

 So why is the encoding conversion necessary?
 And more importantly how do I get around this problem?
 I am on Windows 8.1 Pro, and the very latest versions of Postgres (9.3) and
 Postgis (2.1.1)
 Many thanks for your help




 --
 View this message in context:
 http://postgis.17.x6.nabble.com/more-memory-issues-with-raster2pgsql-tp5006000.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

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

Re: [postgis-users] more memory issues with raster2pgsql

[Bborie Park]

Found the answer.

http://postgis.net/docs/RT_FAQ.html#qa_raster_fails_encoding_conversion

Got to love robe2 and her due diligence.

-bborie
On Mar 28, 2014 5:30 PM, georgew gws...@hotmail.com wrote:

 Hi, I am trying to convert a raster from SAGA GIS to Postgis using
 raster2pgsql.The command line and its output is:
 C:\\Progra~1\\PostgreSQL\\9.3\\bin\\raster2pgsql.exe -F -d -
 I -C -M -s 2193 I:\cb09\cb09_3dem.sdat cb09_3 | psql -d NZTPU

 Processing 1/1: I:\cb09\cb09_3dem.sdat

 BEGIN
 NOTICE:  table cb09_3 does not exist, skipping
 DROP TABLE
 CREATE TABLE
 ERROR:  could not load library C:/Program
 Files/PostgreSQL/9.3/lib/rtpostgis-2.1.dll: unknown error 998
 LINE 1: INSERT INTO cb09_3 (rast,filename) VALUES ('010100...
  ^
 ERROR:  current transaction is aborted, commands ignored until end of
 transactio
 n block
 .

 I then ran again but just to create the output sql and dumped the first few
 lines of the output as follows:

 C:\\Progra~1\\PostgreSQL\\9.3\\bin\\raster2pgsql.exe -F -d -
 I -C -M -s 2193 I:\cb09\cb09_3dem.sdat cb09_3  c:\cb09.sql
 Processing 1/1: I:\cb09\cb09_3dem.sdat

 C:\WINDOWS\system32more  c:\cb09.sql
 BEGIN;
 DROP TABLE IF EXISTS cb09_3;
 CREATE TABLE cb09_3 (rid serial PRIMARY KEY,rast raster,filename
 text);
 INSERT INTO cb09_3 (rast,filename) VALUES
 ('0101001440

 14C000801D5F324100A0BC3F53419108

 C112211C4A804FC3C788A5B3446B9CB3440C9BB34412AAB3447FFFB3449DB9B444D679B54413

 06B6446859B6441C52B6441037B6446788B64468C9B64401CCB644EDAAB644797FB64458BCB64417

 95B744E348B844A0C6B8447C1CB944A13AB944DE0EB944D3E0B8445E14B9445B90B9442619BA443D
 .

 Finally executed the sql:

 C:\WINDOWS\system32psql -U postgres -d NZTPU -f c:\cb09.sql
 BEGIN
 psql:c:/cb09.sql:2: NOTICE:  table cb09_3 does not exist, skipping
 DROP TABLE
 CREATE TABLE
 psql:c:/cb09.sql:4: ERROR:  out of memory
 DETAIL:  String of 276576218 bytes is too long for encoding conversion.
 psql:c:/cb09.sql:5: ERROR:  current transaction is aborted, commands
 ignored
 unt
 il end of transaction block
 
 I assume the DLL not found is due to the lack of memory (because the file
 is
 where it should be)
 Is that assumption correct?

 All my databases and templates are UTF8 as shown here:

 http://postgis.17.x6.nabble.com/file/n5006000/postgres.png

 So why is the encoding conversion necessary?
 And more importantly how do I get around this problem?
 I am on Windows 8.1 Pro, and the very latest versions of Postgres (9.3) and
 Postgis (2.1.1)
 Many thanks for your help




 --
 View this message in context:
 http://postgis.17.x6.nabble.com/more-memory-issues-with-raster2pgsql-tp5006000.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

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

Re: [postgis-users] more memory issues with raster2pgsql

[Bborie Park]

Assuming you are running psql at the terminal/console, you should run set
PGCLIENTENCODING=UTF8 before running psql.

I can't say I can be of much more help as I don't have a windows box handy.

-bborie
On Mar 28, 2014 7:01 PM, georgew gws...@hotmail.com wrote:

 thanks again bborie, my original raster is only 135MB in size, the
 generated
 sql is 270MB in size. As for the FAQ, I had looked at it, but could not
 work
 out how to find out how my client encoding is set and where. Also where is
 the postgresql load script for windows?



 --
 View this message in context:
 http://postgis.17.x6.nabble.com/more-memory-issues-with-raster2pgsql-tp5006000p5006006.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

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

Re: [postgis-users] point - raster, multiband at once

[Bborie Park]

setvalues with a standard array is faster than an array of geomvals.

-bborie


On Mon, Mar 24, 2014 at 11:59 AM, Rémi Cura remi.c...@gmail.com wrote:

 Hey,
 somebody knows a way to set multiple bands at once?

 It is about rasterizing points (into raster).
 Currently it is extremely slow (50*50 pixels, 1 band, 300ms using
 SetValues, *10 = 3 sec / tile).

 Somebody knows if setvalues with straight array is faster than using
 array[geom, value]?

 Thanks,

 Rémi-C

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

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

Re: [postgis-users] no pixels in raster

[Bborie Park]

If your restored raster data was in-db, it would have nothing to do with
GDAL. GDAL only kicks in when accessing out-db raster, polygonization and
warping...

Are you able to post the output of ST_Metadata() and ST_BandMetadata() for
one of the rasters in the database that returning no values?

-bborie


On Wed, Mar 19, 2014 at 9:02 AM, Andreas Laggner andreas.lagg...@ti.bund.de
 wrote:

 Everything was ok, no errors and no warnings, like it is when i import
 raster now. But there are no pixel values!!

 I think it has something to do with my PostGIS installation and gdal




 Am 19.03.2014 16:50, schrieb Bborie Park:

  What was the output when restoring your backup? And what was your restore
 command?

 I've never had problems dumping and restoring in my production
 environments.

 -bborie


 On Wed, Mar 19, 2014 at 6:19 AM, Andreas Laggner 
 andreas.lagg...@ti.bund.de

 wrote:
 Hi list,

 i have some problems with raster datasets since i use postgis 2.1.1 and
 postgres 9.3.3
 POSTGIS=2.1.1 r12113 GEOS=3.3.8-CAPI-1.7.8 PROJ=Rel. 4.8.0, 6 March
 2012 GDAL=GDAL 1.9.2, released 2012/10/08 LIBXML=2.7.8
 LIBJSON=UNKNOWN TOPOLOGY RASTER

 First all my rasters were empty after restoring my dump from 2.03/9.1,
 that was no fun, but i still have the files on my harddrive, so i
 imported
 them again:
 raster2pgsql -s 31467 -I -C -M /disk1/samba/importdb/dgm_25.tif -t
 100x100 rast.dgm25 | psql -d gis

 All seemed to be good, but as i want to use the raster, all my results
 were empty.

 All my imported rasters are empty! Trying to convert the raster to
 Polygons returns No pixels found for band 1

 The raster files are the same that i imported two months ago to 2.03/9.1,
 the raster2pgsql-command is the same too.

 Thanks, Cheers,

 Andreas

 --
 Dipl. Geoökologe Andreas Laggner

 Thünen-Institut für Agrarklimaschutz (AK)
 Arbeitsgruppe Emissionsinventare
 Johann Heinrich von Thünen-Institut (vTI),
 Bundesforschungsinstitut für Ländliche Räume, Wald und Fischerei

 Thünen Institute of Climate-Smart Agriculture
 Johann Heinrich von Thünen Institute (vTI),
 Federal Research Institute for Rural Areas, Forestry and Fisheries

 Bundesallee 50
 D-38116 Braunschweig

 Tel.: (+49) (0)531 596 2636
 Fax : (+49) (0)531 596 2645
 E-mail: andreas.lagg...@ti.bund.de
 Homepage: http://www.ti.bund.de

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


 --
 Dipl. Geoökologe Andreas Laggner

 Thünen-Institut für Agrarklimaschutz (AK)
 Arbeitsgruppe Emissionsinventare
 Johann Heinrich von Thünen-Institut (vTI),
 Bundesforschungsinstitut für Ländliche Räume, Wald und Fischerei

 Thünen Institute of Climate-Smart Agriculture
 Johann Heinrich von Thünen Institute (vTI),
 Federal Research Institute for Rural Areas, Forestry and Fisheries

 Bundesallee 50
 D-38116 Braunschweig

 Tel.: (+49) (0)531 596 2636
 Fax : (+49) (0)531 596 2645
 E-mail: andreas.lagg...@ti.bund.de
 Homepage: http://www.ti.bund.de


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

Re: [postgis-users] no pixels in raster

[Bborie Park]

What was the output when restoring your backup? And what was your restore
command?

I've never had problems dumping and restoring in my production environments.

-bborie


On Wed, Mar 19, 2014 at 6:19 AM, Andreas Laggner andreas.lagg...@ti.bund.de
 wrote:

 Hi list,

 i have some problems with raster datasets since i use postgis 2.1.1 and
 postgres 9.3.3
 POSTGIS=2.1.1 r12113 GEOS=3.3.8-CAPI-1.7.8 PROJ=Rel. 4.8.0, 6 March
 2012 GDAL=GDAL 1.9.2, released 2012/10/08 LIBXML=2.7.8
 LIBJSON=UNKNOWN TOPOLOGY RASTER

 First all my rasters were empty after restoring my dump from 2.03/9.1,
 that was no fun, but i still have the files on my harddrive, so i imported
 them again:
 raster2pgsql -s 31467 -I -C -M /disk1/samba/importdb/dgm_25.tif -t
 100x100 rast.dgm25 | psql -d gis

 All seemed to be good, but as i want to use the raster, all my results
 were empty.

 All my imported rasters are empty! Trying to convert the raster to
 Polygons returns No pixels found for band 1

 The raster files are the same that i imported two months ago to 2.03/9.1,
 the raster2pgsql-command is the same too.

 Thanks, Cheers,

 Andreas

 --
 Dipl. Geoökologe Andreas Laggner

 Thünen-Institut für Agrarklimaschutz (AK)
 Arbeitsgruppe Emissionsinventare
 Johann Heinrich von Thünen-Institut (vTI),
 Bundesforschungsinstitut für Ländliche Räume, Wald und Fischerei

 Thünen Institute of Climate-Smart Agriculture
 Johann Heinrich von Thünen Institute (vTI),
 Federal Research Institute for Rural Areas, Forestry and Fisheries

 Bundesallee 50
 D-38116 Braunschweig

 Tel.: (+49) (0)531 596 2636
 Fax : (+49) (0)531 596 2645
 E-mail: andreas.lagg...@ti.bund.de
 Homepage: http://www.ti.bund.de

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

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

Re: [postgis-users] in-base raster wrong use by gdalinfo and gdal_translate

[Bborie Park]

It sounds like it might be a problem with the GDAL PostGIS Raster driver.
From the GDAL driver page, it looks like they recommend 1.10.1...

http://trac.osgeo.org/gdal/wiki/frmts_wtkraster.html


On Fri, Mar 7, 2014 at 7:42 AM, Rémi Cura remi.c...@gmail.com wrote:

 Hey,
 I am faced with a problem I can't solve :

 I have a raster obtained by using st_setvalues and several points.

 The raster has 2 bands, half of the pixel being set.

 Using the st_pixelaspolygons and the ST_SummaryStats I am sure that some
 of the pixels of the raster are set.

 Now when I use gdalinfo or gdal_translate on this in-base raster, it gives
 the correct information about the raster (size, position, band type, etc),
 *but the raster has no pixel set!*

 I tried several combination of parameters with non luck.


 postgis version :

 --POSTGIS=2.1.0 r11822 GEOS=3.5.0dev-CAPI-1.9.0 r3963 PROJ=Rel.
 4.8.0, 6 March 2012 GDAL=GDAL 1.10.0, released 2013/04/24 LIBXML=2.8.0
 RASTER

 postgres version : 9.3.3


 Summary :
 Raster of 50x50 pixels has 2 bands and extent of BOX(1913.5 20887.5,1914.5
 20888.5)
 band 1 of pixtype 32BSI is in-db with NODATA value of -10
 band 2 of pixtype 32BSI is in-db with NODATA value of -10

 Band 1 contains(histogram, excluding null values):
 4166041666.67320.039360393603936
 41666.6741673.3300
 41673.334168000
 4168041686.676560.806888068880689
 41686.6741693.3300
 41693.33417001250.153751537515375

 gdalinfo :
  gdalinfo  PG:host=localhost port=5433 dbname='test_pointcloud'
 user='postgres' password='youguess' schema='test_raster'
 table='patch_to_raster' -stats
 Driver: PostGISRaster/PostGIS Raster driver
 Files: none associated
 Size is 50, 50
 Coordinate System is:
 * CORRECT*
 Origin = (1913.500,20887.500)
 Pixel Size = (0.020,0.020)
 Corner Coordinates:
 Upper Left  (1913.500,   20887.500) (  1d21'30.09W,  5d51'10.78S)
 Lower Left  (1913.500,   20888.500) (  1d21'30.09W,  5d51'10.75S)
 Upper Right (1914.500,   20887.500) (  1d21'30.06W,  5d51'10.78S)
 Lower Right (1914.500,   20888.500) (  1d21'30.07W,  5d51'10.75S)
 Center  (1914.000,   20888.000) (  1d21'30.08W,  5d51'10.76S)
 Band 1 Block=50x50 Type=Int32, ColorInterp=Undefined
   Minimum=0.000, Maximum=0.000, Mean=0.000, StdDev=0.000
   NoData Value=-10
   Metadata:
 STATISTICS_MAXIMUM=0
 STATISTICS_MEAN=0
 STATISTICS_MINIMUM=0
 STATISTICS_STDDEV=0
 Band 2 Block=50x50 Type=Int32, ColorInterp=Undefined
   Minimum=0.000, Maximum=0.000, Mean=0.000, StdDev=0.000
   NoData Value=-10
   Metadata:
 STATISTICS_MAXIMUM=0
 STATISTICS_MEAN=0
 STATISTICS_MINIMUM=0
 STATISTICS_STDDEV=0
 remi@RemiCura-VirtualUbuntu:/media/sf_E_RemiCura/PROJETS/PC_in_DB/Raster/exported$



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

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

Re: [postgis-users] import raster

[Bborie Park]

Do you need the maximum extent constraint? Generally, it is safe to disable
the maximum extent constraint.

-bborie


On Thu, Feb 27, 2014 at 7:06 AM, Eloi Ribeiro e...@openmailbox.org wrote:

 On 2014-02-27 11:19, Rémi Cura wrote:

 Maybe you can try to separate raster2pgsql and psql .

 First use raster2pgsqland output it to a file on you filesystem.
 Then use psql
 to execute this file.

  You could see how much time each step takes, and how much space the
 sql temp file is.


 The sql file went up to 6.6 GB.


  Maybe you could use the   -Y flag to make it faster?


 (...)
 INSERT 0 1
 INSERT 0 1
 INSERT 0 1

 # Til here with the -Y flag was much faster, jumped from 1h 30 min to only
 13 min.

 CREATE INDEX
 ANALYZE
 psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Adding SRID
 constraint
 CONTEXT:  PL/pgSQL function addrasterconstraints line 53 at RETURN
 psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Adding scale-X
 constraint
 CONTEXT:  PL/pgSQL function addrasterconstraints line 53 at RETURN
 psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Adding scale-Y
 constraint
 CONTEXT:  PL/pgSQL function addrasterconstraints line 53 at RETURN
 psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Adding
 blocksize-X constraint
 CONTEXT:  PL/pgSQL function addrasterconstraints line 53 at RETURN
 psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Adding
 blocksize-Y constraint
 CONTEXT:  PL/pgSQL function addrasterconstraints line 53 at RETURN
 psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Adding
 alignment constraint
 CONTEXT:  PL/pgSQL function addrasterconstraints line 53 at RETURN
 psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Adding number
 of bands constraint
 CONTEXT:  PL/pgSQL function addrasterconstraints line 53 at RETURN
 psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Adding pixel
 type constraint
 CONTEXT:  PL/pgSQL function addrasterconstraints line 53 at RETURN
 psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Adding nodata
 value constraint
 CONTEXT:  PL/pgSQL function addrasterconstraints line 53 at RETURN
 psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Adding
 out-of-database constraint
 CONTEXT:  PL/pgSQL function addrasterconstraints line 53 at RETURN

 # Til here took about 30 minutes. And from here starts the slow part.

 psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Unable to get
 the extent of a sample raster. Attempting memory efficient (slower) approach
 CONTEXT:  PL/pgSQL function addrasterconstraints line 100 at assignment
 PL/pgSQL function addrasterconstraints line 53 at RETURN

 # 3h 30min have passed and still the same.

 The message is clear and searching for this message I came across with the
 code I think is executing [1].
 There I can see that when this notice is raised it try again with function
 'st_memunion' (memory friendly but slower) after failing with 'st_collect'.
 The machine where I am running this has 16 GB RAM and even so it was not
 able to preform this task with 'st_collect'.

 Any suggestions?

 Thanks.

 Cheers,

 Eloi Ribeiro

 [1] https://git.osgeo.org/postgis/tags/2.0.3/raster/rt_pg/
 rtpostgis.sql.in.c




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

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

Re: [postgis-users] import raster

[Bborie Park]

When using raster2pgsql, you wouldn't pass the -C flag. If you're going to
use the generated SQL file, remove the SELECT AddRasterConstraints... line.

You can add the raster constraints that you want added later with the
AddRasterConstraints() function.

-bborie


On Thu, Feb 27, 2014 at 8:31 AM, Eloi e...@openmailbox.org wrote:

 Thanks bborie,
 It is possible to do that in the import statement or just after the import?

 Cheers,

 Eloi

 On February 27, 2014 5:11:23 PM CET, Bborie Park dustym...@gmail.com
 wrote:
 Do you need the maximum extent constraint? Generally, it is safe to
 disable
 the maximum extent constraint.
 
 -bborie
 
 
 On Thu, Feb 27, 2014 at 7:06 AM, Eloi Ribeiro e...@openmailbox.org
 wrote:
 
  On 2014-02-27 11:19, Rémi Cura wrote:
 
  Maybe you can try to separate raster2pgsql and psql .
 
  First use raster2pgsqland output it to a file on you filesystem.
  Then use psql
  to execute this file.
 
   You could see how much time each step takes, and how much space
 the
  sql temp file is.
 
 
  The sql file went up to 6.6 GB.
 
 
   Maybe you could use the   -Y flag to make it faster?
 
 
  (...)
  INSERT 0 1
  INSERT 0 1
  INSERT 0 1
 
  # Til here with the -Y flag was much faster, jumped from 1h 30 min to
 only
  13 min.
 
  CREATE INDEX
  ANALYZE
  psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Adding
 SRID
  constraint
  CONTEXT:  PL/pgSQL function addrasterconstraints line 53 at RETURN
  psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Adding
 scale-X
  constraint
  CONTEXT:  PL/pgSQL function addrasterconstraints line 53 at RETURN
  psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Adding
 scale-Y
  constraint
  CONTEXT:  PL/pgSQL function addrasterconstraints line 53 at RETURN
  psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Adding
  blocksize-X constraint
  CONTEXT:  PL/pgSQL function addrasterconstraints line 53 at RETURN
  psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Adding
  blocksize-Y constraint
  CONTEXT:  PL/pgSQL function addrasterconstraints line 53 at RETURN
  psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Adding
  alignment constraint
  CONTEXT:  PL/pgSQL function addrasterconstraints line 53 at RETURN
  psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Adding
 number
  of bands constraint
  CONTEXT:  PL/pgSQL function addrasterconstraints line 53 at RETURN
  psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Adding
 pixel
  type constraint
  CONTEXT:  PL/pgSQL function addrasterconstraints line 53 at RETURN
  psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Adding
 nodata
  value constraint
  CONTEXT:  PL/pgSQL function addrasterconstraints line 53 at RETURN
  psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Adding
  out-of-database constraint
  CONTEXT:  PL/pgSQL function addrasterconstraints line 53 at RETURN
 
  # Til here took about 30 minutes. And from here starts the slow part.
 
  psql:/home/eloi/Documents/cec_l_b1i.sql:37324808: NOTICE:  Unable to
 get
  the extent of a sample raster. Attempting memory efficient (slower)
 approach
  CONTEXT:  PL/pgSQL function addrasterconstraints line 100 at
 assignment
  PL/pgSQL function addrasterconstraints line 53 at RETURN
 
  # 3h 30min have passed and still the same.
 
  The message is clear and searching for this message I came across
 with the
  code I think is executing [1].
  There I can see that when this notice is raised it try again with
 function
  'st_memunion' (memory friendly but slower) after failing with
 'st_collect'.
  The machine where I am running this has 16 GB RAM and even so it was
 not
  able to preform this task with 'st_collect'.
 
  Any suggestions?
 
  Thanks.
 
  Cheers,
 
  Eloi Ribeiro
 
  [1] https://git.osgeo.org/postgis/tags/2.0.3/raster/rt_pg/
  rtpostgis.sql.in.c
 
 
 
 
  ___
  postgis-users mailing list
  postgis-users@lists.osgeo.org
  http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
 

 --
 Sent from my Android device with K-9 Mail. Please excuse my brevity.
 ___
 postgis-users mailing list
 postgis-users@lists.osgeo.org
 http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

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

Re: [postgis-users] ST_SummaryStatsAgg not working with PostgreSQL 9.3 + PostGIS 2.1.1

[Bborie Park]

PostGIS 2.1 does not have the data type summarystats. Where did you get
this function?

PostGIS 2.2 (in development now) does have that data type though for a
built in ST_SummaryStatsAgg function.

-bborie
On Jan 31, 2014 9:23 AM, Hailey Eckstrand haileyeckstr...@gmail.com
wrote:

 Hi bborie,

 After changing it from 'SQL' to 'sql' this is the error:

 $psql mydb -f ST_SummaryStatsAgg.sql

 psql:ST_SummaryStatsAgg.sql:50: ERROR:  type summarystats does not exist
 psql:ST_SummaryStatsAgg.sql:56: ERROR:  type summarystats does not exist
 psql:ST_SummaryStatsAgg.sql:77: ERROR:  type summarystats does not exist
 psql:ST_SummaryStatsAgg.sql:86: ERROR:  type summarystats does not exist
 psql:ST_SummaryStatsAgg.sql:92: ERROR:  type summarystats does not exist
 CREATE FUNCTION
 psql:ST_SummaryStatsAgg.sql:121: ERROR:  function
 st_summarystatsagg(raster) does not exist
 LINE 8: FROM (SELECT ST_SummaryStatsAgg(rast) as ss, id
  ^
 HINT:  No function matches the given name and argument types. You might
 need to add explicit type casts.


 Does PostgreSQL 9.3 still have the data type summarystats? Also, the
 function that ST_SummaryStatsAgg.sql relies on, ST_SummaryStats.sql, has it
 remained the same?

 Thanks,
 Hailey



 On Fri, Jan 31, 2014 at 6:53 AM, Bborie Park dustym...@gmail.com wrote:

 Can you change line 56 of that code from ...

 $$ LANGUAGE 'SQL';

 to

 $$ LANGUAGE 'sql';

 I believe PostgreSQL 9.3 (maybe 9.2) became case-sensitive with regards
 to the language.

 -bborie



 On Thu, Jan 30, 2014 at 4:41 PM, Hailey Eckstrand 
 haileyeckstr...@gmail.com wrote:

 Hello,
 I've got a new sandbox (Ubuntu 12.04) with a fresh install on PostgreSQL
 9.3 and the following for PostGIS version:

  postgis_full_version

 ---
  POSTGIS=2.1.1 r12113 GEOS=3.4.2-CAPI-1.8.2 r3921 PROJ=Rel. 4.8.0,
 6 March 2012 GDAL=GDAL 1.10.1, released 2013/08/26 LIBXML=2.7.8
 LIBJSON=UNKNOWN TOPOLOGY RASTER
 (1 row)


 I just ran into a problem where I tried to run ST_SummaryStatsAgg on a
 raster for a polygon and ended up with an error. I thought it was b/c I did
 not have the function loaded, so when I tried to load the
 function ST_SummaryStatsAgg.sql, I got the following errors:

 psql:ST_SummaryStatsAgg.sql:50: ERROR:  type summarystats does not exist
 psql:ST_SummaryStatsAgg.sql:56: ERROR:  language SQL does not exist
 psql:ST_SummaryStatsAgg.sql:77: ERROR:  type summarystats does not exist
 psql:ST_SummaryStatsAgg.sql:86: ERROR:  type summarystats does not
 exist
 psql:ST_SummaryStatsAgg.sql:92: ERROR:  type summarystats does not
 exist
 CREATE FUNCTION
 psql:ST_SummaryStatsAgg.sql:121: ERROR:  function
 st_summarystatsagg(raster) does not exist
 LINE 8: FROM (SELECT ST_SummaryStatsAgg(rast) as ss, id
  ^
 HINT:  No function matches the given name and argument types. You might
 need to add explicit type casts.

 Here is the current ST_SummaryStatsAgg that I am trying to load:

 http://hastebin.com/lujenosaro.pas

 Thanks!

 Hailey

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



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



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

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

Re: [postgis-users] ST_SummaryStatsAgg not working with PostgreSQL 9.3 + PostGIS 2.1.1

[Bborie Park]

Put the following at the top of your file...

CREATE TYPE summarystats AS (
count bigint,
sum double precision,
 mean double precision,
stddev double precision,
min double precision,
 max double precision
);

-bborie


On Fri, Jan 31, 2014 at 9:41 AM, Hailey Eckstrand haileyeckstr...@gmail.com
 wrote:

 It was from a post on the PostGIS User Discussion that I made last year
 from Pierre Racine:

 http://lists.osgeo.org/pipermail/postgis-users/2013-August/037514.html

 Is it possible to get this functionality working with PostGIS 2.1?

 Thanks,
 Hailey


 On Fri, Jan 31, 2014 at 9:33 AM, Bborie Park dustym...@gmail.com wrote:

 PostGIS 2.1 does not have the data type summarystats. Where did you get
 this function?

 PostGIS 2.2 (in development now) does have that data type though for a
 built in ST_SummaryStatsAgg function.

 -bborie
 On Jan 31, 2014 9:23 AM, Hailey Eckstrand haileyeckstr...@gmail.com
 wrote:

 Hi bborie,

 After changing it from 'SQL' to 'sql' this is the error:

 $psql mydb -f ST_SummaryStatsAgg.sql

 psql:ST_SummaryStatsAgg.sql:50: ERROR:  type summarystats does not exist
 psql:ST_SummaryStatsAgg.sql:56: ERROR:  type summarystats does not exist
 psql:ST_SummaryStatsAgg.sql:77: ERROR:  type summarystats does not exist
 psql:ST_SummaryStatsAgg.sql:86: ERROR:  type summarystats does not
 exist
 psql:ST_SummaryStatsAgg.sql:92: ERROR:  type summarystats does not
 exist
 CREATE FUNCTION
 psql:ST_SummaryStatsAgg.sql:121: ERROR:  function
 st_summarystatsagg(raster) does not exist
 LINE 8: FROM (SELECT ST_SummaryStatsAgg(rast) as ss, id
  ^
 HINT:  No function matches the given name and argument types. You might
 need to add explicit type casts.


 Does PostgreSQL 9.3 still have the data type summarystats? Also, the
 function that ST_SummaryStatsAgg.sql relies on, ST_SummaryStats.sql, has it
 remained the same?

 Thanks,
 Hailey



 On Fri, Jan 31, 2014 at 6:53 AM, Bborie Park dustym...@gmail.comwrote:

 Can you change line 56 of that code from ...

 $$ LANGUAGE 'SQL';

 to

 $$ LANGUAGE 'sql';

 I believe PostgreSQL 9.3 (maybe 9.2) became case-sensitive with regards
 to the language.

 -bborie



 On Thu, Jan 30, 2014 at 4:41 PM, Hailey Eckstrand 
 haileyeckstr...@gmail.com wrote:

 Hello,
 I've got a new sandbox (Ubuntu 12.04) with a fresh install on
 PostgreSQL 9.3 and the following for PostGIS version:

postgis_full_version

 ---
  POSTGIS=2.1.1 r12113 GEOS=3.4.2-CAPI-1.8.2 r3921 PROJ=Rel.
 4.8.0, 6 March 2012 GDAL=GDAL 1.10.1, released 2013/08/26 
 LIBXML=2.7.8
 LIBJSON=UNKNOWN TOPOLOGY RASTER
 (1 row)


 I just ran into a problem where I tried to run ST_SummaryStatsAgg on a
 raster for a polygon and ended up with an error. I thought it was b/c I 
 did
 not have the function loaded, so when I tried to load the
 function ST_SummaryStatsAgg.sql, I got the following errors:

 psql:ST_SummaryStatsAgg.sql:50: ERROR:  type summarystats does not
 exist
 psql:ST_SummaryStatsAgg.sql:56: ERROR:  language SQL does not exist
 psql:ST_SummaryStatsAgg.sql:77: ERROR:  type summarystats does not
 exist
 psql:ST_SummaryStatsAgg.sql:86: ERROR:  type summarystats does not
 exist
 psql:ST_SummaryStatsAgg.sql:92: ERROR:  type summarystats does not
 exist
 CREATE FUNCTION
 psql:ST_SummaryStatsAgg.sql:121: ERROR:  function
 st_summarystatsagg(raster) does not exist
 LINE 8: FROM (SELECT ST_SummaryStatsAgg(rast) as ss, id
  ^
 HINT:  No function matches the given name and argument types. You
 might need to add explicit type casts.

 Here is the current ST_SummaryStatsAgg that I am trying to load:

 http://hastebin.com/lujenosaro.pas

 Thanks!

 Hailey

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



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



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


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



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

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

Re: [postgis-users] Look for Help on Libraries and Dependencies for Compiling Postgis2.1 Source for Suse 11 SP3

[Bborie Park]

And SP2 does not have gdal-dev packages? I suggest you compile gdal
yourself with what formats you need. It's not that bad to compile,
especially since you're compiling everything else.

-bborie
On Jan 16, 2014 9:54 AM, Marco marco91...@gmail.com wrote:

 Hi Bborie,

 There is no gdal in 11 SP3 in OpenSUSE repo that I can find, but I am
 using the gdal from 11 SP 2 repo.

 Thanks


 On Wed, Jan 15, 2014 at 7:44 PM, Bborie Park dustym...@gmail.com wrote:

 Are you using GDAL provided by OpenSUSE? Or did you compile your own?

 -bborie


 On Wed, Jan 15, 2014 at 7:25 PM, Marco marco91...@gmail.com wrote:

 Hi,



 I am trying to build postgis 2.1 from the source for SUSE 11 SP 3.
 However it requires openjp2, hdf5, mfhdf, df (all devel version I think)
 that I could not find. I have tried few repositories such as Opensuse,
 Packman and the Suse repo from Zypper.



 I would really appreciate if anyone could tell me about other approaches
 of finding these libraries.



 Here is the error message from the terminal when I try to compile the
 source.



 /usr/lib64/gcc/x86_64-suse-linux/4.3/../../../../x86_64-suse-linux/bin/ld:
 cannot find -lopenjp2

 /usr/lib64/gcc/x86_64-suse-linux/4.3/../../../../x86_64-suse-linux/bin/ld:
 cannot find -lhdf5

 /usr/lib64/gcc/x86_64-suse-linux/4.3/../../../../x86_64-suse-linux/bin/ld:
 cannot find -lmfhdf

 /usr/lib64/gcc/x86_64-suse-linux/4.3/../../../../x86_64-suse-linux/bin/ld:
 cannot find -ldf

 collect2: ld returned 1 exit status

 make[2]: *** [raster2pgsql] Error 1

 make[2]: Leaving directory
 `/home/ekeemaa/IPT_NMS/postgis/postgis-2.1.1/raster/loader'

 make[1]: *** [rtloader] Error 2

 make[1]: Leaving directory
 `/home/ekeemaa/IPT_NMS/postgis/postgis-2.1.1/raster’

 make: *** [all] Error 1

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



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



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

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

Re: [postgis-users] Look for Help on Libraries and Dependencies for Compiling Postgis2.1 Source for Suse 11 SP3

[Bborie Park]

No. Your gdal-dev is more than likely fine. It's the dev packages that the
gdal-dev packages that are missing. If you can't find those hdf5, openjp2
dev packages, you'll probably want to compile gdal.

-bborie
On Jan 16, 2014 10:01 AM, Marco marco91...@gmail.com wrote:

 Hi,

 I am using libgdal-devel-1.10.1-1.4. Do you think all the complains of
 missing libraries like openjp2, hdf5 etc. are related to gdal-dev package?

 Thanks


 On Thu, Jan 16, 2014 at 9:57 AM, Bborie Park dustym...@gmail.com wrote:

 And SP2 does not have gdal-dev packages? I suggest you compile gdal
 yourself with what formats you need. It's not that bad to compile,
 especially since you're compiling everything else.

 -bborie
 On Jan 16, 2014 9:54 AM, Marco marco91...@gmail.com wrote:

 Hi Bborie,

 There is no gdal in 11 SP3 in OpenSUSE repo that I can find, but I am
 using the gdal from 11 SP 2 repo.

 Thanks


 On Wed, Jan 15, 2014 at 7:44 PM, Bborie Park dustym...@gmail.comwrote:

 Are you using GDAL provided by OpenSUSE? Or did you compile your own?

 -bborie


 On Wed, Jan 15, 2014 at 7:25 PM, Marco marco91...@gmail.com wrote:

 Hi,



 I am trying to build postgis 2.1 from the source for SUSE 11 SP 3.
 However it requires openjp2, hdf5, mfhdf, df (all devel version I think)
 that I could not find. I have tried few repositories such as Opensuse,
 Packman and the Suse repo from Zypper.



 I would really appreciate if anyone could tell me about other
 approaches of finding these libraries.



 Here is the error message from the terminal when I try to compile the
 source.



 /usr/lib64/gcc/x86_64-suse-linux/4.3/../../../../x86_64-suse-linux/bin/ld:
 cannot find -lopenjp2

 /usr/lib64/gcc/x86_64-suse-linux/4.3/../../../../x86_64-suse-linux/bin/ld:
 cannot find -lhdf5

 /usr/lib64/gcc/x86_64-suse-linux/4.3/../../../../x86_64-suse-linux/bin/ld:
 cannot find -lmfhdf

 /usr/lib64/gcc/x86_64-suse-linux/4.3/../../../../x86_64-suse-linux/bin/ld:
 cannot find -ldf

 collect2: ld returned 1 exit status

 make[2]: *** [raster2pgsql] Error 1

 make[2]: Leaving directory
 `/home/ekeemaa/IPT_NMS/postgis/postgis-2.1.1/raster/loader'

 make[1]: *** [rtloader] Error 2

 make[1]: Leaving directory
 `/home/ekeemaa/IPT_NMS/postgis/postgis-2.1.1/raster’

 make: *** [all] Error 1

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



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



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


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



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

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

Re: [postgis-users] Look for Help on Libraries and Dependencies for Compiling Postgis2.1 Source for Suse 11 SP3

[Bborie Park]

Are you using GDAL provided by OpenSUSE? Or did you compile your own?

-bborie


On Wed, Jan 15, 2014 at 7:25 PM, Marco marco91...@gmail.com wrote:

 Hi,



 I am trying to build postgis 2.1 from the source for SUSE 11 SP 3. However
 it requires openjp2, hdf5, mfhdf, df (all devel version I think) that I
 could not find. I have tried few repositories such as Opensuse, Packman and
 the Suse repo from Zypper.



 I would really appreciate if anyone could tell me about other approaches
 of finding these libraries.



 Here is the error message from the terminal when I try to compile the
 source.



 /usr/lib64/gcc/x86_64-suse-linux/4.3/../../../../x86_64-suse-linux/bin/ld:
 cannot find -lopenjp2

 /usr/lib64/gcc/x86_64-suse-linux/4.3/../../../../x86_64-suse-linux/bin/ld:
 cannot find -lhdf5

 /usr/lib64/gcc/x86_64-suse-linux/4.3/../../../../x86_64-suse-linux/bin/ld:
 cannot find -lmfhdf

 /usr/lib64/gcc/x86_64-suse-linux/4.3/../../../../x86_64-suse-linux/bin/ld:
 cannot find -ldf

 collect2: ld returned 1 exit status

 make[2]: *** [raster2pgsql] Error 1

 make[2]: Leaving directory
 `/home/ekeemaa/IPT_NMS/postgis/postgis-2.1.1/raster/loader'

 make[1]: *** [rtloader] Error 2

 make[1]: Leaving directory
 `/home/ekeemaa/IPT_NMS/postgis/postgis-2.1.1/raster’

 make: *** [all] Error 1

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

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

Re: [postgis-users] PostgreSQL geometric data types vs GEOMETRY data type

[Bborie Park]

Correct. PostGIS supports PostGIS datatypes.

-bborie


On Thu, Jan 2, 2014 at 11:10 AM, Peter Len petera...@earthlink.net wrote:

 I am using PostgreSQL 9.1 with PostGIS.  I am still doing a lot of
 documentation reading but have the following question  PostgreSQL comes
 with some default geometric datatypes like POINT and POLYGON.  I created a
 couple of tables with these types and added some data.  After installing
 PostGIS, I came to find an example of creating a table with a GEOMETRY
 column.  It then went on to show the use of the PostGIS ST_GeomFromText
 method.  That worked fine on my test table that I created with the
 GEOMETRY(Point) data type but did not work on the table with just the POINT
 data type.  So.  to use the PostGIS methods/functions, do the spatial
 columns need to be defined as GEOMETRY / GEOGRAPHY rather than the
 PostgreSQL data types of POINT, POLYGON, etc?

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

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

Re: [postgis-users] ST_RotateX with pointOrigin!

[Bborie Park]

Start in the regress directory found in the root of the postgis source.
You'll see that there are always two files for any particular test
(MYTEST.sql and MYTEST_expected). To include MYTEST as part of make
check, you add MYTEST to regress/Makefile.in

-bborie


On Sat, Dec 14, 2013 at 10:29 PM, Stephen Mather
step...@smathermather.comwrote:

 I have ticketed.  It's a little messy of a ticket (actually two), so
 apologies for that.

 http://trac.osgeo.org/postgis/ticket/2570
 http://trac.osgeo.org/postgis/ticket/2571

 I have not yet written any unit tests.  Where would I begin...



 On Sun, Dec 15, 2013 at 1:02 AM, Bborie Park dustym...@gmail.com wrote:

 Sweet. Can you create a ticket and attach the patch?

 Some unit tests would be nice so that expected behavior is maintained...
 and bugs are caught.

 Thanks!

 -bborie


 On Sat, Dec 14, 2013 at 7:57 PM, Stephen Mather 
 step...@smathermather.com wrote:

 Ahem-- bugs fixed:

 -- Function: st_rotatex(geometry, double precision, geometry)
 CREATE OR REPLACE FUNCTION ST_RotateX(geomA geometry, rotRadians
 double precision, pointOrigin geometry)
   RETURNS geometry AS
 $BODY$

 - Transform geometry to nullsville (0,0,0) so rotRadians will take
 place around the pointOrigin
 WITH transformed AS (
 SELECT ST_Translate(geomA, -1 * ST_X(pointOrigin), -1 *
 ST_Y(pointOrigin), -1 * ST_Z(pointOrigin)) AS the_geom
 ),
 - Rotate in place
 rotated AS (
 SELECT ST_RotateX(the_geom, rotRadians) AS the_geom FROM transformed
 ),
 - Translate back home
 rotTrans AS (
 SELECT ST_Translate(the_geom, ST_X(pointOrigin),
 ST_Y(pointOrigin), ST_Z(pointOrigin)) AS the_geom
 FROM rotated
 )
 - profit
 SELECT the_geom from rotTrans

 ;

 $BODY$
   LANGUAGE sql VOLATILE
   COST 100;


 On Sat, Dec 14, 2013 at 8:37 PM, Stephen Mather
 step...@smathermather.com wrote:
  Hi All,
 
  I think I avoided doing linear algebra, which is good since I never
  studied it... .
 
  This is my cludgy patch for making a version of
 
  geometry ST_RotateX(geometry geomA, float rotRadians, geometry
 pointOrigin)
 
  It's not pretty enough to be a real patch ('cause my brain couldn't do
  that whole linear algebra thing, and hence why it's here and not the
  developers list), but thought I'd share it anyway and get impressions,
  and have some brighter minds make sure I don't have some major logic
  failure here:
 
  ---
 
  DROP FUNCTION st_rotatex(geometry,double precision,geometry);
 
  CREATE OR REPLACE FUNCTION ST_RotateX(geomA geometry, rotRadians
  double precision, pointOrigin geometry)
RETURNS geometry AS
  $BODY$
 
  - Transform geometry to nullsville (0,0,0) so rotRadians will take
  place around the pointOrigin
  WITH transformed AS (
  SELECT ST_Translate(geomA, -1 * ST_X(pointOrigin), -1 *
  ST_Y(pointOrigin), -1 * ST_Z(pointOrigin)) AS the_geom
  ),
  - Rotate in place
  rotated AS (
  SELECT ST_RotateX(the_geom, rotRadians) FROM transformed
  ),
  - Translate back home
  rotTrans AS (
  SELECT ST_Translate(geomA, ST_X(pointOrigin), ST_Y(pointOrigin),
  ST_Z(pointOrigin)) AS the_geom
  )
  - profit
  SELECT the_geom from rotTrans
 
  ;
 
  $BODY$
LANGUAGE sql VOLATILE
COST 100;
 ___
 postgis-users mailing list
 postgis-users@lists.osgeo.org
 http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users



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



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

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

Re: [postgis-users] Raster clipping vs. intersection

[Bborie Park]

The behavior of ST_Clip and ST_Intersection for raster and geometry are
very different.

ST_Intersection() converts the raster into a set of geometries and then
returns the intersection of the input geometry and the raster's geometries.

ST_Clip() does the exact opposite. The input geometry is converted to a
raster after which the geometry's raster is passed into a map-algebra
operation with the input raster.

So, their answers will differ.

It should be noted that the ST_Clip() operation should be faster as it all
parts of it happens in C while the ST_Intersection() bounces back and forth
between SQL and C.

-bborie


On Sun, Dec 1, 2013 at 3:35 PM, Paragon Corporation l...@pcorp.us wrote:

 In doing some tests I was somewhat surprised to find out that doing an
 ST_Clip operation first before an ST_Intersection operation is
 significantly
 faster than just doing a straight ST_Intersection.

 Unfortunately the answers are different and I'm not sure what I am losing.

 I'm guessing with ST_Clip when a pixel only partially intersects a geometry
 it is thrown out and with ST_Intersection if a pixel intersects a geometry
 it is included.  Is that correct.

 This is running in PostGIS 2.1.1 (don't have 2.0 readily set up to compare)


 For example:
 -- gives an answer of 1258.409 but returns it in 14,431 ms  (without
 aggregation returns 307 rows)

 SELECT SUM((gval).val* ST_Area((gval).geom))
  / ST_Area(ST_Union((gval).geom)) As avg_elesqm
 FROM (
 SELECT ST_Intersection(rast,1,buf.geom) As gval
 FROM kauai
  INNER JOIN
 (SELECT ST_Buffer(
   ST_GeomFromText('POINT(444205 2438785)',26904),
 100) As geom
 ) As buf ON
 ST_Intersects(rast,buf.geom)) As foo;


 -- The same operation but adding a ST_Clip step --
 For example:
 -- gives an answer of 1236.834495 but returns it in 511 ms (without
 aggregation returns 281 rows)

 SELECT SUM((gval).val* ST_Area((gval).geom))
  / ST_Area(ST_Union((gval).geom)) As avg_elesqm
 FROM (
 SELECT ST_Intersection(ST_Clip(rast,buf.geom),1,buf.geom) As gval
 FROM kauai
  INNER JOIN
 (SELECT ST_Buffer(
   ST_GeomFromText('POINT(444205 2438785)',26904),
 100) As geom
 ) As buf ON
 ST_Intersects(rast,buf.geom)) As foo;



 Thanks,
 Regina


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

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

Re: [postgis-users] The first release of the PostGIS Add-ons is out!

[Bborie Park]

 Its your code, pick the one you want.


+1. If there was one true answer to the licensing question, this would be
it.

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

Re: [postgis-users] [postgis-devel] ST_Union() performance problem (with possible funding)

[Bborie Park]

Your best bet is to consider splitting the workload among several
postgresql connections.

darkblueb had a blog post about this...

http://blog.light42.com/wordpress/?p=23


On Wed, Oct 16, 2013 at 5:21 PM, Brent Wood pcr...@pcreso.com wrote:

 Hi,

 Any advice appreciated!!

 I'm undertaking a spatial analysis using Postgis (what else would I
 use!!!). The first part works well.

 I take a large number (potentially millions) of lines defined by start 
 end points  buffer them to create polygons. (I'm working in lat/long
 EPSG:4326 but transforming to a custom equal area projection for the
 buffering operation).

 I generate a grid of 5x5km cells (polygons) covering the region of
 interest.

 I clip the line based polygons to the grid, so I can generate statistics
 for each cell describing the lines that intersect with it, various
 quantitative measures such as ST_Union() the clipped line polygons to
 generate a footprint in each cell to work out how much is/is not covered,
 or sum the ST_Area() of the clipped polygons grouped by cell to calculate
 an aggregate cover, which can be several times the actual cell area.


 So far so good, it works well, the code is clear  transparent  provides
 a good result. At least as good as any commercial software can do. My test
 data subset is processed from scratch in about 30 minutes.

 Now I want to ST_Union() all the cell based polygons into an overall
 single multipolygon representing the footprint. The code is simple. The
 performance, even with my subset,  is a problem.

 I have thousands of cell based footprint multipolygons, each potentially
 with thousands of vertices to be ST_Union()ed. Runtime is weeks for an
 iteration. If I need separate total footprints for 20 different species
 annually for 5 years, that is 100 iterations. Memory  I/O use is minimal -
 it is totally cpu bound.

 I am looking at trying to simplify the polygons to be unioned to reduce
 the number of vertices ( hence processing) involved, but to achieve any
 significant benefit I'm having to change the shape of the polygons to
 ST_Union() too much.



 Does anyone have any suggestions as to how this could be made
 significantly faster?
 If I had $$ to throw at developers to work on the codebase (presumably
 GEOS?) could performance be significantly improved?


 Thanks,

Brent Wood

 ___
 postgis-devel mailing list
 postgis-de...@lists.osgeo.org
 http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel

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

Re: [postgis-users] Cannot access offline value raster

[Bborie Park]

You'll want to make sure that the account running the PostgreSQL service
has access to C:\Users\user\Desktop\.

-bborie


On Tue, Oct 8, 2013 at 3:55 AM, irina.coviello
irina.covie...@imaa.cnr.itwrote:

 Dear all,

 this is my first post and I hope someone can help me.
 I cannot access to the information stored in raster mode out-db.
 Rasters are of the type : generic binary + envi hdr file.
 I've run the following command:

 raster2pgsql -R C:\Users\user\Desktop\1KM*_?? public.tmp | psql -U
 postgres -d gisdb_2.1

 so, I specify the absolute full path of the file.
 But for example, when I run the query:

 SELECT ST_Value( rast , 1, 937, 646)
 tmp from where rid = 1 ;

 or

 SELECT St_SummaryStats(rast)
 from tmp where rid=1;


 the error message is as follows:
 ERROR : rt_band_load_offline_data : Can not open raster offline:
 C:\Users\user\Desktop\1KM_2006_01_16_094500_ch31_T_TB


 If I run the query:

 select st_width (rast) , st_height (rast)
 tmp from where rid = 1 ;

 I get the right information, so I think the metadata are stored correctly.

 I haven't problem if the same raster file is stored in mode in-db.

 The work environment is:

  POSTGIS = 2.1.0 r11822  GEOS = 3.4.2 - CAPI - 1.8.2 r0  PROJ = Rel
 4.8.0 , March 6, 2012  = GDAL  GDAL 1.10.0 , released 24/04/2013  libxml
 = 2.7.8  LIBJSON = UNKNOWN  RASTER 

 and Windows 7 Professional 64 -bit and Postgres 9.2



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

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

Re: [postgis-users] About 2-dimensional arrays of postgresql

[Bborie Park]

Miguel,

Are you saying that the FOR loops are taking most of the time? What's the
time breakdown for each major step (ST_DumpValues, the FOR loop and
ST_SetValues).

The looping itself may not be the primary cause of your problem but rather
what is going on within the loop. What is going on within the loop?

-bborie


On Thu, Sep 26, 2013 at 11:25 PM, Miguel-Angel Manso-Callejo (UPM) 
m.ma...@upm.es wrote:

  Dear all,

 I'm writing a stored procedure (pgplSQL) that uses postgis functions  version
 2.1
 Dump the contents of an image on a two-dimensional array and then I update the
 cells according to some calculations.
 The process is slow, but after a while I discovered devote to the
 problem, or the slowness is due to operations that read and update the two
 -dimensional array.

 The code is roughly this:

 DECLARE
 ..
 *img** *double precision [] [];

 i integer j integer;

 BEGIN

 select ST_DumpValues (rast, 1, false) INTO *img** *from ..

 for i IN 1 .. 1000 LOOP
for j IN 1 .. 500 LOOP

*img** *[i] [j]: = ..;
END LOOP;
END LOOP;

select ST_SetValues (image, 1, 1, 1, *img*) INTO imagen2;

 END;

 Anyone know why the update of the values of the two-dimensional array is
 slow?, Poorly defined array for the type of operations performed?

 thank you very much
 Best regards.

 Miguel A. Manso

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

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

Re: [postgis-users] tile size

[Bborie Park]

Thanks for the graphs Duncan! My guess is that at the low tile sizes, the
individual round-trip time would be short but the volume of disk i/o is
what's coming into play. The opposite is true for large tile sizes where
the disk i/o volume is decreased but it takes longer for each round-trip.

Having said all that, I'm not willing to suggest that there is one range
appropriate for every user or deployment. Assuming you ran your scripts on
a typical desktop/laptop, I'd agree that the 200 range is an adequate
starting point. If on the other hand you're deploying with more hardware
(lots of RAM [128GB+], large number of disk spindles (DAS, FC SANs), SSDs
or RAM drives), I would expect that curve to look very different.

-bborie

I really need to build a performance test suite...


On Thu, Sep 19, 2013 at 4:55 PM, Duncan Golicher dgolic...@gmail.comwrote:

 Here is a simple speed test using R system.time to record the result after
 reloading the same raster with different tile sizes.

 https://dl.dropboxusercontent.com/u/2703650/SpeedTest.html

 Not had any time to anotate or explain the code but the test should be
 easily replicable (at least under Ubuntu). I provide a link to the data
 (which was used in the example on the geostat course). The code only runs
 without modification on Linux as I use system to send commands pasted
 toegether in R to the shell. Also needs unix odbc setting up.

 Note that the point on raster overlay can be beaten easily for speed by
 the extract function in the R raster package. However the polygon overlays
 are now very fast and compare well with any alternative way of getting the
 result. Using PLR to run  R functions within PostGIS is great if you want
 medians, quartiles etc or any other derived property.

 Duncan




 On Thu, Sep 19, 2013 at 4:32 PM, Duncan Golicher dgolic...@gmail.comwrote:

 Out of interest I  quickly checked whether the conclusions still hold for
 PostGIS2.2.
 The changes made in ST_Clip, and some other functions including st_value
 seem to have altered not just the absolute timing (much faster) but also
 the relative timing of operations as a function of  tile size.
 Point on raster overlays are now slower when tile size is small (50
 pixels), whereas previously there was an almost linear increase with tile
 size. Bborie may be able to explain why this change has occurred. I will
 try to add an update to the weblog at some point in order to clarify the
 sitation. It is GOOD NEWS as apparently there is now a single optimum tile
 size for both polygon and point overlays and this does seem to lie at
 around 200 - 300 pixels using the same example as the weblog, although I
 have not run enough tests to confirm this. I'll try to find time to confirm
 this.

 Duncan





 On Thu, Sep 19, 2013 at 10:36 AM, Pierre Racine 
 pierre.rac...@sbf.ulaval.ca wrote:

 You can also have a look at this article from Duncan Golicher if you are
 doing raster/vector analysis:


 http://duncanjg.wordpress.com/2012/10/30/tile-size-for-raster-vector-overlays-in-postgis/

  -Original Message-
  From: postgis-users-boun...@lists.osgeo.org [mailto:postgis-users-
  boun...@lists.osgeo.org] On Behalf Of Stephen Crawford
  Sent: Friday, September 13, 2013 12:34 PM
  To: postgis-users@lists.osgeo.org
  Subject: Re: [postgis-users] tile size
 
  OK, thanks. I will give that a try.
 
 
 
  On 9/13/2013 12:31 PM, Adam Eskreis wrote:
 
 
The most common tile size that I've seen in production is 256x256
 
 
On Fri, Sep 13, 2013 at 10:33 AM, Bborie Park
  dustym...@gmail.com wrote:
 
 
Steve,
 
There really isn't. What I do recommend is that if your
 raster
  data is not going to change over time (and you don't need to replicate
 the
  database), load them as out-db rasters. That way, you can easily
 change tile
  size within the database with ST_Tile.
 
 
 
-bborie
 
 
On Fri, Sep 13, 2013 at 6:06 AM, Stephen Crawford
  src...@psu.edu wrote:
 
 
Hi All,
 
Is there a rule of thumb for determining the
 best tile
  size when tiling a raster?
 
Thanks,
Steve
 
 
--
Stephen Crawford
Center for Environmental Informatics
The Pennsylvania State University
tel:814.865.9905
 
 
 
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-
  bin/mailman/listinfo/postgis-users
 
 
 
 
 
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-
  users

Re: [postgis-users] Problem with ST_MapAlgebraExpr

[Bborie Park]

You are not supposed to change the expression values rast1.val and
rast2.val to maxrast.val and minrast.val. The placeholders rast1.val and
rast2.val are to be used AS-IS.

As for the error, the error is correct. You're trying to pass the value
INTERSECTION for the pixeltype parameter...

http://postgis.net/docs/manual-2.0/RT_ST_MapAlgebraExpr2.html

-bborie


On Fri, Sep 13, 2013 at 4:27 AM, g.si...@utwente.nl wrote:

 Thanks Bborie Park,

 Can you please tell me what should I use for rast1.val and rast2.val. I am
 asking this because when I used '[maxrast.val] * [minrast.val]' I get
 erroras under:
 ERROR:  RASTER_mapAlgebra2: Invalid pixel type: INTERSECTION

 ** Error **

 ERROR: RASTER_mapAlgebra2: Invalid pixel type: INTERSECTION
 SQL state: XX000


 The function in the query looks like as below:

 st_MapAlgebraExpr(a.maxrast,b.minrast, '[maxrast.val] * [minrast.val]' ,
 'INTERSECTION', 'NULL', 'NULL', 'NULL') as rast


 thanks
 gaurav


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

Re: [postgis-users] tile size

[Bborie Park]

Steve,

There really isn't. What I do recommend is that if your raster data is not
going to change over time (and you don't need to replicate the database),
load them as out-db rasters. That way, you can easily change tile size
within the database with ST_Tile.

-bborie


On Fri, Sep 13, 2013 at 6:06 AM, Stephen Crawford src...@psu.edu wrote:

 Hi All,

 Is there a rule of thumb for determining the best tile size when tiling a
 raster?

 Thanks,
 Steve


 --
 Stephen Crawford
 Center for Environmental Informatics
 The Pennsylvania State University
 src...@psu.edu
 814.865.9905


 __**_
 postgis-users mailing list
 postgis-users@lists.osgeo.org
 http://lists.osgeo.org/cgi-**bin/mailman/listinfo/postgis-**usershttp://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

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

Re: [postgis-users] PostgreSQL 9.3 Error when trying to run Create Extension postgis

[Bborie Park]

You'll want to ask whoever created the CentOS package as it looks the
package is incomplete.

-bborie


On Fri, Sep 13, 2013 at 8:42 AM, DFE mimmopastic...@gmail.com wrote:

 As reported in [1] there is an error during the postgis2.1
 extension installation.
 [1]
 http://stackoverflow.com/questions/18768644/error-when-trying-to-run-create-extension-postgis

  Regards,
  Domenico



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

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

Re: [postgis-users] When is PostGIS 2.1.0 Windows binary coming out?

[Bborie Park]

http://www.postgresonline.com/journal/archives/319-PostGIS-2.1-windows-bundle.html


On Tue, Sep 10, 2013 at 1:07 PM, John Smith jayzee.sm...@gmail.com wrote:

 (sorry for the double-posting)

 guys,

 I got PostgreSQL 9.3 and looking for a compatible PostGIS Windows binary.

 From this announcement for PostGIS 2.0.4 (http://postgis.17.x6.nabble.
 com/PostGIS-2-0-4-Released-td5004152.html), I got PostGIS 2.0.3 (
 http://download.osgeo.org/postgis/windows/pg92/), but it's incompatible.
 So the best bet looks like postgis-pg93-binaries-2.1.0w64gcc48.zip from
 Winnie (http://winnie.postgis.net/download/windows/pg93/buildbot/ for
 PostgreSQL 9.3 rc1). Does anyone have any problems using that?

 When is PostGIS 2.1.0 Windows binary coming out (
 http://postgis.net/2013/08/17/postgis-2-1-0)?

 thanks, jzs


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

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

Re: [postgis-users] 'Spatial Extensions' missing from Stack Builder?

[Bborie Park]

http://www.postgresonline.com/journal/archives/319-PostGIS-2.1-windows-bundle.html

See Regina's blog post...


On Tue, Sep 10, 2013 at 1:02 PM, Stephen V. Mather 
s...@clevelandmetroparks.com wrote:

 I can't confirm, but given 9.3 dropped yesterday, I'd say it's quite
 likely it hasn't been bundled yet in stackbuilder.

   Stephen V. Mather
 GIS Manager
 (216) 635-3243 (Work)
 clevelandmetroparks.com




 
 From: postgis-users-boun...@lists.osgeo.org [
 postgis-users-boun...@lists.osgeo.org] on behalf of Matthew Baker [
 mattba...@gmail.com]
 Sent: Tuesday, September 10, 2013 4:00 PM
 To: postgis-users@lists.osgeo.org
 Subject: [postgis-users] 'Spatial Extensions' missing from Stack Builder?

 All,

 I'm trying to install PostGIS via the enterpriseDB / Stack Builder
 method, but it seems that the latest version of PostgreSQL and Stack
 Builder is missing that 'spatial extensions' option.

 Can anyone confirm?

 Thanks,

 -m

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


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

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

Re: [postgis-users] raster intersect with polygon resulting in false NULL value

[Bborie Park]

Her table constraints indicate that the NODATA value is properly set to
-.

-bborie


On Wed, Aug 21, 2013 at 12:51 PM, Tim Keitt tke...@utexas.edu wrote:

 Gdal is honoring the no-data value encoded in the raster file, but postgis
 is not. I see this problem in a lot of code out there.

 THK


 On Wed, Aug 21, 2013 at 2:26 PM, Hailey Eckstrand 
 haileyeckstr...@gmail.com wrote:

  Hello,
 I am trying perform aggregate statistics on a raster vector overlay with
 a polygon of 192 sub polys. Many of the resulting 192 mean values are
 correct.. however, I am encountering that one vector overlay mean is NULL
 which I know to be incorrect. The polygon and raster are in the same
 projection and when I look at them in QGIS they are overlaid correctly and
 I can see that the polygon is over top of ~30 raster cells that are all the
 value 3 (it is not over top of any NA values). One thing that is sort of
 fishy is that QGIS is reading the raster statistics and no data value
 incorrectly (values included below) compared to the base ascii raster
 gdalinfo which is included below.

 My query:

 select bid,(ST_SummaryStatsAgg(ST_Clip(rast,1, geom, true))).mean
 FROM aet_1,(select * from zben_allwshds where bid = 61) foo
 WHERE ST_Intersects(rast, geom)
 GROUP BY bid
 order by bid;
 NOTICE:  The two rasters provided have no intersection.  Returning no
 band raster
 CONTEXT:  PL/pgSQL function st_clip line 39 at assignment
 NOTICE:  Could not find raster band of index 1 when setting pixel value.
 Nodata value not set. Returning original raster
 CONTEXT:  PL/pgSQL function st_clip line 41 at assignment
 NOTICE:  Invalid band index (must use 1-based). Returning NULL
 CONTEXT:  PL/pgSQL function raster_summarystatsstate line 9 at
 assignment
 SQL function raster_summarystatsstate statement 1
  bid | mean
 -+--
   61 |


 Query which returns all the Summary Stats (order of stats is count | sum
 | mean | stddev | min | max)

 select bid,(ST_SummaryStatsAgg(ST_Clip(rast,1, geom, true)))
 FROM aet_1,(select * from zben_allwshds where bid = 61) foo
 WHERE ST_Intersects(rast, geom)
 GROUP BY bid
 order by bid;
 NOTICE:  The two rasters provided have no intersection.  Returning no
 band raster
 CONTEXT:  PL/pgSQL function st_clip line 39 at assignment
 NOTICE:  Could not find raster band of index 1 when setting pixel value.
 Nodata value not set. Returning original raster
 CONTEXT:  PL/pgSQL function st_clip line 41 at assignment
 NOTICE:  Invalid band index (must use 1-based). Returning NULL
 CONTEXT:  PL/pgSQL function raster_summarystatsstate line 9 at
 assignment
 SQL function raster_summarystatsstate statement 1
  bid | st_summarystatsagg
 -+
   61 | (,237,,,3,3)



 The raster was loaded into postgresql with the raster2pgsql tool:

 raster2pgsql -s 3005 -I -C -M aet_1.asc -F -t 100x100 public.aet_1 | psql
 -d NWBC

 gdalinfo of the loaded raster:

 gdalinfo aet_1.asc
 Driver: AAIGrid/Arc/Info ASCII Grid
 Files: aet_1.asc
aet_1.asc.aux.xml
 Size is 2331, 2895
  Coordinate System is `'
 Origin = (173461.9049546,1929781.91200011176)
 Pixel Size = (400.000,-400.000)
 Corner Coordinates:
 Upper Left  (  173461.904, 1929781.912)
 Lower Left  (  173461.904,  771781.912)
 Upper Right ( 1105861.904, 1929781.912)
 Lower Right ( 1105861.904,  771781.912)
 Center  (  639661.904, 1350781.912)
 Band 1 Block=2331x1 Type=Float32, ColorInterp=Undefined
   Min=0.000 Max=9.000
   Minimum=0.000, Maximum=9.000, Mean=1.955, StdDev=2.375
   NoData Value=-
   Metadata:
 STATISTICS_MAXIMUM=9
 STATISTICS_MEAN=1.9546895049572
 STATISTICS_MINIMUM=0
 STATISTICS_STDDEV=2.3747620319231

 Incorrect QGIS Raster Statistics:

 STATISTICS_MAXIMUM=1.2341209168929e+033

 STATISTICS_MEAN=5.9293249344197e+031

 STATISTICS_MINIMUM=-

 STATISTICS_STDDEV=2.4574481861697e+032

 NODATA VALUE= -32768

 PostGIS raster info:

   rid serial NOT NULL,
   rast raster,
   filename text,
   CONSTRAINT aet_1_pkey PRIMARY KEY (rid),
   CONSTRAINT enforce_height_rast CHECK (st_height(rast) = 100),
   CONSTRAINT enforce_max_extent_rast CHECK
 (st_coveredby(st_convexhull(rast),
 '010320BD0B010005001D5A643BAF2C0541FCA9F1D2EB7D27411D5A643BAF2C0541FED478E935723D41448B6CE7954B3141FED478E935723D41448B6CE7954B3141FCA9F1D2EB7D27411D5A643BAF2C0541FCA9F1D2EB7D2741'::geometry)),
   CONSTRAINT enforce_nodata_values_rast CHECK
 (_raster_constraint_nodata_values(rast)::numeric(16,10)[] =
 '{-}'::numeric(16,10)[]),
   CONSTRAINT enforce_num_bands_rast CHECK (st_numbands(rast) = 1),
   CONSTRAINT enforce_out_db_rast CHECK (_raster_constraint_out_db(rast) =
 '{f}'::boolean[]),
   CONSTRAINT enforce_pixel_types_rast CHECK
 (_raster_constraint_pixel_types(rast) = '{32BF}'::text[]),
   CONSTRAINT enforce_same_alignment_rast CHECK (st_samealignment(rast,
 

Re: [postgis-users] raster intersect with polygon resulting in false NULL value

[Bborie Park]

Hailey,

Can you run a query such as the following and post the results? I had to do
a double-take for the ST_SummaryStatsAgg() function as I'm not familiar
with that function.

WITH foo AS (
SELECT * FROM zben_allwshds WHERE bid = 61
)
SELECT
a.rid,
 ST_SummaryStats(ST_Clip(a.rast,1, f.geom, true))
FROM aet_1 a
JOIN foo f
ON ST_Intersects(a.rast, f.geom)

I'm suspecting that ST_SummaryStatsAgg() is having an issue where one
tile's summary stats has NULL values being combined with the aggregate
summary stats.

-bborie



On Wed, Aug 21, 2013 at 12:26 PM, Hailey Eckstrand 
haileyeckstr...@gmail.com wrote:

 Hello,
 I am trying perform aggregate statistics on a raster vector overlay with a
 polygon of 192 sub polys. Many of the resulting 192 mean values are
 correct.. however, I am encountering that one vector overlay mean is NULL
 which I know to be incorrect. The polygon and raster are in the same
 projection and when I look at them in QGIS they are overlaid correctly and
 I can see that the polygon is over top of ~30 raster cells that are all the
 value 3 (it is not over top of any NA values). One thing that is sort of
 fishy is that QGIS is reading the raster statistics and no data value
 incorrectly (values included below) compared to the base ascii raster
 gdalinfo which is included below.

 My query:

 select bid,(ST_SummaryStatsAgg(ST_Clip(rast,1, geom, true))).mean
 FROM aet_1,(select * from zben_allwshds where bid = 61) foo
 WHERE ST_Intersects(rast, geom)
 GROUP BY bid
 order by bid;
 NOTICE:  The two rasters provided have no intersection.  Returning no band
 raster
 CONTEXT:  PL/pgSQL function st_clip line 39 at assignment
 NOTICE:  Could not find raster band of index 1 when setting pixel value.
 Nodata value not set. Returning original raster
 CONTEXT:  PL/pgSQL function st_clip line 41 at assignment
 NOTICE:  Invalid band index (must use 1-based). Returning NULL
 CONTEXT:  PL/pgSQL function raster_summarystatsstate line 9 at assignment
 SQL function raster_summarystatsstate statement 1
  bid | mean
 -+--
   61 |


 Query which returns all the Summary Stats (order of stats is count | sum |
 mean | stddev | min | max)

 select bid,(ST_SummaryStatsAgg(ST_Clip(rast,1, geom, true)))
 FROM aet_1,(select * from zben_allwshds where bid = 61) foo
 WHERE ST_Intersects(rast, geom)
 GROUP BY bid
 order by bid;
 NOTICE:  The two rasters provided have no intersection.  Returning no band
 raster
 CONTEXT:  PL/pgSQL function st_clip line 39 at assignment
 NOTICE:  Could not find raster band of index 1 when setting pixel value.
 Nodata value not set. Returning original raster
 CONTEXT:  PL/pgSQL function st_clip line 41 at assignment
 NOTICE:  Invalid band index (must use 1-based). Returning NULL
 CONTEXT:  PL/pgSQL function raster_summarystatsstate line 9 at assignment
 SQL function raster_summarystatsstate statement 1
  bid | st_summarystatsagg
 -+
   61 | (,237,,,3,3)



 The raster was loaded into postgresql with the raster2pgsql tool:

 raster2pgsql -s 3005 -I -C -M aet_1.asc -F -t 100x100 public.aet_1 | psql
 -d NWBC

 gdalinfo of the loaded raster:

 gdalinfo aet_1.asc
 Driver: AAIGrid/Arc/Info ASCII Grid
 Files: aet_1.asc
aet_1.asc.aux.xml
 Size is 2331, 2895
  Coordinate System is `'
 Origin = (173461.9049546,1929781.91200011176)
 Pixel Size = (400.000,-400.000)
 Corner Coordinates:
 Upper Left  (  173461.904, 1929781.912)
 Lower Left  (  173461.904,  771781.912)
 Upper Right ( 1105861.904, 1929781.912)
 Lower Right ( 1105861.904,  771781.912)
 Center  (  639661.904, 1350781.912)
 Band 1 Block=2331x1 Type=Float32, ColorInterp=Undefined
   Min=0.000 Max=9.000
   Minimum=0.000, Maximum=9.000, Mean=1.955, StdDev=2.375
   NoData Value=-
   Metadata:
 STATISTICS_MAXIMUM=9
 STATISTICS_MEAN=1.9546895049572
 STATISTICS_MINIMUM=0
 STATISTICS_STDDEV=2.3747620319231

 Incorrect QGIS Raster Statistics:

 STATISTICS_MAXIMUM=1.2341209168929e+033

 STATISTICS_MEAN=5.9293249344197e+031

 STATISTICS_MINIMUM=-

 STATISTICS_STDDEV=2.4574481861697e+032

 NODATA VALUE= -32768

 PostGIS raster info:

   rid serial NOT NULL,
   rast raster,
   filename text,
   CONSTRAINT aet_1_pkey PRIMARY KEY (rid),
   CONSTRAINT enforce_height_rast CHECK (st_height(rast) = 100),
   CONSTRAINT enforce_max_extent_rast CHECK
 (st_coveredby(st_convexhull(rast),
 '010320BD0B010005001D5A643BAF2C0541FCA9F1D2EB7D27411D5A643BAF2C0541FED478E935723D41448B6CE7954B3141FED478E935723D41448B6CE7954B3141FCA9F1D2EB7D27411D5A643BAF2C0541FCA9F1D2EB7D2741'::geometry)),
   CONSTRAINT enforce_nodata_values_rast CHECK
 (_raster_constraint_nodata_values(rast)::numeric(16,10)[] =
 '{-}'::numeric(16,10)[]),
   CONSTRAINT enforce_num_bands_rast CHECK (st_numbands(rast) = 1),
   CONSTRAINT enforce_out_db_rast CHECK (_raster_constraint_out_db(rast) =
 '{f}'::boolean[]),
   CONSTRAINT enforce_pixel_types_rast CHECK
 

Re: [postgis-users] St_Union crashing

[Bborie Park]

David,

How much RAM does the server have? How many records are there in the
demelevation
table?

-bborie


On Wed, Jul 31, 2013 at 11:43 AM, David Rowley dgrowle...@gmail.com wrote:

 I'm experiencing a crash with PostgreSQL 9.2 on windows 64 bit.

 psql 1060 idle 2013-08-01 06:34:21 NZST 0 LOG:  statement: select
 st_union(rast) from demelevation limit 1;
  4124  2013-08-01 06:35:07 NZST 0 LOG:  server process (PID 1060) was
 terminated by exception 0xC005
  4124  2013-08-01 06:35:07 NZST 0 DETAIL:  Failed process was running:
 select st_union(rast) from demelevation limit 1;
  4124  2013-08-01 06:35:07 NZST 0 HINT:  See C include file ntstatus.h
 for a description of the hexadecimal value.
  4124  2013-08-01 06:35:07 NZST 0 LOG:  terminating any other active
 server processes
  3612  2013-08-01 06:35:07 NZST 0 WARNING:  terminating connection because
 of crash of another server process
  3612  2013-08-01 06:35:07 NZST 0 DETAIL:  The postmaster has commanded
 this server process to roll back the current transaction and exit, because
 another server process exited abnormally and possibly corrupted shared
 memory.
  3612  2013-08-01 06:35:07 NZST 0 HINT:  In a moment you should be able to
 reconnect to the database and repeat your command.
  4124  2013-08-01 06:35:07 NZST 0 LOG:  all server processes terminated;
 reinitializing


 test=# select version();
version
 -
  PostgreSQL 9.2.4, compiled by Visual C++ build 1600, 64-bit

 test=# select postgis_version();
 postgis_version
 ---
  2.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

 I can supply a full recreation script if required. In the meantime here is
 the table definition of demelevation


Table public.demelevation
   Column  |  Type   | Modifiers

 --+-+
  rid  | integer | not null default
 nextval('demelevation_rid_seq'::regclass)
  rast | raster  |
  filename | text|
 Indexes:
 demelevation_pkey PRIMARY KEY, btree (rid)
 demelevation_rast_gist gist (st_convexhull(rast))
 Check constraints:
 enforce_height_rast CHECK (st_height(rast) = 16)
 enforce_max_extent_rast CHECK (st_coveredby(st_convexhull(rast),
 '010320E61001000C0063C92F96FCDF64400E74DA40A70148C063C92F96FCBF64400E74DA4

 0A78147C063C92F96FCBF64408C25BF58F27F46C063C92F96FC7F65408C25BF58F2FF40C0039D36D069C065408C25BF58F2FF40C0039D36D0690066408C25BF58F27F41C0039D36D0696066408C25BF58

 F27F42C0039D36D0696066400E74DA40A70144C0039D36D0692066400E74DA40A70145C0039D36D0696065400E74DA40A78147C0039D36D0692065400E74DA40A70148C063C92F96FCDF64400E74DA40A
 70148C0'::geometry))
 enforce_nodata_values_rast CHECK
 (_raster_constraint_nodata_values(rast)::numeric(16,10)[] =
 '{-32768}'::numeric(16,10)[])
 enforce_num_bands_rast CHECK (st_numbands(rast) = 1)
 enforce_out_db_rast CHECK (_raster_constraint_out_db(rast) =
 '{f}'::boolean[])
 enforce_pixel_types_rast CHECK (_raster_constraint_pixel_types(rast)
 = '{16BSI}'::text[])
 enforce_same_alignment_rast CHECK (st_samealignment(rast,
 '014F1BE8B4814E4B3F4F1BE8B4814E4BBF63C92F96FC7F65408C25BF58F2FF40C000
 00E61001000100'::raster))
 enforce_scalex_rast CHECK (st_scalex(rast)::numeric(16,10) =
 0.000833::numeric(16,10))
 enforce_scaley_rast CHECK (st_scaley(rast)::numeric(16,10) =
 (-0.000833)::numeric(16,10))
 enforce_srid_rast CHECK (st_srid(rast) = 4326)
 enforce_width_rast CHECK (st_width(rast) = 16)

 Regards

 David


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


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

Re: [postgis-users] St_Union crashing

[Bborie Park]

Are you using the latest version of PostGIS 2.0? What's the minor version?

SELECT postgis_full_version()

The exception 0xC005 is indicative of a memory access error. The memory
required for the query isn't crazy ( ~ 200 MB).

select st_union(rast) from demelevation limit 1;

-bborie


On Wed, Jul 31, 2013 at 11:53 AM, David Rowley dgrowle...@gmail.com wrote:


 On Thu, Aug 1, 2013 at 6:47 AM, Bborie Park dustym...@gmail.com wrote:

 David,

 How much RAM does the server have? How many records are there in the 
 demelevation
 table?

  -bborie


 I'm testing on my laptop which has 8GB of RAM.

 test=# select count(*) from demelevation;
  count
 
  375440
 (1 row)

 test=# show work_mem;
  work_mem
 --
  16MB
 (1 row)


 test=# show shared_buffers;
  shared_buffers
 
  128MB
 (1 row)





 On Wed, Jul 31, 2013 at 11:43 AM, David Rowley dgrowle...@gmail.comwrote:

 I'm experiencing a crash with PostgreSQL 9.2 on windows 64 bit.

 psql 1060 idle 2013-08-01 06:34:21 NZST 0 LOG:  statement: select
 st_union(rast) from demelevation limit 1;
  4124  2013-08-01 06:35:07 NZST 0 LOG:  server process (PID 1060) was
 terminated by exception 0xC005
  4124  2013-08-01 06:35:07 NZST 0 DETAIL:  Failed process was running:
 select st_union(rast) from demelevation limit 1;
  4124  2013-08-01 06:35:07 NZST 0 HINT:  See C include file ntstatus.h
 for a description of the hexadecimal value.
  4124  2013-08-01 06:35:07 NZST 0 LOG:  terminating any other active
 server processes
  3612  2013-08-01 06:35:07 NZST 0 WARNING:  terminating connection
 because of crash of another server process
  3612  2013-08-01 06:35:07 NZST 0 DETAIL:  The postmaster has commanded
 this server process to roll back the current transaction and exit, because
 another server process exited abnormally and possibly corrupted shared
 memory.
  3612  2013-08-01 06:35:07 NZST 0 HINT:  In a moment you should be able
 to reconnect to the database and repeat your command.
  4124  2013-08-01 06:35:07 NZST 0 LOG:  all server processes terminated;
 reinitializing


 test=# select version();
version
 -
  PostgreSQL 9.2.4, compiled by Visual C++ build 1600, 64-bit

 test=# select postgis_version();
 postgis_version
 ---
  2.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

 I can supply a full recreation script if required. In the meantime here
 is the table definition of demelevation


Table public.demelevation
   Column  |  Type   | Modifiers

 --+-+
  rid  | integer | not null default
 nextval('demelevation_rid_seq'::regclass)
  rast | raster  |
  filename | text|
 Indexes:
 demelevation_pkey PRIMARY KEY, btree (rid)
 demelevation_rast_gist gist (st_convexhull(rast))
 Check constraints:
 enforce_height_rast CHECK (st_height(rast) = 16)
 enforce_max_extent_rast CHECK (st_coveredby(st_convexhull(rast),
 '010320E61001000C0063C92F96FCDF64400E74DA40A70148C063C92F96FCBF64400E74DA4

 0A78147C063C92F96FCBF64408C25BF58F27F46C063C92F96FC7F65408C25BF58F2FF40C0039D36D069C065408C25BF58F2FF40C0039D36D0690066408C25BF58F27F41C0039D36D0696066408C25BF58

 F27F42C0039D36D0696066400E74DA40A70144C0039D36D0692066400E74DA40A70145C0039D36D0696065400E74DA40A78147C0039D36D0692065400E74DA40A70148C063C92F96FCDF64400E74DA40A
 70148C0'::geometry))
 enforce_nodata_values_rast CHECK
 (_raster_constraint_nodata_values(rast)::numeric(16,10)[] =
 '{-32768}'::numeric(16,10)[])
 enforce_num_bands_rast CHECK (st_numbands(rast) = 1)
 enforce_out_db_rast CHECK (_raster_constraint_out_db(rast) =
 '{f}'::boolean[])
 enforce_pixel_types_rast CHECK
 (_raster_constraint_pixel_types(rast) = '{16BSI}'::text[])
 enforce_same_alignment_rast CHECK (st_samealignment(rast,
 '014F1BE8B4814E4B3F4F1BE8B4814E4BBF63C92F96FC7F65408C25BF58F2FF40C000
 00E61001000100'::raster))
 enforce_scalex_rast CHECK (st_scalex(rast)::numeric(16,10) =
 0.000833::numeric(16,10))
 enforce_scaley_rast CHECK (st_scaley(rast)::numeric(16,10) =
 (-0.000833)::numeric(16,10))
 enforce_srid_rast CHECK (st_srid(rast) = 4326)
 enforce_width_rast CHECK (st_width(rast) = 16)

 Regards

 David


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



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



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

Re: [postgis-users] St_Union crashing

[Bborie Park]

On Wed, Jul 31, 2013 at 12:09 PM, David Rowley dgrowle...@gmail.com wrote:

 On Thu, Aug 1, 2013 at 7:00 AM, Bborie Park dustym...@gmail.com wrote:

 Are you using the latest version of PostGIS 2.0? What's the minor version?

 SELECT postgis_full_version()


 Full version is:

  POSTGIS=2.0.3 r11132 GEOS=3.3.8-CAPI-1.7.8 PROJ=Rel. 4.8.0, 6 March
 2012 GDAL=GDAL 1.9.2, released 2012/10/08 LIBXML=2.7.8
 LIBJSON=UNKNOWN TOPOLOGY
 (topology procs from 2.0.1 r9979 need upgrade) RASTER


Are you able to try PostGIS 2.1? ST_Union() was completely rewritten to use
a different underlying engine and have dramatically better performance.

Are you able to point me to the raster data that you're using? That way,
someone can test on another Windows (and probably other) environment.

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

Re: [postgis-users] St_Union crashing

[Bborie Park]

 Sure. I've created a smallish recreation script with about 2000 16x16
 rasters which still crashes with me.

 http://www.davidrowley.co.uk/files/st_union_crash.sql

 Just send ctrl+C while the St_Union is in progress.

 I get:

 test=# SELECT ST_Union(rast) FROM rasttest;
 Cancel request sent
 server closed the connection unexpectedly
 This probably means the server terminated abnormally
 before or while processing the request.
 The connection to the server was lost. Attempting reset: Succeeded.


I just noticed that this is happening when you're aborting the ST_Union().
I can't replicate it in Linux no longer how long I let the statement run. I
wonder if this is an issue related to the issues with PostGIS compiled with
gcc  4.8.0 vs gcc = 4.8.0 for Windows.

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

Re: [postgis-users] St_Union crashing

[Bborie Park]

You can find experimental builds at...

http://winnie.postgis.net/download/windows/pg92/buildbot/

-bborie


On Wed, Jul 31, 2013 at 3:07 PM, David Rowley dgrowle...@gmail.com wrote:

 On Thu, Aug 1, 2013 at 10:01 AM, Bborie Park dustym...@gmail.com wrote:


 Sure. I've created a smallish recreation script with about 2000 16x16
 rasters which still crashes with me.

 http://www.davidrowley.co.uk/files/st_union_crash.sql

 Just send ctrl+C while the St_Union is in progress.

 I get:

 test=# SELECT ST_Union(rast) FROM rasttest;
 Cancel request sent
 server closed the connection unexpectedly
 This probably means the server terminated abnormally
 before or while processing the request.
 The connection to the server was lost. Attempting reset: Succeeded.


 I just noticed that this is happening when you're aborting the
 ST_Union(). I can't replicate it in Linux no longer how long I let the
 statement run. I wonder if this is an issue related to the issues with
 PostGIS compiled with gcc  4.8.0 vs gcc = 4.8.0 for Windows.


 If you have access to any other 64bit builds for windows, I'd be willing
 to find out for you.

 David


 -bborie

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



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


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

Re: [postgis-users] St_Union crashing

[Bborie Park]

On Wed, Jul 31, 2013 at 4:00 PM, David Rowley dgrowle...@gmail.com wrote:



 On Thu, Aug 1, 2013 at 10:10 AM, Bborie Park dustym...@gmail.com wrote:

 You can find experimental builds at...

 http://winnie.postgis.net/download/windows/pg92/buildbot/


 Thanks,

 OK, I've tried 2 more versions and I don't get any error with either of
 them:

  POSTGIS=2.1.0rc1 r11662 GEOS=3.4.0dev-CAPI-1.8.0 r0 PROJ=Rel. 4.8.0,
 6 March 2012 GDAL=GDAL 1.10.0, released 2013/04/24 LIBXML=2.7.8
 LIBJSON=UNKNOWN
  TOPOLOGY RASTER


  POSTGIS=2.0.4SVN r11708 GEOS=3.4.0dev-CAPI-1.8.0 r0 PROJ=Rel. 4.8.0,
 6 March 2012 GDAL=GDAL 1.10.0, released 2013/04/24 LIBXML=2.7.8
 LIBJSON=UNKNOWN
  (core procs from 2.0.3 r11132 need upgrade) TOPOLOGY (topology procs
 from 2.0.1 r9979 need upgrade) RASTER (raster procs from 2.0.3 r11132
 need upgrade)

 So I guess the problem is void since it's been fixed in the latest minor
 version. I've not looked at the postgis source, but I did notice that the
 Gdal version went from 1.9.2 to 1.10.0 since 2.0.3. Perhaps the problem
 could be there...

 David


No, the problem wouldn't have anything to do with GDAL as ST_Union doesn't
use it. I'm betting it has to do with how exceptions are handled...

http://trac.osgeo.org/postgis/ticket/2375

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

Re: [postgis-users] St_Union crashing

[Bborie Park]

 Did this change between 2.0.3 and 2.0.4?

 Though I guess there's not much that can be done if it is fixed in 2.0.4
 apart from push windows users harder to upgrade.

 I just emailed my hosting company to ask them if they would move me up to
 2.0.4, but I guess if that is the problem then it won't have an affect
 since it's not windows.


If the production environment is not on Windows, this problem never comes
up. This problem is only found in Windows.

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

Re: [postgis-users] Querying Multiple Rasters

[Bborie Park]

Jayson,

Can you share one of the queries? Also, what check constraints are you
using?

-bborie


On Tue, Jul 30, 2013 at 7:49 AM, Jayson Gallardo jaysontra...@gmail.comwrote:

 So, I used Explain on my SELECT statement, and whether
 constraint_exclusion is on or off, it seems to spit out the same number of
 rows in the query plan. Is there something I need to do for my table
 constraints so that it doesn't do a check on every table I have loaded?


 On Tue, Jul 30, 2013 at 9:20 AM, Jayson Gallardo 
 jaysontra...@gmail.comwrote:

 Quick follow up question to my situation... I recently loaded 3m
 resolution NED for Iowa. I have them loaded to one table per source tile,
 and have them inheriting from the parent table that the Arkansas NED is
 inheriting from. Ever since, however, my database seems to be running
 pretty slow. I've run a full vacuum on the data, and there are constraints
 on each table.

 How can I be sure that when I query the parent database that it's not
 querying every single table?


 On Tue, Jul 23, 2013 at 3:14 PM, Bborie Park dustym...@gmail.com wrote:

 I'm just glad to help. Feel free to post your experience, feedback,
 issues and/or wishes on the mailing-list.

 -bborie


 On Tue, Jul 23, 2013 at 1:10 PM, Jayson Gallardo jaysontra...@gmail.com
  wrote:

 Oh, okay. Yeah you're right about it taking time. I wrote a python
 script to generate the raster2pgsql call with the appropriate table name,
 so I can just let it run while I do other things. I really appreciate your
 help on this. I googled your name and I see you're a pretty busy person, so
 I'm glad you're taking the time to answer my questions.


 On Tue, Jul 23, 2013 at 3:05 PM, Bborie Park dustym...@gmail.comwrote:

 No. I'm suggesting it later as it does take time and separates
 operations. Get everything imported first and then add constraints.

 Having said that, you can do it all at once if so desired... just
 preference depending on volume of import data.

 -bborie


 On Tue, Jul 23, 2013 at 1:02 PM, Jayson Gallardo 
 jaysontra...@gmail.com wrote:

 Okay, is there a specific reason why? As your link states: 
 raster2pgsql loader uses this function to register raster tables.
 Are you saying I should specify constraints that will be similar across 
 all
 tables?


 On Tue, Jul 23, 2013 at 2:53 PM, Bborie Park dustym...@gmail.comwrote:

 I'd suggest adding constraints after the fact through SQL instead of
 letting raster2pgsql do it.

 http://www.postgis.net/docs/manual-2.0/RT_AddRasterConstraints.html

 -bborie


 On Tue, Jul 23, 2013 at 12:51 PM, Jayson Gallardo 
 jaysontra...@gmail.com wrote:

 So based on the link you provided, and what else I've gathered, I
 first create a parent table:
  CREATE TABLE dem_elevation
 (
   rid integer NOT NULL PRIMARY KEY
   rast raster,

 );
  Then I run raster2pgsql on all the downloaded elevation data,
 sending each input tile to its own table, ie. dem_elevation_n36w091. 
 Then
 alter table to inherit from parent:
 ALTER TABLE dem_elevation_n36w091 INHERIT dem_elevation;

 With raster2pgsql taking care of setting the constraints for each
 table. Now, I can just query the parent table dem_elevation to get 
 what I
 need?


 On Tue, Jul 23, 2013 at 2:33 PM, Bborie Park 
 dustym...@gmail.comwrote:

 I use the USGS NED 10 meter for California with one table for each
 input raster. In the partitioned table scheme, data tables inherit 
 from a
 template (parent) table. Queries run on the parent table access the
 inherited tables.

 -bborie


 On Tue, Jul 23, 2013 at 11:56 AM, Jayson Gallardo 
 jaysontra...@gmail.com wrote:

 Yes, it's usgs ned. And I initially went with one table for each
 input tile, but I didn't know how to join (or union) them together 
 for my
 query.
  On Jul 23, 2013 1:14 PM, Bborie Park dustym...@gmail.com
 wrote:

 Can you describe your elevation dataset? Is it USGS NED? At
 which resolution (10 meter, 3 meter?)?

 As for table partitioning...

 http://www.postgresql.org/docs/9.0/static/ddl-partitioning.html

 You'll probably partition spatially, though an easy solution is
 to have a table for each input raster file.

 -bborie





 On Tue, Jul 23, 2013 at 11:05 AM, Jayson Gallardo 
 jaysontra...@gmail.com wrote:

 Thanks for responding. Could you outline how I would go about
 doing a partitioned table structure? My only concern with tile 
 size is
 processing time. Most of my queries will involve areas of less 
 than 1 mi^2,
 and I would clip the data into that shape. I just don't know where 
 to
 start! There's not too many resources online/print dealing with 
 postgis
 rasters in detail.


 On Tue, Jul 23, 2013 at 12:57 PM, Bborie Park 
 dustym...@gmail.com wrote:

 You may not need to drop all the constraints when adding
 additional data to the table. You most likely will need to drop 
 is the
 maximum extent constraint. Assuming the input rasters have the 
 same scale,
 skew and SRID as that found in the table, you don't need to drop 
 those
 corresponding

Re: [postgis-users] Querying Multiple Rasters

[Bborie Park]

The quick and dirty approach is to have a query before that query that
transforms the WKT.

Something like SELECT ST_AsEWKT(ST_Transform(...))

-bborie


On Tue, Jul 30, 2013 at 9:35 AM, Jayson Gallardo jaysontra...@gmail.comwrote:

 I suppose I could do that in my script. How should I go about that? My
 process is as follows:

- User selects area of interest on a map (openlayers)
- User clicks submit, and python script is called with the WKT passed
as an argument
- Python script queries the database, which then outputs the raster
- Raster is processed through a library
- Processed raster is displayed as an overlay on the map



 On Tue, Jul 30, 2013 at 11:31 AM, Bborie Park dustym...@gmail.com wrote:

 Are you able to transform the wkt before passing it to the sql?
 Partitioning only works on constant values, not values that need
 processing, e.g. ST_Transform(ST_GeomFromText(\'%s\',%i),%i)).

 -bborie


 On Tue, Jul 30, 2013 at 9:25 AM, Jayson Gallardo 
 jaysontra...@gmail.comwrote:

 Here's the constraints:
   CONSTRAINT dem_elevation_n33w092_pkey PRIMARY KEY (rid ),
   CONSTRAINT enforce_height_rast CHECK (st_height(rast) = 100),
   CONSTRAINT enforce_max_extent_rast CHECK
 (st_coveredby(st_convexhull(rast), '*...truncated...*'::geometry)),
   CONSTRAINT enforce_num_bands_rast CHECK (st_numbands(rast) = 1),
   CONSTRAINT enforce_out_db_rast CHECK (_raster_constraint_out_db(rast)
 = '{f}'::boolean[]),
   CONSTRAINT enforce_pixel_types_rast CHECK
 (_raster_constraint_pixel_types(rast) = '{32BF}'::text[]),
   CONSTRAINT enforce_same_alignment_rast CHECK (st_samealignment(rast, '
 *...truncated...*'::raster)),
   CONSTRAINT enforce_scalex_rast CHECK (st_scalex(rast)::numeric(16,10)
 = 0.92592592593::numeric(16,10)),
   CONSTRAINT enforce_scaley_rast CHECK (st_scaley(rast)::numeric(16,10)
 = (-0.92592592593)::numeric(16,10)),
   CONSTRAINT enforce_srid_rast CHECK (st_srid(rast) = 4269),
   CONSTRAINT enforce_width_rast CHECK (st_width(rast) = 100)

 and my python script:

 wkt = sys.argv[1]  # Polygon shape in WKT format
 raster_type = 'GTiff'
 table_name = 'dem_elevation'
 map_srs = 900913
 table_srs = 4269
 sql_text = 'SELECT ST_AsGDALRaster(ST_CLIP(ST_Union(rast),
 ST_GeomFromText(\'%s\',%i)),\'%s\') FROM %s WHERE ST_Intersects(rast,
 ST_Transform(ST_GeomFromText(\'%s\',%i),%i))' % (wkt, map_srs,
 raster_type, table_name, wkt, map_srs, table_srs)



 On Tue, Jul 30, 2013 at 11:12 AM, Bborie Park dustym...@gmail.comwrote:

 Jayson,

 Can you share one of the queries? Also, what check constraints are you
 using?

 -bborie


 On Tue, Jul 30, 2013 at 7:49 AM, Jayson Gallardo 
 jaysontra...@gmail.com wrote:

 So, I used Explain on my SELECT statement, and whether
 constraint_exclusion is on or off, it seems to spit out the same number of
 rows in the query plan. Is there something I need to do for my table
 constraints so that it doesn't do a check on every table I have loaded?


 On Tue, Jul 30, 2013 at 9:20 AM, Jayson Gallardo 
 jaysontra...@gmail.com wrote:

 Quick follow up question to my situation... I recently loaded 3m
 resolution NED for Iowa. I have them loaded to one table per source tile,
 and have them inheriting from the parent table that the Arkansas NED is
 inheriting from. Ever since, however, my database seems to be running
 pretty slow. I've run a full vacuum on the data, and there are 
 constraints
 on each table.

 How can I be sure that when I query the parent database that it's not
 querying every single table?


 On Tue, Jul 23, 2013 at 3:14 PM, Bborie Park dustym...@gmail.comwrote:

 I'm just glad to help. Feel free to post your experience, feedback,
 issues and/or wishes on the mailing-list.

 -bborie


 On Tue, Jul 23, 2013 at 1:10 PM, Jayson Gallardo 
 jaysontra...@gmail.com wrote:

 Oh, okay. Yeah you're right about it taking time. I wrote a python
 script to generate the raster2pgsql call with the appropriate table 
 name,
 so I can just let it run while I do other things. I really appreciate 
 your
 help on this. I googled your name and I see you're a pretty busy 
 person, so
 I'm glad you're taking the time to answer my questions.


 On Tue, Jul 23, 2013 at 3:05 PM, Bborie Park 
 dustym...@gmail.comwrote:

 No. I'm suggesting it later as it does take time and separates
 operations. Get everything imported first and then add constraints.

 Having said that, you can do it all at once if so desired... just
 preference depending on volume of import data.

 -bborie


 On Tue, Jul 23, 2013 at 1:02 PM, Jayson Gallardo 
 jaysontra...@gmail.com wrote:

 Okay, is there a specific reason why? As your link states: 
 raster2pgsql loader uses this function to register raster
 tables. Are you saying I should specify constraints that will be 
 similar
 across all tables?


 On Tue, Jul 23, 2013 at 2:53 PM, Bborie Park dustym...@gmail.com
  wrote:

 I'd suggest adding constraints after the fact through SQL
 instead of letting raster2pgsql do it.


 http

Re: [postgis-users] Querying Multiple Rasters

[Bborie Park]

3m NED data doesn't exist for the continental US (at least from USGS). But
if you were to do so, you could consider a different scheme...

1. All NED files are stored as out-db rasters
2. Each table is for one state, though in some situations you may want more
than one table per state (e.g. Texas, California).

That should help you keep the # of partitions to a minimum and reduce the
size of each partition.

-bborie



On Tue, Jul 30, 2013 at 9:42 AM, Jayson Gallardo jaysontra...@gmail.comwrote:

 I was reading the page on partitioning, and the very last line says* 
 **Partitioning
 using these techniques will work well with up to perhaps a hundred
 partitions; don't try to use many thousands of partitions. *I'm already
 up to ~400 tables in this partitioning scheme just for Arkansas and Iowa...
 Is this a good idea? Would there be a better way to do the entire
 continental US?


 On Tue, Jul 30, 2013 at 11:39 AM, Bborie Park dustym...@gmail.com wrote:

 The quick and dirty approach is to have a query before that query that
 transforms the WKT.

 Something like SELECT ST_AsEWKT(ST_Transform(...))

 -bborie


 On Tue, Jul 30, 2013 at 9:35 AM, Jayson Gallardo 
 jaysontra...@gmail.comwrote:

 I suppose I could do that in my script. How should I go about that? My
 process is as follows:

- User selects area of interest on a map (openlayers)
- User clicks submit, and python script is called with the WKT
passed as an argument
- Python script queries the database, which then outputs the raster
- Raster is processed through a library
- Processed raster is displayed as an overlay on the map



 On Tue, Jul 30, 2013 at 11:31 AM, Bborie Park dustym...@gmail.comwrote:

 Are you able to transform the wkt before passing it to the sql?
 Partitioning only works on constant values, not values that need
 processing, e.g. ST_Transform(ST_GeomFromText(\'%s\',%i),%i)).

 -bborie


 On Tue, Jul 30, 2013 at 9:25 AM, Jayson Gallardo 
 jaysontra...@gmail.com wrote:

 Here's the constraints:
   CONSTRAINT dem_elevation_n33w092_pkey PRIMARY KEY (rid ),
   CONSTRAINT enforce_height_rast CHECK (st_height(rast) = 100),
   CONSTRAINT enforce_max_extent_rast CHECK
 (st_coveredby(st_convexhull(rast), '*...truncated...*'::geometry)),
   CONSTRAINT enforce_num_bands_rast CHECK (st_numbands(rast) = 1),
   CONSTRAINT enforce_out_db_rast CHECK
 (_raster_constraint_out_db(rast) = '{f}'::boolean[]),
   CONSTRAINT enforce_pixel_types_rast CHECK
 (_raster_constraint_pixel_types(rast) = '{32BF}'::text[]),
   CONSTRAINT enforce_same_alignment_rast CHECK (st_samealignment(rast,
 '*...truncated...*'::raster)),
   CONSTRAINT enforce_scalex_rast CHECK
 (st_scalex(rast)::numeric(16,10) = 0.92592592593::numeric(16,10)),
   CONSTRAINT enforce_scaley_rast CHECK
 (st_scaley(rast)::numeric(16,10) = (-0.92592592593)::numeric(16,10)),
   CONSTRAINT enforce_srid_rast CHECK (st_srid(rast) = 4269),
   CONSTRAINT enforce_width_rast CHECK (st_width(rast) = 100)

 and my python script:

 wkt = sys.argv[1]  # Polygon shape in WKT format
 raster_type = 'GTiff'
 table_name = 'dem_elevation'
 map_srs = 900913
 table_srs = 4269
 sql_text = 'SELECT ST_AsGDALRaster(ST_CLIP(ST_Union(rast),
 ST_GeomFromText(\'%s\',%i)),\'%s\') FROM %s WHERE
 ST_Intersects(rast, ST_Transform(ST_GeomFromText(\'%s\',%i),%i))' %
 (wkt, map_srs, raster_type, table_name, wkt, map_srs, table_srs)



 On Tue, Jul 30, 2013 at 11:12 AM, Bborie Park dustym...@gmail.comwrote:

 Jayson,

 Can you share one of the queries? Also, what check constraints are
 you using?

 -bborie


 On Tue, Jul 30, 2013 at 7:49 AM, Jayson Gallardo 
 jaysontra...@gmail.com wrote:

 So, I used Explain on my SELECT statement, and whether
 constraint_exclusion is on or off, it seems to spit out the same number 
 of
 rows in the query plan. Is there something I need to do for my table
 constraints so that it doesn't do a check on every table I have loaded?


 On Tue, Jul 30, 2013 at 9:20 AM, Jayson Gallardo 
 jaysontra...@gmail.com wrote:

 Quick follow up question to my situation... I recently loaded 3m
 resolution NED for Iowa. I have them loaded to one table per source 
 tile,
 and have them inheriting from the parent table that the Arkansas NED is
 inheriting from. Ever since, however, my database seems to be running
 pretty slow. I've run a full vacuum on the data, and there are 
 constraints
 on each table.

 How can I be sure that when I query the parent database that it's
 not querying every single table?


 On Tue, Jul 23, 2013 at 3:14 PM, Bborie Park 
 dustym...@gmail.comwrote:

 I'm just glad to help. Feel free to post your experience,
 feedback, issues and/or wishes on the mailing-list.

 -bborie


 On Tue, Jul 23, 2013 at 1:10 PM, Jayson Gallardo 
 jaysontra...@gmail.com wrote:

 Oh, okay. Yeah you're right about it taking time. I wrote a
 python script to generate the raster2pgsql call with the appropriate 
 table
 name, so I can just let it run while I do other things. I really

Re: [postgis-users] Querying Multiple Rasters

[Bborie Park]

That should work if you're querying against the subparent instead of the
parent. You'll need to test though...

-bborie


On Tue, Jul 30, 2013 at 9:59 AM, Jayson Gallardo jaysontra...@gmail.comwrote:

 What if I create subparents for each state, and set an extent constraint
 on each subparent? Would that help? Or would the query still check the
 constraint for each child of each subparent?


 On Tue, Jul 30, 2013 at 11:54 AM, Bborie Park dustym...@gmail.com wrote:

 3m NED data doesn't exist for the continental US (at least from USGS).
 But if you were to do so, you could consider a different scheme...

 1. All NED files are stored as out-db rasters
 2. Each table is for one state, though in some situations you may want
 more than one table per state (e.g. Texas, California).

 That should help you keep the # of partitions to a minimum and reduce the
 size of each partition.

 -bborie



 On Tue, Jul 30, 2013 at 9:42 AM, Jayson Gallardo 
 jaysontra...@gmail.comwrote:

 I was reading the page on partitioning, and the very last line says* 
 **Partitioning
 using these techniques will work well with up to perhaps a hundred
 partitions; don't try to use many thousands of partitions. *I'm
 already up to ~400 tables in this partitioning scheme just for Arkansas and
 Iowa... Is this a good idea? Would there be a better way to do the entire
 continental US?


 On Tue, Jul 30, 2013 at 11:39 AM, Bborie Park dustym...@gmail.comwrote:

 The quick and dirty approach is to have a query before that query that
 transforms the WKT.

 Something like SELECT ST_AsEWKT(ST_Transform(...))

 -bborie


 On Tue, Jul 30, 2013 at 9:35 AM, Jayson Gallardo 
 jaysontra...@gmail.com wrote:

 I suppose I could do that in my script. How should I go about that? My
 process is as follows:

- User selects area of interest on a map (openlayers)
- User clicks submit, and python script is called with the WKT
passed as an argument
- Python script queries the database, which then outputs the raster
- Raster is processed through a library
- Processed raster is displayed as an overlay on the map



 On Tue, Jul 30, 2013 at 11:31 AM, Bborie Park dustym...@gmail.comwrote:

 Are you able to transform the wkt before passing it to the sql?
 Partitioning only works on constant values, not values that need
 processing, e.g. ST_Transform(ST_GeomFromText(\'%s\',%i),%i)).

 -bborie


 On Tue, Jul 30, 2013 at 9:25 AM, Jayson Gallardo 
 jaysontra...@gmail.com wrote:

 Here's the constraints:
   CONSTRAINT dem_elevation_n33w092_pkey PRIMARY KEY (rid ),
   CONSTRAINT enforce_height_rast CHECK (st_height(rast) = 100),
   CONSTRAINT enforce_max_extent_rast CHECK
 (st_coveredby(st_convexhull(rast), '*...truncated...*'::geometry)),
   CONSTRAINT enforce_num_bands_rast CHECK (st_numbands(rast) = 1),
   CONSTRAINT enforce_out_db_rast CHECK
 (_raster_constraint_out_db(rast) = '{f}'::boolean[]),
   CONSTRAINT enforce_pixel_types_rast CHECK
 (_raster_constraint_pixel_types(rast) = '{32BF}'::text[]),
   CONSTRAINT enforce_same_alignment_rast CHECK
 (st_samealignment(rast, '*...truncated...*'::raster)),
   CONSTRAINT enforce_scalex_rast CHECK
 (st_scalex(rast)::numeric(16,10) = 0.92592592593::numeric(16,10)),
   CONSTRAINT enforce_scaley_rast CHECK
 (st_scaley(rast)::numeric(16,10) = 
 (-0.92592592593)::numeric(16,10)),
   CONSTRAINT enforce_srid_rast CHECK (st_srid(rast) = 4269),
   CONSTRAINT enforce_width_rast CHECK (st_width(rast) = 100)

 and my python script:

 wkt = sys.argv[1]  # Polygon shape in WKT format
 raster_type = 'GTiff'
 table_name = 'dem_elevation'
 map_srs = 900913
 table_srs = 4269
 sql_text = 'SELECT ST_AsGDALRaster(ST_CLIP(ST_Union(rast),
 ST_GeomFromText(\'%s\',%i)),\'%s\') FROM %s WHERE
 ST_Intersects(rast, ST_Transform(ST_GeomFromText(\'%s\',%i),%i))' %
 (wkt, map_srs, raster_type, table_name, wkt, map_srs, table_srs)



 On Tue, Jul 30, 2013 at 11:12 AM, Bborie Park 
 dustym...@gmail.comwrote:

 Jayson,

 Can you share one of the queries? Also, what check constraints are
 you using?

 -bborie


 On Tue, Jul 30, 2013 at 7:49 AM, Jayson Gallardo 
 jaysontra...@gmail.com wrote:

 So, I used Explain on my SELECT statement, and whether
 constraint_exclusion is on or off, it seems to spit out the same 
 number of
 rows in the query plan. Is there something I need to do for my table
 constraints so that it doesn't do a check on every table I have 
 loaded?


 On Tue, Jul 30, 2013 at 9:20 AM, Jayson Gallardo 
 jaysontra...@gmail.com wrote:

 Quick follow up question to my situation... I recently loaded 3m
 resolution NED for Iowa. I have them loaded to one table per source 
 tile,
 and have them inheriting from the parent table that the Arkansas NED 
 is
 inheriting from. Ever since, however, my database seems to be running
 pretty slow. I've run a full vacuum on the data, and there are 
 constraints
 on each table.

 How can I be sure that when I query the parent database that it's
 not querying every single table

Re: [postgis-users] Querying Multiple Rasters

[Bborie Park]

You may not need to drop all the constraints when adding additional data to
the table. You most likely will need to drop is the maximum extent
constraint. Assuming the input rasters have the same scale, skew and SRID
as that found in the table, you don't need to drop those corresponding
constraints.

If you're going to do the continental US at a fine resolution (e.g. 1
meter), you do NOT want to put all the rasters in one table. You'll want to
use a partitioned table structure and should consider a bigger tile size
(depending on your hardware).

-bborie


On Tue, Jul 23, 2013 at 10:43 AM, Jayson Gallardo jaysontra...@gmail.comwrote:

 I've looked and looked, but I have not been able to find an answer to my
 question. I have downloaded elevation data for the state of Arkansas (in
 the form of multiple tiles), and used raster2pgsql to upload it into a
 single table:

 raster2pgsql -I -C -e -F -t 50x50 -l 2,4 n*/grdn* public.dem_elevation |
 psql -U postgres -d testdb -h localhost -p 5432

 I did this because I didn't know how to pull the data if they were in
 separate tables. Now, however I would like to add elevation data for other
 areas. I tried to just add it to the current table, but that required
 dropping the constraints which for such a huge amount of data seems to take
 a long time (I let it run for 24+ hours and it didn't finish). So, my
 question is, if I load all my rasters as individual tables, how could I run
 something similar to this query on them all (from a python script):

 SELECT ST_AsGDALRaster(ST_CLIP(ST_Union(rast),
 ST_GeomFromText(WKT,900913)),'GTiff') FROM dem_elevation WHERE
 ST_Intersects(rast, ST_Transform(ST_GeomFromText(WKT,900913),4269))

 My goal, if it's not obvious, is to clip elevation data and export it to a
 GTiff format and perform some operations on that raster data. Eventually, I
 would like to put the whole continental US elevation data into my database,
 so I need to be able to do so, while still being able to query them based
 on an area of interest the user selects from a map. I started working with
 PostGIS and Mapserver last month, so please forgive my ignorance on such
 topics. Thanks in advance

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


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

Re: [postgis-users] Status of Postgis-related rpms

[Bborie Park]

Are you using the PostgreSQL Yum repo?

http://yum.postgresql.org/9.2/fedora/fedora-18-x86_64/repoview/
http://yum.postgresql.org/9.2/fedora/fedora-18-i386/repoview/

The PostGIS project itself does not release any packages (except for
Windows) so you'd have to ask the maintainer of the repo/package.

-bborie



On Tue, Jul 23, 2013 at 12:03 PM, Max Pyziur p...@brama.com wrote:

 On Mon, 22 Jul 2013, Bborie Park wrote:

  There shouldn't be any issues between PostgreSQL 9.2 and PostGIS 2.0. If
 there are, they'd be Fedora derived problems...


 Is there any chance that this will be corrected for Fedora 18?
 Technically, Fedora 18 won't go EOL until until one month after the release
 of Fedora 20; it would be nice to have these F18 packages available
 (PostGIS 2.0 and other related packages such as gdal, etc.)


  -bborie


 Max Pyziur
 p...@brama.com


 On Mon, Jul 22, 2013 at 11:48 AM, Max Pyziur p...@brama.com wrote:

   Greetings,


   With the release of Fedora 18 there were interoperability issues
 with PostGIS and Postgresql. Fedora 18 was released with PostgreSQL 9.2.x
 and PostGIS 1.5.x. Given
   the mutual release schedule, only releases of PostGIS 2.0.x and
 higher can work with PostgreSQL 9.2.x.

   I see that Fedora 19 ships with PostGIS 2.0.3 and PostgreSQL 9.2.4.
 Do these packages work together correctly, or are there still issues?

   Much thanks,

   Max Pyziur
   p...@brama.com


   On Mon, 13 May 2013, Max Pyziur wrote:


 Or should we wait for Fedora 19?


 Max Pyziur
 p...@brama.com


   __**_
   postgis-users mailing list
   postgis-users@lists.osgeo.org
   
 http://lists.osgeo.org/cgi-**bin/mailman/listinfo/postgis-**usershttp://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users




  __**_
 postgis-users mailing list
 postgis-users@lists.osgeo.org
 http://lists.osgeo.org/cgi-**bin/mailman/listinfo/postgis-**usershttp://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

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

Re: [postgis-users] Querying Multiple Rasters

[Bborie Park]

No. I'm suggesting it later as it does take time and separates operations.
Get everything imported first and then add constraints.

Having said that, you can do it all at once if so desired... just
preference depending on volume of import data.

-bborie


On Tue, Jul 23, 2013 at 1:02 PM, Jayson Gallardo jaysontra...@gmail.comwrote:

 Okay, is there a specific reason why? As your link states: raster2pgsql 
 loader
 uses this function to register raster tables. Are you saying I should
 specify constraints that will be similar across all tables?


 On Tue, Jul 23, 2013 at 2:53 PM, Bborie Park dustym...@gmail.com wrote:

 I'd suggest adding constraints after the fact through SQL instead of
 letting raster2pgsql do it.

 http://www.postgis.net/docs/manual-2.0/RT_AddRasterConstraints.html

 -bborie


 On Tue, Jul 23, 2013 at 12:51 PM, Jayson Gallardo jaysontra...@gmail.com
  wrote:

 So based on the link you provided, and what else I've gathered, I first
 create a parent table:
  CREATE TABLE dem_elevation
 (
   rid integer NOT NULL PRIMARY KEY
   rast raster,

 );
  Then I run raster2pgsql on all the downloaded elevation data, sending
 each input tile to its own table, ie. dem_elevation_n36w091. Then alter
 table to inherit from parent:
 ALTER TABLE dem_elevation_n36w091 INHERIT dem_elevation;

 With raster2pgsql taking care of setting the constraints for each table.
 Now, I can just query the parent table dem_elevation to get what I need?


 On Tue, Jul 23, 2013 at 2:33 PM, Bborie Park dustym...@gmail.comwrote:

 I use the USGS NED 10 meter for California with one table for each
 input raster. In the partitioned table scheme, data tables inherit from a
 template (parent) table. Queries run on the parent table access the
 inherited tables.

 -bborie


 On Tue, Jul 23, 2013 at 11:56 AM, Jayson Gallardo 
 jaysontra...@gmail.com wrote:

 Yes, it's usgs ned. And I initially went with one table for each input
 tile, but I didn't know how to join (or union) them together for my query.
  On Jul 23, 2013 1:14 PM, Bborie Park dustym...@gmail.com wrote:

 Can you describe your elevation dataset? Is it USGS NED? At which
 resolution (10 meter, 3 meter?)?

 As for table partitioning...

 http://www.postgresql.org/docs/9.0/static/ddl-partitioning.html

 You'll probably partition spatially, though an easy solution is to
 have a table for each input raster file.

 -bborie





 On Tue, Jul 23, 2013 at 11:05 AM, Jayson Gallardo 
 jaysontra...@gmail.com wrote:

 Thanks for responding. Could you outline how I would go about doing
 a partitioned table structure? My only concern with tile size is 
 processing
 time. Most of my queries will involve areas of less than 1 mi^2, and I
 would clip the data into that shape. I just don't know where to start!
 There's not too many resources online/print dealing with postgis 
 rasters in
 detail.


 On Tue, Jul 23, 2013 at 12:57 PM, Bborie Park 
 dustym...@gmail.comwrote:

 You may not need to drop all the constraints when adding additional
 data to the table. You most likely will need to drop is the maximum 
 extent
 constraint. Assuming the input rasters have the same scale, skew and 
 SRID
 as that found in the table, you don't need to drop those corresponding
 constraints.

 If you're going to do the continental US at a fine resolution (e.g.
 1 meter), you do NOT want to put all the rasters in one table. You'll 
 want
 to use a partitioned table structure and should consider a bigger tile 
 size
 (depending on your hardware).

 -bborie


 On Tue, Jul 23, 2013 at 10:43 AM, Jayson Gallardo 
 jaysontra...@gmail.com wrote:

 I've looked and looked, but I have not been able to find an answer
 to my question. I have downloaded elevation data for the state of 
 Arkansas
 (in the form of multiple tiles), and used raster2pgsql to upload it 
 into a
 single table:

 raster2pgsql -I -C -e -F -t 50x50 -l 2,4 n*/grdn*
 public.dem_elevation | psql -U postgres -d testdb -h localhost -p 5432

 I did this because I didn't know how to pull the data if they were
 in separate tables. Now, however I would like to add elevation data 
 for
 other areas. I tried to just add it to the current table, but that 
 required
 dropping the constraints which for such a huge amount of data seems 
 to take
 a long time (I let it run for 24+ hours and it didn't finish). So, my
 question is, if I load all my rasters as individual tables, how could 
 I run
 something similar to this query on them all (from a python script):

 SELECT ST_AsGDALRaster(ST_CLIP(ST_Union(rast),
 ST_GeomFromText(WKT,900913)),'GTiff') FROM dem_elevation WHERE
 ST_Intersects(rast, ST_Transform(ST_GeomFromText(WKT,900913),4269))

 My goal, if it's not obvious, is to clip elevation data and export
 it to a GTiff format and perform some operations on that raster data.
 Eventually, I would like to put the whole continental US elevation 
 data
 into my database, so I need to be able to do so, while still being 
 able to
 query them based on an area

Re: [postgis-users] Querying Multiple Rasters

[Bborie Park]

I'm just glad to help. Feel free to post your experience, feedback, issues
and/or wishes on the mailing-list.

-bborie


On Tue, Jul 23, 2013 at 1:10 PM, Jayson Gallardo jaysontra...@gmail.comwrote:

 Oh, okay. Yeah you're right about it taking time. I wrote a python script
 to generate the raster2pgsql call with the appropriate table name, so I can
 just let it run while I do other things. I really appreciate your help on
 this. I googled your name and I see you're a pretty busy person, so I'm
 glad you're taking the time to answer my questions.


 On Tue, Jul 23, 2013 at 3:05 PM, Bborie Park dustym...@gmail.com wrote:

 No. I'm suggesting it later as it does take time and separates
 operations. Get everything imported first and then add constraints.

 Having said that, you can do it all at once if so desired... just
 preference depending on volume of import data.

 -bborie


 On Tue, Jul 23, 2013 at 1:02 PM, Jayson Gallardo 
 jaysontra...@gmail.comwrote:

 Okay, is there a specific reason why? As your link states: raster2pgsql 
 loader
 uses this function to register raster tables. Are you saying I should
 specify constraints that will be similar across all tables?


 On Tue, Jul 23, 2013 at 2:53 PM, Bborie Park dustym...@gmail.comwrote:

 I'd suggest adding constraints after the fact through SQL instead of
 letting raster2pgsql do it.

 http://www.postgis.net/docs/manual-2.0/RT_AddRasterConstraints.html

 -bborie


 On Tue, Jul 23, 2013 at 12:51 PM, Jayson Gallardo 
 jaysontra...@gmail.com wrote:

 So based on the link you provided, and what else I've gathered, I
 first create a parent table:
  CREATE TABLE dem_elevation
 (
   rid integer NOT NULL PRIMARY KEY
   rast raster,

 );
  Then I run raster2pgsql on all the downloaded elevation data, sending
 each input tile to its own table, ie. dem_elevation_n36w091. Then alter
 table to inherit from parent:
 ALTER TABLE dem_elevation_n36w091 INHERIT dem_elevation;

 With raster2pgsql taking care of setting the constraints for each
 table. Now, I can just query the parent table dem_elevation to get what I
 need?


 On Tue, Jul 23, 2013 at 2:33 PM, Bborie Park dustym...@gmail.comwrote:

 I use the USGS NED 10 meter for California with one table for each
 input raster. In the partitioned table scheme, data tables inherit from a
 template (parent) table. Queries run on the parent table access the
 inherited tables.

 -bborie


 On Tue, Jul 23, 2013 at 11:56 AM, Jayson Gallardo 
 jaysontra...@gmail.com wrote:

 Yes, it's usgs ned. And I initially went with one table for each
 input tile, but I didn't know how to join (or union) them together for 
 my
 query.
  On Jul 23, 2013 1:14 PM, Bborie Park dustym...@gmail.com wrote:

 Can you describe your elevation dataset? Is it USGS NED? At which
 resolution (10 meter, 3 meter?)?

 As for table partitioning...

 http://www.postgresql.org/docs/9.0/static/ddl-partitioning.html

 You'll probably partition spatially, though an easy solution is to
 have a table for each input raster file.

 -bborie





 On Tue, Jul 23, 2013 at 11:05 AM, Jayson Gallardo 
 jaysontra...@gmail.com wrote:

 Thanks for responding. Could you outline how I would go about
 doing a partitioned table structure? My only concern with tile size is
 processing time. Most of my queries will involve areas of less than 1 
 mi^2,
 and I would clip the data into that shape. I just don't know where to
 start! There's not too many resources online/print dealing with 
 postgis
 rasters in detail.


 On Tue, Jul 23, 2013 at 12:57 PM, Bborie Park dustym...@gmail.com
  wrote:

 You may not need to drop all the constraints when adding
 additional data to the table. You most likely will need to drop is 
 the
 maximum extent constraint. Assuming the input rasters have the same 
 scale,
 skew and SRID as that found in the table, you don't need to drop 
 those
 corresponding constraints.

 If you're going to do the continental US at a fine resolution
 (e.g. 1 meter), you do NOT want to put all the rasters in one table. 
 You'll
 want to use a partitioned table structure and should consider a 
 bigger tile
 size (depending on your hardware).

 -bborie


 On Tue, Jul 23, 2013 at 10:43 AM, Jayson Gallardo 
 jaysontra...@gmail.com wrote:

 I've looked and looked, but I have not been able to find an
 answer to my question. I have downloaded elevation data for the 
 state of
 Arkansas (in the form of multiple tiles), and used raster2pgsql to 
 upload
 it into a single table:

 raster2pgsql -I -C -e -F -t 50x50 -l 2,4 n*/grdn*
 public.dem_elevation | psql -U postgres -d testdb -h localhost -p 
 5432

 I did this because I didn't know how to pull the data if they
 were in separate tables. Now, however I would like to add elevation 
 data
 for other areas. I tried to just add it to the current table, but 
 that
 required dropping the constraints which for such a huge amount of 
 data
 seems to take a long time (I let it run for 24+ hours and it didn't
 finish). So, my question

Re: [postgis-users] Status of Postgis-related rpms

[Bborie Park]

There shouldn't be any issues between PostgreSQL 9.2 and PostGIS 2.0. If
there are, they'd be Fedora derived problems...

-bborie


On Mon, Jul 22, 2013 at 11:48 AM, Max Pyziur p...@brama.com wrote:


 Greetings,


 With the release of Fedora 18 there were interoperability issues with
 PostGIS and Postgresql. Fedora 18 was released with PostgreSQL 9.2.x and
 PostGIS 1.5.x. Given the mutual release schedule, only releases of PostGIS
 2.0.x and higher can work with PostgreSQL 9.2.x.

 I see that Fedora 19 ships with PostGIS 2.0.3 and PostgreSQL 9.2.4. Do
 these packages work together correctly, or are there still issues?

 Much thanks,

 Max Pyziur
 p...@brama.com


 On Mon, 13 May 2013, Max Pyziur wrote:


 Or should we wait for Fedora 19?


 Max Pyziur
 p...@brama.com


  __**_
 postgis-users mailing list
 postgis-users@lists.osgeo.org
 http://lists.osgeo.org/cgi-**bin/mailman/listinfo/postgis-**usershttp://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

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

Re: [postgis-users] Creating a multiband raster from 3 single bandrasters

[Bborie Park]

Oops! Use Regina's link instead. I need to flush out my browser's history...

-bborie


On Sun, Jul 7, 2013 at 10:50 PM, Paragon Corporation l...@pcorp.us wrote:

 **
 cough cough: New docs

 http://postgis.net/docs/manual-dev/RT_ST_AddBand.html

 -Regina


  --
 *From:* postgis-users-boun...@lists.osgeo.org [mailto:
 postgis-users-boun...@lists.osgeo.org] *On Behalf Of *Bborie Park
 *Sent:* Monday, July 08, 2013 12:38 AM
 *To:* PostGIS Users Discussion
 *Subject:* Re: [postgis-users] Creating a multiband raster from 3 single
 bandrasters

  Check out the last example (Multi-band versions) of ST_AddBand().

 http://postgis.refractions.net/documentation/manual-svn/RT_ST_AddBand.html

 -bborie


 On Sun, Jul 7, 2013 at 8:43 PM, Mark Wynter m...@dimensionaledge.comwrote:

 I'm hoping someone can suggest a way of efficiently combining 3
 individual (but concordant) single band rasters into single raster having 3
 bands?  We're working with PostgreSQL9.1, PostGIS2.1Beta.

 The starting point is a single tiled raster with 3 bands representing RGB
 values.

 Let's say we perform some color smoothing on each of the RGB bands using
 mapalgebra.

 CREATE TABLE resample_myrastertable_b1 AS
 SELECT rid, st_mapalgebrafctngb(rast, 1, '8BUI', 5, 5,
 'st_mean4ma(float[][],text,text[])'::regprocedure, 'ignore', NULL) as rast
 FROM original_rastertable;

 CREATE TABLE resample_myrastertable_b2 AS
 SELECT rid, st_mapalgebrafctngb(rast, 2, '8BUI', 5, 5,
 'st_mean4ma(float[][],text,text[])'::regprocedure, 'ignore', NULL) as rast
 FROM original_rastertable;

 CREATE TABLE resample_myrastertable_b3 AS
 SELECT rid, st_mapalgebrafctngb(rast, 3, '8BUI', 5, 5,
 'st_mean4ma(float[][],text,text[])'::regprocedure, 'ignore', NULL) as rast
 FROM original_rastertable;

 This gives us 3 single-band results rasters.

 How do we now join the individual smoothed RGB results rasters into a
 single multiband results raster?   We'd also like to add a 4th band to
 the results raster based on say a calculation which references the
 smoothed values in bands 1-3.

 I'm getting reasonably comfortable writing MapAlgebra callback
 expressions.   Its more a case of how do we efficiently handle table
 operations (e.g. appending single band raster outputs) each time we perform
 some MapAlgebra?

 Any suggestions and or a simple worked example would be most appreciated.

 Many thanks

 Mark




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



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


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

Re: [postgis-users] OpenBSd installation problem

[Bborie Park]

Marcelo,

Those missing symbols are all from PostgreSQL. You'll want to make
sure the PostgreSQL libs and dev files are provided.

-bborie

On Mon, Jul 1, 2013 at 1:17 PM, Marcelo Bacha dro...@gmail.com wrote:
 Hi,

 This is my first posting, I´m new to Postgis. I´ve seen similar problems
 related in lists all around, but this one, I couldn´t manage to solve.

 I have an OpenBSD 5.3 server, with PostgreSQL 9.2.4 on it, which always
 worked fine. I have to transfer some geospatial databases from a Windows
 server, so I downloaded Postgis from the source (I needed the latest
 versions), configured, compiled  installed it.
 Everything seems to be fine, but when I try to create the postgis extension
 (with raster support) on psql:

   postgres=# CREATE EXTENSION postgis;
   ERROR:  could not load library /usr/local/pgsql/lib/rtpostgis-2.2.so:
 dlopen (/usr/local/pgsql/lib/rtpostgis-2.2.so) failed: Cannot load specified
 object

 The paths seems all to be OK:

   # ls -l /usr/local/pgsql/lib/*post*
   -rwxr-xr-x  1 root  wheel  1276039 Jul  1 16:50
 /usr/local/pgsql/lib/postgis-2.2.so
   -rwxr-xr-x  1 root  wheel  1208861 Jul  1 16:50
 /usr/local/pgsql/lib/rtpostgis-2.2.so

 (I know it´s the development, and not the stable release, but the results
 are the same with all other releases.)

 When I try to preload the rtpostgis shared object setting the LD_PRELOAD
 environment variable, I get this on psql:

   $ psql
   psql:/usr/local/pgsql/lib/rtpostgis-2.2.so: undefined symbol
 'CurrentMemoryContext'
   psql:/usr/local/pgsql/lib/rtpostgis-2.2.so: undefined symbol
 'SPI_tuptable'
   psql:/usr/local/pgsql/lib/rtpostgis-2.2.so: undefined symbol 'SPI_result'
   psql:/usr/local/pgsql/lib/rtpostgis-2.2.so: undefined symbol
 'InterruptPending'
   psql:/usr/local/pgsql/lib/rtpostgis-2.2.so: undefined symbol
 'SPI_processed'
   psql (9.2.4)

 Didn´t find any further useful info setting LD_DEBUG (I may list the output
 if necessary, anyway).

 Any hint on how to solve this? I´m running out of ideas.

 Thanks in advance,
 Marcelo


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

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

Re: [postgis-users] PostGIS Raster ST_Value ST_SetValue performance

[Bborie Park]

Hey Miguel,

If you're on PostGIS 2.0, there aren't faster alternatives for
ST_SetValue() and ST_Value(). PostGIS 2.1 (hopefully released in the
next several weeks) brings ST_SetValues() and ST_DumpValues().

As for your specific implementation, You could consider the use of
ST_MapAlgebraFct() where the callback function counts the points
included in the cell.

-bborie

On Fri, Jun 28, 2013 at 2:02 AM, Miguel-Angel Manso-Callejo (UPM)
m.ma...@upm.es wrote:
 Good morning,

 It's my first comment to the email-list.

 I'm developing a PLSQL-procedure that generate a raster band counting points
 of a geometry table/layer that are included in a cell. This can be seen as a
 simple clustering algorithm.

 To do it, i'm using a cursor and determining the row and column of the pixel
 do an increment operation over pixel value. This is a ST_Value + ST_SetValue
 operation foreach point.
 This is very slow.

 In Oracle, this is slow too, but i can take an alternative way. Using a
 Array with binary index simulate the image, and latter when all the point
 has been mapped over the matrix transform this to a cellblob and later
 insert it on table that store image.

 Have any one in the list any similar experience?
 Can any give me a new orientation?


 Best regards,
 Miguel A. Manso

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

Re: [postgis-users] ST_AsPNG is problematic in Java

[Bborie Park]

Ahmet,

Can you try one of the example code found in the docs?

http://www.postgis.net/docs/manual-dev/using_raster_dataman.html#RT_Raster_Applications

By testing out one of the examples, you should be able to tell if the
problem is in the database or in your java code.

-bborie

On Mon, Jun 17, 2013 at 6:04 AM, Ahmet Temiz ahmettemi...@gmail.com wrote:
 hello

 postgresql (9.2.2)

 postgresql-jdbc 9.2

 here is the result of SELECT postgis_full_version();


 postgis_full_version
 
  POSTGIS=2.1.0SVN r10952 GEOS=3.3.3-CAPI-1.7.4 PROJ=Rel. 4.8.0, 6 March
 2012 GDAL=GDAL 1.9.2, released 2012/10/08 LIBXML=2.8.0 RASTER


 thank you


 On Mon, Jun 17, 2013 at 3:45 PM, Paragon Corporation l...@pcorp.us wrote:

 Which JDBC driver are you using and which PostgreSQL version.  You might
 be affected by the escape/hex change in 9.0

 detailed here:
 http://postgis.net/docs/manual-2.0/PostGIS_FAQ.html#id338804

 
 From: postgis-users-boun...@lists.osgeo.org
 [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of Ahmet Temiz
 Sent: Monday, June 17, 2013 8:32 AM
 To: PostGIS Users Discussion
 Subject: [postgis-users] ST_AsPNG is problematic in Java


 hello

 I am dealing with java-jpa2-postgis(raster)

 I tried to get an image from postgis(raster) as:

 List list_rast = session.createSQLQuery(SELECT ST_AsPNG(rast) As rastpng
 FROM geoslp5 where rid=184 ;).list();

 and converting object to byte[]
 and
 with usual java process
 ByteArrayOutputStream bos = new ByteArrayOutputStream();
 ... //
 bytes = bos.toByteArray ();


 But resulting image file(png) is defective ?

 What may the probable problems ?

 Do you have any recommendations ?

 regards

 --
 Ahmet Temiz
 Jeoloji Müh.
 Afet ve Acil Durum Yönetimi Başkanlığı
 Planlama ve Zarar Azaltma Dairesi Başkanlığı


 

 Ahmet Temiz
 Geological Eng.
 Information Systems - GIS Group
 Disaster and Emergency Management
 of Presidency

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




 --
 Ahmet Temiz
 Jeoloji Müh.
 Afet ve Acil Durum Yönetimi Başkanlığı
 Planlama ve Zarar Azaltma Dairesi Başkanlığı


 

 Ahmet Temiz
 Geological Eng.
 Information Systems - GIS Group
 Disaster and Emergency Management
 of Presidency

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

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

Re: [postgis-users] selected aligned tiles in pgraster between several large raster tables.

[Bborie Park]

Graeme,

robe2 and I were discussing this thread and we were wondering if using
the ~= operator would work for your problem.

http://www.postgis.net/docs/manual-2.0/ST_Geometry_Same.html

-bborie

On Mon, Jun 17, 2013 at 7:22 AM, Graeme B. Bell g...@skogoglandskap.no wrote:

 Hello bborie (and everyone else).

 To recap. I'm trying to do some work with 4 gigantic raster tables which have 
 been set up in the same way (e.g. georeferenced/tiled identically). I want to 
 compare equivalent tiles in each raster with one another. Each raster has 7 
 million rows now but I'll be using 28 million row versions shortly.

 I wanted to quickly and reliably match up corresponding raster tiles between 
 the raster tables, but I wasn't confident that the geometry description for 
 each rid key would always be *exactly* alike between different imports of 
 rasters via raster2pgsql.

 bborie proposed:

 A.rast::geometry = B.rast::geometry will check for spatially equal
 tiles. This check will be faster on out-db vs in-db as there's less data
 for postgres to load regardless of your SQL.

 If you're asking for a canned SQL query...

 SELECT
   SOMEWORK
 FROM A
 JOIN B
   ON A.rast::geometry = B.rast::geometry

 -bborie

 which is an elegant approach, but unfortunately does not seem practical. The 
 problem is that the geometry has to be generated by the cast and it's not an 
 indexed column. This makes it a very slow process to build the join.

 I tested, and regardless of the type of join I tried, the cost of the join 
 grows as the square of the number of rows.

 n=100: 0.3s
 n=1000: 26s
 n=2000: 105s

 At that rate, n=28 million is going to be a big problem. A possible 
 workaround might be to precalculate and explicitly store some geometry for 
 every raster tile, index it, and then do the comparison. That would be a bit 
 slow and space-consuming, but is viable.

 Preferably it would be nicer to join on the RID column, which is a simple int 
 and is already indexed. But how can I be confident that the rid columns and 
 geometries will line up between the raster rows of each table?


 B0. Common sense - they ought to, in principle; there is no reason why they 
 shouldn't other than algorithm instability.


 B1. Pick out some rows and check visually in QGIS how the numbering works, 
 and that the geometry appears to match.

 select a.rid, a.rast::geometry into temp.test2 from temp.a as a order by rid 
 desc limit 1000;


 B2. Check that the highest rid value matches the number of rows in the table 
 (e.g. 'there is no room for gaps in the rid sequences')

 select rid from temp.b order by rid desc limit 10;
 select rid from temp.b order by rid asc limit 10;
 ...

 B3. Select out the start and the end of the raster tables and ensure that 
 when joined on RID, the geometries match.

 select a.rid as arid, b.rid as brid from temp.a join temp.b using (rid) 
 where st_equals(a.rast::geometry,b.rast::geometry) order by rid desc limit 
 10;

 select a.rid as arid, b.rid as brid from temp.a join temp.b using (rid) 
 where st_equals(a.rast::geometry,b.rast::geometry) order by rid asc limit 10;

 B4. You could also sample 1000's of random rids within the tables and check 
 they line up between rasters, to gain confidence that the ordering is 
 maintained throughout the data.


 in short - if the rasters were given in the same coordinate 
 system/scale/tiling, and if the start of the raster tables line up, and the 
 ends line up, if the rid ordering looks simple/predictable, and if there is 
 no gap in the number sequence along the way, then it seems reasonable to 
 trust the rid as a proxy for the geometry.

 This seems to work well. Using a join on 'rid' across the 4 tables is 
 extremely fast because it is a simple integer and it is already implicitly 
 indexed. I was able to complete the entire analysis (including checking 
 st_value() across the raster) in less than an hour.  The results look as 
 expected.

 I would recommend using rid in this way as a proxy for geometry to anyone 
 else doing analysis between large rasters that can be naturally placed into 
 an identical coordinate system, tiling, position and so on. But if you are 
 doing this, remember to run tests B0-B4 to gain some confidence that your 
 rids really are a safe proxy.

 select ...
   from a join b using (rid) join c using (rid) join d using (rid) ... (join 
 x,y sequence columns)
   where ... (st_value stuff on pixels)

 Hope someone finds this interesting.

 Graeme

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

Re: [postgis-users] selected aligned tiles in pgraster between several large raster tables.

[Bborie Park]

Have you considered storing your rasters outside the database with
raster2pgsql's -R flag? I usually recommend this if the rasters are
readonly. This also minimizes the amount of information stored within
the database to just the metadata of the out-db rasters.

-bborie

On Wed, Jun 12, 2013 at 5:23 AM, Graeme B. Bell g...@skogoglandskap.no wrote:
 Hello everyone,


 Short version:

 I have some identically specified/tiled rasters that differ only in terms of 
 the data values in the pixels and table name*.

 I want to find a quick, easy and 100% trustworthy test of tile position 
 equivalence between the tiles of several raster tables, so that I can match 
 up the pixels in corresponding tiles for mathematical operations.

 Is there a quick and easy way to do this?

 * in theory.


 Painfully long version:

 I have some big rasters, around 18 to 72 giga-pixels each. The rasters have a 
 single band containing 8-bit values. They are specified identically in terms 
 of tiling, georeferencing, skew, scale, pixel size etc. The only difference 
 is in the contents of the pixels.

 I've been processing these with GDAL and Python I'm getting very nice speeds, 
 e.g.  60 minutes to perform calculations using several rasters together.

 I've imported my rasters (A,B,C,D) into PG raster using 100x100 tiles, and 
 I'm trying some analysis in postgis. This is deliberate; I could  do the 
 analysis in gdalcalc very easily, but I want to make it possible for database 
 users to do this kind of work.

 I hit a stumbling block when it comes to carrying out aligned calculations 
 between pixels. I'm basically carrying out an operation over every pixel in D 
 and comparing it with the corresponding pixels in A, B, and C.

 Logically, what I want to say is:

 - for each 'rast' tile in raster table D
   - for each pixel of that tile (x=1..100, y=1..100)
 - get the corresponding pixel value from the corresponding tile in 
 rasters A,B and C and do some work

 The problem arises around the word 'corresponding' and the tradeoff between 
 accuracy, speed and query elegance/debuggability.

 To my mind, if the rasters are specified identically and the import routines 
 are stable (e.g. always producing the same metadata for the same raster 
 tiles), then it should be possible to pick out the identical tiles between 
 rasters.


 Possible approaches:

 1. Bounding box intersection.  If you try bounding box intersection to 
 identify corresponding tiles, it's suitably fast:

where D.rast  A.rast and D.rast  B.rast and D.rast  C.rast

 Unfortunately, you find that the bounding boxes of the neighbouring tiles in 
 all directions (8-neighbours) are also matched for  intersection.

 The consquence is that you get 256 rows where you want to have 1. I've 
 checked this is what is happening by looking at the returned rids. I expect 
 this is happening because of the BBox of a tile is slightly larger than the 
 tile, by definition.


 2. Make a multi-band raster.  One solution would be to merge the rasters 
 outside postgis into a multi-band aligned raster and import that (or do the 
 same inside postgis raster using e.g. addband).

 I don't want to do that because a) the data is big, copies are bad  and  b) 
 the data is logically distinct, so I want to store and address it using 
 logically distinct raster tables.


 3.  Address raster pixels indirectly using geography.  I could iterate across 
 the rasters using geographical points on the landscape that I've calculated 
 to correspond to the center of pixels on the rasters. However, for this 
 problem, please assume that I want to index the polygon tiles/pixels 
 *directly* between corresponding tiles, rather than *indirectly*. I suspect 
 it would be rather slow to do things this way, too.


 4. Identify exactly identical bounding boxes. Checking for equality between 
 st_envelopes of tiles works:

where st_envelope(D.rast) = st_envelope(C.rast) and st_envelope(D.rast) = 
 st_envelope(B.rast) and st_envelope(D.rast) = st_envelope(A.rast)

 but it is very slow - many many times slower than the  method. Also, I 
 expect the st_envelope generation algorithm is stable and will always make 
 exactly the same output for a given tile across each raster, but I can't be 
 100% certain without taking a close look at the source code of postgis.


 5. I can use matching rids (since the raster tiles get numbered identically 
 in practice from identically specified source tiles),

 where D.rid=A.rid and D.rid=B.rid and D.rid=C.rid

 This is ideal - super fast, quick to write - but it seems there might be a 
 theoretical risk that the rids might not be numbered in the order I expect 
 when imported through raster2pgsql. How real is that risk?


 6. I could compare ST_metadata equality between tiles. This also has the 
 advantage that if the tiles aren't set up identically, it will fail horribly. 
 It seems there is a lot of data being compared (skew etc) which I know for 
 certain matches 

Re: [postgis-users] Inconsistency between St_Value and ST_DumpAsPolygons

[Bborie Park]

ST_Value and ST_DumpAsPolygons do very different things. ST_Value gets a
value for a specific pixel while ST_DumpAsPolygons() dumps values by
merging pixels with same value in an area.

-bborie


On Thu, Jun 6, 2013 at 12:38 PM, Jean Marchal jean.d.marc...@gmail.comwrote:

 Dear List,

 value's number 4 in this raster come out with ST_Value:

 SELECT ST_Value(rast, 1, 8, 2), ST_BandnoDataValue(rast),
 ST_BandPixelType(rast)
 FROM (SELECT '
 010188C3400088C3C03FF0F16F910F29C1F38C064BB32D2441C67D0A000A0045010003000400050006000C000D000E000F00100011001200180019001A001B001C001D001E002400250026002700280029002A003400350036003700380039003A004B004C004D004E004F005000510052006A006B006C006D006E006F00700071008F009000910092009300940095009600B500B600B700B800B900BA00BB00BC00'::raster
 rast) foo

 but not with ST_DumpAsPolygons:

 SELECT (ST_DumpAsPolygons(rast)).val
 FROM (SELECT
 '010188C3400088C3C03FF0F16F910F29C1F38C064BB32D2441C67D0A000A0045010003000400050006000C000D000E000F00100011001200180019001A001B001C001D001E002400250026002700280029002A003400350036003700380039003A004B004C004D004E004F005000510052006A006B006C006D006E006F00700071008F009000910092009300940095009600B500B600B700B800B900BA00BB00BC00'::raster
 rast) foo

 Is this a bug or I am doing something wrong ?

 Thanks,

 Jean


 --
 Jean Marchal

 Etudiant au doctorat / PHD student
 Université Laval - Pavillon Abitibi-Price
 Faculté de foresterie, de géographie et de géomatique
 2405, rue de la Terrasse
 Québec (Québec) G1V 0A6

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


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

Re: [postgis-users] ST_MapAlgebra in JAVA app

[Bborie Park]

If you're talking about a pl/java callback function passed to
ST_MapAlgebra, you shouldn't have any problems.

-bborie

On Sun, May 26, 2013 at 12:48 AM, Ahmet Temiz ahmettemi...@gmail.com wrote:

 Is it possible to use ST_MapAlgebra with Create Function in JAVA app ?

 where can I find an example ?

 regards
 --
 Ahmet Temiz
 Jeoloji Müh.
 Afet ve Acil Durum Yönetimi Başkanlığı
 Planlama ve Zarar Azaltma Dairesi Başkanlığı


 

 Ahmet Temiz
 Geological Eng.
 Information Systems - GIS Group
 Disaster and Emergency Management
 of Presidency

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

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

Re: [postgis-users] Raster - is it possible to use names for bands?

[Bborie Park]

No.  Bands are referenced by index in PostGIS.  Your idea of using an
extra column for the band names is probably your best solution.

-bborie

On Fri, May 24, 2013 at 7:56 AM, Ricardo Filipe Soares Garcia da
ricardo.garcia.si...@gmail.com wrote:
 Hello list

 I'm taking my first steps in PostGIS rasterland.

 I have a large set of multiband rasters to store. It would be very handy to
 refer to bands using a descriptive name (for example band1: LST, band2:
 Error, etc) but from what I've been reading, it doesn't seem to be possible
 to give each band a specific name and I must refer to bands by their number.
 Is it really like this?

 If this is not possible, what would you advise as an alternative?
 - Storing only single band rasters in their own tables? This defeats the
 purpose of having multibands.
 - Using an extra column in the table, with the names of each band?
 - Other?

 Thanks

 --
 ___ ___ __
 Ricardo Garcia Silva

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

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

Re: [postgis-users] 2.1.0beta3dev upgrade error

[Bborie Park]

Steve,

Which version of the json library are you using?

http://trac.osgeo.org/postgis/ticket/2213

-bborie

On Wed, May 22, 2013 at 10:22 PM, Stephen Woodbridge
wood...@swoodbridge.com wrote:
 Some more data points. I also created a new database and tried:

 pgis21=# create extension postgis;

 ERROR:  could not load library /usr/lib/postgresql/9.2/lib/postgis-2.1.so:
 /usr/lib/postgresql/9.2/lib/postgis-2.1.so: undefined symbol:
 json_tokener_errors
 pgis21=# create extension postgis version '2.0.3';
 ERROR:  could not load library
 /usr/lib/postgresql/9.2/lib/rtpostgis-2.0.so: libgdal1.6.0.so.1: cannot
 open shared object file: No such file or directory

 So I rebuilt postgis 2.0.3 to use the new gdal that I installed for 2.1 and
 tried create extension postgis; and this installed 2.0.3 by default.

 pgis21=# drop extension postgis ;
 DROP EXTENSION

 pgis21=# create extension postgis version '2.1.0beta3dev';
 CREATE EXTENSION

 and now it loaded 2.1 ok.

 Not sure what caused the strangeness, but it seems to be working there, but
 when I run my pgrouting test script:

 woodbri@maps:~/work/pgrouting$ tools/test-runner.pl
 RUNNING: test-runner.pl
 ERROR:  could not load library /usr/lib/postgresql/9.2/lib/postgis-2.0.so:
 /usr/lib/postgresql/9.2/lib/postgis-2.0.so: undefined symbol:
 json_tokener_errors
 ERROR:  required extension postgis is not installed

 This creates a new database, adds postgis and pgrouting and then runs tests
 in that database.

 -Steve


 On 5/23/2013 1:01 AM, Stephen Woodbridge wrote:

 Hi all,

 I just tried to upgrade postgis from 2.0.3 - 2.1.0beta3dev and got the
 following error:

 pgr_test=# alter extension postgis update to '2.1.0beta3dev';
 ERROR:  could not load library
 /usr/lib/postgresql/9.2/lib/postgis-2.1.so:
 /usr/lib/postgresql/9.2/lib/postgis-2.1.so: undefined symbol:
 json_tokener_errors

 Did I forget a step or do something wrong?

 -Steve

 I have json-c-0.11 built and installed. In fact is is the ./configure
 report:

PostGIS is now configured for i686-pc-linux-gnu

   -- Compiler Info -
C compiler:   gcc -g -O2
C++ compiler: g++ -g -O2
SQL preprocessor: /usr/bin/cpp -traditional-cpp -P

   -- Dependencies --
GEOS config:  /usr/local/bin/geos-config
GEOS version: 3.3.8
GDAL config:  /usr/local/bin/gdal-config
GDAL version: 1.9.2
PostgreSQL config:/usr/lib/postgresql/9.2/bin/pg_config
PostgreSQL version:   PostgreSQL 9.2.4
PROJ4 version:47
Libxml2 config:   /usr/bin/xml2-config
Libxml2 version:  2.7.8
JSON-C support:   yes
PostGIS debug level:  0
Perl: /usr/bin/perl

   --- Extensions ---
PostGIS Raster:   enabled
PostGIS Topology: enabled
SFCGAL support:   disabled

    Documentation Generation 
xsltproc: /usr/bin/xsltproc
xsl style sheets:
dblatex:
convert:
mathml2.dtd:  http://www.w3.org/Math/DTD/mathml2/mathml2.dtd


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


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

Re: [postgis-users] 2.1.0beta3dev upgrade error

[Bborie Park]

I'd suggest using json 0.9 as that's what I'm running.

-bborie

On Thu, May 23, 2013 at 8:10 AM, Stephen Woodbridge
wood...@swoodbridge.com wrote:
 Bborie,

 I downloaded: json-c-0.11-nodoc.tar.gz
 there was also v 0.10 available.

 I will also note that json-c does not appear to be available in the Debian
 Squeeze repository.

 Also, I just noticed the note that I need GEOS 3.4 for the Delauney
 triangles, so I'm rebuilding the latest GEOS nightly snapshot and will
 install that.

 Thanks,
   -Steve


 On 5/23/2013 10:00 AM, Bborie Park wrote:

 Steve,

 Which version of the json library are you using?

 http://trac.osgeo.org/postgis/ticket/2213

 -bborie

 On Wed, May 22, 2013 at 10:22 PM, Stephen Woodbridge
 wood...@swoodbridge.com wrote:

 Some more data points. I also created a new database and tried:

 pgis21=# create extension postgis;

 ERROR:  could not load library
 /usr/lib/postgresql/9.2/lib/postgis-2.1.so:
 /usr/lib/postgresql/9.2/lib/postgis-2.1.so: undefined symbol:
 json_tokener_errors
 pgis21=# create extension postgis version '2.0.3';
 ERROR:  could not load library
 /usr/lib/postgresql/9.2/lib/rtpostgis-2.0.so: libgdal1.6.0.so.1: cannot
 open shared object file: No such file or directory

 So I rebuilt postgis 2.0.3 to use the new gdal that I installed for 2.1
 and
 tried create extension postgis; and this installed 2.0.3 by default.

 pgis21=# drop extension postgis ;
 DROP EXTENSION

 pgis21=# create extension postgis version '2.1.0beta3dev';
 CREATE EXTENSION

 and now it loaded 2.1 ok.

 Not sure what caused the strangeness, but it seems to be working there,
 but
 when I run my pgrouting test script:

 woodbri@maps:~/work/pgrouting$ tools/test-runner.pl
 RUNNING: test-runner.pl
 ERROR:  could not load library
 /usr/lib/postgresql/9.2/lib/postgis-2.0.so:
 /usr/lib/postgresql/9.2/lib/postgis-2.0.so: undefined symbol:
 json_tokener_errors
 ERROR:  required extension postgis is not installed

 This creates a new database, adds postgis and pgrouting and then runs
 tests
 in that database.

 -Steve


 On 5/23/2013 1:01 AM, Stephen Woodbridge wrote:


 Hi all,

 I just tried to upgrade postgis from 2.0.3 - 2.1.0beta3dev and got the
 following error:

 pgr_test=# alter extension postgis update to '2.1.0beta3dev';
 ERROR:  could not load library
 /usr/lib/postgresql/9.2/lib/postgis-2.1.so:
 /usr/lib/postgresql/9.2/lib/postgis-2.1.so: undefined symbol:
 json_tokener_errors

 Did I forget a step or do something wrong?

 -Steve

 I have json-c-0.11 built and installed. In fact is is the ./configure
 report:

 PostGIS is now configured for i686-pc-linux-gnu

-- Compiler Info -
 C compiler:   gcc -g -O2
 C++ compiler: g++ -g -O2
 SQL preprocessor: /usr/bin/cpp -traditional-cpp -P

-- Dependencies --
 GEOS config:  /usr/local/bin/geos-config
 GEOS version: 3.3.8
 GDAL config:  /usr/local/bin/gdal-config
 GDAL version: 1.9.2
 PostgreSQL config:/usr/lib/postgresql/9.2/bin/pg_config
 PostgreSQL version:   PostgreSQL 9.2.4
 PROJ4 version:47
 Libxml2 config:   /usr/bin/xml2-config
 Libxml2 version:  2.7.8
 JSON-C support:   yes
 PostGIS debug level:  0
 Perl: /usr/bin/perl

--- Extensions ---
 PostGIS Raster:   enabled
 PostGIS Topology: enabled
 SFCGAL support:   disabled

 Documentation Generation 
 xsltproc: /usr/bin/xsltproc
 xsl style sheets:
 dblatex:
 convert:
 mathml2.dtd:  http://www.w3.org/Math/DTD/mathml2/mathml2.dtd


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



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

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


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

Re: [postgis-users] ST_Line_Locate_Point VS ST_LineLocatePoint

[Bborie Park]

ST_LineLocatePoint() was introduced in 2.1.  I was going to suggest
using PL/pgSQL exception handling but that has a significant
performance hit...

-bborie

On Thu, May 23, 2013 at 1:52 PM, Stephen Woodbridge
wood...@swoodbridge.com wrote:
 Hi,

 I just notice in pgRouting that I'm getting:

 WARNING:  ST_Line_Locate_Point signature was deprecated in 2.1.0. Please use
 ST_LineLocatePoint

 messages. I can switch this, but I'm wandering when ST_LineLocatePoint was
 introduced because of backwards compatibility with older versions of
 PostGIS.

 So I don't want to change the function only to find that is is not available
 on 1.5 or earlier. Or I need to dynamically modify the plpgsql functions
 based on version of postgis we are installing on.

 Thanks,
   -Steve
 ___
 postgis-users mailing list
 postgis-users@lists.osgeo.org
 http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] Vote: PAGC address standardizer in extras

[Bborie Park]

+1

On Sat, Apr 6, 2013 at 9:36 PM, Paragon Corporation l...@pcorp.us wrote:
 I would like at some point (soon) fork the standardizer part of pagc and
 include in extras/pagc_standardizer folder of PostGIS to make it easier to
 install as part of the PostGIS build.

 I'd like a vote on this (PSC, developers, and general users)

 As discussed here

 http://trac.osgeo.org/postgis/ticket/2193

 As some folks have noticed, I've been working on changing the tiger geocoder
 to use an alternative address standardizer which is part of the PAGC library
 (MIT Licensed).
 This unfortunately will require additional library compile in C, but the way
 I have it structured, this piece is not mandatory to use the tiger geocoder


 The logic to use as a drop in replacement is there but not documented since
 still working out some kinks.

 Steve Woodbridge broke out just the standardizer part as a postgresql
 extension and was discussed
 On postgis-users discussion a while back

 http://lists.osgeo.org/pipermail/postgis-users/2012-December/036019.html

 The purpose is:

 1)
 The PAGC does generally a better job of standardizing than the built in
 tiger geocoder one so will resolve many of complaints people have had

 2) Provide a standardizer separate from tiger geocoder that we can reuse for
 other PostGIS based geocoders


 As such the standardizer will be documented in PostGIS documentation
 describing how to build and enable in tiger geocoder
 And how to use it separate from tiger geocoder.

 3) Should be generally faster too once  we work out some issues on how its
 called and its ability to cache the C built standardizer object.


 Thoughts, concerns

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






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

Re: [postgis-users] ST_SetSRID

[Bborie Park]

Your query needs a FROM clause to specify the table.  The error
message is indicating that the database does not know the rast
column...

SELECT rast As bd_ortho, ST_Transform(rast,4326) As bd_ortho_wgs84 FROM mytable

Also, you really should upgrade your PostGIS installation to 2.0.1.

-bborie

On Thu, Feb 21, 2013 at 2:36 AM, laurent
lcel...@latitude-geosystems.com wrote:
 Hello,
 --
 SELECT postgis_full_version()

 POSTGIS=2.0.0 r9605 GEOS=3.3.3-CAPI-1.7.4 PROJ=Rel. 4.8.0, 6 March
 2012 GDAL=GDAL 1.9.0, released 2011/12/29 LIBXML=2.7.8
 LIBJSON=UNKNOWN RASTER
 ---

 Yes, yesterday i tried to do a reprojection thanks to st_transform  to do a
 ST_SetSRID in order to update metadata but eventually I do not think I
 succeeded.

 Could you tell me the SQL query please? I thought that following queries are
 valid :

 SELECT rast As bd_ortho, ST_Transform(rast,4326) As bd_ortho_wgs84

 But i have today an error message saying :

 ERREUR:  la colonne « rast » n'existe pas
 LINE 1: SELECT rast As bd_ortho, ST_Transform(rast,4326) As bd_ortho...

 Once I'm sure I reprojected this layer, I will try to update the metadata


 Thank you to throw light for me.

 Regards.











 --
 View this message in context: 
 http://postgis.17.n6.nabble.com/ST-SetSRID-tp5002645p5002650.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
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] problem with 'sobel' ST_MapAlgebraFctNgb

[Bborie Park]

Hey,

Your sobel function looks fine.  How you're calling it is incorrect
though.  You only have two function parameters defined in the
regprocedure argument of your ST_MapAlgebraFctNgb query.

-bborie



On 01/18/2013 03:10 PM, laurent wrote:
 Hello,
 
 I would like apply a sobel filter to my raster DEM. In a 1st time, i defined
 the sobel function in a sql.file. 
 In a 2nd time, i tried to run the 'ST_MapAlgebraFctNgb'  via PSQL console
 available via pgadmin3.
 
 When i run the query, i have an error message saying : The Sobel(float []
 [], text [] does not exist. 
 
 
 I send you by attachment the 'sobel.sql'  the 'ST_MapAlgebraFctNgb' query. 
 
 Could you throw light for me please? 
 
 Regards. 
 
 IMAGE. 
 
 sobel.sql http://postgis.17.n6.nabble.com/file/n5002352/sobel.sql  
 Query_ST_MapAlgebraFctNgb.sql
 http://postgis.17.n6.nabble.com/file/n5002352/Query_ST_MapAlgebraFctNgb.sql 
  
 
 
 
 --
 View this message in context: 
 http://postgis.17.n6.nabble.com/problem-with-sobel-ST-MapAlgebraFctNgb-tp5002352.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
 

-- 
Bborie Park
Programmer
Center for Vectorborne Diseases
UC Davis
530-752-8380
bkp...@ucdavis.edu
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] No intersection between two rasters notice

[Bborie Park]

Yes, part of the issue is due to adjacent tiles.  If you're using
PostGIS 2.0, add something like...

AND ST_Touches(rast1::geometry, rast2::geometry) != TRUE

If you're running PostGIS from svn...

AND ST_Touches(rast1, rast2) != TRUE

-bborie

On 01/15/2013 08:45 AM, Francois Hugues wrote:
 Hi everyone,
 
 I'm facing a new difficulty with rasters. This time with
 st_MapAlgebraExpr (the whole query is below). I have this notice when I
 try to cross two rasters with exactly similar tiles.
 NOTICE:  The two rasters provided have no intersection.  Returning no
 band raster
 
 To avoid to cross tiles with others where there were effectively no
 intersection I tried the where clause where st_intersects(rast1,
 rast2) but the problem is not really solved. Is it because of adjacent
 tiles ?
 
 Is there a more effective (and faster) way to use mapalgebra and to
 avoid this creation of empty tiles which prevent to use st_convexhull
 (and every function which use convexhull) ?
 
 Hugues.
 
 select alti.rid, st_mapalgebraexpr(alti.rast, slopes.rast,
 '[rast1]*[rast2]', '32BF', 'NULL', 'NULL', 'NULL') rast
 from rasters.alti_cons_ign_alpes alti, rasters.slopes_alpes slopes
 where st_intersects(alti.rast, slopes.rast)
 
 ___
 postgis-users mailing list
 postgis-users@lists.osgeo.org
 http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
 

-- 
Bborie Park
Programmer
Center for Vectorborne Diseases
UC Davis
530-752-8380
bkp...@ucdavis.edu
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] should I be using st_mapalgebra?

[Bborie Park]

Rhys,

ST_MapAlgebra would be faster as it is in C.  I'm assuming you're using
2.1 since you seem to be using ST_PixelAsCentroids().

The serial ST_SetValue() kills performance. Use ST_SetValues() if possible.

-bborie

On 11/29/2012 12:40 PM, Rhys A.D. Stewart wrote:
 Greetings,
 
 I'm am trying to create a raster that is coloured based on the distance
 from an arbitrary point. kinda like the attached tiff file. To do that I
 used the following ACB:
 __
 DO
 $$
 DECLARE
 qq record;
 BEGIN
 for qq in
 with one as
   (select (st_pixelascentroids(rast)).* from r.island_ouline_raster_125
  ),
 two as
   (select *, st_distance(geom, 'srid=3448;POINT(60
 66)'::geometry)::int dist from one where val is not null ),
 three as
   (select * , percent_rank() over (order by dist) pr from two),
 four as
   (select *, dev.range_percent(200, 125, pr) red, dev.range_percent(25,
 250, pr) green, dev.range_percent(100, 25, pr) blue from three order by
 dist )
 /*dev.range takes a percent and two endoints and returns the % of he diff
 plus endpoint 1 */
 select * from four LOOP
 update r.island_ouline_raster_12_point_5 set rast = st_setvalue(rast,1,
 qq.geom, qq.red);
 update r.island_ouline_raster_12_point_5 set rast = st_setvalue(rast,2,
 qq.geom, qq.green);
 update r.island_ouline_raster_12_point_5 set rast = st_setvalue(rast,3,
 qq.geom, qq.blue);
 
 
 END LOOP;
 /* below just writes the raster to disk */
 perform dev.wf(st_astiff(rast), '__kml/1250_changed5.tiff') from
 r.island_ouline_raster_1250;
 END;
 
 $$ language plpgsql
 
 
 I used an ACB because I couldn't think of anyway to update the raster with
 each column/row value in plain sql. Even so,  it seems like overkill. Is
 there an easier way, possibly using st_mapalgebra to accomplish the same
 objective?
 
 Also I'm running the same ACB on a raster which is ~ 1800x700 and it it
 still not finished, its currently at 3570123 ms. The attached raster is
 185x73 and that took about 40 seconds to generate. So anything that would
 be faster would also be great.
 
 Regards,
 
 Rhys
 
 
 
 ___
 postgis-users mailing list
 postgis-users@lists.osgeo.org
 http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
 

-- 
Bborie Park
Programmer
Center for Vectorborne Diseases
UC Davis
530-752-8380
bkp...@ucdavis.edu
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] Posgist raster overviews created with bicubic resampling

[Bborie Park]

Ticket #2113 added.

http://trac.osgeo.org/postgis/ticket/2113

I'll see about getting to this before PostGIS 2.1 is released.

On Wed, Nov 28, 2012 at 2:53 AM, daigu davor.ra...@gmail.com wrote:
 I suppose i would have to resample union of 4 tiles, each one 512x512 dpi,
 and then insert the new tile of dpi 512x512 into the overview table. This
 would be easy if i have one single raster, but i have multiple rasters
 divided into multiple tiles. Rasters are same size and aligned, but the
 mosaic of all rasters is irregularly shaped. I could use bounding boxes of
 existing overviews, and then take intersection of higher resolution raster
 and bounding box of lower resolution overview, and then resample it to
 512x512. This all looks very complicated to me...

 I'm stuck here...

 This would be a very useful feature for future versions of raster2pgsql.
 Overviews of aerial photos don't look good created this way.




 --
 View this message in context: 
 http://postgis.17.n6.nabble.com/Posgist-raster-overviews-created-with-bicubic-resampling-tp5001480p5001553.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



-- 
Bborie Park
Programmer
Center for Vectorborne Diseases
UC Davis
530-752-8380
bkp...@ucdavis.edu
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] Effectively combining rasters

[Bborie Park]

Hi Kim,

Can you post the query for the single raster and its EXPLAIN ANALYZE?
When posting an EXPLAIN ANALYZE, I've found that using
http://explain.depesz.com/ makes it more readable.

Looking at the EXPLAIN ANALYZE for the WITH query, most of the time is
spent on the foo query.  I'm guessing on the two ST_Intersections().

-bborie

On Wed, Nov 28, 2012 at 12:01 AM, Kim Bisgaard k...@dmi.dk wrote:
 Thanks Bborie,

 That is an alternative solution, but it gives about the same runtime here, 
 perhaps because I use PostgreSQL 9.1 and not 9.2.

 Sort  (cost=22.14..22.15 rows=1 width=64) (actual time=1402.745..1402.746 
 rows=14 loops=1)
   Sort Key: (st_x(st_centroid((foo.AAA).geom))), 
 (st_y(st_centroid((foo.AAA).geom)))
   Sort Method: quicksort  Memory: 26kB
   CTE pos
 -  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 
 rows=1 loops=1)
   CTE foo
 -  Nested Loop  (cost=0.01..22.09 rows=1 width=96) (actual 
 time=101.003..1402.181 rows=210 loops=1)
   Join Filter: _st_intersects(B.rast, pos.geom, NULL::integer)
   -  Nested Loop  (cost=0.00..10.81 rows=1 width=64) (actual 
 time=0.198..0.203 rows=1 loops=1)
 Join Filter: _st_intersects(A.rast, pos.geom, NULL::integer)
 -  CTE Scan on pos  (cost=0.00..0.02 rows=1 width=32) 
 (actual time=0.005..0.006 rows=1 loops=1)
 -  Index Scan using A_rast_gist on A (cost=0.00..8.27 
 rows=1 width=32) (actual time=0.022..0.024 rows=1 loops=1)
   Index Cond: ((rast)::geometry  pos.geom)
   -  Index Scan using B_rast_gist on B (cost=0.00..8.27 rows=1 
 width=32) (actual time=0.014..0.021 rows=1 loops=1)
 Index Cond: ((rast)::geometry  pos.geom)
   -  CTE Scan on foo  (cost=0.00..0.03 rows=1 width=64) (actual 
 time=101.041..1402.723 rows=14 loops=1)
 Filter: ((AA).geom = (BB).geom)
 Total runtime: 1402.833 ms

 I will see if I can find a way to try it on a 9.2 ...

 If there is no better idea as to reformulate the query? I will have to merge 
 results in the program - with 60ms per raster it's faster to do
 in the program and not in the DB.

 Thanks again,
 Kim

 On 2012-11-27 19:04, Bborie Park wrote:
 WITH pos AS (
   SELECT ST_GeomFromText('MULTIPOLYGON(((15.000 54.800,15.000
 55.000,14.800 55.000,14.800 54.800,15.000 54.800)))',4326) AS geom
 ), foo AS (
   SELECT
   st_intersection(A.rast,1,pos.geom) AS AA,
   st_intersection(B.rast,1,pos.geom) AS BB
   FROM pos
   JOIN A
   ON st_intersects(A.rast,pos.geom)
   JOIN B
   ON st_intersects(B.rast,pos.geom)
 )
 SELECT
   st_x(st_centroid((AA).geom)) AS lon,
   st_y(st_centroid((AA).geom)) AS lat,
   (AA).val AS AAA,
   (BB).val As BBB
 FROM foo
 WHERE (AA).geom = (BB).geom
 ORDER BY lon, lat;


 --
 Kim Bisgaard

 Application Development Division Phone: +45 3915 7562 (direct)
 Danish Meteorological Institute  Fax: +45 3915 7460 (division)
 ___
 postgis-users mailing list
 postgis-users@lists.osgeo.org
 http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users



-- 
Bborie Park
Programmer
Center for Vectorborne Diseases
UC Davis
530-752-8380
bkp...@ucdavis.edu
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] Effectively combining rasters

[Bborie Park]

You may want to try pulling out the nested SELECTs into WITH blocks

WITH pos AS (
SELECT ST_GeomFromText('MULTIPOLYGON(((15.000 54.800,15.000
55.000,14.800 55.000,14.800 54.800,15.000 54.800)))',4326) AS geom
), foo AS (
SELECT  
st_intersection(A.rast,1,pos.geom) AS AA,
st_intersection(B.rast,1,pos.geom) AS BB
FROM pos
JOIN A
ON st_intersects(A.rast,pos.geom)
JOIN B
ON st_intersects(B.rast,pos.geom)
)
SELECT
st_x(st_centroid((AA).geom)) AS lon,
st_y(st_centroid((AA).geom)) AS lat,
(AA).val AS AAA,
(BB).val As BBB
FROM foo
WHERE (AA).geom = (BB).geom
ORDER BY lon, lat;

That should eliminate the number of times the inner SELECTs are run.

-bborie

On 11/27/2012 05:16 AM, Kim Bisgaard wrote:
 Hi,
 
 I am trying to combine rasters, or in other words pick a value at a position 
 in one raster and at the same time get the value at same the 
 same position in the other raster. The way I'm doing it, takes a long time 
 compare to only picking values from one raster, which makes me 
 think that there ought to be a more efficient way?
 
 I am currently using this SQL:
 select st_x(st_centroid((AA).geom)) as lon,
 st_y(st_centroid((AA).geom)) as lat,
 (AA).val as AAA,
 (BB).val as BBB
 from (select st_intersection(A.rast,1,pos.geom) as AA,
   st_intersection(B.rast,1,pos.geom) as BB
from (select ST_GeomFromText('MULTIPOLYGON(((15.000 54.800,15.000 
 55.000,14.800 55.000,14.800 54.800,15.000 54.800)))',4326) as geom) 
 as pos join
 A on st_intersects(A.rast,pos.geom) join
 B on st_intersects(B.rast,pos.geom) ) as foo
 where (AA).geom = (BB).geom
 order by lon, lat;
 
 As a side note I can tell that both rasters are equal in geographical sense 
 (different bands from the same data).
 
 Looking in 2 rasters takes 1.4 sec. and in one raster is 60ms
 
 This is PostgreSQL 9.1.6, PostGIS 2.0.1
 
 Thanks in advance!
 
 Regards,
 

-- 
Bborie Park
Programmer
Center for Vectorborne Diseases
UC Davis
530-752-8380
bkp...@ucdavis.edu
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] Adding raster to PostGIS database

[Bborie Park]

Run the following...

gdalinfo Quercus_acutifolia_raw.tif

If that returns an error, something may be wrong with the tif file.

-bborie

On 11/26/2012 02:03 PM, Smaran Harihar wrote:
 But I am executing the command in the directory where it is located
 
 I can list that raster using `ls`
 
 On Mon, Nov 26, 2012 at 2:46 PM, Bborie Park bkp...@ucdavis.edu wrote:
 
 That error would only happen if raster2pgsql can't find the file in your
 current working directory. raster2pgsql supports relative paths for
 input rasters.

 -bborie

 On 11/26/2012 01:37 PM, Smaran Harihar wrote:
 Thanks for the reply bborie,

 I ran this command,

 raster2pgsql -c -s 4326 -I Quercus_acutifolia_raw.tif
 public.Quercus_acutifolia_raw | psql -h localhost -d rasterdb -U postgres

 But this is the new error,

 ERROR: Unable to read raster file: Quercus_acutifolia_raw.tif

 Thanks,
 Smaran

 On Mon, Nov 26, 2012 at 2:27 PM, Bborie Park bkp...@ucdavis.edu wrote:

 -D is not a valid flag recognized by raster2pgsql.  Remove the flag as
 raster2pgsql is assuming that -D is a file.

 -bborie

 On 11/26/2012 01:23 PM, Smaran Harihar wrote:
 Hi Guys,

 I am trying to add a raster to PostGIS database using,

 raster2pgsql -c -D -s 4326 -I Quercus_acutifolia_raw.tif
 public.Quercus_acutifolia_raw | psql -h localhost -d dbnm -U usr

 But I am getting this error,

 ERROR: Unable to read raster file: -D

 Any idea what is wrong? The database already has numerous point and
 polygon
 shapefiles. Is that a problem that I cannot add rasters to the same
 database having shapefiles?



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


 --
 Bborie Park
 Programmer
 Center for Vectorborne Diseases
 UC Davis
 530-752-8380
 bkp...@ucdavis.edu
 ___
 postgis-users mailing list
 postgis-users@lists.osgeo.org
 http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users






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


 --
 Bborie Park
 Programmer
 Center for Vectorborne Diseases
 UC Davis
 530-752-8380
 bkp...@ucdavis.edu
 ___
 postgis-users mailing list
 postgis-users@lists.osgeo.org
 http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

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

-- 
Bborie Park
Programmer
Center for Vectorborne Diseases
UC Davis
530-752-8380
bkp...@ucdavis.edu
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] ST_AsRaster and pixeltype

[Bborie Park]

This would be a limitation on the datatypes supported by GDAL vs
PostGIS Raster.  GDAL does not have any data types smaller than 8BUI,
so any datatype smaller is treated as 8BUI.  8BSI also does not have
an equivalent in GDAL so is treated as 16BSI.

Feel free to file a ticket.  The only way to correct this is to add
another pass converting the output raster's pixel type from GDAL to
PostGIS Raster.

-bborie

On Mon, Nov 19, 2012 at 2:03 AM,  michael.sch...@dlr.de wrote:
 Moin!
 ST_AsRaster does not always create a raster with the specified pixel type. 
 Instead of a band with 1BB I get one with 8BUI, which is the default 
 according to the documentation. Example:
 -
 CREATE TEMP TABLE foo (rid serial PRIMARY KEY, rast raster);

 INSERT INTO foo (rast)
 SELECT ST_AsRaster (ST_MakePolygon (ST_GeomFromText ('LINESTRING(604770 
 5793000, 604870 5793000, 604870 5792900, 604770 5793000)',32632)), 0.1, -0.1, 
 '1BB', 1.0, 0.0);

 SELECT AddRasterConstraints ('foo'::name,'rast'::name);

 SELECT srid,scale_x,scale_y,num_bands,pixel_types,nodata_values
 FROM raster_columns
 WHERE r_table_name = 'foo';
 -
 The result:
 32632 | 0.10 | -0.1 | 1 | {8BUI} | {0}
 If I specify 16BUI, I indeed get a 16BUI-band. Is this a special feature of 
 ST_AsRaster or some limitation of GDAL?

 My version:
 POSTGIS=2.0.1 r9979 GEOS=3.3.5-CAPI-1.7.5 PROJ=Rel. 4.8.0, 6 March 
 2012 GDAL=GDAL 1.9.2, released 2012/10/08 GDAL_DATA not found 
 LIBXML=2.7.8 RASTER

 Ciaoi, Michi
 ___
 postgis-users mailing list
 postgis-users@lists.osgeo.org
 http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users



-- 
Bborie Park
Programmer
Center for Vectorborne Diseases
UC Davis
530-752-8380
bkp...@ucdavis.edu
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] ST_AsRaster and pixeltype

[Bborie Park]

I'd rather keep the datatypes.  I've added a ticket to convert to the
requested datatype.

http://trac.osgeo.org/postgis/ticket/2100

-bborie

On Mon, Nov 19, 2012 at 8:53 AM, Pierre Racine
pierre.rac...@sbf.ulaval.ca wrote:
 Or to remove support for 1BB and 8BSI?

 -Original Message-
 From: postgis-users-boun...@lists.osgeo.org [mailto:postgis-users-
 boun...@lists.osgeo.org] On Behalf Of Bborie Park
 Sent: Monday, November 19, 2012 11:30 AM
 To: PostGIS Users Discussion
 Subject: Re: [postgis-users] ST_AsRaster and pixeltype

 This would be a limitation on the datatypes supported by GDAL vs
 PostGIS Raster.  GDAL does not have any data types smaller than 8BUI,
 so any datatype smaller is treated as 8BUI.  8BSI also does not have
 an equivalent in GDAL so is treated as 16BSI.

 Feel free to file a ticket.  The only way to correct this is to add
 another pass converting the output raster's pixel type from GDAL to
 PostGIS Raster.

 -bborie

 On Mon, Nov 19, 2012 at 2:03 AM,  michael.sch...@dlr.de wrote:
  Moin!
  ST_AsRaster does not always create a raster with the specified pixel type.
 Instead of a band with 1BB I get one with 8BUI, which is the default 
 according to
 the documentation. Example:
  -
  CREATE TEMP TABLE foo (rid serial PRIMARY KEY, rast raster);
 
  INSERT INTO foo (rast)
  SELECT ST_AsRaster (ST_MakePolygon (ST_GeomFromText
 ('LINESTRING(604770 5793000, 604870 5793000, 604870 5792900, 604770
 5793000)',32632)), 0.1, -0.1, '1BB', 1.0, 0.0);
 
  SELECT AddRasterConstraints ('foo'::name,'rast'::name);
 
  SELECT srid,scale_x,scale_y,num_bands,pixel_types,nodata_values
  FROM raster_columns
  WHERE r_table_name = 'foo';
  -
  The result:
  32632 | 0.10 | -0.1 | 1 | {8BUI} | {0}
  If I specify 16BUI, I indeed get a 16BUI-band. Is this a special feature of
 ST_AsRaster or some limitation of GDAL?
 
  My version:
  POSTGIS=2.0.1 r9979 GEOS=3.3.5-CAPI-1.7.5 PROJ=Rel. 4.8.0, 6 March
 2012 GDAL=GDAL 1.9.2, released 2012/10/08 GDAL_DATA not found
 LIBXML=2.7.8 RASTER
 
  Ciaoi, Michi
  ___
  postgis-users mailing list
  postgis-users@lists.osgeo.org
  http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users



 --
 Bborie Park
 Programmer
 Center for Vectorborne Diseases
 UC Davis
 530-752-8380
 bkp...@ucdavis.edu
 ___
 postgis-users mailing list
 postgis-users@lists.osgeo.org
 http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
 ___
 postgis-users mailing list
 postgis-users@lists.osgeo.org
 http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users



-- 
Bborie Park
Programmer
Center for Vectorborne Diseases
UC Davis
530-752-8380
bkp...@ucdavis.edu
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users