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
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
