Re: [postgis-users] ST_StartPoint fails with MultiLineString

2011-11-24 Thread Jose Carlos Martinez Llario 
I agree that imagination and creative thinking is a good thing as 
PostGIS did improving the standard (not following it) many times.

But we are not talking about that now.
ST_StartPoint, ST_EndPoint, ST_GeometryN, etc. dont have an homogenous 
behavior, and others functions either.


If you guys are changing a lot of things in PostGIS 2.0 (what is really 
nice),  why you dont try to use
a homogenous  approach to these kind of functions?  I dont mind if 
following the standard or not  but my opinion is that PostGIS team 
should chose one side. If not we users are going to need a memo with 
every function to know what is the expected behavior because it does not 
follow any pattern, it means PostGIS is less easy to use than it could be.


Anyways this is just an opinion that I wanted to share with you.
Jose

On 24/11/2011 23:17, Paul Ramsey wrote:

On Thu, Nov 24, 2011 at 1:39 PM, Sandro Santilli  wrote:


I guess nowadays it's not that worth being silly to follow
the standard ...

Right, non-brain-dead behavior should be our default. Silly that we
have st_npoints that works and st_numpoints that doesn't.
P.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users


___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] ST_StartPoint fails with MultiLineString

2011-11-24 Thread Sandro Santilli 
On Thu, Nov 24, 2011 at 02:17:30PM -0800, Paul Ramsey wrote:
> On Thu, Nov 24, 2011 at 1:39 PM, Sandro Santilli  wrote:
> 
> > I guess nowadays it's not that worth being silly to follow
> > the standard ...
> 
> Right, non-brain-dead behavior should be our default. Silly that we
> have st_npoints that works and st_numpoints that doesn't.

Indeed.
It was clearer when one of the two didn't have the 'st_' prefix.
You could tell the broken one was the other...

--strk; 

  ()   Free GIS & Flash consultant/developer
  /\   http://strk.keybit.net/services.html
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] ST_StartPoint fails with MultiLineString

2011-11-24 Thread Paul Ramsey 
On Thu, Nov 24, 2011 at 1:39 PM, Sandro Santilli  wrote:

> I guess nowadays it's not that worth being silly to follow
> the standard ...

Right, non-brain-dead behavior should be our default. Silly that we
have st_npoints that works and st_numpoints that doesn't.
P.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] ST_StartPoint fails with MultiLineString

2011-11-24 Thread Sandro Santilli 
On Thu, Nov 24, 2011 at 05:06:20PM +0100, Jose Carlos Martinez wrote:
> Ok, I understand the strictness but what makes me confused if the
> mixed policy about standards.
> 
> For example:
> PostGIS 1.5 -> ST_StartPoint and ST_Endoints with Multi types are
> working  (against SQL/MM)
> PostGIS 2.0 -> ST_StartPoint and ST_Endoints with Multi types return
> null  (folllow SQL/MM)
> 
> PostGIS 1.5 -> ST_GeometryN (geom, 1) with simple types returns null
> (folllow SQL/MM)
> PostGIS 2.0 -> ST_GeometryN (geom, 1) with simple types are working
> (against SQL/MM)
> 
> In some cases it follows the standards but in other cases it follows
> the usability.

It all started with 'ST_' enforcement.
Before that, we could be silly with 'ST_' names and smart w/out.

I guess nowadays it's not that worth being silly to follow
the standard ...

File tickets and fight for them !

--strk;

  ()   Free GIS & Flash consultant/developer
  /\   http://strk.keybit.net/services.html
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Installing PostGIS on FreeBSD : trouble with libpq

2011-11-24 Thread Sandro Santilli 
On Thu, Nov 24, 2011 at 10:11:32AM -0600, Dheeraj Chand wrote:

> creating template1 database in /usr/local/pgsql/data/base/1 ... FATAL:  could 
> not create semaphores: No space left on device
> DETAIL:  Failed system call was semget(1, 17, 03600).
> HINT:  This error does *not* mean that you have run out of disk space.
>It occurs when either the system limit for the maximum number
>of semaphore sets (SEMMNI), or the system wide maximum number
>of semaphores (SEMMNS), would be exceeded.  You need to raise
>the respective kernel parameter.  Alternatively, reduce
>PostgreSQL's consumption of semaphores by reducing its
>max_connections parameter. The PostgreSQL documentation contains
>more information about configuring your system for PostgreSQL.
[...]
> Now, this is on FreeBSD in a jail. I have no idea how to make the
> changes it's asking for in order to initialise. Any suggestions?

Did you actually _read_ that message ? Particularly the HINT ?

--strk;

  ()   Free GIS & Flash consultant/developer
  /\   http://strk.keybit.net/services.html
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] How to clean a geometrycollections table for pgsql2shp?

2011-11-24 Thread Muhammad Imran 

Hi Pierre,

You can try st_collectionextract(geometry, integer) to extract polygon 
geometery by specifying (3 == POLYGON as integer),  given your 
GEOMETRYCOLLECTION of points, lines and polygons.

Regards,
Muhammad Imran
ITC, The Netherlands


--- On Thu, 11/24/11, Pierre Racine  wrote:

> From: Pierre Racine 
> Subject: [postgis-users] How to clean a geometrycollections table for 
> pgsql2shp?
> To: " (postgis-users@postgis.refractions.net)" 
> 
> Date: Thursday, November 24, 2011, 10:19 PM
> Hi,
> 
> When doing intersections between two tables containing many
> polygons the resulting table often contains
> geometrycollections of points, polylines and polygons. You
> have to clean such a table of point and lines if you want to
> export it as a polygon shapefile.
> 
> Isn't there a function in PostGIS to remove line and points
> from a table of geometrycollections?
> 
> SELECT ST_ExtractPolygons(geom) FROM mymessyresults ?
> 
> Thanks
> 
> Pierre 
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] How to clean a geometrycollections table for pgsql2shp?

2011-11-24 Thread Pierre Racine 
Hi,

When doing intersections between two tables containing many polygons the 
resulting table often contains geometrycollections of points, polylines and 
polygons. You have to clean such a table of point and lines if you want to 
export it as a polygon shapefile.

Isn't there a function in PostGIS to remove line and points from a table of 
geometrycollections?

SELECT ST_ExtractPolygons(geom) FROM mymessyresults ?

Thanks

Pierre 
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Installing PostGIS on FreeBSD : trouble with libpq

2011-11-24 Thread Dheeraj Chand 
It was whichever one was in the ports tree, and currently is in the ports tree. 
Current update:

1. I deinstalled 8.4.9 completely, 
2. I deinstalled PostGIS completey,
3. I installed 9.1

When I tried to initialise the PSQL upon startup, I saw the following:

dirty:/home/dheerajchand# sudo  /usr/local/etc/rc.d/postgresql initdb
postgresql_enable=YES: not found
The files belonging to this database system will be owned by user "pgsql".
This user must also own the server process.

The database cluster will be initialized with locale C.
The default text search configuration will be set to "english".

creating directory /usr/local/pgsql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 10
selecting default shared_buffers ... 400kB
creating configuration files ... ok
creating template1 database in /usr/local/pgsql/data/base/1 ... FATAL:  could 
not create semaphores: No space left on device
DETAIL:  Failed system call was semget(1, 17, 03600).
HINT:  This error does *not* mean that you have run out of disk space.  It 
occurs when either the system limit for the maximum number of semaphore sets 
(SEMMNI), or the system wide maximum number of semaphores (SEMMNS), would be 
exceeded.  You need to raise the respective kernel parameter.  Alternatively, 
reduce PostgreSQL's consumption of semaphores by reducing its max_connections 
parameter.
The PostgreSQL documentation contains more information about 
configuring your system for PostgreSQL.
child process exited with exit code 1
initdb: removing data directory "/usr/local/pgsql/data"
dirty:/home/dheerajchand# 

Now, this is on FreeBSD in a jail. I have no idea how to make the changes it's 
asking for in order to initialise. Any suggestions?

Dheeraj



On Nov 24, 2011, at 6:17 AM, Ben Madin wrote:

