[GIS] ST_MakePoint or ST_PointFromText to generate points

coordinatesordnance-surveypointpostgispostgresql

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?

Best 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.

Related Question