[postgis-users] PostGIS: make check works, but I can't manually create a spatial database

2010-05-26 Thread Peter Cotroneo 
Hi,

I've just installed PostgreSQL 8.4.2, Proj 4.6.1, GEOS 3.2.0 (CAPI 1.6.0),
libxml 2.7.6, and PostGIS 1.5.0 on an Amazon EC2 instance running the 64-bit
version of Fedora 8.  (I compiled using GCC 4.1.2.)  After installing
PostGIS, I ran make check and that was successful.

When I try to create a spatial database (e.g., psql -d geodb -f
postgis.sql), however, it doesn't work.  I get numerous errors that look as
follows:

psql:postgis.sql:7739: ERROR:  current transaction is aborted, commands
ignored until end of transaction block
ROLLBACK
psql:postgis.sql:7744: NOTICE:  aggregate geomunion(geometry) does not
exist, skipping

My understanding is that make check creates a spatial database using the
postgis.sql script, which is essentially what I'm trying to do manually.
So, I'm a bit confused...  Can someone please tell me what I'm doing wrong?

Cheers,

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

Re: [postgis-users] PostGIS: make check works, but I can't manually create a spatial database

2010-05-26 Thread Devrim GÜNDÜZ 
On Wed, 2010-05-26 at 12:16 +0100, Peter Cotroneo wrote:
> 
> When I try to create a spatial database (e.g., psql -d geodb -f
> postgis.sql), however, it doesn't work.  I get numerous errors that
> look as follows:
> 
> psql:postgis.sql:7739: ERROR:  current transaction is aborted,
> commands ignored until end of transaction block 

There should be something different before this. Could you please paste
it?

BTW, did you load plpgsql before running postgis.sql script?
-- 
Devrim GÜNDÜZ
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
PostgreSQL RPM Repository: http://yum.pgrpms.org
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz


signature.asc
Description: This is a digitally signed message part
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] No index usage on geography query plan?

2010-05-26 Thread Mark Cave-Ayland 

Nicholas Bower wrote:

But simply swapping the query region above from geometry to geography 
we're back to no index usage,


explain analyze select count(*) from wastac.t_tile_geometry_old where 
ST_Intersects(border, 
ST_GeographyFromText('SRID=4316;POLYGON((116.751709 
-31.381779,116.883545 -32.676373,114.741211 -32.796510,114.796143 
-31.316101,116.751709 -31.381779))'));


 Aggregate  (cost=362164.77..362164.78 rows=1 width=0) (actual 
time=80302.237..80302.237 rows=1 loops=1)   ->  Seq Scan on 
t_tile_geometry_old  (cost=0.00..361101.11 rows=425463 width=0) (actual 
time=19680.252..80302.172 rows=95 loo

ps=1)
 Filter: st_intersects((border)::geography, 
'010320DC1001000500F8C610001C305D40B24CBF44BC613FC0D6E253008

C385D40DE72F563935640C0DF1B430070AF5C40EE77280AF46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geogra
phy)
 Total runtime: 80302.285 ms


Right. So the give away in the above query is the cast in the 
st_intersects() filter; it looks as if your border column in 
wastac.t_tile_geometry_old is a *geometry* while your intersection 
geometry is a *geography*. Since PostgreSQL sees that you are trying to 
perform an operation on two different types, it cannot make use of the 
indexes on those two columns. Hence it performs a sequential scan across 
the whole table converting on the fly, which is going to take a lot more 
time.


Try making a copy of your wastac.t_tile_geometry_old table but with a 
geography instead of geometry column for border, and you should see an 
improvement.



HTH,

Mark.

--
Mark Cave-Ayland - Senior Technical Architect
PostgreSQL - PostGIS
Sirius Corporation plc - control through freedom
http://www.siriusit.co.uk
t: +44 870 608 0063

Sirius Labs: http://www.siriusit.co.uk/labs
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] No index usage on geography query plan?

2010-05-26 Thread Nick Bower 




Try making a copy of your wastac.t_tile_geometry_old table but with  
a geography instead of geometry column for border, and you should  
see an improvement.


That's precisely what I showed in the original post - geography  
intersecting geography column. See the table def. I was outlining in  
this latter geometry example that a 9s intersection is not right and  
there is a problem with geograhies.


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

Re: [postgis-users] No index usage on geography query plan?

2010-05-26 Thread Mark Cave-Ayland 

Nick Bower wrote:

Try making a copy of your wastac.t_tile_geometry_old table but with a 
geography instead of geometry column for border, and you should see an 
improvement.


That's precisely what I showed in the original post - geography 
intersecting geography column. See the table def. I was outlining in 
this latter geometry example that a 9s intersection is not right and 
there is a problem with geograhies.


Okay - so can we see the explain analyze output for your last query for 
the geography intersecting geography case for comparison?



ATB,

