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
> ST_Resample('010200F03FF0BF020

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 function addrasterconstrai

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

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 
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 
> *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]

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] 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 width=0)"
> "I

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

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

[Bborie Park]

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  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] 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 
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] 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 
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] ST_Value on tiled raster

[Bborie Park]

Yes. The result of that query is the value for the first pixel. If you're
going to dump out all the values of all tiles, you're better off with
something like ST_DumpValues instead...

http://postgis.net/docs/manual-2.1/RT_ST_DumpValues.html

If you're scanning the whole MODIS scene(s), nothing beats the performance
of directly going to the files.

Since you're new, two bits of advice...

1. If your raster data is read-only, keep the raster data outside the
database using out-db (raster2pgsql's -R flag)

2. There is no optimal tile size for storing data in-db as that is highly
dependent on the source raster data (# of bands, pixel types of each band).
For out-db though, I strongly recommend using the block size specified by
the output of gdalinfo. The reason for this is that GDAL is used to get the
data from the out-db raster.

$ gdalinfo myraster.tif

Driver: GTiff/GeoTIFF
Files: myraster.tif
Size is 1405, 621
Coordinate System is:
GEOGCS["NAD83",
DATUM["North_American_Datum_1983",
SPHEROID["GRS 1980",6378137,298.2572221010002,
AUTHORITY["EPSG","7019"]],
AUTHORITY["EPSG","6269"]],
PRIMEM["Greenwich",0],
UNIT["degree",0.0174532925199433],
AUTHORITY["EPSG","4269"]]
Origin = (-125.0208329,49.93750002032)
Pixel Size = (0.0416670,-0.0416670)
Metadata:
  AREA_OR_POINT=Area
Image Structure Metadata:
  INTERLEAVE=BAND
Corner Coordinates:
Upper Left  (-125.0208333,  49.9375000) (125d 1'15.00"W, 49d56'15.00"N)
Lower Left  (-125.0208333,  24.0625000) (125d 1'15.00"W, 24d 3'45.00"N)
Upper Right ( -66.4791667,  49.9375000) ( 66d28'45.00"W, 49d56'15.00"N)
Lower Right ( -66.4791667,  24.0625000) ( 66d28'45.00"W, 24d 3'45.00"N)
Center  ( -95.750,  37.000) ( 95d45' 0.00"W, 37d 0' 0.00"N)
Band 1 *Block=1405x1* Type=Float32, ColorInterp=Gray
  Min=10.920 Max=49.370
  Minimum=10.920, Maximum=49.370, Mean=29.345, StdDev=5.782
  NoData Value=-

 -bborie

On Tue, Apr 21, 2015 at 9:18 AM, Albury, Carl -FS  wrote:

>  Hi
>
> I’m new to PostGIS  so please bear with me.
>
> I’m working with MODIS data that I tiled using the auto tile switch in
> raster2pgsql resulting in 13250 82x94 tiles.
>
> When I try to query for pixel values using ST_Value, such as:
>
>
>
> SELECT
>
> ST_Value(rast, 2, 1,1) -- multiband raster
>
> FROM
>
>   MODIS_test;
>
>
>
> I get a value for each tile, not for each pixel (I assume the value is for
> the first pixel in the tile?).
>
> If I want to query each pixel do I need to loop through each tile and have
> a sub loop for each pixel in that tile? If so I can’t figure out the syntax
> to designate the tiles vs pixels.
>
>
>
> Thanks very much,
>
> Carl
>
>
>
> ___
> 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 wkb documentation

[Bborie Park]

Take a look at the raster specific RFCs.

https://trac.osgeo.org/postgis/wiki/DevWikiRFC

RFC2 is what you're looking for

https://trac.osgeo.org/postgis/wiki/WKTRaster/RFC/RFC2_V0WKBFormat

On Tue, Apr 21, 2015 at 12:06 AM, Martin Landa 
wrote:

> Hi all,
>
> is there any complete documentation of WKB available online? I just
> found [1] (Table 2 - Structure of a raster type).
>
> Thanks, Martin
>
> [1] https://trac.osgeo.org/postgis/wiki/WKTRaster/Documentation01
>
> --
> Martin Landa
> http://geo.fsv.cvut.cz/gwiki/Landa
> http://gismentors.cz/mentors/landa
> ___
> 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 
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"  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] Loading raster data into postgis

[Bborie Park]

Try reformatting the path to the file.

raster2pgsql -s 4326 -I -C -M D://WQM//Tb.asc -F -t 100x100 testras|psql -h
localhost -p 5432 -d wqr -U postgres

On Sun, Mar 1, 2015 at 11:40 PM, Ronald Muchini 
wrote:

> How can I deal with this,
> Loading rasters into postgis from the commandline using the following
> command
>
> raster2pgsql -s 4326 -I -C -M \D:\WQM\Tb.asc -F -t 100x100 testras|psql -h
> localhost -p 5432 -d wqr -U postgres;
>
> gives error: "unable to read raster file \D:\WQM\Tb.asc "
> ___
> 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]

Going back a few messages, it looks like you're specifying these out-db
rasters with relative paths. Use absolute paths.

Instead of:

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

Use:

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

PostgreSQL cannot access paths that were relative to the current working
directory when calling raster2pgsql.

Also, specify a tile size.

-bborie



On Wed, Feb 25, 2015 at 12:51 PM, Phil Hurvitz  wrote:

> Thank you, Bborie. This seems to be a partial solution (at least now
> PostGIS isn't complaining about the out-db raster). But now I get a
> different problem, which is that attempting to access the out-db raster
> makes the connection choke:
>
> select st_summarystats(rast) from slope;
> The connection to the server was lost. Attempting reset: Failed.
> !>
>
> -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 12:41, Bborie Park wrote:
>
>> Ah. I use a script similar to this.
>>
>> In the following block...
>>
>> start)
>>  ...
>>  su - $PGUSER -c "$DAEMON -D '$PGDATA' &" >>$PGLOG 2>&1
>>  ...
>>
>> You'll want to modify the su line to be like:
>>
>>  su - $PGUSER -c "POSTGIS_ENABLE_OUTDB_RASTERS=1
>> POSTGIS_GDAL_ENABLED_DRIVERS=__ENABLE_ALL $DAEMON -D '$PGDATA' &"
>>  >>$PGLOG 2>&1
>>
>> See if that works...
>>
>> -bborie
>>
>>
>> On Wed, Feb 25, 2015 at 12:29 PM, Phil Hurvitz > <mailto:phurv...@uw.edu>> wrote:
>>
>> Certainly!
>>
>> ! /bin/sh
>>
>> # chkconfig: 2345 98 02
>> # description: PostgreSQL RDBMS
>>
>> # This is an example of a start/stop script for SysV-style init, such
>> # as is used on Linux systems.  You should edit some of the variables
>> # and maybe the 'echo' commands.
>> #
>> # Place this file at /etc/init.d/postgresql (or
>> # /etc/rc.d/init.d/postgresql) and make symlinks to
>> #   /etc/rc.d/rc0.d/K02postgresql
>> #   /etc/rc.d/rc1.d/K02postgresql
>> #   /etc/rc.d/rc2.d/K02postgresql
>> #   /etc/rc.d/rc3.d/S98postgresql
>> #   /etc/rc.d/rc4.d/S98postgresql
>> #   /etc/rc.d/rc5.d/S98postgresql
>> # Or, if you have chkconfig, simply:
>> # chkconfig --add postgresql
>> #
>> # Proper init scripts on Linux systems normally require setting lock
>> # and pid files under /var/run as well as reacting to network
>> # settings, so you should treat this with care.
>>
>> # Original author:  Ryan Kirkpatrick > <mailto:pg...@rkirkpat.net>>
>>
>> # contrib/start-scripts/linux
>>
>> ## EDIT FROM HERE
>>
>> # Installation prefix
>> prefix=/usr/local/pgsql
>>
>> # Data directory
>> PGDATA="/usr/local/pgsql/data"
>> POSTGIS_GDAL_ENABLED_DRIVERS=__ENABLE_ALL
>>
>> # Who to run the postmaster as, usually "postgres".  (NOT "root")
>> PGUSER=postgres
>>
>> # Where to keep a log file
>> PGLOG="$PGDATA/serverlog"
>>
>> # It's often a good idea to protect the postmaster from being killed
>> by the
>> # OOM killer (which will tend to preferentially kill the postmaster
>> because
>> # of the way it accounts for shared memory).  Setting the
>> OOM_SCORE_ADJ value
>> # to -1000 will disable OOM kill altogether.  If you enable this,
>> you probably
>> # want to compile PostgreSQL with "-DLINUX_OOM_SCORE_ADJ=0", so that
>> # individual backends can still be killed by the OOM killer.
>> #OOM_SCORE_ADJ=-1000
>> # Older Linux kernels may not have /proc/self/oom_score_adj, but
>> instead
>> # /proc/self/oom_adj, which works similarly except the disable value
>> is -17.
>> # For such a system, enable this and compile with "-DLINUX_OOM_ADJ=0".
>> #OOM_ADJ=-17
>>
>

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  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" > <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, Phi

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"  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  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] 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 
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 *  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]

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 
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 :
>
> 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 
>> 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] [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 
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] 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 
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] raster2pgsql is not creating a raster table in postgis

[Bborie Park]

Comments interspersed...

On Wed, Dec 31, 2014 at 12:40 PM, garret  wrote:

> Hello,
>
> Im am attempting to follow along in the book Postgis Cookbook, and am
> importing a raster via raster2pgsql.  So far, unsuccesfully.
>
> *the original code shows:* (Ive altered it only to fit my current set up)
> raster2pgsql -I -C -M -F -t 100x100 worldclim/tmax1.bil chp1.tmax01 | psql
> -d postgis_cookbook -U me -f tmax1.sql
>
> *But I get an error stating, Processing 1/1: tmax1.bil tmax1.sql: No such
> file or directory.*
>
>
This is happening because you have "-f tmax1.sql" specified to psql.



> *So, Ive altered the query to this:*
> raster2pgsql -I -C -M -F -t 100x100 tmax1.bil chp1.tmax1 | psql -d
> postgisCookbook -U gisuser
>
> *the output is:*
> Processing 1/1: tmax1.bil
> BEGIN
> CREATE TABLE
> INSERT 0 1
> INSERT 0 1
> INSERT 0 1
>

snip...


>  addrasterconstraints
> --
>  t
> (1 row)
>
> COMMIT
> VACUUM
>
> ===
>
> So it seems to me to have completed successfully. But then when I go to
> check the raster in QGIS I only see a table with no geometry.
>
>
raster loading through raster2pgsql does not generate any geometries.

You're better off checking the metadata of the loaded rasters...

SELECT ST_Metadata(rast) FROM chp1.tmax1 LIMIT 1

And...

SELECT ST_BandMetadata(rast, 1) FROM chp1.tmax1 LIMIT 1

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

Re: [postgis-users] postgis.gdal_enabled_drivers not working out for me

[Bborie Park]

The GUC postgis.gdal_enabled_drivers is not available in 2.1. Instead you
need to specify the equivalent environmental variable for PostgreSQL.

POSTGIS_GDAL_ENABLED_DRIVERS=ENABLE_ALL

I don't know which OS you're using but at least on Ubuntu, you would add
the above to

/etc/postgresql/9.3/CLUSTER_NAME/environment

where CLUSTER_NAME is the name you gave for the PostgreSQL data cluster.

On Sat, Dec 6, 2014 at 3:42 AM, Rhys A.D. Stewart 
wrote:

> Greetings all,
>
> Tried using st_aspng and got a lovely error saying
>
>ERROR:  rt_raster_to_gdal: Could not load the output GDAL driver
>
> Did some research and found out about postgis.gdal_enabled_drivers. I
> set it to 'ENABLE_ALL'  in postgres.conf and no luck. Tried as a
> session variable but still no luck.
>
> I can't figure out what I am missing.
>
> Here is my version info:
> POSTGIS="2.1.4 r12966" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0,
> 6 March 2012" GDAL="GDAL 1.11.1, released 2014/09/24" LIBXML="2.9.2"
> LIBJSON="UNKNOWN" RASTER
>
> PostgreSQL 9.3.5 on x86_64-pc-linux-gnu, compiled by
> x86_64-pc-linux-gnu-gcc (Funtoo 4.8.2-r3) 4.8.2, 64-bit
>
> Rhys
> Peace & Love|Live Long & Prosper
> ___
> 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  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  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] (no subject)

[Bborie Park]

It looks like your installed is looking for libpq and can't find it. You'll
want to see what PostgreSQL packages are installed... or not installed.

And please don't post to both -devel and -users. Pick one.


On Thu, Jun 19, 2014 at 5:42 PM, Aryeh Hillman 
wrote:

>  Hi all,
>
>  I am trying to compile postgis-2.1.3 from source. I seem to be hitting
> this error:
>
>  configure:20049: error: could not find GDAL
>
>  I do have GDAL installed at /usr/local/lib and I believe ldconfig is
> setup properly.
>
>  Dump from ldconfig --verbose:
> https://gist.github.com/anonymous/65665a274cc7728c57ed
>
>  Dump from failing section of configure.log:
> https://gist.github.com/anonymous/93c03c07e4e8b0030129
>
>  Any ideas? Thanks so much in advance for the help. This is my first time
> installing postgis from source.
>
>  aryeh
>
> ___
> 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
wrote:

> Hello,
>
> below, i paste my DOS interface showing gdalinfo for the srtm file :
>
> SS_gdalinfo.JPG
> 
>
>
> below the information with the postgres query : SELECT
> (ST_SummaryStats(rast)).* FROM public.srtm_test
>
>
> 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] Restoring a dump

[Bborie Park]

Pulling things out of thin air here but is your PostGIS installed in a
separate schema? It makes me wonder as to the "public" schema in
"st_bandmetadata(public.raster,
integer[])".

