[GIS] How to create a line into a table in Postgis

postgispostgresqlsql

I need to draw a line and create a new entry on an existing table in Postgis.

Say I have a table called myTable and two points with coordinates 528115,181037 and 528115,181037 using the EPSG:27700 (OSGB British National Grid)

I know that I need to use the command ST_MakeLine and I tried something like: 

 INSERT INTO myTable(id, name, geom)
 VALUES('1','test', ST_SetSRID(ST_MakeLine((528115,181037),(533903,180877), 27700)));

But it doesn't work. Error returned is:

   ERROR:  function st_makeline(record, record, integer) does not exist
LINE 2:      VALUES('1','test', ST_SetSRID(ST_MakeLine((528115,18103...
                                       ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

********** Error **********

ERROR: function st_makeline(record, record, integer) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Character: 69

My create table statement is:

CREATE TABLE public.myTable(
  id integer NOT NULL,
  name character varying(25),
  geom geometry(LineString,27700),
  CONSTRAINT pk_bft PRIMARY KEY (id)
)
WITH (
OIDS=TRUE
 );

And in the documentation it's not clear on how to add the created line into an existing table. How to do this?

Best Answer

You need to use ST_GeomFromText to specify your point geometry, note that the coordinates are separated by a blank space and not a comma.

Try the following (changed one of your point coordinates so that it isn't the same as the other): 

INSERT INTO myTable(id, name, geom)
 VALUES('1','test', ST_SetSRID(ST_MakeLine(ST_GeomFromText('POINT(528115 181037)'),ST_GeomFromText('POINT(528115 181137)')), 27700));
Related Question