> Which version of PostgreSQL did you install?
> 
> cheers
> 
> Ben
> 
> 
> On 24/11/2011, at 4:47 PM, dhee...@dheerajchand.com wrote:
> 
>> 
>> Mark Stosberg  summersault.com> writes:
>> 
>>> 
>>> On 10/27/2011 08:34 PM, Dheeraj Chand wrote:
 Hi, all,
 
 I'm not that great with BSD, but I'm running into this error trying to
>> install from the ports tree. Anyone
>>> willing to help?
>>> 
>>> What version of FreeBSD are you using? Did you install PostgreSQL 8.4.9
>>> from ports, a package, or by hand?
>>> 
>>>  Mark
>> 
>> 
>> 
>> ACK! I never got this email. Stupid junk filters. It all came from ports. 
>> I've
>> done nothing by hand.
>> 
>> Dheeraj
>> 
>> 
>> ___
>> postgis-users mailing list
>> postgis-users@postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] ST_StartPoint fails with MultiLineString

2011-11-24 Thread Jose Carlos Martinez 
Ok, I understand the strictness but what makes me confused if the mixed 
policy about standards.


For example:
PostGIS 1.5 -> ST_StartPoint and ST_Endoints with Multi types are 
working  (against SQL/MM)
PostGIS 2.0 -> ST_StartPoint and ST_Endoints with Multi types return 
null  (folllow SQL/MM)


PostGIS 1.5 -> ST_GeometryN (geom, 1) with simple types returns null 
(folllow SQL/MM)
PostGIS 2.0 -> ST_GeometryN (geom, 1) with simple types are working 
(against SQL/MM)


In some cases it follows the standards but in other cases it follows the 
usability.


There are more examples but I cant remember now... I think LRS functions 
do not allow using Multi types with just one geometry either.
Of course we users can fix it using ST_Multi or ST_GeomeryN (geom, 1) 
but I think its waste of time, code and performance. If we have to 
sacrifice that because of the standards then its ok but in that case the 
above example (mixed standards policy) does not make sense for me.


Regards,


On 24/11/2011 16:30, Paul Ramsey wrote:

It's strictness. Not sure how much I like it, but that's been the
behavior for quite a while.
P.

On Thu, Nov 24, 2011 at 5:39 AM, Jose Carlos Martinez
  wrote:

HI,
p2211=# select st_startpoint ('MULTILINESTRING ((0 0, 10 0))');
  st_startpoint
---

(1 row)

returns null, according to SQL/MM  ST_StartPoint is working with ST_Curve,
  this is ok.  But I do not think its practical, besides previous versions of
PostGIS returned the first point correctly.

Is this a bug or PostGIS is trying to follow standards in a more strict way?

same with st_endpoint.
Cheers,


PD:
p2211=# select postgis_lib_build_date(), postgis_full_Version();
  postgis_lib_build_date |
 postgis_full_version

+--
  2011-11-22 08:39:41| POSTGIS="2.0.0SVN" GEOS="3.3.2dev-CAPI-1.7.2"
PROJ="Rel. 4.6.1, 21 August 2008" GDAL="GDAL 1.9dev, released 2011/01/18"
LIBXML="2.7.8" USE_STATS
(1 fila)




___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users


___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users


___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] ST_StartPoint fails with MultiLineString

2011-11-24 Thread Paul Ramsey 
It's strictness. Not sure how much I like it, but that's been the
behavior for quite a while.
P.

On Thu, Nov 24, 2011 at 5:39 AM, Jose Carlos Martinez
 wrote:
> HI,
> p2211=# select st_startpoint ('MULTILINESTRING ((0 0, 10 0))');
>  st_startpoint
> ---
>
> (1 row)
>
> returns null, according to SQL/MM  ST_StartPoint is working with ST_Curve,
>  this is ok.  But I do not think its practical, besides previous versions of
> PostGIS returned the first point correctly.
>
> Is this a bug or PostGIS is trying to follow standards in a more strict way?
>
> same with st_endpoint.
> Cheers,
>
>
> PD:
> p2211=# select postgis_lib_build_date(), postgis_full_Version();
>  postgis_lib_build_date |
>             postgis_full_version
>
> +--
>  2011-11-22 08:39:41    | POSTGIS="2.0.0SVN" GEOS="3.3.2dev-CAPI-1.7.2"
> PROJ="Rel. 4.6.1, 21 August 2008" GDAL="GDAL 1.9dev, released 2011/01/18"
> LIBXML="2.7.8" USE_STATS
> (1 fila)
>
>
>
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Possibility of connecting and writing Queries from OpenJumP

2011-11-24 Thread Joan 
Hi Iyke,

Check out this link.

http://www.manning-sandbox.com/thread.jspa?threadID=45833

Regards

Joan

On 24 November 2011 15:48, maduako ikechukwu  wrote:

>
> Hi Guys,
> Is it possible to connect to my database from OpenJumP and write my
> queries of any kind and obtain results?.
> I don't mean to visualize the data that we usually do by using the
> ''ST_asBinary'' but just like accessing my database and running queries
> from the Desktop application such as OpenJumP.
> For example, I sent this query to my database:
>
> SELECT v as elevation
> FROM (SELECT ST_Value(rast,ST_Point(-106.2,38.2))as v FROM srtm
> WHERE ST_Value(rast,ST_Point(-106.2,38.2)) IS NOT NULL)foo;
>
> and OpenJumP says "-1 Array Index Out Of Bound Exception "
>
> I need suggestions guys, am trying something out.
>
>
> --
> Iyke Maduako
>
> Masters in Geospatial Technologies
>  Institute for Geoinformatics,IfGI
>  University of Muenster Germany
> Phone: +4915129048460
> Alternative Email:iykefirstcl...@yahoo.com
>
>
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] Possibility of connecting and writing Queries from OpenJumP

2011-11-24 Thread maduako ikechukwu 
Hi Guys,
Is it possible to connect to my database from OpenJumP and write my queries
of any kind and obtain results?.
I don't mean to visualize the data that we usually do by using the
''ST_asBinary'' but just like accessing my database and running queries
from the Desktop application such as OpenJumP.
For example, I sent this query to my database:

SELECT v as elevation
FROM (SELECT ST_Value(rast,ST_Point(-106.2,38.2))as v FROM srtm
WHERE ST_Value(rast,ST_Point(-106.2,38.2)) IS NOT NULL)foo;

and OpenJumP says "-1 Array Index Out Of Bound Exception "

I need suggestions guys, am trying something out.


-- 
Iyke Maduako

Masters in Geospatial Technologies
Institute for Geoinformatics,IfGI
University of Muenster Germany
Phone: +4915129048460
Alternative Email:iykefirstcl...@yahoo.com
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Raster pixel value

2011-11-24 Thread Tom van Tilburg 

Andreas,

Would it be possible to share code and/or sample data?
With me St_SummaryStats seems to work correct, even when I set all my 
raster values to 1.


Chrs,
Tom

On 24-11-2011 13:42, Andreas Forø Tollefsen wrote:
I double checked the values that I get from this query and they do not 
seem to be correct.
For polygon cells that have only pixels with the value 1 inside only 
have a mean of 0.85 (see image).
The correct value should be a mean of 1. After some visual controls I 
am not sure if these numbers are correct.
There are many polygon cells with only pixel value 1, but no polygon 
cell with a higher mean than .87.


Any idea why this could be? Both the raster and polygons are in srid 4326.

Andreas



2011/11/24 Andreas Forø Tollefsen >


I know. RTFM before asking dumb questions. Set the
ST_Summary(rast, false) so it does not exclude_nodata_value? Right?

Andreas


2011/11/24 Andreas Forø Tollefsen mailto:andrea...@gmail.com>>

Great. I updated to latest rev and added the st_union.sql to
my function list. Now it works.
However, I need to ask one more question.
When running the script, it calculates the average pixel value
inside the polygon. It does not take into account the NULL
DATA values.
Is there any way i can make the script calculate the average
pixel value but count NULL DATA values as 0?

For instance, one pixel in a cell belonging to the Comoros
have a value (0.95). The rest is NULL. Hence, it seems a bit
wrong that the whole polygon get 0.95 when almost none of its
area have any elevation?

Any ideas?

Thank you so much for this info Tom!

Andreas

2011/11/24 Tom van Tilburg mailto:tom.van.tilb...@gmail.com>>

Yes, I think so.
Current windows build is *r8221
*
Also, I think ST_Union(raster) (which I used in the
example) is not included in this version yet.
You would have to download a prototype from.