-bborie


On Mon, May 19, 2014 at 3:34 PM, Jayson Gallardo wrote:

> Well, the the thing is psql is what's initially giving me the error
> that function st_bandmetadata(public.raster, integer[]) does not exist.
>
> Would it be okay to let the restore continue even with those errors? I
> just simply want all the data back into a database so I can get our webapp
> back online.
>
> Our data is static, so I don't see the benefit of dumping each database
> individually. I only did this since we were having issues upgrading
> Postgres and PostGIS, along with other Ubuntu issues.
>
> Thanks for all the help. I will start afresh tomorrow morning.
>
> Cheers,
> Jayson
>
>
> On Mon, May 19, 2014 at 5:24 PM, Bborie Park  wrote:
>
>> Jayson,
>>
>> Too late now but you generally don't want to use pg_dumpall as that dumps
>> a database cluster (all databases and catalog data) into one text (SQL)
>> file. You may need to go down this nasty road...
>>
>> 1. Set up a clean postgresql server
>>
>> 2. Use psql to restore the database cluster to that new server
>>
>> 3. Dump your databases individually using pg_dump and the custom file
>> format ("-F c")
>>
>> 4. On your final database server, restore each database using pg_restore.
>> I usually extract the backup from the custom file format to a text file as
>> I have to modify the search_path parameter to include my schema layout.
>>
>> -bborie
>>
>>
>> On Mon, May 19, 2014 at 3:11 PM, Jayson Gallardo 
>> wrote:
>>
>>> We have about that many raster sources that are tiled into the database.
>>> I would like to try your suggestion, however when I run pg_restore it tells
>>> me that my dump is text format and that I should use psql. I don't see an
>>> option for psql to do a restore schema only.
>>>
>>>
>>> On Mon, May 19, 2014 at 4:14 PM, Jason Mathis <
>>> jmat...@redzonesoftware.com> wrote:
>>>
>>>> Are you getting that error on the postgis_restore.pl?
>>>>
>>>> If yes I am not sure about that one I will leave it up to the pros. I
>>>> think there is a bug open somewhere about this issue…Basically its calling
>>>> a function (to create the constraint) that is in another schema not the one
>>>> you are restoring to and the call is not fully qualified.
>>>>
>>>> In the past with raster tables I have had to go this route:
>>>>
>>>>1. restore schema only
>>>>2. drop all raster constraints for each table
>>>>http://postgis.net/docs/RT_DropRasterConstraints.html
>>>>3. restore the data
>>>>4. add all raster constants back for each table
>>>>http://postgis.net/docs/RT_AddRasterConstraints.htm
>>>>
>>>> I actually had to create a separate script to run through my tables
>>>> because we have about 25-30 raster tables.
>>>>
>>>>
>>>> On May 19, 2014 at 3:02:32 PM, Jayson Gallardo (jaysontra...@gmail.com)
>>>> wrote:
>>>>
>>>>  Yes. The database has rasters. I just tried loading rtpostgis.sql,
>>>> etc., but none of those seemed to help at all. I did load legacy.sql before
>>>> the dump as well.
>>>>
>>>>
>>>> On Mon, May 19, 2014 at 3:54 PM, Jason Mathis <
>>>> jmat...@redzonesoftware.com> wrote:
>>>>
>>>>>
>>>>>  Do you have raster columns/tables?
>>>>>
>>>>> On May 19, 2014 at 2:47:47 PM, Jayson Gallardo (jaysontra...@gmail.com)
>>>>> wrote:
>>>>>
>>>>>   So, I finally got around to running the restore, but I get this
>>>>> following error:
>>>>>
>>>>> ERROR:  function st_bandmetadata(public.raster, integer[]) does not
>>>>> exist
>>>>>
>>>>> Any ideas?
>>>>>
>>>>>
>>>>> On Mon, May 19, 2014 at 12:29 PM, Jason Mathis <
>>>>> jmat...@redzonesoftware.com> wrote:
>>>>>
>>>>>>  What do you mean, starting from scratch? You have to install
>>>>>> postgresql/postgis and then follow the answers/links from sandro. You
>>>>>> should be good with that dump.
>&g

