[postgis-users] RE: 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:25:58 PST 2005
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
********************************************
More information about the postgis-users
mailing list