<!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=371430711-31072008><FONT face=Arial
color=#0000ff size=2>Actually now I realize the PostGIS binary notation is
HexEWKB</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=371430711-31072008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=371430711-31072008><FONT face=Arial
color=#0000ff size=2>SELECT ST_AsHEXEWKB(ST_GeomFromText('POLYGON((0 0,0 1,1 1,1
0,0 0))',4326))<BR></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=371430711-31072008><FONT face=Arial
color=#0000ff size=2>SELECT ST_GeomFromText('POLYGON((0 0,0 1,1 1,1 0,0
0))',4326)::text</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=371430711-31072008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=371430711-31072008><FONT face=Arial
color=#0000ff size=2>Gives same answer</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 1:12 PM<BR><B>To:</B>
PostGIS Users Discussion<BR><B>Subject:</B> Re: [postgis-users] optimal
textbased geometry format<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV dir=ltr>funny, the EWKB is exactly half the size of the "PostGIS binary
notation", in all cases i tested.<BR>there is a small performance impact, which
fluctuates somewhat. The internal format was a factor 1.01 to 1.07 faster (94 to
99% of the time needed). <BR><BR>cheers,<BR><BR>WBL<BR><BR>
<DIV class=gmail_quote>On Tue, Jul 29, 2008 at 6:48 PM, Willy-Bas Loos <SPAN
dir=ltr><<A
href="mailto:willybas@gmail.com">willybas@gmail.com</A>></SPAN> wrote:<BR>
<BLOCKQUOTE class=gmail_quote
style="PADDING-LEFT: 1ex; MARGIN: 0pt 0pt 0pt 0.8ex; BORDER-LEFT: rgb(204,204,204) 1px solid">
<DIV dir=ltr>thanks.<BR>yes it helps :)<BR><BR>
<DIV class=gmail_quote>
<DIV>
<DIV></DIV>
<DIV class=Wj3C7c>On Tue, Jul 29, 2008 at 4:46 PM, Obe, Regina <SPAN
dir=ltr><<A href="mailto:robe.dnd@cityofboston.gov"
target=_blank>robe.dnd@cityofboston.gov</A>></SPAN> wrote:<BR></DIV></DIV>
<BLOCKQUOTE class=gmail_quote
style="PADDING-LEFT: 1ex; MARGIN: 0pt 0pt 0pt 0.8ex; BORDER-LEFT: rgb(204,204,204) 1px solid">
<DIV>
<DIV></DIV>
<DIV class=Wj3C7c>
<DIV>
<DIV dir=ltr align=left><SPAN><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><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><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><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><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><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><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><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><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><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><FONT face=Arial color=#0000ff size=2>Hope
that helps,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT face=Arial color=#0000ff
size=2>Regina</FONT></SPAN></DIV><BR>
<DIV lang=en-us dir=ltr align=left>
<HR>
<FONT face=Tahoma size=2><B>From:</B> <A
href="mailto:postgis-users-bounces@postgis.refractions.net"
target=_blank>postgis-users-bounces@postgis.refractions.net</A> [mailto:<A
href="mailto:postgis-users-bounces@postgis.refractions.net"
target=_blank>postgis-users-bounces@postgis.refractions.net</A>] <B>On
Behalf Of </B>Willy-Bas Loos<BR><B>Sent:</B> Tuesday, July 29, 2008 8:43
AM<BR><B>To:</B> <A href="mailto:postgis-users@postgis.refractions.net"
target=_blank>postgis-users@postgis.refractions.net</A><BR><B>Subject:</B>
[postgis-users] optimal textbased geometry format<BR></FONT><BR></DIV>
<DIV>
<DIV></DIV>
<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></DIV></DIV></DIV>
<DIV>
<P></P>
<HR SIZE=1>
<P><B>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. </B></P></DIV>
<P></P>
<HR SIZE=1>
<P><B><FONT color=#339900 size=2>Help make the earth a greener place. If at
all possible resist printing this email and join us in saving paper.
</FONT></B></P>
<P></P><BR></DIV></DIV>_______________________________________________<BR>postgis-users
mailing list
<DIV class=Ih2E3d><BR><A href="mailto:postgis-users@postgis.refractions.net"
target=_blank>postgis-users@postgis.refractions.net</A><BR></DIV><A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target=_blank>http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR><BR></BLOCKQUOTE></DIV><BR></DIV></BLOCKQUOTE></DIV><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>