<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.6000.16481" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=911504114-29072008><FONT face=Arial
color=#0000ff size=2>Haven't done any benchmarks, but I know the
following</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=911504114-29072008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=911504114-29072008><FONT face=Arial
color=#0000ff size=2>the ::text display you get is basically the string
representation of the native binary format that PostGIS actually stores the
geometries.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=911504114-29072008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=911504114-29072008><FONT face=Arial
color=#0000ff size=2>It is not WKB nor WKT. Its a PostGIS proprietary
format if you will. I would guess casting from that format is the most
efficient since in theory there is very little processing that PostgreSQL needs
to do to reconstitute from that format.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=911504114-29072008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=911504114-29072008><FONT face=Arial
color=#0000ff size=2>Keep in mind EWKT and WKT also may result in floating point
errors since they will round the decimals so be careful using those.
</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=911504114-29072008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=911504114-29072008><FONT face=Arial
color=#0000ff size=2>So your best formats to maintain precision (and not have
lossiness) are EWKB and the ::text representation (which I shall dubb the native
PostGIS binary notation (which I guess we can call light-weight something or
other)).</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=911504114-29072008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=911504114-29072008><FONT face=Arial
color=#0000ff size=2>Hope that helps,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=911504114-29072008><FONT face=Arial
color=#0000ff size=2>Regina</FONT></SPAN></DIV><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of
</B>Willy-Bas Loos<BR><B>Sent:</B> Tuesday, July 29, 2008 8:43 AM<BR><B>To:</B>
postgis-users@postgis.refractions.net<BR><B>Subject:</B> [postgis-users] optimal
textbased geometry format<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV dir=ltr>Sometimes i build queries inside plpgsql scripts, that i then run
using EXECUTE.<BR>Is there a preferred way to include geometries in
those?<BR><BR>I use postgis 1.1 (for now). Are there are any changes in later
versions that influence this aspect of PostGIS?<BR>The SRID is a factor for me,
so asText(the_geom) is no option.<BR>I eiter use asEWKT(the_geom), or just
the_geom::text .<BR>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.<BR>asEWKT results in human readable
format, possibly shorter, but it might require more processing power (back and
forth!). <BR><BR>I've tested both difference in length and performance, all on a
limited dataset.<BR>LENGTH:<BR>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).<BR>code:<BR> --select length(the_geom::text),
length(asewkt(the_geom)),
round(((length(asewkt(the_geom))::float)/(length(the_geom::text))::float)*100)
as perc<BR> select
avg(((length(asewkt(the_geom))::float)/(length(the_geom::text))::float)*100) as
perc<BR> from atable<BR> order by perc<BR><BR>PERFORMANCE:<BR>I've
tested these "warm" (not the first run), and averaged the scores from 3
measurements each. The fluctuations were minimal.<BR>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)<BR>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)!<BR>I
hope my method (code below) is adequate?<BR><BR>code:<BR> select
area(the_geom::text::geometry)<BR> --select
area(asewkt(the_geom)::geometry)<BR> from atable<BR><BR>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?<BR>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?<BR><BR>Cheers,<BR><BR>WBL<BR><BR><BR></DIV></BODY></HTML>
<HTML><BODY><P><hr size=1></P>
<P><STRONG>
The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.
</STRONG></P></BODY></HTML>
<P><hr size=1></P>
<P><STRONG><font size="2" color="339900"> Help make the earth a greener place. If at all possible resist printing this email and join us in saving paper. </p> <p> </font></STRONG></P>