Re: [postgis-users] No index usage on geography query plan?
In the meantime perhaps someone could add a warning to the docs that
geography indexes are an order of magnitude slower than geometries for
intersections? It's pretty important stuff for anyone appraising a
migration.
Actually I was after containment (db polygons enclose search ROI) but that's
a feature yet to come I know.
No complaints about on the basis it's free, but people have to admit 10s for
intersecting 150k polygons is not stellar given how we've been spoiled with
geometries in the past.
Anyway, maybe a warning is in order for people considering moving across
what do you think?
On 1 June 2010 07:00, Paul Ramsey pram...@opengeo.org wrote:
Actually it's slower because the calculations require lots of
transcendental math. Anyhow, it's slower. If anyone wants a quote on
speed improvements, I'm happy to provide one, I have some good ideas
about how to speed things up with some better data structures and
caching.
P.
On Mon, May 31, 2010 at 2:48 PM, Paragon Corporation l...@pcorp.us wrote:
Nicholas,
I fear that may be the way it is. The intersects functionality of
geography
is slower than intersects of geometry because it piggy backs on the
distance
function rather than using an intersection matrix.
With the index scan you are left with 10,347 records to check via the
slower
distance function.
One thought is to create an ST_Intersects that uses the geometry
_ST_Intersects instead of geography _ST_Distance
you might get better speed or you might not.
CREATE OR REPLACE FUNCTION st_intersectswitht(geography, geography)
RETURNS boolean AS
$$SELECT $1 $2 AND _ST_Intersects(ST_Transform(geometry($1),
_ST_BestSRID($1)), ST_Transform(geometry($2), _ST_BestSRID($1)))$$
LANGUAGE 'sql' IMMUTABLE;
From: postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of
Nicholas
Bower
Sent: Sunday, May 30, 2010 7:38 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] No index usage on geography query plan?
Well the index says it is being used, however I'm still quite suspicious
because of performance results below.
I attach 3 versions of a simply query (Geography ST_Intersects, Geometry
ST_Intersects, Geography ) which is a simple square ROI intersection
over
150k rows, each having a single polygon around 50-80 verticies.
1) Geography ST_Intersects gives 13s :-(
wastac=# explain analyze SELECT count(1) AS count_1
wastac-# FROM wastac.t_swath_metadata
wastac-# WHERE wastac.t_swath_metadata.quicklook = True
wastac-# AND
ST_Intersects(swath_bounding,ST_GeographyFromText('SRID=4326;POLYGON((104.765625
-39.0277188402,131.30859375 -39.0277188402,131.30859375
-15.7076627696,104.765625 -15.7076627696,104.765625 -39.0277188402))'));
Aggregate (cost=13556.17..13556.18 rows=1 width=0) (actual
time=12886.056..12886.057 rows=1 loops=1)
- Bitmap Heap Scan on t_swath_metadata (cost=506.07..13554.65
rows=607
width=0) (actual time=17.168..12883.162 rows=8462 loops=1)
Recheck Cond: (swath_bounding
'010320E610010005315A402F127C4A8C8343C0E06960402F127C4A8C8343C00
000E0696040BD0E48C6526A2FC000315A40BD0E48C6526A2FC000315A402F127C4A8C8343C0'::geography)
Filter: (quicklook AND (_st_distance(swath_bounding,
'010320E610010005315A402F127C4A8C8343C0E0696
0402F127C4A8C8343C0E0696040BD0E48C6526A2FC000315A40BD0E48C6526A2FC000315A402F127C4A8C8343C0'::geography,
0::double pre
cision, false) 1e-05::double precision))
- Bitmap Index Scan on t_swath_metadata_swath_bounding_key
(cost=0.00..505.91 rows=10347 width=0) (actual time=8.148..8.148 rows=1
4261 loops=1)
Index Cond: (swath_bounding
'010320E610010005315A402F127C4A8C8343C0E06960402F127C4A8C834
3C0E0696040BD0E48C6526A2FC000315A40BD0E48C6526A2FC000315A402F127C4A8C8343C0'::geography)
Total runtime: 12886.287 ms
(7 rows)
2) Geometry ST_Intersects gives 1s :-)
wastac=# explain analyze SELECT count(1) AS count_1
wastac-# FROM wastac.t_swath_metadata_old
wastac-# WHERE quicklook = True
wastac-# AND
ST_Intersects(swath_bounding,ST_GeomFromText('POLYGON((104.765625
-39.0277188402,131.30859375 -39.0277188402,131.30859375
-15.7076627696,104.765625 -15.7076627696,104.765625 -39.0277188402))',
-1));
Aggregate (cost=9505.13..9505.14 rows=1 width=0) (actual
time=95.681..95.682 rows=1 loops=1) - Bitmap Heap Scan on
t_swath_metadata_old (cost=506.77..9503.27 rows=745 width=0) (actual
time=4.198..93.366 rows=7274 loops=1)
Recheck Cond: (swath_bounding
'010300010005315A402F127C4A8C8343C0E06960402F127C4A8C8343C0E
Re: [postgis-users] No index usage on geography query plan?
I understand your explanation now - it's why our alternative queries over a pre-calculated 20km grid from overlaying the large boundary approached geometry in spatial performance (but then we incur far worse overall performance because of the sheer number of features in the query). The reason we are querying geographies instead of geometries is because we have large-scale global coverages that cross IDL and get warped near poles. To me, querying large scale features is the whole reason for geography, and so ideally my scenario should reflect best case performance not worst for geography. Otherwise if I had small features (minimal warping - eg my 20km grid), I'd just continue as I have for years and use geometry operators and handle IDL wrapping by adding 360 onto any negative longitude. Not fancy but fast. On 1 June 2010 12:27, Paragon Corporation l...@pcorp.us wrote: Paul, On that thought. Remember how geometry intersects performance significantly increased with prepared geometry algorithm, are we using that same kind of prepared geometry logic for geography. Just thinking out loud that aside from the slower algorithm, for cases like these where thousands of records need to be checked by the non-index check, we are losing performance there too. I imagine that may be an even easier enhancement. Not sure it makes a difference in this particular case since Nicholas bounding constant geography is pretty simple. Nicholas -- as Paul stated -- its not the index that is orders of magnitude slower, its the secondary check. For most use cases (needle in a hay stack where you are trying to get rid of 1,000,000 records and check 100 or so candidates, the speed is pretty decent and not too far off from geometry). Decent once we fix the ST_Intersects minor bug that prevents the index from being used. Though we should have a caveat somewhere explaining in detail these scenarios. Hope that helps, Regina -- *From:* postgis-users-boun...@postgis.refractions.net [mailto: postgis-users-boun...@postgis.refractions.net] *On Behalf Of *Paul Ramsey *Sent:* Monday, May 31, 2010 9:20 PM *To:* PostGIS Users Discussion *Subject:* Re: [postgis-users] No index usage on geography query plan? FYI, it is not the index that is slower, it is the op. The index is actually (surprisingly) faster. P On May 31, 2010, at 5:06 PM, Nicholas Bower n...@petangent.net wrote: In the meantime perhaps someone could add a warning to the docs that geography indexes are an order of magnitude slower than geometries for intersections? It's pretty important stuff for anyone appraising a migration. Actually I was after containment (db polygons enclose search ROI) but that's a feature yet to come I know. No complaints about on the basis it's free, but people have to admit 10s for intersecting 150k polygons is not stellar given how we've been spoiled with geometries in the past. Anyway, maybe a warning is in order for people considering moving across what do you think? On 1 June 2010 07:00, Paul Ramsey pram...@opengeo.org pram...@opengeo.org wrote: Actually it's slower because the calculations require lots of transcendental math. Anyhow, it's slower. If anyone wants a quote on speed improvements, I'm happy to provide one, I have some good ideas about how to speed things up with some better data structures and caching. P. On Mon, May 31, 2010 at 2:48 PM, Paragon Corporation l...@pcorp.us l...@pcorp.us wrote: Nicholas, I fear that may be the way it is. The intersects functionality of geography is slower than intersects of geometry because it piggy backs on the distance function rather than using an intersection matrix. With the index scan you are left with 10,347 records to check via the slower distance function. One thought is to create an ST_Intersects that uses the geometry _ST_Intersects instead of geography _ST_Distance you might get better speed or you might not. CREATE OR REPLACE FUNCTION st_intersectswitht(geography, geography) RETURNS boolean AS $$SELECT $1 $2 AND _ST_Intersects(ST_Transform(geometry($1), _ST_BestSRID($1)), ST_Transform(geometry($2), _ST_BestSRID($1)))$$ LANGUAGE 'sql' IMMUTABLE; From: postgis-users-boun...@postgis.refractions.net postgis-users-boun...@postgis.refractions.net [mailto: postgis-users-boun...@postgis.refractions.net postgis-users-boun...@postgis.refractions.net] On Behalf Of Nicholas Bower Sent: Sunday, May 30, 2010 7:38 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] No index usage on geography query plan? Well the index says it is being used, however I'm still quite suspicious because of performance results below. I attach 3 versions of a simply query (Geography ST_Intersects, Geometry ST_Intersects, Geography ) which is a simple square ROI intersection over 150k rows, each having
Re: [postgis-users] No index usage on geography query plan?
Well the index says it is being used, however I'm still quite suspicious
because of performance results below.
I attach 3 versions of a simply query (Geography ST_Intersects, Geometry
ST_Intersects, Geography ) which is a simple square ROI intersection over
150k rows, each having a single polygon around 50-80 verticies.
1) Geography ST_Intersects gives 13s :-(
wastac=# explain analyze SELECT count(1) AS count_1
wastac-# FROM wastac.t_swath_metadata
wastac-# WHERE wastac.t_swath_metadata.quicklook = True
wastac-# AND
ST_Intersects(swath_bounding,ST_GeographyFromText('SRID=4326;POLYGON((104.765625
-39.0277188402,131.30859375 -39.0277188402,131.30859375
-15.7076627696,104.765625 -15.7076627696,104.765625 -39.0277188402))'));
Aggregate (cost=13556.17..13556.18 rows=1 width=0) (actual
time=12886.056..12886.057 rows=1 loops=1)
- Bitmap Heap Scan on t_swath_metadata (cost=506.07..13554.65 rows=607
width=0) (actual time=17.168..12883.162 rows=8462 loops=1)
Recheck Cond: (swath_bounding
'010320E610010005315A402F127C4A8C8343C0E06960402F127C4A8C8343C00
000E0696040BD0E48C6526A2FC000315A40BD0E48C6526A2FC000315A402F127C4A8C8343C0'::geography)
Filter: (quicklook AND (_st_distance(swath_bounding,
'010320E610010005315A402F127C4A8C8343C0E0696
0402F127C4A8C8343C0E0696040BD0E48C6526A2FC000315A40BD0E48C6526A2FC000315A402F127C4A8C8343C0'::geography,
0::double pre
cision, false) 1e-05::double precision))
- Bitmap Index Scan on t_swath_metadata_swath_bounding_key
(cost=0.00..505.91 rows=10347 width=0) (actual time=8.148..8.148 rows=1
4261 loops=1)
Index Cond: (swath_bounding
'010320E610010005315A402F127C4A8C8343C0E06960402F127C4A8C834
3C0E0696040BD0E48C6526A2FC000315A40BD0E48C6526A2FC000315A402F127C4A8C8343C0'::geography)
Total runtime: 12886.287 ms
(7 rows)
2) Geometry ST_Intersects gives 1s :-)
wastac=# explain analyze SELECT count(1) AS count_1
wastac-# FROM wastac.t_swath_metadata_old
wastac-# WHERE quicklook = True
wastac-# AND
ST_Intersects(swath_bounding,ST_GeomFromText('POLYGON((104.765625
-39.0277188402,131.30859375 -39.0277188402,131.30859375
-15.7076627696,104.765625 -15.7076627696,104.765625 -39.0277188402))', -1));
Aggregate (cost=9505.13..9505.14 rows=1 width=0) (actual
time=95.681..95.682 rows=1 loops=1) - Bitmap Heap Scan on
t_swath_metadata_old (cost=506.77..9503.27 rows=745 width=0) (actual
time=4.198..93.366 rows=7274 loops=1)
Recheck Cond: (swath_bounding
'010300010005315A402F127C4A8C8343C0E06960402F127C4A8C8343C0E
0696040BD0E48C6526A2FC000315A40BD0E48C6526A2FC000315A402F127C4A8C8343C0'::geometry)
Filter: (quicklook AND _st_intersects(swath_bounding,
'010300010005315A402F127C4A8C8343C0E06960402F12
7C4A8C8343C0E0696040BD0E48C6526A2FC000315A40BD0E48C6526A2FC000315A402F127C4A8C8343C0'::geometry))
- Bitmap Index Scan on t_swath_metadata_old_swath_bounding_key
(cost=0.00..506.58 rows=16840 width=0) (actual time=3.557..3.557 ro
ws=9020 loops=1)
Index Cond: (swath_bounding
'010300010005315A402F127C4A8C8343C0E06960402F127C4A8C8343C00
000E0696040BD0E48C6526A2FC000315A40BD0E48C6526A2FC000315A402F127C4A8C8343C0'::geometry)
Total runtime: 95.757 ms
(7 rows)
3) Geography bounding box 1s:
wastac=# explain analyze SELECT count(1) AS count_1
wastac-# FROM wastac.t_swath_metadata
wastac-# WHERE wastac.t_swath_metadata.quicklook = True
wastac-# AND swath_bounding
ST_GeographyFromText('SRID=4326;POLYGON((104.765625
-39.0277188402,131.30859375 -39.0277188402,131.30859375
-15.7076627696,104.765625 -15.7076627696,104.765625 -39.0277188402))');
Aggregate (cost=10948.03..10948.04 rows=1 width=0) (actual
time=30.583..30.584 rows=1 loops=1) - Bitmap Heap Scan on
t_swath_metadata (cost=506.38..10943.48 rows=1820 width=0) (actual
time=8.884..27.786 rows=9806 loops=1)
Recheck Cond: (swath_bounding
'010320E610010005315A402F127C4A8C8343C0E06960402F127C4A8C8343C00
000E0696040BD0E48C6526A2FC000315A40BD0E48C6526A2FC000315A402F127C4A8C8343C0'::geography)
Filter: quicklook
- Bitmap Index Scan on t_swath_metadata_swath_bounding_key
(cost=0.00..505.92 rows=10348 width=0) (actual time=7.403..7.403 rows=1
4263 loops=1)
Index Cond: (swath_bounding
'010320E610010005315A402F127C4A8C8343C0E06960402F127C4A8C834
3C0E0696040BD0E48C6526A2FC000315A40BD0E48C6526A2FC000315A402F127C4A8C8343C0'::geography)
Total runtime: 30.637 ms
(7 rows)
On 28 May 2010 16:31, Paragon Corporation l...@pcorp.us wrote:
Okay I think the fix is a really
Re: [postgis-users] No index usage on geography query plan?
Right you are.
wastac= explain analyze 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))')
wastac- 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))'));
Aggregate (cost=30.48..30.49 rows=1 width=0) (actual time=11.459..11.459
rows=1 loops=1) - Index Scan using t_tile_geometry_border_key on
t_tile_geometry (cost=0.00..30.47 rows=2 width=0) (actual
time=0.697..11.416 rows=95 loops=1)
Index Cond: (border
'010320E61001000500F8C610001C305D40B24CBF44BC613FC0D6E253008C385D40DE72F563935640C0DF1B430070AF5C40EE77280AF4654
0C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geography)
Filter: st_intersects(border,
'010320E61001000500F8C610001C305D40B24CBF44BC613FC0D6E253008C385D40DE72F563935640C0DF1B430070AF5C40EE7728
0AF46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geography)
Total runtime: 11.537 ms
(5 rows)
In case it's relevant this was how I created the geography column from a
geometry one - I noticed the Polygon restriction had to be added in the
last statement (ie you don't get it for free from the cast in the first
statement).
create table wastac.t_tile_geometry as select *,
geography(ST_SetSRID(border, 4326)) as border_new,
geography(ST_SetSRID(centre, 4326)) as centre_new from
wastac.t_tile_geometry;
alter table wastac.t_tile_geometry_new drop column border;
alter table wastac.t_tile_geometry_new rename column border_new to border;
alter table wastac.t_tile_geometry alter column border set data type
geography(Polygon,4326);
On 27 May 2010 15:42, Paragon Corporation l...@pcorp.us wrote:
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
http://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
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,
'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
[postgis-users] No index usage on geography query plan?
Neither of the ST_Intersects clauses below invoke index usage according to
explain output, despite docs saying they should automatically be doing bbox
on the index;
explain analyze SELECT wastac.t_swath_metadata.swath_id AS
wastac_t_swath_metadata_swath_id
FROM wastac.t_swath_metadata
JOIN wastac.t_tile_metadata ON wastac.t_swath_metadata.swath_id =
wastac.t_tile_metadata.swath_id
JOIN wastac.t_tile_geometry ON wastac.t_tile_metadata.tile_id =
wastac.t_tile_geometry.tile_id
AND wastac.t_tile_metadata.grid_id = wastac.t_tile_geometry.grid_id
WHERE wastac.t_swath_metadata.quicklook = True
AND
ST_Intersects(swath_bounding,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))'))
AND wastac.t_tile_metadata.ob_cloud_m 2.0 GROUP BY
wastac.t_swath_metadata.swath_id
HAVING count(wastac.t_tile_metadata.tile_id) = 95 LIMIT 20
One of the above ST_Intersects uses this (just note the border
column/index):
\d wastac.t_tile_geometry
Table wastac.t_tile_geometry
Column | Type | Modifiers
-+-+--
tile_id | integer | not null
grid_id | integer | not null default
nextval('wastac.t_grid_type_grid_id_seq'::regclass)
* border | geography(Polygon,4326) |*
centre | geography(Point,4326) |
Indexes:
t_tile_geometry_pkey PRIMARY KEY, btree (tile_id, grid_id)
*t_tile_geometry_border_key gist (border)*
t_tile_geometry_centre_key gist (centre)
Foreign-key constraints:
fk_t_tile_geometry_1 FOREIGN KEY (grid_id) REFERENCES
wastac.t_grid_type(grid_id)
Referenced by:
TABLE wastac.t_tile_metadata CONSTRAINT fk_t_tile_metadata_2 FOREIGN
KEY (tile_id, grid_id) REFERENCES wastac.t_tile_geometry(tile_id, grid_id)
Yet we see no evidence of GIST index usage here:
Limit (cost=0.00..2159649.87 rows=20 width=8) (actual
time=4036.091..44743.940 rows=20 loops=1)
- GroupAggregate (cost=0.00..1001537626.11 rows=9275 width=8) (actual
time=4036.088..44743.894 rows=20 loops=1)
Filter: (count(t_tile_metadata.tile_id) = 95)
- Nested Loop (cost=0.00..1001387855.03 rows=29926392 width=8)
(actual time=53.319..44735.239 rows=11683 loops=1)
- Nested Loop (cost=0.00..743388879.45 rows=29926392
width=12) (actual time=32.931..21859.582 rows=1719454 loops=1)
- Index Scan using t_swath_metadata_pkey on
t_swath_metadata (cost=0.00..669641.79 rows=9275 width=4) (actual
time=0.765..255.304 rows=451
loops=1)
Filter: (quicklook AND
st_intersects(swath_bounding,
'010320E61001000500F8C610001C305D40B24CBF44BC613FC0D6E253008C385D4
0DE72F563935640C0DF1B430070AF5C40EE77280AF46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geography))
- Index Scan using t_tile_metadata_pkey on
t_tile_metadata (cost=0.00..80037.21 rows=3227 width=12) (actual
time=6.403..45.723 rows=3813 l
oops=451)
Index Cond: (t_tile_metadata.swath_id =
t_swath_metadata.swath_id)
Filter: (t_tile_metadata.ob_cloud_m 2::double
precision)
- Index Scan using t_tile_geometry_pkey on t_tile_geometry
(cost=0.00..8.61 rows=1 width=8) (actual time=0.013..0.013 rows=0
loops=1719454)
Index Cond: ((t_tile_geometry.tile_id =
t_tile_metadata.tile_id) AND (t_tile_geometry.grid_id =
t_tile_metadata.grid_id))
*Filter: st_intersects(t_tile_geometry.border,
'010320E61001000500F8C610001C305D40B24CBF44BC613FC0D6E253008C385D40DE72F5639356
*
*
40C0DF1B430070AF5C40EE77280AF46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geography)
*
Total runtime: 44744.241 ms
(14 rows)
However if I swap out ST_Intersects for , things start to look more
familiar (note appearance of t_tile_geometry_border_key), but paradoxically
the query is longer;
Limit (cost=46397.20..46397.42 rows=15 width=8) (actual
time=65951.363..65951.692 rows=8 loops=1)
- HashAggregate (cost=46397.20..46397.42 rows=15 width=8) (actual
time=65951.361..65951.685 rows=8 loops=1)
Filter: (count(t_tile_metadata.tile_id) = 95)
- Hash Join (cost=2986.26..46397.12 rows=15 width=8) (actual
time=960.682..65886.428 rows=67553 loops=1)
Hash Cond: (t_tile_metadata.swath_id =
t_swath_metadata.swath_id)
- Nested Loop (cost=86.37..43490.92 rows=1644 width=8)
(actual time=106.668..64931.709 rows=84257 loops=1)
Join Filter: (t_tile_metadata.grid_id =
Re: [postgis-users] No index usage on geography query plan?
On 25 May 2010 22:35, Mark Cave-Ayland mark.cave-ayl...@siriusit.co.ukwrote: Nicholas Bower wrote: Neither of the ST_Intersects clauses below invoke index usage according to explain output, despite docs saying they should automatically be doing bbox on the index; (cut) What's going on - the difference in total cost above proves to me the indexes are not being used. I think you're missing the point here; the aim of the planner is to work out which join order will produce the query that executes in the shortest time. Therefore just because an index is present does not necessarily mean it is correct to use it. From your first query (where the spatial index is not being used): Total runtime: 44744.241 ms From your second query (where the spatial index is being used): Total runtime: 65952.140 ms So I'd say that this is working exactly as it should be, since the join order chosen by the planner has resulted in the shortest query time. A repeated explain analyze on the first query showed about 45s second time around (same). However for the version, second time around was just 2s (from 65s). Saving 15s and losing caching seems like a bit of a false win no? ___ 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?
Perhaps this makes it more obvious - 9s to query a table of just 1.3M rows
with ST_Intersects and 20ms using .
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=9175.760..9175.761 rows=1 loops=1)
- Seq Scan on t_tile_geometry (cost=0.00..362993.14 rows=425463
width=0) (actual time=2164.049..9175.706 rows=95 loops=1)
Filter: st_intersects(border,
'010320E61001000500F8C610001C305D40B24CBF44BC613FC0D6E253008C385D40DE72F563935640C0DF1B430070AF5C40EE77280A
F46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geography)
Total runtime: 9175.859 ms
(4 rows)
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,
'010320E61001000500F8C610001C305D40B24CBF44BC613FC0D6E253008C385D40DE72F563935640C0DF1B430070AF5C40EE77280A
F46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geography)
Total runtime: 8854.976 ms
(4 rows)
wastac= explain analyze 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))');
Aggregate (cost=28.99..29.00 rows=1 width=0) (actual time=24.443..24.443
rows=1 loops=1)
- Index Scan using t_tile_geometry_border_key on t_tile_geometry
(cost=0.00..28.97 rows=6 width=0) (actual time=9.952..24.407 rows=112
loops=1)
Index Cond: (border
'010320E61001000500F8C610001C305D40B24CBF44BC613FC0D6E253008C385D40DE72F563935640C0DF1B430070AF5C40EE77280AF46540C
01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geography)
Total runtime: 24.681 ms
On 25 May 2010 22:45, Nicholas Bower n...@petangent.net wrote:
On 25 May 2010 22:35, Mark Cave-Ayland mark.cave-ayl...@siriusit.co.ukwrote:
Nicholas Bower wrote:
Neither of the ST_Intersects clauses below invoke index usage according
to explain output, despite docs saying they should automatically be doing
bbox on the index;
(cut)
What's going on - the difference in total cost above proves to me the
indexes are not being used.
I think you're missing the point here; the aim of the planner is to work
out which join order will produce the query that executes in the shortest
time. Therefore just because an index is present does not necessarily mean
it is correct to use it.
From your first query (where the spatial index is not being used):
Total runtime: 44744.241 ms
From your second query (where the spatial index is being used):
Total runtime: 65952.140 ms
So I'd say that this is working exactly as it should be, since the join
order chosen by the planner has resulted in the shortest query time.
A repeated explain analyze on the first query showed about 45s second time
around (same). However for the version, second time around was just 2s
(from 65s).
Saving 15s and losing caching seems like a bit of a false win no?
___
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?
What does the output of:
SELECT version(), postgis_full_version();
return?
PostgreSQL 8.4.3 on x86_64-pc-solaris2.10, compiled by GCC gcc (GCC) 3.4.3
(csl-sol210-3_4-branch+sol_rpath), 64-bit | POSTGIS=1.5.1
GEOS=3.2.1-CAPI-1.6.1 PROJ=Rel. 4.7.1, 23 September 2009 LIBXML=2.6.23
USE_STATS
I went back to the pre-geography verison of the table that still had
geometries (SRID=-1) and it's a different story with ST_Intersects. Quite
clearly there's a problem here - ST_Intersects printing out index usage fine
when geometries are used.
explain analyze select count(*) from wastac.t_tile_geometry_old where
ST_Intersects(border, ST_GeometryFromText('POLYGON((116.751709
-31.381779,116.883545 -32.676373,114.741211 -32.796510,114.796143
-31.316101,116.751709 -31.381779))', -1));
Aggregate (cost=256.30..256.31 rows=1 width=0) (actual time=81.997..81.998
rows=1 loops=1) - Bitmap Heap Scan on t_tile_geometry_old
(cost=5.09..256.25 rows=20 width=0) (actual time=76.303..81.950 rows=96
loops=1)
Recheck Cond: (border
'01030001000500F8C610001C305D40B24CBF44BC613FC0D6E253008C385D40DE72F563935640C0DF1
B430070AF5C40EE77280AF46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geometry)
Filter: _st_intersects(border,
'01030001000500F8C610001C305D40B24CBF44BC613FC0D6E253008C385D40DE72F56393564
0C0DF1B430070AF5C40EE77280AF46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geometry)
- Bitmap Index Scan on t_tile_geometry_old_border_key
(cost=0.00..5.09 rows=61 width=0) (actual time=0.307..0.307 ro
ws=100 loops=1)
Index Cond: (border
'01030001000500F8C610001C305D40B24CBF44BC613FC0D6E253008C385D40DE72F563935640C
0DF1B430070AF5C40EE77280AF46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geometry)
Total runtime: 82.107 ms
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
\d wastac.t_tile_geometry_old
Table wastac.t_tile_geometry_old
Column | Type | Modifiers
-+--+---
tile_id | integer | not null
grid_id | integer | not null
centre | geometry |
border | geometry |
Indexes:
t_tile_geometry_old_border_key gist (border)
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] installation on windows
Capture the start of the log and the first statement that fails. Below is just repeating that there was an initial failure and it is not executing more statements within this transaction. For example I've often found aborts caused by inadequate permissions, in which case something like -u db owner can be required. On 16 May 2010 21:26, arnauld ndefo ndefo2...@yahoo.fr wrote: Hi everybody, i am a new developer who use a mapnik to build my own map. I use Window instead of ubuntu.I want to know how to install postgis on windows? when i do psql -d gis -f c:\Program Files\PostgreSQL\8.3\share\co ntrib\lwpostgis.sql on my windows command shell i have this list of error: psql:c:/Program Files/PostgreSQL/8.3/share/contrib/lwpostgis.sql:4858: ERROR: c urrent transaction is aborted, commands ignored until end of transaction block psql:c:/Program Files/PostgreSQL/8.3/share/contrib/lwpostgis.sql:4864: ERROR: c urrent transaction is aborted, commands ignored until end of transaction block psql:c:/Program Files/PostgreSQL/8.3/share/contrib/lwpostgis.sql:4870: ERROR: c urrent transaction is aborted, commands ignored until end of transaction block psql:c:/Program Files/PostgreSQL/8.3/share/contrib/lwpostgis.sql:4876: ERROR: c urrent transaction is aborted, commands ignored until end of transaction block psql:c:/Program Files/PostgreSQL/8.3/share/contrib/lwpostgis.sql:4882: ERROR: c urrent transaction is aborted, commands ignored until end of transaction block psql:c:/Program Files/PostgreSQL/8.3/share/contrib/lwpostgis.sql:4888: ERROR: c urrent transaction is aborted, commands ignored until end of transaction block psql:c:/Program Files/PostgreSQL/8.3/share/contrib/lwpostgis.sql:4894: ERROR: c urrent transaction is aborted, commands ignored until end of transaction block psql:c:/Program Files/PostgreSQL/8.3/share/contrib/lwpostgis.sql:4900: ERROR: c urrent transaction is aborted, commands ignored until end of transaction block psql:c:/Program Files/PostgreSQL/8.3/share/contrib/lwpostgis.sql:4906: ERROR: c urrent transaction is aborted, commands ignored until end of transaction block psql:c:/Program Files/PostgreSQL/8.3/share/contrib/lwpostgis.sql:4912: ERROR: c urrent transaction is aborted, commands ignored until end of transaction block psql:c:/Program Files/PostgreSQL/8.3/share/contrib/lwpostgis.sql:4918: ERROR: c urrent transaction is aborted, commands ignored until end of transaction block psql:c:/Program Files/PostgreSQL/8.3/share/contrib/lwpostgis.sql:4923: ERROR: c urrent transaction is aborted, commands ignored until end of transaction block psql:c:/Program Files/PostgreSQL/8.3/share/contrib/lwpostgis.sql:4928: ERROR: c urrent transaction is aborted, commands ignored until end of transaction block psql:c:/Program Files/PostgreSQL/8.3/share/contrib/lwpostgis.sql:4933: ERROR: c urrent transaction is aborted, commands ignored until end of transaction block psql:c:/Program Files/PostgreSQL/8.3/share/contrib/lwpostgis.sql:4938: ERROR: c urrent transaction is aborted, commands ignored until end of transaction block psql:c:/Program Files/PostgreSQL/8.3/share/contrib/lwpostgis.sql:4948: ERROR: c urrent transaction is aborted, commands ignored until end of transaction block psql:c:/Program Files/PostgreSQL/8.3/share/contrib/lwpostgis.sql:4958: ERROR: c urrent transaction is aborted, commands ignored until end of transaction block psql:c:/Program Files/PostgreSQL/8.3/share/contrib/lwpostgis.sql:4968: ERROR: c urrent transaction is aborted, commands ignored until end of transaction block psql:c:/Program Files/PostgreSQL/8.3/share/contrib/lwpostgis.sql:4979: ERROR: c urrent transaction is aborted, commands ignored until end of transaction block psql:c:/Program Files/PostgreSQL/8.3/share/contrib/lwpostgis.sql:4989: ERROR: c urrent transaction is aborted, commands ignored until end of transaction block psql:c:/Program Files/PostgreSQL/8.3/share/contrib/lwpostgis.sql:4999: ERROR: c urrent transaction is aborted, commands ignored until end of transaction block psql:c:/Program Files/PostgreSQL/8.3/share/contrib/lwpostgis.sql:5009: ERROR: c urrent transaction is aborted, commands ignored until end of transaction block psql:c:/Program Files/PostgreSQL/8.3/share/contrib/lwpostgis.sql:5019: ERROR: c urrent transaction is aborted, commands ignored until end of transaction block psql:c:/Program Files/PostgreSQL/8.3/share/contrib/lwpostgis.sql:5025: ERROR: c urrent transaction is aborted, commands ignored until end of transaction block psql:c:/Program Files/PostgreSQL/8.3/share/contrib/lwpostgis.sql:5031: ERROR: c urrent transaction is aborted, commands ignored until end of transaction block psql:c:/Program Files/PostgreSQL/8.3/share/contrib/lwpostgis.sql:5041: ERROR: c urrent transaction is aborted, commands ignored until end of transaction block psql:c:/Program
[postgis-users] ST_CoveredBy supporting geography?
I've read the 1.5 reference docs but can't figure out what is wrong with
below. ST_CoveredBy(geography, geography) should work right?
select count(*) from t_swath_metadata where ST_CoveredBy(
ST_GeogFromText('SRID=4326;POLYGON((123.1773295292851 -16.07501950971949,
122.745660066063 -16.83965661151543,
124.1310486688905 -16.44978157737539,
123.1773295292851 -16.07501950971949))'),
swath_bounding);
ERROR: geography_covers: only POLYGON and POINT types are currently
supported
CONTEXT: SQL function st_covers statement 1
** Error **
ERROR: geography_covers: only POLYGON and POINT types are currently
supported
SQL state: XX000
Context: SQL function st_covers statement 1
db= \d t_swath_metadata;
...
swath_bounding| geography(Polygon,4326) |
Indexes:
...
t_swath_metadata_swath_bounding_key gist (swath_bounding)
Thanks, Nick
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_CoveredBy supporting geography?
On 12 May 2010 08:14, Paul Ramsey pram...@opengeo.org wrote:
The doc example appears to not mention the limitation in the geography
implementation, which can be fixed. The example is correct, insofar
as it is against geometries, but again, doesn't illustrate the
limitation in the geography implementation.
I see that would explain it. So how do I actually determine if 1 geography
polygon is wholly inside another (on the WGS spheroid)?
You can cast geography to geometry and use the function that way but
be forewarned about any shape that crosses the datesline/poles or has
very long edges.
Not an option - this warning is precisely why I spent substantial effort
migrating our DB out of 1.3 geometries into 1.5 geographies. We have global
satellite views that cross the IDL, and intersections of spatial regions
nowhere near these shapes were previously returning true using geometries
(wrapping the wrong way around the Earth).
Am very grateful for geographies - just need a way to do the query with 2
polygons...
On Tue, May 11, 2010 at 3:06 PM, Nick Bower n...@petangent.net wrote:
Thanks but not according to the doc I think?
1) Abstract superclass is mentioned in API, not point,
2) the SQL example uses circles
3) the blog link specifically uses polygons in it's discussion of OGC
coverage behaviour.
http://postgis.refractions.net/documentation/manual-1.5/ST_CoveredBy.html
I'm not disputing your explanation given the evidence, but suggest the
docs
are entirely misleading for me figuring out if one polygon geography is
wholey inside another.
On 11/05/2010, at 11:48 PM, Paul Ramsey pram...@opengeo.org wrote:
Bad sentence construction in the error, perhaps? The restriction only
polygon and point means that one argument must be a polygon and one
must be a point. Both your arguments are polygons.
P.
On Tue, May 11, 2010 at 4:51 AM, Nicholas Bower n...@petangent.net
wrote:
I've read the 1.5 reference docs but can't figure out what is wrong
with
below. ST_CoveredBy(geography, geography) should work right?
select count(*) from t_swath_metadata where ST_CoveredBy(
ST_GeogFromText('SRID=4326;POLYGON((123.1773295292851
-16.07501950971949,
122.745660066063 -16.83965661151543,
124.1310486688905 -16.44978157737539,
123.1773295292851 -16.07501950971949))'),
swath_bounding);
ERROR: geography_covers: only POLYGON and POINT types are currently
supported
CONTEXT: SQL function st_covers statement 1
** Error **
ERROR: geography_covers: only POLYGON and POINT types are currently
supported
SQL state: XX000
Context: SQL function st_covers statement 1
db= \d t_swath_metadata;
...
swath_bounding| geography(Polygon,4326) |
Indexes:
...
t_swath_metadata_swath_bounding_key gist (swath_bounding)
Thanks, Nick
___
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
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_CoveredBy supporting geography?
Thanks for the clarification. True intersects (ST_Intersects) or bounding
box intersects ()?
I'll ask around and see if there is interest in funding something.
Personally however I've been working with no secured funding on this for 3
years (not my day job) so can't fund myself sorry;
http://wastac.ivec.org
The issue I think we have is that intersection of bounding boxes simply
isn't very good for querying global space views from a small ROI as the
views are quite pin cushioned as you would expect. Maybe it's just a
compromise we have to live with for not returning views of New Zealand in
searches of the Indian Ocean. ;-)
On 12 May 2010 10:08, Paul Ramsey pram...@opengeo.org wrote:
On Tue, May 11, 2010 at 4:30 PM, Nicholas Bower n...@petangent.net
wrote:
On 12 May 2010 08:14, Paul Ramsey pram...@opengeo.org wrote:
The doc example appears to not mention the limitation in the geography
implementation, which can be fixed. The example is correct, insofar
as it is against geometries, but again, doesn't illustrate the
limitation in the geography implementation.
I see that would explain it. So how do I actually determine if 1
geography
polygon is wholly inside another (on the WGS spheroid)?
Right now you don't. You've got intersects on all types, but that's
it. I can provide a quote if you want to fund the work.
Workarounds would including testing for index interaction in geography
land, then flipping both candidates out to a suitable projection for
a test in geometry land. See the ST_Buffer(geography) implementation
for an example of that approach. This would avoid the singularity
issues around the poles/dateline, but would fail for very large
(geographic size-wise) objects.
P.
You can cast geography to geometry and use the function that way but
be forewarned about any shape that crosses the datesline/poles or has
very long edges.
Not an option - this warning is precisely why I spent substantial effort
migrating our DB out of 1.3 geometries into 1.5 geographies. We have
global
satellite views that cross the IDL, and intersections of spatial regions
nowhere near these shapes were previously returning true using geometries
(wrapping the wrong way around the Earth).
Am very grateful for geographies - just need a way to do the query with 2
polygons...
On Tue, May 11, 2010 at 3:06 PM, Nick Bower n...@petangent.net wrote:
Thanks but not according to the doc I think?
1) Abstract superclass is mentioned in API, not point,
2) the SQL example uses circles
3) the blog link specifically uses polygons in it's discussion of OGC
coverage behaviour.
http://postgis.refractions.net/documentation/manual-1.5/ST_CoveredBy.html
I'm not disputing your explanation given the evidence, but suggest the
docs
are entirely misleading for me figuring out if one polygon geography
is
wholey inside another.
On 11/05/2010, at 11:48 PM, Paul Ramsey pram...@opengeo.org wrote:
Bad sentence construction in the error, perhaps? The restriction
only
polygon and point means that one argument must be a polygon and one
must be a point. Both your arguments are polygons.
P.
On Tue, May 11, 2010 at 4:51 AM, Nicholas Bower n...@petangent.net
wrote:
I've read the 1.5 reference docs but can't figure out what is wrong
with
below. ST_CoveredBy(geography, geography) should work right?
select count(*) from t_swath_metadata where ST_CoveredBy(
ST_GeogFromText('SRID=4326;POLYGON((123.1773295292851
-16.07501950971949,
122.745660066063 -16.83965661151543,
124.1310486688905 -16.44978157737539,
123.1773295292851 -16.07501950971949))'),
swath_bounding);
ERROR: geography_covers: only POLYGON and POINT types are currently
supported
CONTEXT: SQL function st_covers statement 1
** Error **
ERROR: geography_covers: only POLYGON and POINT types are currently
supported
SQL state: XX000
Context: SQL function st_covers statement 1
db= \d t_swath_metadata;
...
swath_bounding| geography(Polygon,4326) |
Indexes:
...
t_swath_metadata_swath_bounding_key gist (swath_bounding)
Thanks, Nick
___
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
Re: [postgis-users] Conversion from undefined geometry to geography?
Interesting. So if I do it as you've suggested, how do I get a polygon constraint on the new column as normally present with, alter table xxx add column boundary geography(POLYGON, 4326); On Thu, Apr 15, 2010 at 1:08 AM, Paul Ramsey pram...@cleverelephant.cawrote: Don't update a 250M row table, do create newtable as select *,geography(the_geom) as geog from oldtable; drop oldtable; alter table newtable rename to newtable; Even with indexes and constraints dropped, the cost of updating all those rows in a transactional context is very very high. If you don't mind taking some extra risks, you can also set fsync=off; before you start and turn it back on when you're done. P On Tue, Apr 13, 2010 at 11:03 PM, Nicholas Bower n...@petangent.net wrote: Ok thanks - so this is really my solution by the looks; UPDATE mytable SET new_geog_col = geography(ST_SetSRID(old_geom_col, 4326)); Be curious to see how it performs on a quarter billion rows - hopefully should be fine with constraints/indexes dropped as usual. On Wed, Apr 14, 2010 at 3:50 PM, Paragon Corporation l...@pcorp.us wrote: You mean ST_SetSRID :) R -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Paul Ramsey Sent: Tuesday, April 13, 2010 11:59 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] Conversion from undefined geometry to geography? Just use geography(the_geom), it'll work fine. If you fine you have srid issues (I don't think you should) use geography(setsrid(the_geom,4326)) P On Tue, Apr 13, 2010 at 8:47 PM, Nicholas Bower n...@petangent.net wrote: We have some tables in which we've used Geometry to store polygon and point lan/lon (srid = -1). We embarked on this before PostGIS 1.5.x's spherical Geography objects. Now we intend to upgrade and migrate these to new Geography types (eg new geography col, copy/convert, drop original geometry col). Is there an existing function that provides a conversion from Geometry to Geography that could be used in a simple table update? This doesn't give away too many clues what will happen when given a geometry with srid = -1 for example: http://postgis.refractions.net/docs/geography.html TIA. ___ 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 ___ 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] PostGIS 1.5.1 causing postmaster crash on index creation
Just upgraded to PostGIS 1.5.1 on a windows XP machine (from 1.3.4) and when creating an index on a geography column using this single statement; CREATE INDEX t_swath_metadata_swath_bounding_key ON wastac.t_swath_metadata USING gist (swath_bounding); I get a client error DEBUG: StartTransactionCommandDEBUG: StartTransactionDEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:DEBUG: ProcessUtilityDEBUG: mapped win32 error code 2 to 2 ** Error ** and find the entire server / postmaster has crashed and no longer running. Log below; 2010-04-15 22:35:12 EST LOG: database system was interrupted; last known up at 2010-04-15 22:32:33 EST 2010-04-15 22:35:12 EST LOG: database system was not properly shut down; automatic recovery in progress 2010-04-15 22:35:13 EST LOG: redo starts at C/F5D9EAB0 2010-04-15 22:35:13 EST LOG: loaded library $libdir/plugins/plugin_debugger.dll 2010-04-15 22:35:13 EST FATAL: the database system is starting up 2010-04-15 22:35:13 EST LOG: unexpected pageaddr C/EEFDE000 in log file 12, segment 246, offset 16637952 2010-04-15 22:35:13 EST LOG: redo done at C/F6FDCC90 2010-04-15 22:35:13 EST LOG: last completed transaction was at log time 2010-04-15 22:33:37.687+10 2010-04-15 22:35:14 EST LOG: loaded library $libdir/plugins/plugin_debugger.dll 2010-04-15 22:35:14 EST FATAL: the database system is starting up 2010-04-15 22:35:14 EST LOG: database system is ready to accept connections 2010-04-15 22:35:15 EST LOG: loaded library $libdir/plugins/plugin_debugger.dll 2010-04-15 22:36:22 EST LOG: loaded library $libdir/plugins/plugin_debugger.dll 2010-04-15 22:36:26 EST LOG: loaded library $libdir/plugins/plugin_debugger.dll 2010-04-15 22:36:27 EST LOG: loaded library $libdir/plugins/plugin_debugger.dll *2010-04-15 22:36:35 EST LOG: server process (PID 14600) was terminated by exception 0xC005* *2010-04-15 22:36:35 EST HINT: See C include file ntstatus.h for a description of the hexadecimal value.* 2010-04-15 22:36:35 EST LOG: terminating any other active server processes 2010-04-15 22:36:35 EST WARNING: terminating connection because of crash of another server process 2010-04-15 22:36:35 EST 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. 2010-04-15 22:36:35 EST HINT: In a moment you should be able to reconnect to the database and repeat your command. 2010-04-15 22:36:35 EST WARNING: terminating connection because of crash of another server process 2010-04-15 22:36:35 EST 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. 2010-04-15 22:36:35 EST HINT: In a moment you should be able to reconnect to the database and repeat your command. 2010-04-15 22:36:35 EST LOG: all server processes terminated; reinitializing 2010-04-15 22:36:36 EST FATAL: pre-existing shared memory block is still in use 2010-04-15 22:36:36 EST HINT: Check if there are any old server processes still running, and terminate them. I have been using this Postgresql instance (8.3) with PostGIS 1.3.4 for a long time previously to do similar operations on geometries and never encountered a crash like this. I must admit there is not much to go on with that log above - raising to debug shows below: 2010-04-15 22:48:57 EST DEBUG: StartTransactionCommand 2010-04-15 22:48:57 EST DEBUG: StartTransaction 2010-04-15 22:48:57 EST DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: 2010-04-15 22:48:57 EST DEBUG: ProcessUtility 2010-04-15 22:48:57 EST DEBUG: mapped win32 error code 2 to 2 2010-04-15 22:48:59 EST DEBUG: reaping dead processes 2010-04-15 22:48:59 EST DEBUG: server process (PID 15736) was terminated by exception 0xC005 2010-04-15 22:48:59 EST HINT: See C include file ntstatus.h for a description of the hexadecimal value. 2010-04-15 22:48:59 EST LOG: server process (PID 15736) was terminated by exception 0xC005 2010-04-15 22:48:59 EST HINT: See C include file ntstatus.h for a description of the hexadecimal value. 2010-04-15 22:48:59 EST LOG: terminating any other active server processes 2010-04-15 22:48:59 EST DEBUG: sending SIGQUIT to process 13536 2010-04-15 22:48:59 EST DEBUG: sending SIGQUIT to process 14200 2010-04-15 22:48:59 EST DEBUG: sending SIGQUIT to process 13948 2010-04-15 22:48:59 EST DEBUG: reaping dead processes 2010-04-15 22:48:59 EST DEBUG: reaping dead processes 2010-04-15 22:48:59 EST DEBUG: reaping dead processes 2010-04-15 22:48:59 EST LOG: all server processes terminated; reinitializing 2010-04-15 22:48:59 EST DEBUG: shmem_exit(0) 2010-04-15 22:48:59 EST DEBUG: invoking IpcMemoryCreate(size=36741120) 2010-04-15 22:49:00 EST FATAL:
Re: [postgis-users] Conversion from undefined geometry to geography?
Ok thanks - so this is really my solution by the looks; UPDATE mytable SET new_geog_col = geography(ST_SetSRID(old_geom_col, 4326)); Be curious to see how it performs on a quarter billion rows - hopefully should be fine with constraints/indexes dropped as usual. On Wed, Apr 14, 2010 at 3:50 PM, Paragon Corporation l...@pcorp.us wrote: You mean ST_SetSRID :) R -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Paul Ramsey Sent: Tuesday, April 13, 2010 11:59 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] Conversion from undefined geometry to geography? Just use geography(the_geom), it'll work fine. If you fine you have srid issues (I don't think you should) use geography(setsrid(the_geom,4326)) P On Tue, Apr 13, 2010 at 8:47 PM, Nicholas Bower n...@petangent.net wrote: We have some tables in which we've used Geometry to store polygon and point lan/lon (srid = -1). We embarked on this before PostGIS 1.5.x's spherical Geography objects. Now we intend to upgrade and migrate these to new Geography types (eg new geography col, copy/convert, drop original geometry col). Is there an existing function that provides a conversion from Geometry to Geography that could be used in a simple table update? This doesn't give away too many clues what will happen when given a geometry with srid = -1 for example: http://postgis.refractions.net/docs/geography.html TIA. ___ 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
[postgis-users] Conversion from undefined geometry to geography?
We have some tables in which we've used Geometry to store polygon and point lan/lon (srid = -1). We embarked on this before PostGIS 1.5.x's spherical Geography objects. Now we intend to upgrade and migrate these to new Geography types (eg new geography col, copy/convert, drop original geometry col). Is there an existing function that provides a conversion from Geometry to Geography that could be used in a simple table update? This doesn't give away too many clues what will happen when given a geometry with srid = -1 for example: http://postgis.refractions.net/docs/geography.html TIA. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] how to backup/restore
Thanks - I ended up with the opposite solution actually and not dumping postgis at all. 1. All my data tables in separate schema (doesn't include postgis - that's in default) 2. Dump just this separate data schema using pg_dump -Fc -N schema 3. Build new DB, install postgis manually according to docs (public schema by default). 4. Restore the data dump Now free of errors, but yet to see it work/finish. Tends to thrash disk after a few hours, no CPU activity and no subsequent DB increase after a couple of gigs (my DB is 40G, 2G dump size). Looking at dropping constraints - indexes were already mostly absent. Probably postgresql territory not postgis though. My conclusion - postgis internals are not capable of a standard portable dump due to observation of hard coded paths and who knows what else in there. Sort of makes sense if you consider them to be system tables. However I'm still unsure what I lose by not dumping them along with my own schema. I note your solution of the separate schema using default path hack - interesting that this works (assume you change the search path for all db updater roles). On Mon, Apr 12, 2010 at 11:47 AM, Ben Madin li...@remoteinformation.com.auwrote: Nicholas, I can't answer 'What's the right way?', as I'm sure I don't have it yet, but I have found the biggest issue is in getting postgis to transfer, so I do it in steps: 1. always install postgis into it's own schema (either create the schema - gis - and set the search-path before \i postgis.sql command, or edit the postgis.sql file) 2. when dumping, ignore the gis schema = pg-dump -N gis database database.dump 3. when recreating, create new database, import postgis (as in 1) then restore database.dump (psql newdatabase database.dump) Or some variant of the above - and I'd love to know / be shown a better way! As a side benefit (If it helps,) I also use the gis schema for reasonably static GIS data - background maps etc. Then my backups don't include it, and are often much smaller for it, which helps going between machines. cheers Ben On 09/04/2010, at 21:20 , Nicholas Bower wrote: I'm trying to create a new instance on a different platform of a postgis-enabled database, starting with just the schema definition. What's the right way? This doesn't work below - do I have to partition into separate schemas to have this work perhaps? Thanks, Nick [Solaris Postgis 1.3.4] pg_dump -scFc database schema.sql [Windows Postgis 1.5.1] pgrestore -d database schema.sql End result - lots of errors and missing any tables containing postgis objects. Note the hard coded c language function library paths below; pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 6; 2615 18201 SCHEMA wastac wastacad pg_restore: [archiver (db)] could not execute query: ERROR: schema wastac already exists Command was: CREATE SCHEMA wastac; pg_restore: [archiver (db)] Error from TOC entry 1223; 2612 17398 PROCEDURAL LANGUAGE plpgsql postgres pg_restore: [archiver (db)] could not execute query: ERROR: language plpgsql already exists Command was: CREATE PROCEDURAL LANGUAGE plpgsql; pg_restore: [archiver (db)] Error from TOC entry 1115; 0 0 SHELL TYPE box2d postgres pg_restore: [archiver (db)] could not execute query: ERROR: type box2d already exists Command was: CREATE TYPE box2d; pg_restore: [archiver (db)] Error from TOC entry 216; 1255 17477 FUNCTION st_box2d_in(cstring) postgres pg_restore: [archiver (db)] could not execute query: ERROR: permission denied for language c Command was: CREATE FUNCTION st_box2d_in(cstring) RETURNS box2d AS '/usr/local/postgis-1.3.4/lib/liblwgeom', 'BOX2DFLOAT4_in' LAN... pg_restore: [archiver (db)] could not execute query: ERROR: function public.st_box2d_in(cstring) does not exist Command was: ALTER FUNCTION public.st_box2d_in(cstring) OWNER TO postgres; pg_restore: [archiver (db)] Error from TOC entry 218; 1255 17479 FUNCTION st_box2d_out(box2d) postgres pg_restore: [archiver (db)] could not execute query: ERROR: permission denied for language c Command was: CREATE FUNCTION st_box2d_out(box2d) RETURNS cstring AS '/usr/local/postgis-1.3.4/lib/liblwgeom', 'BOX2DFLOAT4_out' L... pg_restore: [archiver (db)] could not execute query: ERROR: function public.st_box2d_out(box2d) does not exist Command was: ALTER FUNCTION public.st_box2d_out(box2d) OWNER TO postgres; pg_restore: [archiver (db)] Error from TOC entry 1114; 1247 17475 TYPE box2d postgres ... pg_restore: [archiver (db)] could not execute query: ERROR: relation t_tile_geometry does not exist Command was: GRANT SELECT ON TABLE t_tile_geometry TO wastacportal; pg_restore: [archiver (db)] could not execute query: ERROR: relation t_tile_geometry does not exist Command was: GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE
Re: [postgis-users] how to backup/restore
On Mon, Apr 12, 2010 at 12:21 PM, Ben Madin li...@remoteinformation.com.auwrote: OK On 12/04/2010, at 10:03 , Nicholas Bower wrote: 2. Dump just this separate data schema using pg_dump -Fc -N schema I think here you mean -n?, but it's six of one and half a dozen of the other. I routinely use different schema's for different aspects of the database, hence easier to just exclude one. Yes -n you're right. I note your solution of the separate schema using default path hack - interesting that this works (assume you change the search path for all db updater roles). I'm not sure what you mean by this question, sorry. I do change the search_path for the database - ALTER database SET search_path TO data, reference, users, gis; if that is what you are referring to? Yep that's it - you're changing the search path not just of the restore, but all roles using that database ongoing so they can find the postgis functions. When I started experimenting with Postgis back in 2003, I couldn't get it to work so ever since I've used public schema for postgis. I should have tried harder ;) Btw have you restored your backups from scratch before and found them to work? ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] how to backup/restore
I'm trying to create a new instance on a different platform of a postgis-enabled database, starting with just the schema definition. What's the right way? This doesn't work below - do I have to partition into separate schemas to have this work perhaps? Thanks, Nick [Solaris Postgis 1.3.4] pg_dump -scFc database schema.sql [Windows Postgis 1.5.1] pgrestore -d database schema.sql End result - lots of errors and missing any tables containing postgis objects. Note the hard coded c language function library paths below; pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 6; 2615 18201 SCHEMA wastac wastacad pg_restore: [archiver (db)] could not execute query: ERROR: schema wastac already exists Command was: CREATE SCHEMA wastac; pg_restore: [archiver (db)] Error from TOC entry 1223; 2612 17398 PROCEDURAL LANGUAGE plpgsql postgres pg_restore: [archiver (db)] could not execute query: ERROR: language plpgsql already exists Command was: CREATE PROCEDURAL LANGUAGE plpgsql; pg_restore: [archiver (db)] Error from TOC entry 1115; 0 0 SHELL TYPE box2d postgres pg_restore: [archiver (db)] could not execute query: ERROR: type box2d already exists Command was: CREATE TYPE box2d; pg_restore: [archiver (db)] Error from TOC entry 216; 1255 17477 FUNCTION st_box2d_in(cstring) postgres pg_restore: [archiver (db)] could not execute query: ERROR: permission denied for language c Command was: CREATE FUNCTION st_box2d_in(cstring) RETURNS box2d AS '/usr/local/postgis-1.3.4/lib/liblwgeom', 'BOX2DFLOAT4_in' LAN... pg_restore: [archiver (db)] could not execute query: ERROR: function public.st_box2d_in(cstring) does not exist Command was: ALTER FUNCTION public.st_box2d_in(cstring) OWNER TO postgres; pg_restore: [archiver (db)] Error from TOC entry 218; 1255 17479 FUNCTION st_box2d_out(box2d) postgres pg_restore: [archiver (db)] could not execute query: ERROR: permission denied for language c Command was: CREATE FUNCTION st_box2d_out(box2d) RETURNS cstring AS '/usr/local/postgis-1.3.4/lib/liblwgeom', 'BOX2DFLOAT4_out' L... pg_restore: [archiver (db)] could not execute query: ERROR: function public.st_box2d_out(box2d) does not exist Command was: ALTER FUNCTION public.st_box2d_out(box2d) OWNER TO postgres; pg_restore: [archiver (db)] Error from TOC entry 1114; 1247 17475 TYPE box2d postgres ... pg_restore: [archiver (db)] could not execute query: ERROR: relation t_tile_geometry does not exist Command was: GRANT SELECT ON TABLE t_tile_geometry TO wastacportal; pg_restore: [archiver (db)] could not execute query: ERROR: relation t_tile_geometry does not exist Command was: GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE t_tile_geometry TO wastac; WARNING: errors ignored on restore: 1586 ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