Mark.

--
Mark Cave-Ayland - Senior Technical Architect
PostgreSQL - PostGIS
Sirius Corporation plc - control through freedom
http://www.siriusit.co.uk
t: +44 870 608 0063

Sirius Labs: http://www.siriusit.co.uk/labs
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Adding new points to multipoints and new polygons to multipolygons

2010-05-26 Thread strk 
On Tue, May 25, 2010 at 09:29:05PM -0400, James Chamberlain wrote:
> Hello,
> 
> What is the best way to add a new point to a multipoint or a new polygon 
> to a multipolygon while maintaining order. I read about using union, but 
> it will not keep the order and therefore will not work for me. Speed and 
> efficiency are important as these updates will happen frequently.

See ST_AddPoint and ST_AddGeometry.
The latter should work in both cases.
If it doesn't, file a feature request as I think it should :D

--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] Adding new points to multipoints and new polygons to multipolygons

2010-05-26 Thread James Chamberlain 
Which version added ST_AddGeometry? I am running 1.5.1 and don't see
it listed in the docs or available functions in pgadmin.

- James


On Wed, May 26, 2010 at 10:30 AM, strk  wrote:
> On Tue, May 25, 2010 at 09:29:05PM -0400, James Chamberlain wrote:
>> Hello,
>>
>> What is the best way to add a new point to a multipoint or a new polygon
>> to a multipolygon while maintaining order. I read about using union, but
>> it will not keep the order and therefore will not work for me. Speed and
>> efficiency are important as these updates will happen frequently.
>
> See ST_AddPoint and ST_AddGeometry.
> The latter should work in both cases.
> If it doesn't, file a feature request as I think it should :D
>
> --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
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] No index usage on geography query plan?

2010-05-26 Thread Paragon Corporation 
Mark,

He did include it in an earlier email

wastac=> explain analyze select count(*) from wastac.t_tile_geometry where
ST_Intersects(border, ST_GeographyFromText('SRID=4326;POLYGON((116.751709
-31.381779,116.883545 -32.676373,114.741211 -32.796510,114.796143
-31.316101,116.751709 -31.381779))'));


 Aggregate  (cost=364056.80..364056.81 rows=1 width=0) (actual
time=8854.932..8854.933 rows=1 loops=1)
   ->  Seq Scan on t_tile_geometry  (cost=0.00..362993.14 rows=425463
width=0) (actual time=2017.629..8854.879 rows=95 loops=1)
 Filter: st_intersects(border,
'010320E61001000500F8C610001C305D40B24CBF44BC613FC0D6E253008
C385D40DE72F563935640C0DF1B430070AF5C40EE77280A
F46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::g
eography)
 Total runtime: 8854.976 ms
(4 rows)

That does look like a pretty huge bounding polygon, but the geography we
agree should still be using the spatial index, so probably making the index
cost higher than it should

Nick,

Can you just for contrast, try to force it to use the index by doing

set enable_seqscan = off;

 explain analyze select count(*) from wastac.t_tile_geometry where
ST_Intersects(border, ST_GeographyFromText('SRID=4326;POLYGON((116.751709
-31.381779,116.883545 -32.676373,114.741211 -32.796510,114.796143
-31.316101,116.751709 -31.381779))'));

That should force it to use the index unless it can't for some other reason.

Leo and Regina,
http://www.postgis.us



-Original Message-
From: postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Mark
Cave-Ayland
Sent: Wednesday, May 26, 2010 8:44 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] No index usage on geography query plan?

Nick Bower wrote:

>> Try making a copy of your wastac.t_tile_geometry_old table but with a 
>> geography instead of geometry column for border, and you should see 
>> an improvement.
> 
> That's precisely what I showed in the original post - geography 
> intersecting geography column. See the table def. I was outlining in 
> this latter geometry example that a 9s intersection is not right and 
> there is a problem with geograhies.

Okay - so can we see the explain analyze output for your last query for the
geography intersecting geography case for comparison?


ATB,

Mark.

-- 
Mark Cave-Ayland - Senior Technical Architect
PostgreSQL - PostGIS
Sirius Corporation plc - control through freedom
http://www.siriusit.co.uk
t: +44 870 608 0063

Sirius Labs: http://www.siriusit.co.uk/labs
___
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] No index usage on geography query plan?

2010-05-26 Thread Nicholas Bower 
> That does look like a pretty huge bounding polygon, but the geography we
> agree should still be using the spatial index, so probably making the index
> cost higher than it should
>

Fyi the border values are are simply composed of a regular 20km grid of
ajoining polygon squares covering Australia (about 1.3M tiles).
 Incidentally, with the border of tiles being square, the bounding box *is*
the shape I guess.  It's not relevant, but the query returns a count 95 of
these 20km tiles.

