postgresql - Which function for creating a POINT in PostGIS?

When defining a Point in PostGIS, when do you decide to use which of the following?

• ST_SetSRID(ST_MakePoint(lon,lat),4326)



• ST_SetSRID(ST_Point(long,lat),4326)


• ST_SetSRID(ST_GeomFromText('POINT(lon lat)',4326)


• ST_GeomFromEWKT('SRID=4326;POINT(lon lat)')

If its essentially a difference in performance, which will be the fastest?

Answer

My guess is that ST_MakePoint is fastest, but this is easy enough to benchmark with 100k random points.

\timing

WITH test AS (

  SELECT 
  FROM generate_series(1,100000)
)
SELECT count(*) FROM test;

And here are some results with PostGIS 2.1 (trunk) on PostgreSQL 9.1, x64 Debian. I did them a few times to get an approximate average. Here are the in order from fastest to slowest:

1. ST_SetSRID(ST_MakePoint(random(), random()), 4326)
   
   
   
   • avg 160 ms
   
   
   
   • by far fastest, and preserves double-point precision (lossless)
   
   
   • easiest way to make a parameterized query with numeric coordinate data
   
   
   
   


2. ST_GeomFromText('POINT(' || random()::text || ' ' || random()::text || ')', 4326)
   
   
   
   • avg 760 ms
   
   
   • slow, as the number is cast to text, then the string is pieced together, then PostGIS needs to parse it to find the numbers
   
   
   • lossy, due to number -> text -> number conversions
   
   
   
   
   


3. ST_GeomFromEWKT('SRID=4326;POINT(' || random()::text || ' ' || random()::text || ')')
   
   
   
   • avg 810 ms
   
   
   • slowest, not sure why it's slower than ST_GeomFromText
   
   
   
   

---

Lastly, a wee footnote on the difference between lossless/lossy conversions with the above methods. Only ST_MakePoint preserves the binary floating point precision data, and the text conversions truncate a very small part of the data off. Although the two points may have binary differences (seen in the WKB), they should always be spatially equal. The distance differences are essentially the machine epsilon for double precision.

SELECT
  (geom_text = geom_binary) AS spatially_equal,
  (geom_text::text = geom_binary::text) AS binary_equal,
  (ST_AsText(geom_text) = ST_AsText(geom_binary)) AS wkt_equal,
  ST_Distance(geom_text, geom_binary)
FROM (
  SELECT x, y,
    ST_GeomFromText('POINT(' || x::text || ' ' || y::text || ')') AS geom_text,
    ST_MakePoint(x, y) AS geom_binary
  FROM (SELECT random()::float8 as x, random()::float8 as y) AS f1

) AS f2;

 spatially_equal | binary_equal | wkt_equal |     st_distance
-----------------+--------------+-----------+----------------------
 t               | f            | t         | 1.38777878078145e-16