[postgis-users] optimal textbased geometry format

[Willy-Bas Loos]

Sometimes i build queries inside plpgsql scripts, that i then run using
EXECUTE.
Is there a preferred way to include geometries in those?

I use postgis 1.1 (for now). Are there are any changes in later versions
that influence this aspect of PostGIS?
The SRID is a factor for me, so asText(the_geom) is no option.
I eiter use asEWKT(the_geom), or just the_geom::text .
Just casting the geometry directly into text results in a very long text
value (what is the name of this format?). The length of the string might be
a network transfer drawback.
asEWKT results in human readable format, possibly shorter, but it might
require more processing power (back and forth!).

I've tested both difference in length and performance, all on a limited
dataset.
LENGTH:
The difference in length varies greatly. I use SRID 28992, which is a
national grid that has coordinates in meters, so it is possible to have
coordinates with 0 decimal places (1 meter precision). Simple geometries
seem to benefit most from the WKT format. The EWKT is 40% of the number of
characters for some cases (square polygons from coordinates without
decimals). Most polygons score 50-60% of length, but in some cases the WKT
representation is up to 10% longer than the direct cast (geom::text).
code:
  --select length(the_geom::text), length(asewkt(the_geom)),
round(((length(asewkt(the_geom))::float)/(length(the_geom::text))::float)*100)
as perc
  select
avg(((length(asewkt(the_geom))::float)/(length(the_geom::text))::float)*100)
as perc
  from atable
  order by perc

PERFORMANCE:
I've tested these "warm" (not the first run), and averaged the scores from 3
measurements each. The fluctuations were minimal.
The diference in performance is small for converting from geometries to a
text representation. Casting directly is a factor 1.1 faster (90% of the
time needed)
The other way around is a diferent story. Converting a directly casted
geometry back to a geometry is a factor 6 faster than a EWKT string (16% of
the time needed)!
I hope my method (code below) is adequate?

code:
  select area(the_geom::text::geometry)
  --select area(asewkt(the_geom)::geometry)
  from atable

Regarding my tests, i would say that the format that results from a direct
cast is the better string representation for intra-application
communication. Are there any drawbacks?
Of course my test was limited to a small dataset on a windows pc (mem 2GB,
athlon 64 3200+) with postgis 1.1. Does anyone else have different
results/ideas about this?

Cheers,

WBL
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080729/f3e448ae/attachment.html>