Re: [postgis-users] Problem with postgis raster

[Bborie Park]

QGIS can't establish a database connection. Are you able to do the same
through gdalinfo (since QGIS using GDAL under the hood)?


On Mon, May 19, 2014 at 3:31 PM, dandrigo
wrote:

> Hello,
>
> i import the raster with -t option. The import seems work fine. But
> regarding the visualization of the data in qgis i have an error message. I
> paste below this message :
>
> SS_error_pg_rasrter.JPG
> 
>
>
>
> --
> View this message in context:
> http://postgis.17.x6.nabble.com/Problem-with-postgis-raster-tp5006344p5006349.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] Restoring a dump

[Bborie Park]

Jayson,

Too late now but you generally don't want to use pg_dumpall as that dumps a
database cluster (all databases and catalog data) into one text (SQL) file.
You may need to go down this nasty road...

1. Set up a clean postgresql server

2. Use psql to restore the database cluster to that new server

3. Dump your databases individually using pg_dump and the custom file
format ("-F c")

4. On your final database server, restore each database using pg_restore. I
usually extract the backup from the custom file format to a text file as I
have to modify the search_path parameter to include my schema layout.

-bborie


On Mon, May 19, 2014 at 3:11 PM, Jayson Gallardo wrote:

> We have about that many raster sources that are tiled into the database. I
> would like to try your suggestion, however when I run pg_restore it tells
> me that my dump is text format and that I should use psql. I don't see an
> option for psql to do a restore schema only.
>
>
> On Mon, May 19, 2014 at 4:14 PM, Jason Mathis  > wrote:
>
>> Are you getting that error on the postgis_restore.pl?
>>
>> If yes I am not sure about that one I will leave it up to the pros. I
>> think there is a bug open somewhere about this issue…Basically its calling
>> a function (to create the constraint) that is in another schema not the one
>> you are restoring to and the call is not fully qualified.
>>
>> In the past with raster tables I have had to go this route:
>>
>>1. restore schema only
>>2. drop all raster constraints for each table
>>http://postgis.net/docs/RT_DropRasterConstraints.html
>>3. restore the data
>>4. add all raster constants back for each table
>>http://postgis.net/docs/RT_AddRasterConstraints.htm
>>
>> I actually had to create a separate script to run through my tables
>> because we have about 25-30 raster tables.
>>
>>
>> On May 19, 2014 at 3:02:32 PM, Jayson Gallardo (jaysontra...@gmail.com)
>> wrote:
>>
>>  Yes. The database has rasters. I just tried loading rtpostgis.sql,
>> etc., but none of those seemed to help at all. I did load legacy.sql before
>> the dump as well.
>>
>>
>> On Mon, May 19, 2014 at 3:54 PM, Jason Mathis <
>> jmat...@redzonesoftware.com> wrote:
>>
>>>
>>>  Do you have raster columns/tables?
>>>
>>> On May 19, 2014 at 2:47:47 PM, Jayson Gallardo (jaysontra...@gmail.com)
>>> wrote:
>>>
>>>   So, I finally got around to running the restore, but I get this
>>> following error:
>>>
>>> ERROR:  function st_bandmetadata(public.raster, integer[]) does not exist
>>>
>>> Any ideas?
>>>
>>>
>>> On Mon, May 19, 2014 at 12:29 PM, Jason Mathis <
>>> jmat...@redzonesoftware.com> wrote:
>>>
  What do you mean, starting from scratch? You have to install
 postgresql/postgis and then follow the answers/links from sandro. You
 should be good with that dump.

  good luck!


 On May 19, 2014 at 10:22:38 AM, Jayson Gallardo (jaysontra...@gmail.com)
 wrote:

  Here's the thing, all I have is a dump (postgres_dump_03172014.gz)
 using pg_dumpall. The server has been reformatted with a clean install of
 Debian. I haven't even installed Postgres yet. So, what are my options
 then? I really would hate to start from scratch.


 On Mon, May 19, 2014 at 10:15 AM, Sandro Santilli wrote:

