Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 8 down vote accepted

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

create temp table points(geom geometry(Point, 4326));

-- repeat the remainder for each <POINT CONSTRUCTOR METHOD>
truncate points;

insert into points(geom)
select <POINT CONSTRUCTOR METHOD>
from generate_series(1,100000);

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 280 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 850 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 920 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.

select x, y, geom_text = geom_binary as spatially_equal,
             geom_text::text = geom_binary::text as binary_equal
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 f) as f;
         x         |         y         | spatially_equal | binary_equal 
-------------------+-------------------+-----------------+--------------
 0.886159958783537 | 0.370768354274333 | t               | f
(1 row)
share|improve this answer
    
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
1  
@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
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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