[postgis-users] Usage of Transform() function in a query.
David.Jacques at CCRS.NRCan.gc.ca
David.Jacques at CCRS.NRCan.gc.ca
Thu Feb 3 06:32:23 PST 2005
Since my original post is not visible below....
I should mention that it would be 42304 / NRCan LCC Canada
-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
postgis-users-request at postgis.refractions.net
Sent: Thursday, February 03, 2005 9:36 AM
To: postgis-users at postgis.refractions.net
Subject: postgis-users Digest, Vol 28, Issue 8
Send postgis-users mailing list submissions to
postgis-users at postgis.refractions.net
To subscribe or unsubscribe via the World Wide Web, visit
http://postgis.refractions.net/mailman/listinfo/postgis-users
or, via email, send a message with subject or body 'help' to
postgis-users-request at postgis.refractions.net
You can reach the person managing the list at
postgis-users-owner at postgis.refractions.net
When replying, please edit your Subject line so it is more specific than
"Re: Contents of postgis-users digest..."
Today's Topics:
1. RE: About Invalid OGC WKT Error (Kralidis,Tom [Burlington])
2. FW: [postgis-users] About Invalid OGC WKT Error
(Kralidis,Tom [Burlington])
3. Re: About Invalid OGC WKT Error (strk at refractions.net)
4. Re: Bad query optimizer misestimation because of TOAST tables
(Markus Schaber)
5. RE: About Invalid OGC WKT Error (Mark Cave-Ayland)
6. RE: About Invalid OGC WKT Error (Kralidis,Tom [Burlington])
7. RE: About Invalid OGC WKT Error (Kralidis,Tom [Burlington])
8. RE: Usage of Transform() function in a query.
(David.Jacques at CCRS.NRCan.gc.ca)
----------------------------------------------------------------------
Message: 1
Date: Wed, 2 Feb 2005 18:46:23 -0500
From: "Kralidis,Tom [Burlington]" <Tom.Kralidis at ec.gc.ca>
Subject: RE: [postgis-users] About Invalid OGC WKT Error
To: "PostGIS Users Discussion"
<postgis-users at postgis.refractions.net>, "Paul Ramsey"
<pramsey at refractions.net>
Message-ID:
<2576812186CDD411BF1500508B6DCE950972F525 at ecnwri1.ontario.int.ec.gc.ca>
Content-Type: text/plain; charset="us-ascii"
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
> Paul Ramsey
> Sent: Wednesday, 02 February, 2005 18:39
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] About Invalid OGC WKT Error
>
>
> What happens when you plug the query into psql?
>
> SELECT
> astext(force_collection(force_2d(the_geom)),'NDR'),OID::text
> from service_endpoints WHERE the_geom &&
> setSRID('BOX3D(-141.089 36.392987,-52.089 89.784987)'::BOX3D,
> find_srid('','service_endpoints','the_geom') )
>
I get this output:
[postgres at devgeo postgres]$ psql -f
/usr/local/wwwsites/apache/devgeo.cciw.ca/apps/mapserv/services/eccat/te
st.sql -d devgeodb
psql:/usr/local/wwwsites/apache/devgeo.cciw.ca/apps/mapserv/services/ecc
at/sss:1: ERROR: function astext(geometry, "unknown") does not exist
HINT: No function matches the given name and argument types. You may need
to add explicit type casts.
[postgres at devgeo postgres]$
>
> Kralidis,Tom [Burlington] wrote:
>
> > [Wed Feb 02 18:45:22 2005] [error] [client 192.75.68.5]
> > query_string_0_6:DECLARE mycursor BINARY CURSOR FOR SELECT
> > asbinary(force_collection(force_2d(the_geom)),'NDR'),OID::text from
> > service_endpoints WHERE the_geom && setSRID('BOX3D(-141.089
> > 36.392987,-52.089 89.784987)'::BOX3D,
> > find_srid('','service_endpoints','the_geom') )
>
> _______________________________________________
> postgis-users mailing list postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
------------------------------
Message: 2
Date: Thu, 3 Feb 2005 08:34:02 -0500
From: "Kralidis,Tom [Burlington]" <Tom.Kralidis at ec.gc.ca>
Subject: FW: [postgis-users] About Invalid OGC WKT Error
To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
Message-ID:
<2576812186CDD411BF1500508B6DCE950972F526 at ecnwri1.ontario.int.ec.gc.ca>
Content-Type: text/plain; charset="us-ascii"
I get this:
$ /usr/local/pgsql/bin/psql -f ./t.sql -d thedb -U scott
psql:./t.sql:1: ERROR: function astext(geometry, "unknown") does not exist
HINT: No function matches the given name and argument types. You may need
to add explicit type casts.
..Tom
-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Paul
Ramsey
Sent: Wednesday, 02 February, 2005 18:39
To: PostGIS Users Discussion
Subject: Re: [postgis-users] About Invalid OGC WKT Error
What happens when you plug the query into psql?
SELECT astext(force_collection(force_2d(the_geom)),'NDR'),OID::text from
service_endpoints WHERE the_geom && setSRID('BOX3D(-141.089
36.392987,-52.089 89.784987)'::BOX3D,
find_srid('','service_endpoints','the_geom') )
Kralidis,Tom [Burlington] wrote:
> [Wed Feb 02 18:45:22 2005] [error] [client 192.75.68.5]
> query_string_0_6:DECLARE mycursor BINARY CURSOR FOR SELECT
> asbinary(force_collection(force_2d(the_geom)),'NDR'),OID::text from
> service_endpoints WHERE the_geom && setSRID('BOX3D(-141.089
> 36.392987,-52.089 89.784987)'::BOX3D,
> find_srid('','service_endpoints','the_geom') )
_______________________________________________
postgis-users mailing list postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
------------------------------
Message: 3
Date: Thu, 3 Feb 2005 14:35:45 +0100
From: strk at refractions.net
Subject: Re: [postgis-users] About Invalid OGC WKT Error
To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
Message-ID: <20050203133545.GA14693 at freek.keybit.net>
Content-Type: text/plain; charset=us-ascii
On Wed, Feb 02, 2005 at 06:22:56PM -0500, Kralidis,Tom [Burlington] wrote:
>
> Here's what shows up in my error logs when MapServer attempts to
> access when I have MapServer in debugging mode:
(cuts)
> query_string_0_6:DECLARE mycursor BINARY CURSOR FOR SELECT
> asbinary(force_collection(force_2d(the_geom)),'NDR'),OID::text from
> service_endpoints WHERE the_geom && setSRID('BOX3D(-141.089
> 36.392987,-52.089 89.784987)'::BOX3D,
> find_srid('','service_endpoints','the_geom') )
(cuts)
> msPOSTGISLayerClose -- query_result is NULL
That query does not return any result. Check manually invoking it.
Also, check:
# SELECT extent(asbinary(force_collection(force_2d(the_geom)),'NDR'));
# SELECT setSRID('BOX3D(-141.089 36.392987,-52.089 89.784987)'::BOX3D,
find_srid('','service_endpoints','the_geom') )
--strk;
------------------------------
Message: 4
Date: Thu, 03 Feb 2005 14:59:25 +0100
From: Markus Schaber <schabios at logi-track.com>
Subject: Re: [postgis-users] Bad query optimizer misestimation because
of TOAST tables
To: PostgreSQL Performance List <pgsql-performance at postgresql.org>,
PostGIS Users Discussion <postgis-users at postgis.refractions.net>
Message-ID: <42022E3D.80108 at logi-track.com>
Content-Type: text/plain; charset="iso-8859-15"
Hi, all,
Markus Schaber schrieb:
> As a small workaround, I could imagine to add a small additional
> column in the table that contains the geometry's bbox, and which I use
> the && operator against. This should avoid touching the TOAST for the
skipped rows.
For your personal amusement: I just noticed that, after adding the
additional column containing the bbox and running VACUUM FULL, the table now
has a size of 4 pages, and that's enough for the query optimizer to choose
an index scan. At least that saves us from modifying and redeploying a bunch
of applications to use the && bbox query:-)
Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax
+41-43-888 62 53 mailto:schabios at logi-track.com | www.logi-track.com
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 256 bytes
Desc: OpenPGP digital signature
Url :
http://lists.refractions.net/pipermail/postgis-users/attachments/20050203/fa
3a14e1/signature-0001.bin
------------------------------
Message: 5
Date: Thu, 3 Feb 2005 14:09:35 -0000
From: "Mark Cave-Ayland" <m.cave-ayland at webbased.co.uk>
Subject: RE: [postgis-users] About Invalid OGC WKT Error
To: "'PostGIS Users Discussion'"
<postgis-users at postgis.refractions.net>, "'Paul Ramsey'"
<pramsey at refractions.net>
Message-ID:
<9EB50F1A91413F4FA63019487FCD251DADFB at WEBBASEDDC.webbasedltd.local>
Content-Type: text/plain; charset="US-ASCII"
Hi Tom,
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
> Kralidis,Tom [Burlington]
> Sent: 02 February 2005 23:46
> To: PostGIS Users Discussion; Paul Ramsey
> Subject: RE: [postgis-users] About Invalid OGC WKT Error
(cut)
> > What happens when you plug the query into psql?
> >
> > SELECT
> > astext(force_collection(force_2d(the_geom)),'NDR'),OID::text
> > from service_endpoints WHERE the_geom &&
> > setSRID('BOX3D(-141.089 36.392987,-52.089 89.784987)'::BOX3D,
> > find_srid('','service_endpoints','the_geom') )
The query above doesn't look right: you need to replace astext() with
asbinary() to get the exact query issued by Mapserver.
SELECT asbinary(force_collection(force_2d(the_geom)),'NDR'),OID::text from
service_endpoints WHERE the_geom && setSRID('BOX3D(-141.089
36.392987,-52.089 89.784987)'::BOX3D,
find_srid('','service_endpoints','the_geom') )
Could you let us know whether this works for you?
Kind regards,
Mark.
------------------------
WebBased Ltd
South West Technology Centre
Tamar Science Park
Plymouth
PL6 8BT
T: +44 (0)1752 791021
F: +44 (0)1752 791023
W: http://www.webbased.co.uk
------------------------------
Message: 6
Date: Thu, 3 Feb 2005 09:15:11 -0500
From: "Kralidis,Tom [Burlington]" <Tom.Kralidis at ec.gc.ca>
Subject: RE: [postgis-users] About Invalid OGC WKT Error
To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
Message-ID:
<2576812186CDD411BF1500508B6DCE9508A7D979 at ecnwri1.ontario.int.ec.gc.ca>
Content-Type: text/plain; charset="us-ascii"
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
> strk at refractions.net
> Sent: Thursday, 03 February, 2005 08:36
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] About Invalid OGC WKT Error
>
>
> On Wed, Feb 02, 2005 at 06:22:56PM -0500, Kralidis,Tom [Burlington]
> wrote:
> >
> > Here's what shows up in my error logs when MapServer attempts to
> > access when I have MapServer in debugging mode:
>
> (cuts)
>
> > query_string_0_6:DECLARE mycursor BINARY CURSOR FOR SELECT
> > asbinary(force_collection(force_2d(the_geom)),'NDR'),OID::text from
> > service_endpoints WHERE the_geom && setSRID('BOX3D(-141.089
> > 36.392987,-52.089 89.784987)'::BOX3D,
> > find_srid('','service_endpoints','the_geom') )
>
> (cuts)
>
> > msPOSTGISLayerClose -- query_result is NULL
>
>
> That query does not return any result. Check manually invoking it.
When I manually invoke, I get records:
devgeodb=# select astext(the_geom) from service_endpoints ;
astext
------------------------------------------------------------------------
------------------
POLYGON((-141.089 36.393,-141.089 89.785,-52.089 89.785,-52.089
36.393,-141.089 36.393))
(1 row)
devgeodb=# select asbinary(the_geom) from service_endpoints ;
asbinary
------------------------------------------------------------------------
------------------------------------------------------------------------
------------
------------------------------------------------------------------------
--------------------------------------------
\001\003\000\000\000\001\000\000\000\005\000\000\000\002+\207\026\331\24
2a\300\374\251\361\322M2B@\002+\207\026\331\242a\300\012\327\243p=rV@\01
0\254\034Zd
\013J\300\012\327\243p=rV@\010\254\034Zd\013J\300\374\251\361\322M2B@\00
2+\207\026\331\242a\300\374\251\361\322M2B@
(1 row)
...so I know there's data in the table.
>Also, check:
>
> # SELECT extent(asbinary(force_collection(force_2d(the_geom)),'NDR'));
Appending "from <tablename>" makes this query work:
devgeodb=# SELECT
extent(asbinary(force_collection(force_2d(the_geom)),'NDR')) from
service_endpoints ;
extent
------------------------------------------------------------------------
---
BOX(-141.089004516602 36.3929977416992,-52.088996887207
89.7850036621094)
(1 row)
> # SELECT setSRID('BOX3D(-141.089 36.392987,-52.089 89.784987)'::BOX3D,
> find_srid('','service_endpoints','the_geom') )
>
devgeodb=# SELECT setSRID('BOX3D(-141.089 36.392987,-52.089
89.784987)'::BOX3D,find_srid('','service_endpoints','the_geom') );
setsrid
------------------------------------------------------------------------
------------------------------------------------------------------------
------------
----------------------------------------
0103000020E61000000100000005000000022B8716D9A261C06760E4654D324240022B87
16D9A261C040321D3A3D72564008AC1C5A640B4AC040321D3A3D72564008AC1C5A640B4A
C06760E4654
D324240022B8716D9A261C06760E4654D324240
(1 row)
> --strk;
> _______________________________________________
> postgis-users mailing list postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
------------------------------
Message: 7
Date: Thu, 3 Feb 2005 09:24:59 -0500
From: "Kralidis,Tom [Burlington]" <Tom.Kralidis at ec.gc.ca>
Subject: RE: [postgis-users] About Invalid OGC WKT Error
To: "PostGIS Users Discussion"
<postgis-users at postgis.refractions.net>, "Paul Ramsey"
<pramsey at refractions.net>
Message-ID:
<2576812186CDD411BF1500508B6DCE950972F52E at ecnwri1.ontario.int.ec.gc.ca>
Content-Type: text/plain; charset="us-ascii"
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
> Mark Cave-Ayland
> Sent: Thursday, 03 February, 2005 09:10
> To: 'PostGIS Users Discussion'; 'Paul Ramsey'
> Subject: RE: [postgis-users] About Invalid OGC WKT Error
>
>
> Hi Tom,
>
> > -----Original Message-----
> > From: postgis-users-bounces at postgis.refractions.net
> > [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
> > Kralidis,Tom [Burlington]
> > Sent: 02 February 2005 23:46
> > To: PostGIS Users Discussion; Paul Ramsey
> > Subject: RE: [postgis-users] About Invalid OGC WKT Error
>
> (cut)
>
> > > What happens when you plug the query into psql?
> > >
> > > SELECT
> astext(force_collection(force_2d(the_geom)),'NDR'),OID::text
> > > from service_endpoints WHERE the_geom &&
> > > setSRID('BOX3D(-141.089 36.392987,-52.089 89.784987)'::BOX3D,
> > > find_srid('','service_endpoints','the_geom') )
>
> The query above doesn't look right: you need to replace astext() with
> asbinary() to get the exact query issued by Mapserver.
>
> SELECT
> asbinary(force_collection(force_2d(the_geom)),'NDR'),OID::text
> from service_endpoints WHERE the_geom &&
> setSRID('BOX3D(-141.089 36.392987,-52.089 89.784987)'::BOX3D,
> find_srid('','service_endpoints','the_geom') )
>
> Could you let us know whether this works for you?
>
This is my output:
[devgeo:/usr/local/wwwsites/apache/devgeo.cciw.ca/apps/mapserv/services/
eccat/dbadmin]$ /usr/local/pgsql/bin/psql -d devgeodb -f ./b.sql -U postgres
asbinary
| oid
------------------------------------------------------------------------
------------------------------------------------------------------------
------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------
---------+-------
\001\007\000\000\000\001\000\000\000\001\003\000\000
\346\020\000\000\001\000\000\000\005\000\000\000\002+\207\026\331\242a\3
00\374\251\361\322M2B@\002+\20
7\026\331\242a\300\012\327\243p=rV@\010\254\034Zd\013J\300\012\327\243p=
rV@\010\254\034Zd\013J\300\374\251\361\322M2B@\002+\207\026\331\242a\300
\374\251\361
\322M2B@ | 28517
(1 row)
>
> Kind regards,
>
> Mark.
>
> ------------------------
> WebBased Ltd
> South West Technology Centre
> Tamar Science Park
> Plymouth
> PL6 8BT
>
> T: +44 (0)1752 791021
> F: +44 (0)1752 791023
> W: http://www.webbased.co.uk
>
>
> _______________________________________________
> postgis-users mailing list postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
------------------------------
Message: 8
Date: Thu, 3 Feb 2005 09:25:58 -0500
From: David.Jacques at CCRS.NRCan.gc.ca
Subject: [postgis-users] RE: Usage of Transform() function in a query.
To: postgis-users at postgis.refractions.net
Message-ID:
<B7F9814D6850C949947CF8DEA13477810207ABA0 at s5-ccr-r2.ccrs.nrcan.gc.ca>
Content-Type: text/plain; charset="ISO-8859-1"
Oops...
I've discovered that that SRID is not defined in my SPATIAL_REF_SYS table.
That would do it wouldn't it ? Does anyone have that defined ?
I need to insert it via SQL and I'm not sure of the exact format for the
SRTEXT field.
-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
postgis-users-request at postgis.refractions.net
Sent: Wednesday, February 02, 2005 1:39 PM
To: postgis-users at postgis.refractions.net
Subject: postgis-users Digest, Vol 28, Issue 4
Send postgis-users mailing list submissions to
postgis-users at postgis.refractions.net
To subscribe or unsubscribe via the World Wide Web, visit
http://postgis.refractions.net/mailman/listinfo/postgis-users
or, via email, send a message with subject or body 'help' to
postgis-users-request at postgis.refractions.net
You can reach the person managing the list at
postgis-users-owner at postgis.refractions.net
When replying, please edit your Subject line so it is more specific than
"Re: Contents of postgis-users digest..."
Today's Topics:
1. Re: Usage of Transform() function in a query. (Paul Ramsey)
2. Re: About Invalid OGC WKT Error (Markus Schaber)
3. Re: Buggy transform() function in 1.0RC1 ? (Paul Ramsey)
4. Re: Index scan performance difference between 0.8 and rc1;
(fwd) (Ron Mayer)
5. RE: About Invalid OGC WKT Error (Kralidis,Tom [Burlington])
6. Re: [PERFORM] Bad query optimizer misestimation because of
TOAST tables (Markus Schaber)
7. Re: About Invalid OGC WKT Error (Markus Schaber)
8. Re: Index scan performance difference between 0.8 and rc1;
(fwd) (strk at refractions.net)
----------------------------------------------------------------------
Message: 1
Date: Wed, 02 Feb 2005 09:49:27 -0800
From: Paul Ramsey <pramsey at refractions.net>
Subject: Re: [postgis-users] Usage of Transform() function in a query.
To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
Message-ID: <420112A7.9040205 at refractions.net>
Content-Type: text/plain; charset=us-ascii; format=flowed
Your call looks exactly right. You compiled postgis with proj support?
You have an entry for 42304 in your spatial_ref_sys table, that has a
proj4 definition in it?
P
David.Jacques at CCRS.NRCan.gc.ca wrote:
> I need to reproject a spatial table from one SRID to another Both the
> source and destination SRIDs are in the SPATIAL_REF_SYS table.
> I've consulted the manual and the following is presented...
>
> SELECT transform(GEOM,4269) FROM GEOTABLE
>
> Here is the exact query
>
> SELECT transform(the_geom,42304) FROM xxmodishotspots;
>
> What I get is all records consisting of 1 column called "transform"
> with all null values.
>
> I expected to get the same output as SELECT * from xxmodishotspots;
> would produce, with the exception being that "the_geom" column would
> contain a value like
>
> SRID=42304;POINT(-552256.351008368 22239.2415119158)
>
> instead of
>
> SRID=4269;POINT(-90.673 69.4310000006199)
>
>
> Does anyone have a better example than the manual ? I can't see what I
> am doing wrong.
>
>
> Thanks in advance..
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
------------------------------
Message: 2
Date: Wed, 02 Feb 2005 18:51:19 +0100
From: Markus Schaber <schabios at logi-track.com>
Subject: Re: [postgis-users] About Invalid OGC WKT Error
To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
Message-ID: <42011317.5050808 at logi-track.com>
Content-Type: text/plain; charset=ISO-8859-1
Hi, Tom,
Kralidis,Tom [Burlington] schrieb:
> How does affect the MapServer connections to PostGIS?
To be honest, I don't know. That depends on which method MapServer uses to
access PostGIS. As I never used MapServer, I have no idea.
When using the JDBC classes, replacing the postgis.jar in the classpath
should be enough. Equally, any other toolkit should provide its update, with
noch changes to mapserver itsself.
When using OpenGIS methods (asText() or asBinary() etc.), no changes should
be necessary.
When using PostGIS specific, like "SELECT geom FROM table;" then MapServer
will need a new HexWKB parser or be changed to use OpenGIS methods or the
new asEWKT() PostGIS specific methods.
Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax
+41-43-888 62 53 mailto:schabios at logi-track.com | www.logi-track.com
------------------------------
Message: 3
Date: Wed, 02 Feb 2005 09:51:02 -0800
From: Paul Ramsey <pramsey at refractions.net>
Subject: Re: [postgis-users] Buggy transform() function in 1.0RC1 ?
To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
Cc: Frank Warmerdam <warmerdam at pobox.com>
Message-ID: <42011306.4000701 at refractions.net>
Content-Type: text/plain; charset=us-ascii; format=flowed
Yes, I would fear other errors. This spatial_ref_sys was generated from the
latest EPSG database by Frank, but there may be some errors I guess...
RIBOT, Nicolas wrote:
> Transform() is not buggy.
>
> To follow my previous post, and thanks to Michael Michaud that noticed
> the differences, the spatial_ref_sys.sql provided with postgis 1.0 RC
> is not
the
> same as the one provided with the previous Postgis versions, at least
> for french projections.
> Namely, for SRID=27582, the proj4 parameters in postgis 0.8:
>
> +proj=lcc
> +lat_1=46.8
> +lat_0=46.8
> +lon_0=2.337229166666667
> +k_0=0.99987742
> +x_0=600000
> +y_0=2200000
> +a=6378249.2
> +b=6356514.999904194
> +pm=2.337229166666667
> +units=m
>
> and in postgis 1.0RC:
>
> +proj=lcc
> +lat_1=46.8
> +lat_0=46.8
> +lon_0=-2.33722917
> +k_0=0.99987742
> +x_0=600000
> +y_0=2200000
> +a=6378249.2
> +b=6356515
> +towgs84=-168,-60,320,0,0,0,0
> +pm=paris
> +units=m
> +no_defs
>
> the sign of the longitude of origin is not the same, the value of the
prime
> meridian is also different.
>
> Can we fear other projection systems to be inaccurate ?
> Why the values of these well-know systems have changed between versions ?
>
> Thanx
> Nicolas Ribot
>
>
> ----------------------------------------------------------------------
> --
>
> ---------------------------------------------------------
>
> CE COURRIER ELECTRONIQUE EST A USAGE STRICTEMENT INFORMATIF ET NE
> SAURAIT
ENGAGER DE QUELQUE MANIERE QUE CE SOIT EADS ASTRIUM SAS, NI SES FILIALES.
>
> SI UNE ERREUR DE TRANSMISSION OU UNE ADRESSE ERRONEE A MAL DIRIGE CE
COURRIER, MERCI D'EN INFORMER L'EXPEDITEUR EN LUI FAISANT UNE REPONSE PAR
COURRIER ELECTRONIQUE DES RECEPTION. SI VOUS N'ETES PAS LE DESTINATAIRE DE
CE COURRIER, VOUS NE DEVEZ PAS L'UTILISER, LE CONSERVER, EN FAIRE ETAT, LE
DISTRIBUER, LE COPIER, L'IMPRIMER OU EN REVELER LE CONTENU A UNE TIERCE
PARTIE.
>
>
>
> This email is for information only and will not bind EADS Astrium SAS
> in
any contract or obligation, nor its subsidiaries.
>
> If you have received it in error, please notify the sender by return
email. If you are not the addressee of this email, you must not use, keep,
disseminate, copy, print or otherwise deal with it.
>
> ---------------------------------------------------------
>
>
> ----------------------------------------------------------------------
> --
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
------------------------------
Message: 4
Date: Wed, 2 Feb 2005 10:16:40 -0800
From: Ron Mayer <rm_postgis at cheapcomplexdevices.com>
Subject: Re: [postgis-users] Index scan performance difference between
0.8 and rc1; (fwd)
To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
Cc: postgis-devel at postgis.refractions.net
Message-ID: <Pine.CYG.4.58.0502021013100.1172 at FLI-RAM.luxor.com>
Content-Type: TEXT/PLAIN; charset=US-ASCII
On Tue, 1 Feb 2005 strk at refractions.net wrote:
> On Mon, Jan 31, 2005 at 03:53:20PM -0800, Ron Mayer wrote:
> > ...the performance changes a lot if I use find_srid() or "-1" when
> > defining my BOX3D; even though my srid is -1.
>
> Mmm.. this is interesting.
> I've tested a sample query three times, with find_srid defined as
> VOLATILE, STABLE, IMMUTABLE. Here are the results:
>
> VOLATILE: Total runtime: 1096.708 ms
> STABLE: Total runtime: 42.457 ms
> IMMUTABLE: Total runtime: 18.457 ms
Thanks! Making find_srid IMMUTABLE solved my performance problem.
Now small queries are just as fast as they were in 0.8, and large queries
are considerably faster (probably due to more compact geometry on disk?).
Ron
------------------------------
Message: 5
Date: Wed, 2 Feb 2005 13:18:15 -0500
From: "Kralidis,Tom [Burlington]" <Tom.Kralidis at ec.gc.ca>
Subject: RE: [postgis-users] About Invalid OGC WKT Error
To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
Message-ID:
<2576812186CDD411BF1500508B6DCE950972F519 at ecnwri1.ontario.int.ec.gc.ca>
Content-Type: text/plain; charset="iso-8859-1"
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
> Markus Schaber
> Sent: Wednesday, 02 February, 2005 12:51
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] About Invalid OGC WKT Error
>
>
> Hi, Tom,
>
> Kralidis,Tom [Burlington] schrieb:
> > How does affect the MapServer connections to PostGIS?
>
> To be honest, I don't know. That depends on which method MapServer
> uses to access PostGIS. As I never used MapServer, I have no idea.
>
> When using the JDBC classes, replacing the postgis.jar in the
> classpath should be enough. Equally, any other toolkit should provide
> its update, with noch changes to mapserver itsself.
>
> When using OpenGIS methods (asText() or asBinary() etc.), no changes
> should be necessary.
>
> When using PostGIS specific, like "SELECT geom FROM table;"
> then MapServer will need a new HexWKB parser or be changed to use
> OpenGIS methods or the new asEWKT() PostGIS specific methods.
>
MapServer connects via (user config):
DATA "the_geom from table_name"
Does one need to pass a function (like asEWKT(the_geom from table) on this?
I'm not sure how this is evaluated in the MapServer / PostGIS connector code
itself, though.
..Tom
> Markus
> --
> markus schaber | dipl. informatiker
> logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52
> | fax +41-43-888 62 53 mailto:schabios at logi-track.com |
> www.logi-track.com _______________________________________________
> postgis-users mailing list postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
------------------------------
Message: 6
Date: Wed, 02 Feb 2005 19:25:15 +0100
From: Markus Schaber <schabios at logi-track.com>
Subject: [postgis-users] Re: [PERFORM] Bad query optimizer
misestimation because of TOAST tables
To: Tom Lane <tgl at sss.pgh.pa.us>
Cc: PostgreSQL Performance List <pgsql-performance at postgresql.org>,
PostGIS Users Discussion <postgis-users at postgis.refractions.net>
Message-ID: <42011B0B.2050305 at logi-track.com>
Content-Type: text/plain; charset="iso-8859-15"
Hi, Tom,
Tom Lane schrieb:
>>IMHO, this tells the reason. The query planner has a table size of 3
>>pages, which clearly is a case for a seqscan. But during the seqscan,
>>the database has to fetch an additional amount of 8225 toast pages and
>>127 toast index pages, and rebuild the geometries contained therein.
>
> I don't buy this analysis at all. The toasted columns are not those
> in the index (because we don't support out-of-line-toasted index
> entries), so a WHERE clause that only touches indexed columns isn't
> going to need to fetch anything from the toast table. The only stuff
> it would fetch is in rows that passed the WHERE and need to be
> returned to the client
> --- and those costs are going to be the same either way.
>
> I'm not entirely sure where the time is going, but I do not think you
> have proven your theory about it. I'd suggest building the backend
> with -pg and getting some gprof evidence.
The column is a PostGIS column, and the index was created using GIST.
Those are lossy indices that do not store the whole geometry, but only the
bounding box corners of the Geometry (2 Points).
Without using the index, the && Operator (which tests for bbox
overlapping) has to load the whole geometry from disk, and extract the bbox
therein (as it cannot make use of partial fetch).
Some little statistics:
logigis=# select max(mem_size(geom)), avg(mem_size(geom))::int,
max(npoints(geom)) from adminbndy1;
max | avg | max
----------+---------+--------
20998856 | 1384127 | 873657
(1 Zeile)
So the geometries use are about 1.3 MB average size, and have a maximum size
of 20Mb. I'm pretty shure this cannot be stored without TOASTing.
Additionally, my suggested workaround using a separate bbox column really
works:
logigis=# alter table adminbndy1 ADD column bbox geometry; ALTER TABLE
logigis=# update adminbndy1 set bbox = setsrid(box3d(geom)::geometry, 4326);
UPDATE 83 logigis=# explain analyze SELECT geom FROM adminbndy1 WHERE bbox
&&
setsrid('BOX3D(9.4835390946502 47.39365740740741,9.5164609053498
47.40634259259259)'::box3d,4326);
QUERY PLAN
----------------------------------------------------------------------------
-----------------------------------------------
Seq Scan on adminbndy1 (cost=100000000.00..100000022.50 rows=1
width=32) (actual time=0.554..0.885 rows=5 loops=1)
Filter: (bbox && 'SRID=4326;BOX3D(9.4835390946502 47.3936574074074
0,9.5164609053498 47.4063425925926 0)'::geometry) Total runtime: 0.960 ms
(3 Zeilen)
Here, the seqential scan matching exactly the same 5 rows only needs about
1/8000th of time, because it does not have to touch the TOAST pages at all.
logigis=# \o /dev/null
logigis=# \timing
Zeitmessung ist an.
logigis=# SELECT geom FROM adminbndy1 WHERE geom &&
setsrid('BOX3D(9.4835390946502 47.39365740740741,9.5164609053498
47.40634259259259)'::box3d,4326);
Zeit: 11224,185 ms
logigis=# SELECT geom FROM adminbndy1 WHERE bbox &&
setsrid('BOX3D(9.4835390946502 47.39365740740741,9.5164609053498
47.40634259259259)'::box3d,4326);
Zeit: 7689,720 ms
So you can see that, when actually detoasting the 5 rows and deserializing
the geometries to WKT format (their canonical text representation), the time
relation gets better, but there's still a noticeable difference.
Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax
+41-43-888 62 53 mailto:schabios at logi-track.com | www.logi-track.com
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 256 bytes
Desc: OpenPGP digital signature
Url :
http://lists.refractions.net/pipermail/postgis-users/attachments/20050202/38
4b68b7/signature-0001.bin
------------------------------
Message: 7
Date: Wed, 02 Feb 2005 19:27:21 +0100
From: Markus Schaber <schabios at logi-track.com>
Subject: Re: [postgis-users] About Invalid OGC WKT Error
To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
Message-ID: <42011B89.40600 at logi-track.com>
Content-Type: text/plain; charset="iso-8859-1"
Hi, Tom,
Kralidis,Tom [Burlington] schrieb:
> MapServer connects via (user config):
>
> DATA "the_geom from table_name"
>
> Does one need to pass a function (like asEWKT(the_geom from table) on
this? I'm not sure how this is evaluated in the MapServer / PostGIS
connector code itself, though.
Hmm, maybe "asEWKT(the_geom) from table" or "asText(the_geom) from table"
work, but MapServer experts may be of better help here.
markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax
+41-43-888 62 53 mailto:schabios at logi-track.com | www.logi-track.com
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 256 bytes
Desc: OpenPGP digital signature
Url :
http://lists.refractions.net/pipermail/postgis-users/attachments/20050202/a8
3820ea/signature-0001.bin
------------------------------
Message: 8
Date: Wed, 2 Feb 2005 19:28:22 +0100
From: strk at refractions.net
Subject: Re: [postgis-users] Index scan performance difference between
0.8 and rc1; (fwd)
To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>,
postgis-devel at postgis.refractions.net
Message-ID: <20050202182822.GC5315 at freek.keybit.net>
Content-Type: text/plain; charset=us-ascii
On Wed, Feb 02, 2005 at 10:16:40AM -0800, Ron Mayer wrote:
> On Tue, 1 Feb 2005 strk at refractions.net wrote:
> > On Mon, Jan 31, 2005 at 03:53:20PM -0800, Ron Mayer wrote:
> > > ...the performance changes a lot if I use find_srid() or "-1" when
> > > defining my BOX3D; even though my srid is -1.
> >
> > Mmm.. this is interesting.
> > I've tested a sample query three times, with find_srid defined as
> > VOLATILE, STABLE, IMMUTABLE. Here are the results:
> >
> > VOLATILE: Total runtime: 1096.708 ms
> > STABLE: Total runtime: 42.457 ms
> > IMMUTABLE: Total runtime: 18.457 ms
>
> Thanks! Making find_srid IMMUTABLE solved my performance problem.
>
> Now small queries are just as fast as they were in 0.8, and large
> queries are considerably faster (probably due to more compact geometry
> on disk?).
Yes, and smaller index keys.
--strk;
>
> Ron
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
------------------------------
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
End of postgis-users Digest, Vol 28, Issue 4
********************************************
------------------------------
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
End of postgis-users Digest, Vol 28, Issue 8
********************************************
More information about the postgis-users
mailing list