http://trac.osgeo.org/postgis/browser/trunk/raster/scripts/plpgsql/st_union.sql

Chrs,
 Tom


On 24-11-2011 12:07, Andreas Forø Tollefsen wrote:

Great, Thank you so much for this.
However, I do not seem to have the
ST_MapAlgebraExpr(rast, rast.) function. Was this
implemented in rev 8001?

Andreas

2011/11/24 Tom van Tilburg mailto:tom.van.tilb...@gmail.com>>

Andreas,

We did approx. the same thing for non-quadrate
polygons. Perhaps it might be useful:

Step 1: Make a raster from every polygon, based on
the grid specifications of the elevation raster. Here
is also the solution: the raster cells will only be
created for cells that have their midpoint *inside*
your geometry.
ST_AsRaster(a.geom, b.rast, '')

Step 2: Overlay the elevation raster with the raster
you just created and keep only the values of the
elevation raster
ST_MapAlgebraExpr(

,b.rast
,'rast2' -- <-- keep only raster 2 value
, '','INTERSECTION','0','0',0
)

Step 3: get the mean from the statistics on the
resulting raster
(ST_SummaryStats(
(ST_Union( -- < --- we did a UNION because we
occasionaly had vectors crossing tiled rasters

)).rast
)).mean As avg_height

That did the trick. Complete script is below.

I suspect your method of doing a ST_Intersection for
every pix. makes it slower because it creates a
geometry first that you do not really need.

Cheers,
 Tom

--
FULL SCRIPT


SELECT
a.gid As id,
(ST_SummaryStats(
(ST_Union(
ST_MapAlgebraExpr(
ST_AsRaster(a.geom, b.rast, '32BF')
,b.rast
,'rast2', '32BF','INTERSECTION','0','0',0
)
)).rast
)).mean As avg_height

FROM
polygons.grid a LEFT JOIN
rasters.elev b
ON ST_Intersects(a.geom, b.rast)
GROUP BY a.gid



On 24-11-2011 11:14, Andreas Forø Tollefsen wrote:

Hi,

I am trying to calculate the average pixel value in
a elevation raster i

[postgis-users] ST_StartPoint fails with MultiLineString

2011-11-24 Thread Jose Carlos Martinez 

HI,
p2211=# select st_startpoint ('MULTILINESTRING ((0 0, 10 0))');
 st_startpoint
---

(1 row)

returns null, according to SQL/MM  ST_StartPoint is working with 
ST_Curve,  this is ok.  But I do not think its practical, besides 
previous versions of PostGIS returned the first point correctly.


Is this a bug or PostGIS is trying to follow standards in a more strict way?

same with st_endpoint.
Cheers,


PD:
p2211=# select postgis_lib_build_date(), postgis_full_Version();
 postgis_lib_build_date 
|   
postgis_full_version


+--
 2011-11-22 08:39:41| POSTGIS="2.0.0SVN" GEOS="3.3.2dev-CAPI-1.7.2" 
PROJ="Rel. 4.6.1, 21 August 2008" GDAL="GDAL 1.9dev, released 
2011/01/18" LIBXML="2.7.8" USE_STATS

(1 fila)




___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Raster pixel value

2011-11-24 Thread Andreas Forø Tollefsen 
I know. RTFM before asking dumb questions. Set the ST_Summary(rast, false)
so it does not exclude_nodata_value? Right?

Andreas

2011/11/24 Andreas Forø Tollefsen 

> Great. I updated to latest rev and added the st_union.sql to my function
> list. Now it works.
> However, I need to ask one more question.
> When running the script, it calculates the average pixel value inside the
> polygon. It does not take into account the NULL DATA values.
> Is there any way i can make the script calculate the average pixel value
> but count NULL DATA values as 0?
>
> For instance, one pixel in a cell belonging to the Comoros have a value
> (0.95). The rest is NULL. Hence, it seems a bit wrong that the whole
> polygon get 0.95 when almost none of its area have any elevation?
>
> Any ideas?
>
> Thank you so much for this info Tom!
>
> Andreas
>
> 2011/11/24 Tom van Tilburg 
>
>>  Yes, I think so.
>> Current windows build is *r8221
>> *
>> Also, I think ST_Union(raster) (which I used in the example) is not
>> included in this version yet.
>> You would have to download a prototype from.
>>
>> http://trac.osgeo.org/postgis/browser/trunk/raster/scripts/plpgsql/st_union.sql
>>
>> Chrs,
>>  Tom
>>
>>
>> On 24-11-2011 12:07, Andreas Forø Tollefsen wrote:
>>
>> Great, Thank you so much for this.
>> However, I do not seem to have the ST_MapAlgebraExpr(rast, rast.)
>> function. Was this implemented in rev 8001?
>>
>>  Andreas
>>
>> 2011/11/24 Tom van Tilburg 
>>
>>>  Andreas,
>>>
>>> We did approx. the same thing for non-quadrate polygons. Perhaps it
>>> might be useful:
>>>
>>> Step 1: Make a raster from every polygon, based on the grid
>>> specifications of the elevation raster. Here is also the solution: the
>>> raster cells will only be created for cells that have their midpoint
>>> *inside* your geometry.
>>> ST_AsRaster(a.geom, b.rast, '')
>>>
>>> Step 2: Overlay the elevation raster with the raster you just created
>>> and keep only the values of the elevation raster
>>> ST_MapAlgebraExpr(
>>> 
>>> ,b.rast
>>> ,'rast2' -- <-- keep only raster 2 value
>>> , '','INTERSECTION','0','0',0
>>> )
>>>
>>> Step 3: get the mean from the statistics on the resulting raster
>>> (ST_SummaryStats(
>>> (ST_Union( -- < --- we did a UNION because we occasionaly had
>>> vectors crossing tiled rasters
>>> 
>>> )).rast
>>> )).mean As avg_height
>>>
>>> That did the trick. Complete script is below.
>>>
>>> I suspect your method of doing a ST_Intersection for every pix. makes it
>>> slower because it creates a geometry first that you do not really need.
>>>
>>> Cheers,
>>>  Tom
>>>
>>> --
>>> FULL SCRIPT
>>>
>>>
>>> SELECT
>>> a.gid As id,
>>> (ST_SummaryStats(
>>> (ST_Union(
>>> ST_MapAlgebraExpr(
>>> ST_AsRaster(a.geom, b.rast, '32BF')
>>> ,b.rast
>>> ,'rast2', '32BF','INTERSECTION','0','0',0
>>> )
>>> )).rast
>>> )).mean As avg_height
>>>
>>> FROM
>>> polygons.grid a LEFT JOIN
>>> rasters.elev b
>>> ON ST_Intersects(a.geom, b.rast)
>>> GROUP BY a.gid
>>>
>>>
>>>
>>> On 24-11-2011 11:14, Andreas Forø Tollefsen wrote:
>>>
>>>  Hi,
>>>
>>>  I am trying to calculate the average pixel value in a elevation raster
>>> inside quadrate polygons.
>>> However, I am not getting the correct values from my query:
>>>
>>>  SELECT gid, AVG(((foo.geomval).val)) as avgmnt
>>> FROM (SELECT p.gid, ST_Intersection(p.cell, r.rast) AS geomval FROM
>>> mountain r, priogrid_land p WHERE ST_Intersects(p.cell, r.rast, ) AND p.gid
>>> =186124) AS foo
>>> GROUP BY gid ORDER BY gid;
>>>
>>>  The problem here is that the ST_Intersects(geom, rast) takes into
>>> consideration the pixels that is outside, but touches the border of the
>>> quadrate polygons. Then, the average values for each quadrate polygon is
>>> affected by pixels inside other polygons. This will potentially lead to a
>>> flawed result.
>>> So what I want is to be able to calculate the average value for the
>>> pixels INSIDE the polygon excluding those outside.
>>>
>>>  How can i restrict the AVG pixel value to be calculated only for
>>> pixels that is inside the polygon, and not the pixels that touch the
>>> outside of the border?
>>>
>>>  Thanks!
>>>
>>>  Best,
>>> Andreas
>>>
>>>
>>>
>>>
>>>  ___
>>> postgis-users mailing 
>>> listpostgis-users@postgis.refractions.nethttp://postgis.refractions.net/mailman/listinfo/postgis-users
>>>
>>>
>>>
>>> ___
>>> postgis-users mailing list
>>> postgis-users@postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>
>>>
>>
>>
>> ___
>> postgis-users mailing 
>> listpostgis-users@postgis.refractions.nethttp://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
>>
>> ___
>> postgis-users mailing list
>> pos

