0

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?

  • What do you mean by "doesn't work"? What is the specific error message you get? Can you add your create table statement? Also, those two points have the same coordinates! – mlinth Jun 2 '16 at 9:04
  • Made the edits you requested – Luffydude Jun 2 '16 at 9:14
1

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));
| improve this answer | |
  • Worked like a charm. Thanks! Query returned successfully: one row with OID 23570 inserted, 297 msec execution time. – Luffydude Jun 2 '16 at 9:17
  • 2
    Since you are already using ST_GeomFromText(), why not just give it a LINESTRING() well-known text, instead calling it with many POINT() WKT strings and using makeline? ST_Makeline is really more for assembling lines out of points that are already in the database; st_geomfromtext is more for importing. – chodgson Jun 2 '16 at 22:54

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.