> On Mon, May 19, 2014 at 09:39:20AM -0500, Jayson Gallardo wrote:
> > Hey, all. So, we recently re-did our server. We were previously
> running
> > Ubuntu, and now we are running Debian. I had performed a pg_dumpall
> on our
> > Postgres database (with PostGIS) and would now like to restore said
> dump.
> > However, I want to make sure I do it right.
>
> Follow the instructions here:
>
>
>> http://postgis.net/docs/manual-2.1/postgis_installation.html#hard_upgrade
>>
>>   > Since I dumped everything, will I need to create all the databases
> before
> > restoring the dump?
> >
> > I should probably install the Postgis plug-in first, right?
>
> You should create the databases and spatially-enable them
> (could be a chance to switch to EXTENSION-based install).
>
> > My dump is pretty big at 101 GB (it contains 3m resolution DEMs of
> Iowa).
> > Is there anything I need to do in order to minimize errors in the
> restore?
>
> I guess you could reinstall each database separately, to at least
> be more in control of what fails ...
>
> --strk;
>
>  ()  ASCII ribbon campaign  --  Keep it simple !
>  /\  http://strk.keybit.net/rants/ascii_mails.txt
> ___
> 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


 This

Re: [postgis-users] Problem with postgis raster

[Bborie Park]

raster2pgsql does not create a "geom" column. You should consider tiling
your rasters though with -t WIDTHxHEIGHT.

As for visualizing with QGIS, I have no suggestions. You can do a quick
spot-check that the data is present and valid in PostGIS though...

SELECT (ST_SummaryStats(rast)).* FROM public.srtm_test

Compare the stats with that from