Re: [postgis-users] Installing PostGIS on FreeBSD : trouble with libpq

2011-11-24 Thread Ben Madin 
Which version of PostgreSQL did you install?

cheers

Ben


On 24/11/2011, at 4:47 PM, dhee...@dheerajchand.com wrote:

> 
> Mark Stosberg  summersault.com> writes:
> 
>> 
>> On 10/27/2011 08:34 PM, Dheeraj Chand wrote:
>>> Hi, all,
>>> 
>>> I'm not that great with BSD, but I'm running into this error trying to
> install from the ports tree. Anyone
>> willing to help?
>> 
>> What version of FreeBSD are you using? Did you install PostgreSQL 8.4.9
>> from ports, a package, or by hand?
>> 
>>   Mark
> 
> 
> 
> ACK! I never got this email. Stupid junk filters. It all came from ports. I've
> done nothing by hand.
> 
> Dheeraj
> 
> 
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Raster pixel value

2011-11-24 Thread Andreas Forø Tollefsen 
Great. I updated to latest rev and added the st_union.sql to my function
list. Now it works.
However, I need to ask one more question.
When running the script, it calculates the average pixel value inside the
polygon. It does not take into account the NULL DATA values.
Is there any way i can make the script calculate the average pixel value
but count NULL DATA values as 0?

For instance, one pixel in a cell belonging to the Comoros have a value
(0.95). The rest is NULL. Hence, it seems a bit wrong that the whole
polygon get 0.95 when almost none of its area have any elevation?

Any ideas?

Thank you so much for this info Tom!

Andreas

2011/11/24 Tom van Tilburg 

>  Yes, I think so.
> Current windows build is *r8221
> *
> Also, I think ST_Union(raster) (which I used in the example) is not
> included in this version yet.
> You would have to download a prototype from.
>
> http://trac.osgeo.org/postgis/browser/trunk/raster/scripts/plpgsql/st_union.sql
>
> Chrs,
>  Tom
>
>
> On 24-11-2011 12:07, Andreas Forø Tollefsen wrote:
>
> Great, Thank you so much for this.
> However, I do not seem to have the ST_MapAlgebraExpr(rast, rast.)
> function. Was this implemented in rev 8001?
>
>  Andreas
>
> 2011/11/24 Tom van Tilburg 
>
>>  Andreas,
>>
>> We did approx. the same thing for non-quadrate polygons. Perhaps it might
>> be useful:
>>
>> Step 1: Make a raster from every polygon, based on the grid
>> specifications of the elevation raster. Here is also the solution: the
>> raster cells will only be created for cells that have their midpoint
>> *inside* your geometry.
>> ST_AsRaster(a.geom, b.rast, '')
>>
>> Step 2: Overlay the elevation raster with the raster you just created and
>> keep only the values of the elevation raster
>> ST_MapAlgebraExpr(
>> 
>> ,b.rast
>> ,'rast2' -- <-- keep only raster 2 value
>> , '','INTERSECTION','0','0',0
>> )
>>
>> Step 3: get the mean from the statistics on the resulting raster
>> (ST_SummaryStats(
>> (ST_Union( -- < --- we did a UNION because we occasionaly had vectors
>> crossing tiled rasters
>> 
>> )).rast
>> )).mean As avg_height
>>
>> That did the trick. Complete script is below.
>>
>> I suspect your method of doing a ST_Intersection for every pix. makes it
>> slower because it creates a geometry first that you do not really need.
>>
>> Cheers,
>>  Tom
>>
>> --
>> FULL SCRIPT
>>
>>
>> SELECT
>> a.gid As id,
>> (ST_SummaryStats(
>> (ST_Union(
>> ST_MapAlgebraExpr(
>> ST_AsRaster(a.geom, b.rast, '32BF')
>> ,b.rast
>> ,'rast2', '32BF','INTERSECTION','0','0',0
>> )
>> )).rast
>> )).mean As avg_height
>>
>> FROM
>> polygons.grid a LEFT JOIN
>> rasters.elev b
>> ON ST_Intersects(a.geom, b.rast)
>> GROUP BY a.gid
>>
>>
>>
>> On 24-11-2011 11:14, Andreas Forø Tollefsen wrote:
>>
>>  Hi,
>>
>>  I am trying to calculate the average pixel value in a elevation raster
>> inside quadrate polygons.
>> However, I am not getting the correct values from my query:
>>
>>  SELECT gid, AVG(((foo.geomval).val)) as avgmnt
>> FROM (SELECT p.gid, ST_Intersection(p.cell, r.rast) AS geomval FROM
>> mountain r, priogrid_land p WHERE ST_Intersects(p.cell, r.rast, ) AND p.gid
>> =186124) AS foo
>> GROUP BY gid ORDER BY gid;
>>
>>  The problem here is that the ST_Intersects(geom, rast) takes into
>> consideration the pixels that is outside, but touches the border of the
>> quadrate polygons. Then, the average values for each quadrate polygon is
>> affected by pixels inside other polygons. This will potentially lead to a
>> flawed result.
>> So what I want is to be able to calculate the average value for the
>> pixels INSIDE the polygon excluding those outside.
>>
>>  How can i restrict the AVG pixel value to be calculated only for pixels
>> that is inside the polygon, and not the pixels that touch the outside of
>> the border?
>>
>>  Thanks!
>>
>>  Best,
>> Andreas
>>
>>
>>
>>
>>  ___
>> postgis-users mailing 
>> listpostgis-users@postgis.refractions.nethttp://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
>>
>> ___
>> postgis-users mailing list
>> postgis-users@postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
>
>
> ___
> postgis-users mailing 
> listpostgis-users@postgis.refractions.nethttp://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Help me.

2011-11-24 Thread Birgit Laggner 

Hi Atul,

I often have tasks like this and in the end wrote a set of functions to 
get the wanted result. (if you are interested, have look at: 
http://trac.osgeo.org/postgis/wiki/UsersWikiExamplesOverlayTables2)


But I don't know if that wouldn't be to complicated in your use case... 
You would have to test yourself which way would be more suitable for you.


But if you only want to display three different results together in a 
map, why can't you display them as three separate layers (which may be 
transparent, so you would see the borders of them all)?


Regards,

Birgit.


Am 24.11.2011 11:47, schrieb Atul Kumar:


Hi Birgit,

I want all boundaries of the geometries resulting from the three 
queries to be display in one layer. Its better to me.


If it is not possible then will go with dissolving all inner boundaries .

Please suggest.

Thanks

Atul

*From:*postgis-users-boun...@postgis.refractions.net 
[mailto:postgis-users-boun...@postgis.refractions.net] *On Behalf Of 
*Birgit Laggner

*Sent:* Thursday, November 24, 2011 4:12 PM
*To:* postgis-users@postgis.refractions.net
*Subject:* Re: [postgis-users] Help me.

Hi Atul,

with the st_union function, you aggregate all geometries dissolving 
all inner boundaries. Is this what you want, or do you want all 
boundaries of the geometries resulting from the three queries to be 
displayed in one layer??


Regards,

Birgit.


Am 24.11.2011 10:36, schrieb Atul Kumar:

Hi Birgit,

I also don't know whether st_union is using spatial_index because new 
to postgis.


I am having GUI which shows the data over map.

Suppose user will make query on table1 and queried data display on 
 map. Queried data is geometry type data.


Again user will make query on table2 and queried data display on map. 
Queried data is also geometry type data


Again user will make query on table3 and queried data display on map. 
Queried data is also geometry type data


At the end I want to union above three queried data and display on map 
using resultant queried union data.


Please suggest me is there other way to union geometry data.

Thanks

Atul

*From:*postgis-users-boun...@postgis.refractions.net 
 
[mailto:postgis-users-boun...@postgis.refractions.net] *On Behalf Of 
*Birgit Laggner

*Sent:* Thursday, November 24, 2011 2:42 PM
*To:* postgis-users@postgis.refractions.net 