We're dealing with satellite data - the datasets themselves have far bigger
coverages (different table entirely), but this is why you've noticed the
"region of interest" in the query is large.


> Nick,
>
> Can you just for contrast, try to force it to use the index by doing
>
> set enable_seqscan = off;
>
>  explain analyze select count(*) from wastac.t_tile_geometry where
> ST_Intersects(border, ST_GeographyFromText('SRID=4326;POLYGON((116.751709
> -31.381779,116.883545 -32.676373,114.741211 -32.796510,114.796143
> -31.316101,116.751709 -31.381779))'));
>

Strange - no difference.

wastac=> set enable_seqscan = off;
SET
wastac=> explain analyze select count(*) from wastac.t_tile_geometry where
ST_Intersects(border, ST_GeographyFromText('SRID=4326;POLYGON((116.751709
-31.381779,116.883545 -32.676373,114.741211
-32.796510,114.796143-31.316101,116.751709 -31.381779))'));

  QUERY PLAN



 Aggregate  (cost=1364056.80..1364056.81 rows=1 width=0) (actual
time=8909.585..8909.586 rows=1 loops=1)
   ->  Seq Scan on t_tile_geometry  (cost=100.00..1362993.14
rows=425463 width=0) (actual time=2028.827..8909.533 rows=95 loops=1)
 Filter: st_intersects(border,
'010320E61001000500F8C610001C305D40B24CBF44BC613FC0D6E253008C385D40DE72F563935640C0DF1B430070AF5C40EE77280AF46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geography)
 Total runtime: 8909.626 ms
(4 rows)
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] No index usage on geography query plan?

2010-05-26 Thread Paragon Corporation 
Nick,
Okay we are seeing the same issue with our fastfoods data even with smaller
windows.  I think the clue is  the plan here.
 
The ST_Intersects geography function seems to be treated as a blackbox
rather than a transparent function composed of && and _ST_Distance
 
The magic of the geometry ST_Intersects is that the query plan can see into
the function and decomposes it into
&& + _ST_Intersects
 
For some reason, that's just not happening with geography.  Maybe Paul or
Mark have a clue why that is.
 
So I bet this uses an index and is much faster
select count(*) from wastac.t_tile_geometry where
border && ST_GeographyFromText('SRID=4326;POLYGON((116.751709

-31.381779,116.883545 -32.676373,114.741211 -32.796510,114.796143
-31.316101,116.751709 -31.381779))') AND ST_Intersects(border,
ST_GeographyFromText('SRID=4326;POLYGON((116.751709 -31.381779,116.883545
-32.676373,114.741211 -32.796510,114.796143-31.316101,116.751709
-31.381779))'));
 
Thanks
Regina  and Leo
h  ttp://www.postgis.us

  _  

From: postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Nicholas
Bower
Sent: Wednesday, May 26, 2010 6:42 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] No index usage on geography query plan?


 

That does look like a pretty huge bounding polygon, but the geography we
agree should still be using the spatial index, so probably making the index
cost higher than it should



Fyi the border values are are simply composed of a regular 20km grid of
ajoining polygon squares covering Australia (about 1.3M tiles).
Incidentally, with the border of tiles being square, the bounding box *is*
the shape I guess.  It's not relevant, but the query returns a count 95 of
these 20km tiles.

We're dealing with satellite data - the datasets themselves have far bigger
coverages (different table entirely), but this is why you've noticed the
"region of interest" in the query is large.



Nick,

Can you just for contrast, try to force it to use the index by doing

set enable_seqscan = off;

 explain analyze select count(*) from wastac.t_tile_geometry where
ST_Intersects(border, ST_GeographyFromText('SRID=4326;POLYGON((116.751709

-31.381779,116.883545 -32.676373,114.741211 -32.796510,114.796143
-31.316101,116.751709 -31.381779))'));



Strange - no difference.

wastac=> set enable_seqscan = off;
SET
wastac=> explain analyze select count(*) from wastac.t_tile_geometry where
ST_Intersects(border, ST_GeographyFromText('SRID=4326;POLYGON((116.751709
-31.381779,116.883545 -32.676373,114.741211
-32.796510,114.796143-31.316101,116.751709 -31.381779))'));
 
QUERY PLAN





 Aggregate  (cost=1364056.80..1364056.81 rows=1 width=0) (actual
time=8909.585..8909.586 rows=1 loops=1)
   ->  Seq Scan on t_tile_geometry  (cost=100.00..1362993.14
rows=425463 width=0) (actual time=2028.827..8909.533 rows=95 loops=1)
 Filter: st_intersects(border,
'010320E61001000500F8C610001C305D40B24CBF44BC613FC0D6E253008
C385D40DE72F563935640C0DF1B430070AF5C40EE77280AF46540C01EFCC401F4B25C409F3BC
1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geography)
 Total runtime: 8909.626 ms
(4 rows)

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