Haven't done any benchmarks, but I know the following the ::text display you get is basically the string representation of the native binary format that PostGIS actually stores the geometries. 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. Keep in mind EWKT and WKT also may result in floating point errors since they will round the decimals so be careful using those. 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)). Hope that helps, Regina
________________________________ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Willy-Bas Loos Sent: Tuesday, July 29, 2008 8:43 AM To: [email protected] Subject: [postgis-users] optimal textbased geometry format 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 ----------------------------------------- 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.
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