*Subject:* Re: [postgis-users] Help me.

Hi Atul,

are you really sure, you want to union all geometries of the three 
tables into one big multipolygon? It seems to me that this might be a 
little bit too complex and big...


But, if you really want to union all these geometries (and I am not 
sure, if this would really be your plan - because maybe you are 
mistaken that union in PostGIS means the same as in ArcGIS??), then I 
would split the query again to first do the union of the first two 
tables, while immediately dumping the resulting multipolygon into the 
consisting single polygons, and next, I would go for the union with 
the third table. The queries could look like this:


select (st_dump(st_union(t1.geometry, t2.geometry))).geom as geometry 
into new_table from TEMP_OUTPUTTREE_2 t1, TEMP_OUTPUTTREE2_4 t2;


select (st_dump(st_union(nt.geometry, t3.geometry))).geom as geometry 
from new_table nt, TEMP_OUTPUTTREE_5 t3;


I don't know if st_union is using the spatial index. If the answer 
would be yes, it might be useful to create one on the new_table. But, 
depending on the size of your tables, I am afraid, that the queries 
will still be much slower than your intersection-queries.


Good luck and regards,

Birgit.



Am 24.11.2011 07:37, schrieb Atul Kumar:

Hi Birgit,

Now its taking less time as compare to previous one.

One more help.

Is there any way to optimize this query because its also taking long 
time to execute.


select st_union (st_union (t1.geometry, t2.geometry), t3.geometry) 
geometry from TEMP_OUTPUTTREE_2 t1,TEMP_OUTPUTTREE2_4 t2, 
TEMP_OUTPUTTREE_5 t3


Thanks

Atul

*From:*postgis-users-boun...@postgis.refractions.net 
 
[mailto:postgis-users-boun...@postgis.refractions.net] *On Behalf Of 
*Birgit Laggner

*Sent:* Wednesday, November 23, 2011 6:07 PM
*To:* postgis-users@postgis.refractions.net 


*Subject:* Re: [postgis-users] Help me.

Hallo Atul,

perhaps, I would split the query in two queries, because otherwise I 
think it might be difficult to use a spatial index on the second 
intersection. The queries could be like this:


--create a spatial index on each input table:
create index temp_outputtree_2_gist_geometry on temp_outputtree_2 
using gist(geometry);
create index temp_outputtree2_4_gist_geometry on temp_outputtree2_4 
using gist(geometry);
create index temp_outputtree_5_gist_geometry on temp_outputtree_5 
using gist(geometry);


--intersection of the first 2 tables usin

Re: [postgis-users] Raster pixel value

2011-11-24 Thread Tom van Tilburg 

Yes, I think so.
Current windows build is *r8221
*
Also, I think ST_Union(raster) (which I used in the example) is not 
included in this version yet.

You would have to download a prototype from.
http://trac.osgeo.org/postgis/browser/trunk/raster/scripts/plpgsql/st_union.sql

Chrs,
 Tom

On 24-11-2011 12:07, Andreas Forø Tollefsen wrote:

Great, Thank you so much for this.
However, I do not seem to have the ST_MapAlgebraExpr(rast, rast.) 
function. Was this implemented in rev 8001?


Andreas

2011/11/24 Tom van Tilburg >


Andreas,

We did approx. the same thing for non-quadrate polygons. Perhaps
it might be useful:

Step 1: Make a raster from every polygon, based on the grid
specifications of the elevation raster. Here is also the solution:
the raster cells will only be created for cells that have their
midpoint *inside* your geometry.
ST_AsRaster(a.geom, b.rast, '')

Step 2: Overlay the elevation raster with the raster you just
created and keep only the values of the elevation raster
ST_MapAlgebraExpr(

,b.rast
,'rast2' -- <-- keep only raster 2 value
, '','INTERSECTION','0','0',0
)

Step 3: get the mean from the statistics on the resulting raster
(ST_SummaryStats(
(ST_Union( -- < --- we did a UNION because we occasionaly had
vectors crossing tiled rasters

)).rast
)).mean As avg_height

That did the trick. Complete script is below.

I suspect your method of doing a ST_Intersection for every pix.
makes it slower because it creates a geometry first that you do
not really need.

Cheers,
 Tom

--
FULL SCRIPT


SELECT
a.gid As id,
(ST_SummaryStats(
(ST_Union(
ST_MapAlgebraExpr(
ST_AsRaster(a.geom, b.rast, '32BF')
,b.rast
,'rast2', '32BF','INTERSECTION','0','0',0
)
)).rast
)).mean As avg_height

FROM
polygons.grid a LEFT JOIN
rasters.elev b
ON ST_Intersects(a.geom, b.rast)
GROUP BY a.gid



On 24-11-2011 11:14, Andreas Forø Tollefsen wrote:

Hi,

I am trying to calculate the average pixel value in a elevation
raster inside quadrate polygons.
However, I am not getting the correct values from my query:

SELECT gid, AVG(((foo.geomval).val)) as avgmnt
FROM (SELECT p.gid, ST_Intersection(p.cell, r.rast) AS geomval
FROM mountain r, priogrid_land p WHERE ST_Intersects(p.cell,
r.rast, ) AND p.gid =186124) AS foo
GROUP BY gid ORDER BY gid;

The problem here is that the ST_Intersects(geom, rast) takes into
consideration the pixels that is outside, but touches the border
of the quadrate polygons. Then, the average values for each
quadrate polygon is affected by pixels inside other polygons.
This will potentially lead to a flawed result.
So what I want is to be able to calculate the average value for
the pixels INSIDE the polygon excluding those outside.

How can i restrict the AVG pixel value to be calculated only for
pixels that is inside the polygon, and not the pixels that touch
the outside of the border?

Thanks!

Best,
Andreas




___
postgis-users mailing list
postgis-users@postgis.refractions.net  

http://postgis.refractions.net/mailman/listinfo/postgis-users



___
postgis-users mailing list
postgis-users@postgis.refractions.net

http://postgis.refractions.net/mailman/listinfo/postgis-users




___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users


___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Raster pixel value

2011-11-24 Thread Andreas Forø Tollefsen 
Great, Thank you so much for this.
However, I do not seem to have the ST_MapAlgebraExpr(rast, rast.)
function. Was this implemented in rev 8001?

Andreas

2011/11/24 Tom van Tilburg 

>  Andreas,
>
> We did approx. the same thing for non-quadrate polygons. Perhaps it might
> be useful:
>
> Step 1: Make a raster from every polygon, based on the grid specifications
> of the elevation raster. Here is also the solution: the raster cells will
> only be created for cells that have their midpoint *inside* your geometry.
> ST_AsRaster(a.geom, b.rast, '')
>
> Step 2: Overlay the elevation raster with the raster you just created and
> keep only the values of the elevation raster
> ST_MapAlgebraExpr(
> 
> ,b.rast
> ,'rast2' -- <-- keep only raster 2 value
> , '','INTERSECTION','0','0',0
> )
>
> Step 3: get the mean from the statistics on the resulting raster
> (ST_SummaryStats(
> (ST_Union( -- < --- we did a UNION because we occasionaly had vectors
> crossing tiled rasters
> 
> )).rast
> )).mean As avg_height
>
> That did the trick. Complete script is below.
>
> I suspect your method of doing a ST_Intersection for every pix. makes it
> slower because it creates a geometry first that you do not really need.
>
> Cheers,
>  Tom
>
> --
> FULL SCRIPT
>
>
> SELECT
> a.gid As id,
> (ST_SummaryStats(
> (ST_Union(
> ST_MapAlgebraExpr(
> ST_AsRaster(a.geom, b.rast, '32BF')
> ,b.rast
> ,'rast2', '32BF','INTERSECTION','0','0',0
> )
> )).rast
> )).mean As avg_height
>
> FROM
> polygons.grid a LEFT JOIN
> rasters.elev b
> ON ST_Intersects(a.geom, b.rast)
> GROUP BY a.gid
>
>
>
> On 24-11-2011 11:14, Andreas Forø Tollefsen wrote:
>
> Hi,
>
>  I am trying to calculate the average pixel value in a elevation raster
> inside quadrate polygons.
> However, I am not getting the correct values from my query:
>
>  SELECT gid, AVG(((foo.geomval).val)) as avgmnt
> FROM (SELECT p.gid, ST_Intersection(p.cell, r.rast) AS geomval FROM
> mountain r, priogrid_land p WHERE ST_Intersects(p.cell, r.rast, ) AND p.gid
> =186124) AS foo
> GROUP BY gid ORDER BY gid;
>
>  The problem here is that the ST_Intersects(geom, rast) takes into
> consideration the pixels that is outside, but touches the border of the
> quadrate polygons. Then, the average values for each quadrate polygon is
> affected by pixels inside other polygons. This will potentially lead to a
> flawed result.
> So what I want is to be able to calculate the average value for the pixels
> INSIDE the polygon excluding those outside.
>
>  How can i restrict the AVG pixel value to be calculated only for pixels
> that is inside the polygon, and not the pixels that touch the outside of
> the border?
>
>  Thanks!
>
>  Best,
> Andreas
>
>
>
>
> ___
> postgis-users mailing 
> listpostgis-users@postgis.refractions.nethttp://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Help me.