gdalinfo --stat C:\picardie-nature\Stage\Qgis\Alti\srtm\srtm_37_02.tif

-bborie


On Mon, May 19, 2014 at 2:26 PM, dandrigo
wrote:

> Dear all,
>
> I tried to import via pgraster2pgsql several DEM raster files.
>
> Below, i paste a screenshot representing my DOS interface :
>
> SS_pg_raster.JPG
> 
>
>
> After the import, it's impossible to visualize the postgis raster layer via
> qgis. And via qgis db manager or pgadmin3, we can notice thaht the import
> did not create a geom column. Via qgis db manager, no overview of the layer
> is visible.
>
> Below the qgis db manager showing the characteristic of this layer :
>
> qgis_dbmanager.JPG
> 
>
>
> I'm working with windows 8 with postgres 9. and postgis 2.1
> In advance thank you t othrow light for me.
>
>
>
> --
> View this message in context:
> http://postgis.17.x6.nabble.com/Problem-with-postgis-raster-tp5006344.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]

I think your query will explode...


On Tue, May 6, 2014 at 7:01 AM, Pierre Racine
wrote:

> 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
> >  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] Raster ST_Union memory limit

[Bborie Park]

RAM available.

-bborie


On Tue, May 6, 2014 at 6:35 AM, Pierre Racine
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

Re: [postgis-users] [postgis-devel] PSC/Development Vote -- Baris to release upcoming 2.1.3 jars and manually sign

[Bborie Park]

+1 for release of 2.1.3 jar.


On Sat, Apr 19, 2014 at 5:47 PM, Paragon Corporation  wrote:

>  Baris,
>
> Regarding this:
>
> > Here is my proposal: I can take care of the last  targeted 2.1 release
> which is 2.1.3 and change pom.xmls as I did in the trunk and apply shanes
> patches and after the release of 2.1.3 I can manually sign the jars and
> submit them to OSSRH. And keep discussing the separation of
> > repositories on trunk.
>
> +1 for that.  Others in PSC and development please vote on this as well so
> we can start moving on this.
>
> I think we'll be releasing a 2.1.3 fairly soon.  2.2.0 we are looking at
> at least another 4 months and that's being hopeful :)
>
> Thanks,
> Regina
> http://www.postgis.us
> http://postgis.net
>
>
>  --
> *From:* postgis-users-boun...@lists.osgeo.org [mailto:
> postgis-users-boun...@lists.osgeo.org] *On Behalf Of *Baris Ergun
> *Sent:* Saturday, April 19, 2014 4:50 AM
> *To:* PostGIS Users Discussion
> *Cc:* PostGIS Development Discussion
> *Subject:* Re: [postgis-users] [postgis-devel] looking for a public well
> known maven repository for postgis-jdbc 2.1 and later on versions
>
>  Hi Shane;
>
> People are busy with other stuff so until we decide to separate the
> repositories for postgis java projects I have applied ur patches to both
> jdbc and jdbc_jtsparser projects. So what this means is "when we release
> 2.2.0 postgis I would be ready to submit these two jars manually to OSSRH".
>
> Baris
>
>
> On 7 April 2014 21:26, Shane StClair  wrote:
>
>>  Hi Baris,
>>
>> Oops, I didn't see that you were already chasing this down. Your approach
>> sounds great, thanks for taking the lead on this! In the past there have
>> been issues with other projects having non-resolvable dependencies on
>> versions of postgis-jdbc (Hibernate Spatial), so it will be great to have
>> these artifacts in the central repo.
>>
>> Best,
>> Shane
>>
>>
>> On Sun, Apr 6, 2014 at 2:29 AM, Baris Ergun wrote:
>>
>>> Hi Shane;
>>>
>>> Thanks for ur patch and consideration. I think we are on the same path
>>> if u check the issue #2626. I have applied the OSSRH and got the
>>> credentials for being able to upload postgis jdbc jars to central maven
>>> rep. For that case I have separated the jdbc and jdbc-jtsparser jars on the
>>> trunk and updated the pom.xml's according to Central maven repository
>>> requirements.. On the trunk actually I was waiting to agree  with all the
>>> developers to separate the postgis jdbc jars  repository and continue with
>>> 1st suggested approach.  If we do that way we wouldnt need to make the
>>> explicit plugin declarations as you patched in 2702.
>>>
>>> Here is my proposal: I can take care of the last  targeted 2.1 release
>>> which is 2.1.3 and change pom.xmls as I did in the trunk and apply shanes
>>> patches and after the release of 2.1.3 I can manually sign the jars and
>>> submit them to OSSRH. And keep discussing the separation of repositories on
>>> trunk.
>>>
>>>
>>>  On 6 April 2014 05:32, Paragon Corporation  wrote:
>>>
   I don't have any objections.  +1 for that.

 Thanks,
 Regina

  --
  *From:* postgis-users-boun...@lists.osgeo.org [mailto:
 postgis-users-boun...@lists.osgeo.org] *On Behalf Of *Shane StClair
 *Sent:* Saturday, April 05, 2014 5:24 PM
 *To:* PostGIS Users Discussion
 *Cc:* PostGIS Development Discussion
 *Subject:* Re: [postgis-users] looking for a public well known maven
 repository for postgis-jdbc 2.1 and later on versions

   Sounds good. Any objection to the community manually uploading
 pre-2.2 versions to Maven Central?

 Thanks,
 Shane


 On Sat, Apr 5, 2014 at 1:38 PM, Paragon Corporation wrote:

>  Shane,
> I think we can change our build bot to upload to maven as part of the
> official release job when we build tagged docs etc.  Have to talk over 
> with
> rest of PostGIS psc and dev folks on that.
>
> I guess before that we'll need to completely cleanup the issues with
> our current jdbc which sounds like we are close.  Thanks for the patch 
> BTW.
>
> Thanks,
> Regina Obe
> PostGIS PSC member
> http://www.postgis.us
> http://postgis.net
>
>
>
>  --
> *From:* postgis-users-boun...@lists.osgeo.org [mailto:
> postgis-users-boun...@lists.osgeo.org] *On Behalf Of *Shane StClair
> *Sent:* Saturday, April 05, 2014 3:29 PM
> *To:* postgis-users@lists.osgeo.org
> *Subject:* [postgis-users] looking for a public well known maven
> repository for postgis-jdbc 2.1 and later on versions
>
>   I just submitted a patch [1] to generate javadoc and source jars as
> part of the postgis-jdbc build. Otherwise, the existing pom meets the 
> Maven
> Central sync requirements [2].
>
> There are two options for getting up to date postgis-jd

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  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  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] Accessing lots of missing outdb/offline rasters appear to cause PostgreSQL to crash

[Bborie Park]

Strange that you're getting a process termination. The problem with loading
out-db rasters was just resolved in 2.0.5 and 2.1.2...

http://postgis.net/2014/03/31/postgis-2.1.2

The specific ticket is #2607...

Can you trying upgrading and see if the problem persists? I've never seen
the process terminate whenever I've hit the open file limit.

-bborie


On Mon, Mar 31, 2014 at 9:44 AM, 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  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\system32>set PGCLIENTENCODING=UTF8
>
> C:\WINDOWS\system32>psql -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]

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"  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] 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"  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\system32>more < 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\system32>psql -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:
>
> 
>
> 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]

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

-bborie
On Mar 28, 2014 5:30 PM, "georgew"  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\system32>more < 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\system32>psql -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:
>
> 
>
> 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"  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]

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  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\system32>more < 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\system32>psql -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:
>
> 
>
> 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] 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  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]

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  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] 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  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] ST_Union() about 1179 raster-tiles cause ProgreSQL crashes

[Bborie Park]

We'll need a bit more info before any viable advice can be given. How big
is that polygon for the overlaps? Any error messages in the postgresql
logs? Is there just one date's scenes in the table or is it longitudinal?

-bborie
On Mar 17, 2014 9:16 AM, "Jackie"  wrote:

>  Hi PostGIS users,
> I have a PostGIS database, which stores raster from Landsat 8. Each scene
> was divided with tile-size 500x500. Then each row in database will keep 1
> tile with 11 bands (except band No. 8).
> I'm trying to ST_Union() about 1179 raster-tiles with 3 bands (2,3,4) and
> pgAdminIII shows a window said "Connection lost - Try to reconnect
> database";
> Here's my query:
> SELECT ST_Union(rast, ARRAY[ROW(2,'LAST'),ROW(3,'LAST'),ROW(4,'LAST')]::
> unionarg[])
> FROM landsat8
> WHERE ST_Overlaps(polygon, rast_geom_4326)
>
>  I'm using PostGIS 2.1, my raster is retrieved from earthexplorer.usgs.gov
>
> ___
> 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  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.09"W,  5d51'10.78"S)
> Lower Left  (1913.500,   20888.500) (  1d21'30.09"W,  5d51'10.75"S)
> Upper Right (1914.500,   20887.500) (  1d21'30.06"W,  5d51'10.78"S)
> Lower Right (1914.500,   20888.500) (  1d21'30.07"W,  5d51'10.75"S)
> Center  (1914.000,   20888.000) (  1d21'30.08"W,  5d51'10.76"S)
> 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]

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  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 
> 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 
> >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] 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  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] 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  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  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" 
>> 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 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
>>>>> ps

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" 
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  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] stddev from average cell value of a raster

[Bborie Park]

Your usage of ST_SummaryStats() won't work. ST_SummaryStats provides the
average and the standard deviation of the input raster...

SELECT (ST_SummaryStats(rast)).mean, (ST_SummaryStats(rast)).stddev FROM
mytable

-bborie


On Fri, Jan 31, 2014 at 5:59 AM,  wrote:

>  Dear All,
>
>
>
> I have a raster in which the pixel values ranges from 0 - 1. I want to
> find the standard deviation of from the average cell value of the raster
> for which I use a query as under. The query can be executed but do not see
> any values in data output window. I initially thought that it could be
>  smaller cell values, and to compensate that I use a factor of 10^10 but
> still the problem remains. Can someone please suggest where I am going
> wrong.
>
>
>
> with
>
> cellscores as (
>
> select rid, gaztext_id, dist_meter, direction,sigma_dist,
> sigma_dir,sdis,sdir, (10^10) * cellvalue as cellvalue, maxprob, rastersize
>
> from cellscores_new_final_raster_parm_disdir_spatialhint where
> gaztext_id=2502 and count=1 and rid=651
>
> )
>
> ,avg_value as (
>
> select c.rid, c.gaztext_id, c.dist_meter, c.direction,c.sigma_dist,
> c.sigma_dir,c.sdis,c.sdir, c.cellvalue, c.maxprob, c.rastersize, n.rast,
>
> (10^10)*(sum(st_summarystats(n.rast))/c.rastersize) as average_cellvalue
>
> from cellscores c JOIN new_final_raster_parm_disdir_spatialhint n ON
> n.rid=c.rid
>
> order by c.gaztext_id
>
> )
>
> select stddev(sum(st_summarystats(rast))/rastersize)
>
> from avg_value
>
>
>
>
>
> Tanks and Regards,
>
> Gaurav
>
> ___
> 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]

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

Re: [postgis-users] Migration from 1.3.1 to 2.0

[Bborie Park]

The only thing that should be different is that the geometry datatype will
not be constrained (geometry vs geometry(POINT, 4326)). It'll probably be
constrained using the traditional column constraints.

Otherwise, I don't see any problems. Just make sure to have a full backup
of the DB.

-bborie


On Fri, Jan 24, 2014 at 8:53 PM, Stephen Woodbridge  wrote:

> Hi,
>
> I need to migrate a database
>
> from:
>
> PostgreSQL 8.2.14
> POSTGIS="1.3.1" GEOS="2.2.3-CAPI-1.1.1" PROJ="Rel. 4.5.0, 22 Oct 2006"
> USE_STATS
>
> to:
>
> Postgresql 9.2.x
> POSTGIS 2.0.x
>
> Will using pg_dump to dump just the tables I need in plain text format,
> then load them into a new database with the postgis extension work? or will
> I run into problems doing this?
>
> Thanks,
>   -Steve W
> ___
> 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"  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  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"  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 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  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]

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"  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  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  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]

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

-bborie


On Wed, Jan 15, 2014 at 7:25 PM, Marco  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] Copy raster data to other tables in PostGIS

[Bborie Park]

Remove the ST_MakeEmptyRaster() from your INSERT statement.

-bborie


On Tue, Jan 7, 2014 at 12:31 PM, Gerardo Zárate
wrote:

> I'm experimenting with rater in PostGIS 2.1.1. I manage to load a raster
> file into a table using raster2pgsql in Windows 7. I loaded the raster file
> into a single row of the table and displayed in QGIS 1.8 and QGIS 2.0.
>
> What I'm trying to accomplish is a table with multiple raster files like
> the following example:
>
> CREATE TABLE public.dummy_raster
> (
>   rid integer NOT NULL,
>   rast1 raster,
>   rast2 raster,
>   CONSTRAINT dummy_raster_pkey PRIMARY KEY (rid)
> );
>
> I tried to copy the raster column of the table created by raster2pgsql to
> my dummy table using the following code.
>
> INSERT INTO dummy_raster (rid, rast1)
> SELECT 1, ST_MakeEmptyRaster(rast)
>   FROM rastertest WHERE rid = 1;
>
> UPDATE dummy_raster SET rast1 = (SELECT ST_AsRaster(NULL,p.rast) FROM
> rastertest p WHERE p.rid=1) WHERE rid=1;
> COMMIT;
>
> Both statements were executed successfully but when I try to import the
> raster data into QGIS nothing is displayed.
>
> Is this the correct way to copy raster data from one column to other or is
> there a better way to do this?
>
> Best regards,
>
> Gerardo Zárate
>
>
>
>
>
> ___
> 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  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
wrote:

> 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  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
>>>  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] ST_RotateX with pointOrigin!

[Bborie Park]

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

Re: [postgis-users] Old question resurfacing

[Bborie Park]

You could consider using pointcloud

https://github.com/pramsey/pointcloud

-bborie
On Dec 10, 2013 9:15 AM, "Gerry Creager - NOAA Affiliate" <
gerry.crea...@noaa.gov> wrote:

> Raster is always a possibility, but we lose some data therein. If I use
> what we refer to as "Level III" data, then it's certainly a potential. I'm
> sorta thinking of using Level II data which comprise
> azimuth/range/elevation and one of: reflectivity, radial velocity or
> spectrum width (standard deviation of velocity). The programmatic results
> of this, creating new Level III data products, are big.
>
> Thanks for the suggestion. Simply taking it to raster hadn't, honestly,
> occurred to me because I was overthinking some aspects of the problem.
>
> gerry
>
>
> On Tue, Dec 10, 2013 at 11:00 AM, George Silva wrote:
>
>> If you transform it to rasters PostGIS Raster can handle them already.
>>
>> Now, about the new datatype, I'll let the experts discuss :P.
>>
>> Cheers
>>
>>
>> On Tue, Dec 10, 2013 at 2:52 PM, Gerry Creager - NOAA Affiliate <
>> gerry.crea...@noaa.gov> wrote:
>>
>>> I asked this years ago, and I think Paul was less than pleased with me
>>> (:-), but:
>>>
>>> Has anyone, in the ensuing years looked at encoding radar data into a
>>> postGIS database? We've a little idea that might benefit one project, and
>>> getting the radar data into a good geospatial format would be
>>> beneficial.The data, of coure, would start out as radial-distance and
>>> intensity from the radar site, although we could preprocess it by gridding.
>>>
>>> Thanks, Gerry
>>> --
>>> Gerry Creager
>>> NSSL/CIMMS
>>> 405.325.6371
>>> ++
>>> “Big whorls have little whorls,
>>> That feed on their velocity;
>>> And little whorls have lesser whorls,
>>> And so on to viscosity.”
>>> Lewis Fry Richardson (1881-1953)
>>>
>>> ___
>>> postgis-users mailing list
>>> postgis-users@lists.osgeo.org
>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>
>>
>>
>>
>> --
>> George R. C. Silva
>> SIGMA Consultoria
>> 
>> http://www.consultoriasigma.com.br/
>>
>> ___
>> postgis-users mailing list
>> postgis-users@lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>
>
>
>
> --
> Gerry Creager
> NSSL/CIMMS
> 405.325.6371
> ++
> “Big whorls have little whorls,
> That feed on their velocity;
> And little whorls have lesser whorls,
> And so on to viscosity.”
> Lewis Fry Richardson (1881-1953)
>
> ___
> 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.backend -- how to set?

[Bborie Park]

SET postgis.backend = geos;

The docs for postgis.gdal_datapath have examples...

http://postgis.net/docs/manual-dev/postgis_gdal_datapath.html

-bborie


On Sat, Dec 7, 2013 at 9:40 PM, Stephen Mather wrote:

> The title says it all-- I'm sure I'm being dense here, but how and where
> do I set postgis.backend?  AFAIU, I can set it at multiple levels from
> database to session, but have no idea where to begin for GUCs... .
>
> Thanks in advance,
> Best,
> 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] 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  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] st_band with out-db raster data

[Bborie Park]

Correct. ST_Band creates a new raster comprised of the bands extracted from
the existing raster.

If you're looking looking to convert an out-db into in-db, I think the
following will work...

SELECT (ST_AsBinary(rast, TRUE))::raster

-bborie


On Fri, Nov 1, 2013 at 8:10 AM, Dilley, Andrew-P57500 <
andrew.dil...@gdc4s.com> wrote:

> From that thread,
>
> "We need to do away with the entire in-db and out-db notion and just
> return the pixel values."
>
> I guess this is what I mean.  Should st_band be doing this?  (I'm
> admittedly very new to postgis so maybe this a stupid question).
>
> andy
>
> -Original Message-
> From: postgis-users-boun...@lists.osgeo.org [mailto:
> postgis-users-boun...@lists.osgeo.org] On Behalf Of Mateusz Loskot
> Sent: Friday, November 01, 2013 10:44 AM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] st_band with out-db raster data
>
> On 1 November 2013 14:18, Dilley, Andrew-P57500 
> wrote:
> > When using out-db raster data, st_band appears to return path data for
> > the raster file rather than a raster.  Is that intentional?
>
> You may want to check the "[raster] Paths local to server" thread here
>
> http://lists.osgeo.org/pipermail/postgis-devel/2013-February/023252.html
>
> and related ticket  http://trac.osgeo.org/postgis/ticket/2217
>
> It should clarify the semantic of out-db raster.
>
> Best regards,
> --
> Mateusz  Loskot, 
> http://mateusz.loskot.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
>
___
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Re: [postgis-users] st_band with out-db raster data

[Bborie Park]

Can you elaborate on what you mean by "return path data"? If ST_Band is
used on a raster band that is out-db, the new raster returned will have a
band that is also out-db.

Are you trying to make out-db in-db?

-bborie


On Fri, Nov 1, 2013 at 7:18 AM, Dilley, Andrew-P57500 <
andrew.dil...@gdc4s.com> wrote:

>  When using out-db raster data, st_band appears to return path data for
> the raster file rather than a raster.  Is that intentional?
>
> ** **
>
> 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] [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  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
wrote:

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

> 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.
>
>
I wonder what is going on to make the point on raster overlay in PostGIS
slower than in R. I'd have expected the numbers to be about the same as
that operation is conceptually very simple.

-bborie
___
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 wrote:

> 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 wrote:
>
>> 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
>>> >  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
>>> >  wrote:
>>> &g

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  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] 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  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-**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,  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] '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] 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  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] Problem with ST_MapAlgebraExpr

[Bborie Park]

Your expression is not using the correct placeholders. It should look
something like:

...
ST_MapAlgebraExpr(a.maxrast,b.minrast,'[rast1.val] * [rast2.val]',
'INTERSECTION', 'NULL', 'NULL', 'NULL') as rast from max_rast a, min_rast b



On Tue, Sep 10, 2013 at 12:36 PM,  wrote:
>
> Dear Postgis Community,
>
>
>
> I have two rasters and I want to create new raster using intersection
through ST_MapALgepraExpre() function. The new raster should have pixel
values as max pixel values from either of the two rasters. I guess that I
want to use product of these two rasters for what I want in new raster. I
use following query and encounter the problem. I am sure there is something
wrong in my query with expression. Can someone please guide me how to
correct it. My Postgis version is of data "2012-03-29 09:07:33" and raster
lib build data "2012-03-29 09:07:33"
>
>
>
> With env as(
>
> select rid, gaztext_id, st_area(ST_Envelope(rast)) as area, rast from
text_rast_intersection ),
>
> max_rast as(select e.rid, e.gaztext_id, e.rast as maxrast from env e, env
b where e.area > b.area )
>
> ,min_rast as (select e.rid, e.gaztext_id, e.rast as minrast from env e,
env b where e.area < b.area )
>
> select '222' as rid, 964 as gaztext_id,
>
> st_MapAlgebraExpr(a.maxrast,b.minrast,'minrast'*'mnaxrast',
'INTERSECTION', 'NULL', 'NULL', 'NULL') as rast from max_rast a, min_rast b
>
>
>
> ERROR:
>
> ERROR:  operator is not unique: unknown * unknown
>
> LINE 14: st_MapAlgebraExpr(a.maxrast,b.minrast,'minrast'*'mnaxrast', ...
>
> ^
>
> HINT:  Could not choose a best candidate operator. You might need to add
explicit type casts.
>
>
>
> ** Error **
>
>
>
> ERROR: operator is not unique: unknown * unknown
>
> SQL state: 42725
>
> Hint: Could not choose a best candidate operator. You might need to add
explicit type casts.
>
> Character: 468
>
>
>
>
>
> Thanks,
>
> Gaurav
>
>
>
>
> ___
> 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]

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 

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  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
>> (_rast

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] St_Union crashing

[Bborie Park]

On Wed, Jul 31, 2013 at 4:00 PM, David Rowley  wrote:

>
>
> On Thu, Aug 1, 2013 at 10:10 AM, Bborie Park  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]

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  wrote:

> On Thu, Aug 1, 2013 at 10:01 AM, Bborie Park  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]

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

On Wed, Jul 31, 2013 at 12:09 PM, David Rowley  wrote:

> On Thu, Aug 1, 2013 at 7:00 AM, Bborie Park  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]

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  wrote:

>
> On Thu, Aug 1, 2013 at 6:47 AM, Bborie Park  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 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
>>&

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  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] 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 wrote:

> 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  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 
>> wrote:
>>
>>> 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 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.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 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.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_wi

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 wrote:

> 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  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 
>> 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 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.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 wrote:
>>>>>
>>>>>> Jayson,
>>>>>>
>>>>>> Can you share one of the queries? Also, what check constraints are
>>>>>> you using?
>>>>>>
>>>>>> -bborie
>>>>>>
>>>>>

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 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  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 
>> 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 wrote:
>>>
>>>> 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 wrote:
>>>>>>
>>>>>>> I'm just glad to help. Feel free to post you

Re: [postgis-users] Querying Multiple Rasters

[Bborie Park]

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 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  wrote:
>
>> 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 
>> 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 >> > 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 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 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.
>>>>>>>
>>>>>>> -b

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

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 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  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 
>> 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 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 wrote:
>>>>>
>>>>>> 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"  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:
>>>>>>>>
>>>>>>>>> Th

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 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  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 > > 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 wrote:
>>>
>>>> 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"  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 
>>>>>>> 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