4

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?

  • I benchmarked several functions in this answer, including ST_MakePoint. ST_PointFromText uses ST_GeomFromText under the hood (see the function definition). – Mike T Feb 8 '16 at 20:13
6

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.

3

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)
2

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

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.