2011-11-24 Thread Atul Kumar 
Hi Birgit,

I want all boundaries of the geometries resulting from the three queries to be 
display in one layer. Its better to me.
If it is not possible then will go with dissolving all inner boundaries .

Please suggest.

Thanks
Atul

From: postgis-users-boun...@postgis.refractions.net 
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Birgit 
Laggner
Sent: Thursday, November 24, 2011 4:12 PM
To: postgis-users@postgis.refractions.net
Subject: Re: [postgis-users] Help me.

Hi Atul,

with the st_union function, you aggregate all geometries dissolving all inner 
boundaries. Is this what you want, or do you want all boundaries of the 
geometries resulting from the three queries to be displayed in one layer??

Regards,

Birgit.


Am 24.11.2011 10:36, schrieb Atul Kumar:
Hi Birgit,

I also don't know whether st_union is using spatial_index because new to 
postgis.


I am having GUI which shows the data over map.
Suppose user will make query on table1 and queried data display on  map. 
Queried data is geometry type data.
Again user will make query on table2 and queried data display on map. Queried 
data is also geometry type data
Again user will make query on table3 and queried data display on map. Queried 
data is also geometry type data

At the end I want to union above three queried data and display on map using 
resultant queried union data.

Please suggest me is there other way to union geometry data.


Thanks
Atul

