30

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?

26

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

\timing

WITH test AS (
  SELECT <POINT CONSTRUCTOR METHOD>
  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 <POINT CONSTRUCTOR METHOD> 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
  • 1
    Thanks for a great explanation about how to calculate this. I'm curious about the SQL syntax, <POINT CONSTRUCTOR METHOD>. Is that just pseudocode to refer to the four different approaches, or are you making some kind of function? – djq Apr 21 '13 at 19:20
  • 2
    @djq yup, it's just a placeholder for the actual SQL code in 1, 2 and 3. – Mike T Apr 21 '13 at 20:01
  • Detail about precision limits on float datatype to use as reference... The machine epsilon is ~1e-14... Change f1 table to FROM (SELECT random()::float8 as x, random()::float8 as y UNION SELECT 12.24343484842,34.58384538483434) AS f1 to see it in your psql. – Peter Krauss Sep 14 '18 at 7:31
5

ST_MakePoint and ST_Point are the same - they both call LWGEOM_makepoint (you can see this in the postgis/postgis.sql.in file in the source code). I'd use ST_MakePoint. The text conversion routines produce the same result, but are slower because of the amount of parsing that is required.

1

SRID 4326 and Geometry

As a side note to the excellent, comprehensive, and current answer by MikeT. Many people seem to ask this question because they want to set the SRID on a POINT column.

CREATE TABLE foo ( geom geometry(Point,4326) );

But when they do they run into problems with what seems like the best method of creating a point, but alas they run into trouble.

INSERT INTO foo (geom) VALUES ( ST_MakePoint(1,2) );
ERROR:  Geometry SRID (0) does not match column SRID (4326);

From there, they reason they have two options

  • Set the SRID manually, ST_SetSRID( ST_MakePoint(1,2) ) which is the right-most way but crufty, or
  • Construct from text using ST_GeomFromText, this is logically slower and doesn't need benchmarks: PostgreSQL has to parse the constructor's arguments from the text. It's also extremely ugly itself.

Alas, there is another way.

Geography Type

The default SRID for geography is 4326. If you're new, I would suggest using geography instead of geometry. In fact, generally if you don't know the difference you likely want geography. You can switch the columns pretty easily.

BEGIN;
  ALTER TABLE foo ADD COLUMN geog geography(point,4326);
  UPDATE foo SET geog = geom::geography;
  ALTER TABLE foo DROP COLUMN geom;
COMMIT;

Now insertion is easier because the type is already default-associated with SRID 4326. Now you can explicitly cast to geography, or just let the implicit cast work

ST_MakePoint(x,y)                     -- implicit cast and srid
ST_MakePoint(x,y)::geography          -- explicit cast, implicit srid
ST_SetSRID( ST_MakePoint(3,4), 4326 ) -- explicit cast and srid

Which looks like this, (they all insert hte same thing)

INSERT INTO foo (geog) VALUES
  -- implicit cast and SRID
  ( ST_MakePoint(1,2) ),

  -- explicit cast, implicit SRID
  ( ST_MakePoint(1,2)::geography ),

   -- explicit cast and SRID
  ( ST_SetSRID( ST_MakePoint(3,4), 4326 )::geography );

Converting to text and then forcing PostgreSQL to parse the text with ST_GeomFromText or ST_GeogFromText is silly and slow.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.