ST_MakePoint or ST_PointFromText to generate points

Question

I'm new to postGIS and am having trouble finding an answer to this question. I have loaded UK postcode data (OSGB36, SRID 27700 with eastings and northings fields) into a postGIS database. I now want to generate a geom column to store the point information.

I can find some clear instructions on how to do this using ST_MakePoint from here: http://twiav-tt.blogspot.co.uk/2012/07/postgis-using-latitude-and-longitude-to.html

This gives the nice straightforward syntax example of:

UPDATE netherlands.airports SET geom = ST_SetSRID(ST_MakePoint(longitude,latitude),4326);

I've read that although ST_MakePoint is fast, it's not as standards compliant/transferable as ST_PointFromText.

But no matter where I look for a similar example for ST_PointFromText, I can't find one. For example, on Boston GIS, the following example is given:

UPDATE points_of_interest SET thepoint_lonlat = PointFromText('POINT(' || longitude || ' ' || latitude || ')',4326)

The postGIS page gives a near identical example:

SELECT ST_PointFromText('POINT(-71.064544 42.28787)', 4326);

These previous two example just seem to show you how to generate a point for some text that you've hand-typed in, they don't show you the syntax for how to derive it from other fields in your table.

So I know I'm being a noob here, but I have one column called 'easting' and one column called 'northing'. What is the syntax to create my geom column using ST_PointFromText?

John Powell · Accepted Answer

First of all, some performance metrics, comparing the two different ways of producing points for a random selection of a million points.

create table test (id serial, x real, y real, geom geometry(POINT, 27700));
insert into test (x, y) select random(), random() from generate_series(1, 1000000);
update test set geom = ST_SetSRID(ST_MakePoint(x, y),27700);
update test set geom = ST_PointFromText('POINT(' || x || ' ' || y || ')', 27700);

On my laptop, ST_MakePoint took about 10 seconds, whereas the ST_PointFromText approach took about 25 seconds (I ran this a few times to be sure), so if performance is all you care about, the former is a no-brainer. However, in reality, you typically load data once and then do interesting things with it, so, load performance might not be that critical in the big picture.

You mention standards compliance, which is a laudable aim, but in my experience, the implementations of spatial between Oracle, Postgres, MySQL, SQL Server, etc, in terms of both how functions are called and internal storage, are so different, that the difference between two different loading functions becomes almost irrelevant. Further, there are  many interesting no ANSI standards extensions in all DBs, the generate_series used above does not exist in SQL Server or MySQL, for example, and not using them for the purposes of standards compliance often feels like coding with one hand tied behind your back. I could never give up WITH RECURSIVE or the array datatype or generate_series or the raster extensions from Postgres, just because I might want to migrate to SQL Server or MySQL one day.

Polyglot database implementations are becoming very common, it is true, but usually combining an RDBMS with a NoSQL (or other) solution to benefit from the strengths of both. I would think it unlikely that you would have the same spatial data in two different RDBMS, though I accept that the realities of corporate IT implementations over many years often throws up some Frankenstein solutions. I realize that this was a bit of a long way of saying I wouldn't really worry about ST_PointFromText versus ST_MakePoint too much -- I always use the latter, for what it is worth, as it seems more natural.

ThomasG77 · Answer

I clearly don't know the "best" solution between ST_PointFromText and ST_MakePoint.

The thing you should notice is that ST_PointFromText and PointFromText are the same function (syntax change due to function normalisation SQL MM). Now, the right ones are ST_*

So what you want is something like below (|| are for concatenation)

UPDATE points_of_interest SET thepoint_lonlat = PointFromText('POINT(' || easting || ' ' || northing || ')',4326)

Martin F · Answer

Just wish to add two things to John Barça's fine answer:

First, ST_PointFromText() would be most useful if your coordinate data were already in the form of lines of text like this

POINT(xxx.xx yyy.yy)

where each xxx.xx yyy.yy were actual coordinates, 
perhaps as output from another process/system.
Going out of your way to add in the text "POINT" is ... pointless.

Second, you can even use ST_Point() as it is an alias for ST_MakePoint().
(Some programmers prefer the simpler forms -- easier to read.)

Theo F · Answer

Old question, but if you are using the OS postcode point data (Code-point Open), and you have it in the form of a PostgreSQL table (let's assume it's in your public schema and called 'postcodes') with columns: postcode, easting, northing...
You could firstly add an empty geometry column:
ALTER TABLE public.postcodes 
ADD COLUMN geom geometry(POINT,27700);

Then using ST_POINT, you can populate this new geometry column using the coordinates available in the easting (x) and northing (y) columns:
UPDATE public.postcodes 
SET geom = ST_SetSRID(ST_POINT(easting, northing),27700);

To be extra safe here, I have wrapped ST_POINT inside an ST_SetSRID, to make sure the new geometry is in the correct SRID. In this case, the postcode point data uses 27700 when dealing with eastings and northings.

ST_MakePoint or ST_PointFromText to generate points

4 Answers

Add your own answers!

Ask a Question