From: 
postgis-users-boun...@postgis.refractions.net
 [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Birgit 
Laggner
Sent: Thursday, November 24, 2011 2:42 PM
To: 
postgis-users@postgis.refractions.net
Subject: Re: [postgis-users] Help me.

Hi Atul,

are you really sure, you want to union all geometries of the three tables into 
one big multipolygon? It seems to me that this might be a little bit too 
complex and big...

But, if you really want to union all these geometries (and I am not sure, if 
this would really be your plan - because maybe you are mistaken that union in 
PostGIS means the same as in ArcGIS??), then I would split the query again to 
first do the union of the first two tables, while immediately dumping the 
resulting multipolygon into the consisting single polygons, and next, I would 
go for the union with the third table. The queries could look like this:

select (st_dump(st_union(t1.geometry, t2.geometry))).geom as geometry into 
new_table from TEMP_OUTPUTTREE_2 t1, TEMP_OUTPUTTREE2_4 t2;

select (st_dump(st_union(nt.geometry, t3.geometry))).geom as geometry from 
new_table nt, TEMP_OUTPUTTREE_5 t3;

I don't know if st_union is using the spatial index. If the answer would be 
yes, it might be useful to create one on the new_table. But, depending on the 
size of your tables, I am afraid, that the queries will still be much slower 
than your intersection-queries.

Good luck and regards,

Birgit.



Am 24.11.2011 07:37, schrieb Atul Kumar:
Hi Birgit,

Now its taking less time as compare to previous one.

One more help.

Is there any way to optimize this query because its also taking long time to 
execute.

select st_union (st_union (t1.geometry, t2.geometry), t3.geometry) geometry 
from TEMP_OUTPUTTREE_2 t1,TEMP_OUTPUTTREE2_4 t2, TEMP_OUTPUTTREE_5 t3

Thanks
Atul


From: 
postgis-users-boun...@postgis.refractions.net
 [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Birgit 
Laggner
Sent: Wednesday, November 23, 2011 6:07 PM
To: 
postgis-users@postgis.refractions.net
Subject: Re: [postgis-users] Help me.

Hallo Atul,

perhaps, I would split the query in two queries, because otherwise I think it 
might be difficult to use a spatial index on the second intersection. The 
queries could be like this:

--create a spatial index on each input table:
create index temp_outputtree_2_gist_geometry on temp_outputtree_2 using 
gist(geometry);
create index temp_outputtree2_4_gist_geometry on temp_outputtree2_4 using 
gist(geometry);
create index temp_outputtree_5_gist_geometry on temp_outputtree_5 using 
gist(geometry);

--intersection of the first 2 tables using the spatial index, writing the 
result of it into a new table:
select st_intersection(t1.geometry,t2.geometry) as geometry into new_table from 
TEMP_OUTPUTTREE_2 t1 inner join TEMP_OUTPUTTREE2_4 t2 on t1.geometry && 
t2.geometry where st_intersects(t1.geometry,t2.geometry);

--create a spatial index on the new geometries of the first intersection:
create index new_table_gist_geometry on new_table using gist(geometry);

--intersection with the third table:
select st_intersection(nt.geometry,t3.geometry) as geometry from new_table nt 
inner join TEMP_OUTPUTTREE_5 t3 on nt.geometry && t3.geometry where 
st_intersects(nt.geometry,t3.geometry);

Hope that helps,

Birgit.

Am 23.11.201

Re: [postgis-users] Help me.

2011-11-24 Thread Birgit Laggner 

Hi Atul,

with the st_union function, you aggregate all geometries dissolving all 
inner boundaries. Is this what you want, or do you want all boundaries 
of the geometries resulting from the three queries to be displayed in 
one layer??


Regards,

Birgit.


Am 24.11.2011 10:36, schrieb Atul Kumar:


Hi Birgit,

I also don't know whether st_union is using spatial_index because new 
to postgis.


I am having GUI which shows the data over map.

Suppose user will make query on table1 and queried data display on 
 map. Queried data is geometry type data.


Again user will make query on table2 and queried data display on map. 
Queried data is also geometry type data


Again user will make query on table3 and queried data display on map. 
Queried data is also geometry type data


At the end I want to union above three queried data and display on map 
using resultant queried union data.


Please suggest me is there other way to union geometry data.

Thanks

Atul

*From:*postgis-users-boun...@postgis.refractions.net 
[mailto:postgis-users-boun...@postgis.refractions.net] *On Behalf Of 
*Birgit Laggner

*Sent:* Thursday, November 24, 2011 2:42 PM
*To:* postgis-users@postgis.refractions.net
*Subject:* Re: [postgis-users] Help me.

Hi Atul,

are you really sure, you want to union all geometries of the three 
tables into one big multipolygon? It seems to me that this might be a 
little bit too complex and big...


But, if you really want to union all these geometries (and I am not 
sure, if this would really be your plan - because maybe you are 
mistaken that union in PostGIS means the same as in ArcGIS??), then I 
would split the query again to first do the union of the first two 
tables, while immediately dumping the resulting multipolygon into the 
consisting single polygons, and next, I would go for the union with 
the third table. The queries could look like this:


select (st_dump(st_union(t1.geometry, t2.geometry))).geom as geometry 
into new_table from TEMP_OUTPUTTREE_2 t1, TEMP_OUTPUTTREE2_4 t2;


select (st_dump(st_union(nt.geometry, t3.geometry))).geom as geometry 
from new_table nt, TEMP_OUTPUTTREE_5 t3;


I don't know if st_union is using the spatial index. If the answer 
would be yes, it might be useful to create one on the new_table. But, 
depending on the size of your tables, I am afraid, that the queries 
will still be much slower than your intersection-queries.


Good luck and regards,

Birgit.



Am 24.11.2011 07:37, schrieb Atul Kumar:

Hi Birgit,

Now its taking less time as compare to previous one.

One more help.

Is there any way to optimize this query because its also taking long 
time to execute.


select st_union (st_union (t1.geometry, t2.geometry), t3.geometry) 
geometry from TEMP_OUTPUTTREE_2 t1,TEMP_OUTPUTTREE2_4 t2, 
TEMP_OUTPUTTREE_5 t3


Thanks

Atul

*From:*postgis-users-boun...@postgis.refractions.net 
 
[mailto:postgis-users-boun...@postgis.refractions.net] *On Behalf Of 
*Birgit Laggner

*Sent:* Wednesday, November 23, 2011 6:07 PM
*To:* postgis-users@postgis.refractions.net 


*Subject:* Re: [postgis-users] Help me.

Hallo Atul,

perhaps, I would split the query in two queries, because otherwise I 
think it might be difficult to use a spatial index on the second 
intersection. The queries could be like this:


--create a spatial index on each input table:
create index temp_outputtree_2_gist_geometry on temp_outputtree_2 
using gist(geometry);
create index temp_outputtree2_4_gist_geometry on temp_outputtree2_4 
using gist(geometry);
create index temp_outputtree_5_gist_geometry on temp_outputtree_5 
using gist(geometry);


--intersection of the first 2 tables using the spatial index, writing 
the result of it into a new table:
select st_intersection(t1.geometry,t2.geometry) as geometry into 
new_table from TEMP_OUTPUTTREE_2 t1 inner join TEMP_OUTPUTTREE2_4 t2 
on t1.geometry && t2.geometry where 
st_intersects(t1.geometry,t2.geometry);


--create a spatial index on the new geometries of the first intersection:
create index new_table_gist_geometry on new_table using gist(geometry);

--intersection with the third table:
select st_intersection(nt.geometry,t3.geometry) as geometry from 
new_table nt inner join TEMP_OUTPUTTREE_5 t3 on nt.geometry && 
t3.geometry where st_intersects(nt.geometry,t3.geometry);


Hope that helps,

Birgit.

Am 23.11.2011 13:16, schrieb Atul Kumar:

Hi All,

I am trying  to intersection multiple sets of  geographical data using 
ST_intersection function. But query execution time is long.


My Query is :

select st_intersection (st_intersection (t1.geometry, t2.geometry), 
t3.geometry) geometry from TEMP_OUTPUTTREE_2 t1,TEMP_OUTPUTTREE2_4 t2, 
TEMP_OUTPUTTREE_5 t3


I am having three table its having geometry data. I want to intersect 
operation on those data.


Please suggest, Is there any optimal way to get the intersection with 
less execution time?


Thank

Re: [postgis-users] Raster pixel value

2011-11-24 Thread Tom van Tilburg 

Andreas,

We did approx. the same thing for non-quadrate polygons. Perhaps it 
might be useful:


Step 1: Make a raster from every polygon, based on the grid 
specifications of the elevation raster. Here is also the solution: the 
raster cells will only be created for cells that have their midpoint 
*inside* your geometry.

ST_AsRaster(a.geom, b.rast, '')

Step 2: Overlay the elevation raster with the raster you just created 
and keep only the values of the elevation raster

ST_MapAlgebraExpr(

,b.rast
,'rast2' -- <-- keep only raster 2 value
, '','INTERSECTION','0','0',0
)

Step 3: get the mean from the statistics on the resulting raster
(ST_SummaryStats(
(ST_Union( -- < --- we did a UNION because we occasionaly had 
vectors crossing tiled rasters


)).rast
)).mean As avg_height

That did the trick. Complete script is below.

I suspect your method of doing a ST_Intersection for every pix. makes it 
slower because it creates a geometry first that you do not really need.


Cheers,
 Tom

--
FULL SCRIPT


SELECT
a.gid As id,
(ST_SummaryStats(
(ST_Union(
ST_MapAlgebraExpr(
ST_AsRaster(a.geom, b.rast, '32BF')
,b.rast
,'rast2', '32BF','INTERSECTION','0','0',0
)
)).rast
)).mean As avg_height

FROM
polygons.grid a LEFT JOIN
rasters.elev b
ON ST_Intersects(a.geom, b.rast)
GROUP BY a.gid


On 24-11-2011 11:14, Andreas Forø Tollefsen wrote:

Hi,

I am trying to calculate the average pixel value in a elevation raster 
inside quadrate polygons.

However, I am not getting the correct values from my query:

SELECT gid, AVG(((foo.geomval).val)) as avgmnt
FROM (SELECT p.gid, ST_Intersection(p.cell, r.rast) AS geomval FROM 
mountain r, priogrid_land p WHERE ST_Intersects(p.cell, r.rast, ) AND 
p.gid =186124) AS foo

GROUP BY gid ORDER BY gid;

The problem here is that the ST_Intersects(geom, rast) takes into 
consideration the pixels that is outside, but touches the border of 
the quadrate polygons. Then, the average values for each quadrate 
polygon is affected by pixels inside other polygons. This will 
potentially lead to a flawed result.
So what I want is to be able to calculate the average value for the 
pixels INSIDE the polygon excluding those outside.


How can i restrict the AVG pixel value to be calculated only for 
pixels that is inside the polygon, and not the pixels that touch the 
outside of the border?


Thanks!

Best,
Andreas




___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users


___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] Raster pixel value

2011-11-24 Thread Andreas Forø Tollefsen 
Hi,

I am trying to calculate the average pixel value in a elevation raster
inside quadrate polygons.
However, I am not getting the correct values from my query:

SELECT gid, AVG(((foo.geomval).val)) as avgmnt
FROM (SELECT p.gid, ST_Intersection(p.cell, r.rast) AS geomval FROM
mountain r, priogrid_land p WHERE ST_Intersects(p.cell, r.rast, ) AND p.gid
=186124) AS foo
GROUP BY gid ORDER BY gid;

The problem here is that the ST_Intersects(geom, rast) takes into
consideration the pixels that is outside, but touches the border of the
quadrate polygons. Then, the average values for each quadrate polygon is
affected by pixels inside other polygons. This will potentially lead to a
flawed result.
So what I want is to be able to calculate the average value for the pixels
INSIDE the polygon excluding those outside.

How can i restrict the AVG pixel value to be calculated only for pixels
that is inside the polygon, and not the pixels that touch the outside of
the border?

Thanks!

Best,
Andreas
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Help me.

2011-11-24 Thread Atul Kumar 
Hi Birgit,

I also don't know whether st_union is using spatial_index because new to 
postgis.


I am having GUI which shows the data over map.
Suppose user will make query on table1 and queried data display on  map. 
Queried data is geometry type data.
Again user will make query on table2 and queried data display on map. Queried 
data is also geometry type data
Again user will make query on table3 and queried data display on map. Queried 
data is also geometry type data

At the end I want to union above three queried data and display on map using 
resultant queried union data.

Please suggest me is there other way to union geometry data.


Thanks
Atul

From: postgis-users-boun...@postgis.refractions.net 
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Birgit 
Laggner
Sent: Thursday, November 24, 2011 2:42 PM
To: postgis-users@postgis.refractions.net
Subject: Re: [postgis-users] Help me.

Hi Atul,

are you really sure, you want to union all geometries of the three tables into 
one big multipolygon? It seems to me that this might be a little bit too 
complex and big...

But, if you really want to union all these geometries (and I am not sure, if 
this would really be your plan - because maybe you are mistaken that union in 
PostGIS means the same as in ArcGIS??), then I would split the query again to 
first do the union of the first two tables, while immediately dumping the 
resulting multipolygon into the consisting single polygons, and next, I would 
go for the union with the third table. The queries could look like this:

select (st_dump(st_union(t1.geometry, t2.geometry))).geom as geometry into 
new_table from TEMP_OUTPUTTREE_2 t1, TEMP_OUTPUTTREE2_4 t2;

select (st_dump(st_union(nt.geometry, t3.geometry))).geom as geometry from 
new_table nt, TEMP_OUTPUTTREE_5 t3;

I don't know if st_union is using the spatial index. If the answer would be 
yes, it might be useful to create one on the new_table. But, depending on the 
size of your tables, I am afraid, that the queries will still be much slower 
than your intersection-queries.

Good luck and regards,

Birgit.



Am 24.11.2011 07:37, schrieb Atul Kumar:
Hi Birgit,

Now its taking less time as compare to previous one.

One more help.

Is there any way to optimize this query because its also taking long time to 
execute.

select st_union (st_union (t1.geometry, t2.geometry), t3.geometry) geometry 
from TEMP_OUTPUTTREE_2 t1,TEMP_OUTPUTTREE2_4 t2, TEMP_OUTPUTTREE_5 t3

Thanks
Atul


From: 
postgis-users-boun...@postgis.refractions.net
 [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Birgit 
Laggner
Sent: Wednesday, November 23, 2011 6:07 PM
To: 
postgis-users@postgis.refractions.net
Subject: Re: [postgis-users] Help me.

Hallo Atul,

perhaps, I would split the query in two queries, because otherwise I think it 
might be difficult to use a spatial index on the second intersection. The 
queries could be like this:

--create a spatial index on each input table:
create index temp_outputtree_2_gist_geometry on temp_outputtree_2 using 
gist(geometry);
create index temp_outputtree2_4_gist_geometry on temp_outputtree2_4 using 
gist(geometry);
create index temp_outputtree_5_gist_geometry on temp_outputtree_5 using 
gist(geometry);

--intersection of the first 2 tables using the spatial index, writing the 
result of it into a new table:
select st_intersection(t1.geometry,t2.geometry) as geometry into new_table from 
TEMP_OUTPUTTREE_2 t1 inner join TEMP_OUTPUTTREE2_4 t2 on t1.geometry && 
t2.geometry where st_intersects(t1.geometry,t2.geometry);

--create a spatial index on the new geometries of the first intersection:
create index new_table_gist_geometry on new_table using gist(geometry);

--intersection with the third table:
select st_intersection(nt.geometry,t3.geometry) as geometry from new_table nt 
inner join TEMP_OUTPUTTREE_5 t3 on nt.geometry && t3.geometry where 
st_intersects(nt.geometry,t3.geometry);

Hope that helps,

Birgit.

Am 23.11.2011 13:16, schrieb Atul Kumar:
Hi All,

I am trying  to intersection multiple sets of  geographical data using 
ST_intersection function. But query execution time is long.
My Query is :
select st_intersection (st_intersection (t1.geometry, t2.geometry), 
t3.geometry) geometry from TEMP_OUTPUTTREE_2 t1,TEMP_OUTPUTTREE2_4 t2, 
TEMP_OUTPUTTREE_5 t3

I am having three table its having geometry data. I want to intersect operation 
on those data.

Please suggest, Is there any optimal way to get the intersection with less 
execution time?

Thanks
Atul Kumar


DISCLAIMER == This e-mail may contain privileged and confidential 
information which is the property of Persistent Systems Ltd. It is intended 
only for the use of the individual or entity to which it is addressed. If you 
are not the intended recipient, you are not authorized to read, retain, copy, 
print, distribute or use this m

Re: [postgis-users] Help me.

2011-11-24 Thread Birgit Laggner 

Hi Atul,

are you really sure, you want to union all geometries of the three 
tables into one big multipolygon? It seems to me that this might be a 
little bit too complex and big...


But, if you really want to union all these geometries (and I am not 
sure, if this would really be your plan - because maybe you are mistaken 
that union in PostGIS means the same as in ArcGIS??), then I would split 
the query again to first do the union of the first two tables, while 
immediately dumping the resulting multipolygon into the consisting 
single polygons, and next, I would go for the union with the third 
table. The queries could look like this:


select (st_dump(st_union(t1.geometry, t2.geometry))).geom as geometry 
into new_table from TEMP_OUTPUTTREE_2 t1, TEMP_OUTPUTTREE2_4 t2;


select (st_dump(st_union(nt.geometry, t3.geometry))).geom as geometry 
from new_table nt, TEMP_OUTPUTTREE_5 t3;


I don't know if st_union is using the spatial index. If the answer would 
be yes, it might be useful to create one on the new_table. But, 
depending on the size of your tables, I am afraid, that the queries will 
still be much slower than your intersection-queries.


Good luck and regards,

Birgit.



Am 24.11.2011 07:37, schrieb Atul Kumar:


Hi Birgit,

Now its taking less time as compare to previous one.

One more help.

Is there any way to optimize this query because its also taking long 
time to execute.


select st_union (st_union (t1.geometry, t2.geometry), t3.geometry) 
geometry from TEMP_OUTPUTTREE_2 t1,TEMP_OUTPUTTREE2_4 t2, 
TEMP_OUTPUTTREE_5 t3


Thanks

Atul

*From:*postgis-users-boun...@postgis.refractions.net 
[mailto:postgis-users-boun...@postgis.refractions.net] *On Behalf Of 
*Birgit Laggner

*Sent:* Wednesday, November 23, 2011 6:07 PM
*To:* postgis-users@postgis.refractions.net
*Subject:* Re: [postgis-users] Help me.

Hallo Atul,

perhaps, I would split the query in two queries, because otherwise I 
think it might be difficult to use a spatial index on the second 
intersection. The queries could be like this:


--create a spatial index on each input table:
create index temp_outputtree_2_gist_geometry on temp_outputtree_2 
using gist(geometry);
create index temp_outputtree2_4_gist_geometry on temp_outputtree2_4 
using gist(geometry);
create index temp_outputtree_5_gist_geometry on temp_outputtree_5 
using gist(geometry);


--intersection of the first 2 tables using the spatial index, writing 
the result of it into a new table:
select st_intersection(t1.geometry,t2.geometry) as geometry into 
new_table from TEMP_OUTPUTTREE_2 t1 inner join TEMP_OUTPUTTREE2_4 t2 
on t1.geometry && t2.geometry where 
st_intersects(t1.geometry,t2.geometry);


--create a spatial index on the new geometries of the first intersection:
create index new_table_gist_geometry on new_table using gist(geometry);

--intersection with the third table:
select st_intersection(nt.geometry,t3.geometry) as geometry from 
new_table nt inner join TEMP_OUTPUTTREE_5 t3 on nt.geometry && 
t3.geometry where st_intersects(nt.geometry,t3.geometry);


Hope that helps,

Birgit.

Am 23.11.2011 13:16, schrieb Atul Kumar:

Hi All,

I am trying  to intersection multiple sets of  geographical data using 
ST_intersection function. But query execution time is long.


My Query is :

select st_intersection (st_intersection (t1.geometry, t2.geometry), 
t3.geometry) geometry from TEMP_OUTPUTTREE_2 t1,TEMP_OUTPUTTREE2_4 t2, 
TEMP_OUTPUTTREE_5 t3


I am having three table its having geometry data. I want to intersect 
operation on those data.


Please suggest, Is there any optimal way to get the intersection with 
less execution time?


Thanks

Atul Kumar

DISCLAIMER == This e-mail may contain privileged and 
confidential information which is the property of Persistent Systems 
Ltd. It is intended only for the use of the individual or entity to 
which it is addressed. If you are not the intended recipient, you are 
not authorized to read, retain, copy, print, distribute or use this 
message. If you have received this communication in error, please 
notify the sender and delete all copies of this message. Persistent 
Systems Ltd. does not accept any liability for virus infected mails.





___
postgis-users mailing list
postgis-users@postgis.refractions.net  

http://postgis.refractions.net/mailman/listinfo/postgis-users

DISCLAIMER == This e-mail may contain privileged and 
confidential information which is the property of Persistent Systems 
Ltd. It is intended only for the use of the individual or entity to 
which it is addressed. If you are not the intended recipient, you are 
not authorized to read, retain, copy, print, distribute or use this 
message. If you have received this communication in error, please 
notify the sender and delete all copies of this message. Persistent 
Systems Ltd. does not accept any liability for virus infected mails.




_

Re: [postgis-users] Installing PostGIS on FreeBSD : trouble with libpq

2011-11-24 Thread dheeraj 

Mark Stosberg  summersault.com> writes:

> 
> On 10/27/2011 08:34 PM, Dheeraj Chand wrote:
> > Hi, all,
> > 
> > I'm not that great with BSD, but I'm running into this error trying to
install from the ports tree. Anyone
> willing to help?
> 
> What version of FreeBSD are you using? Did you install PostgreSQL 8.4.9
> from ports, a package, or by hand?
> 
>Mark



ACK! I never got this email. Stupid junk filters. It all came from ports. I've
done nothing by hand.

Dheeraj


___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users