1

I have the coordinates of a lot of points in a table. In pgadmin I want to make a line that passes on these points. I already have my Coordinate point im my table. I want to make line with those cooirdinate point that already have in my table. In the same table I want to INSERT geom line in the geom geometry column enter image description here

Description of the table: ID,X,Y, description column, length, description column. I want to make the line that passes on this point.

I have done this query:

SELECT AddGeometryColumn('MyTable', 'geom', 32634, 'LINESTRING', 2);
UPDATE "MyTable" 
SET geom = ST_SetSRID(ST_MakeLINE((ST_MakePOINT1("X", "Y"), ST_MakePOINT2("X", "Y")), 32634));

but it doesn't work. Can anyone help me please?

closed as unclear what you're asking by Devdatta Tengshe, John Powell, Mapperz Mar 26 '15 at 16:11

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    Can you provide a bit more detail on what is in your table "myTable". Is 2 points per row or a single point per row? Are you trying to create a linestring per row or one that tracks through all rows? – MickyT Mar 25 '15 at 21:41
  • Yes, I have two column with X and Y coordinate. In one row I have only one point with two coordinate ( X/Y). Thanks – Po Po Mar 26 '15 at 9:07
  • Your question is currently not understandable. How are you having two columns each Named X & Y? Please give us more details on your table structure, and the column names – Devdatta Tengshe Mar 26 '15 at 9:23
  • @DevdattaTengshe is more clear for you? – Po Po Mar 26 '15 at 14:58
  • A line needs at the minimum 2 points. How do you expect to draw a unique line, when you have only one x & y coordinates? – Devdatta Tengshe Mar 30 '15 at 4:34
2

As far as I know, a table cannot have two geometry types (unless the type is a collection). Instead of modifying your existant table, try creating a new one:

The geometry column is like any other column, it has one value for each row of the table. You are trying to create one geometry from many geometries, so what you want is not update the geometry of your current rows, but create a new row with a new geometry. For that you'll need to create a new table, which will only have one row, with the entire line geometry:

create table "MyLineTable" (
    geom geometry(LINESTRING,32634)
    );

To create the geometry, you can either use the ST_MakeLine with an array of point geometries, or using the geometry column of a point table. Since your coordinates are in non-geometric columns, you can either pass them to an array:

with points as (
    select array_agg(st_makepoint("X","Y")) as pts
    from "MyPointsTable"
)
select st_setsrid(st_makeline(pts),32634)
from points;

Or pass them to the geom column of your points table, and use that to feed ST_MakeLine:

update "MyPointsTable"
set geom = st_setsrid(st_makepoint("X","Y"),32634);

insert into "MyLineTable" (geom)
select st_makeline(pts.geom)
from "MyPointsTable" as pts;
  • Thanks @Roberto Ribeiro. In fack I haven't populate yet the geom geometry column. I want to populate that with line geom. – Po Po Mar 26 '15 at 9:17
  • Ok. Still, you want to create one row of line geometry from a bunch of (currently geometry-less) rows, and for that you'll need a new table. You could, in theory, pass a new row to your current table and have that one be the line, but you'd be mixing different types of data, which would only lead to a messy db. Best to create a new table just for the line, and later drop the points table if no longer needed. Added some clarification to my answer. – Roberto Ribeiro Mar 26 '15 at 15:15
  • It's not problem for me to create new table for the line geom. I tried with the first code but that code only create column for line and didn't populate that. – Po Po Mar 26 '15 at 15:37
1

It's a bit difficult to determine exactly what you are trying to achieve.

I've provided an example of how to handle the two scenarios that I think you are most likely trying to achieve. I have tried to keep the example short and simple, so I haven't included any SRID stuff and have just used INTs for coordinates.

Scenario 1 is adding a line for each of the rows in your table.

Scenario 2 is creating a line that connects the points of all the rows. Note that this should not be added as a column in your table. You will also require some way to sequence the points.

-- Setup Test 
CREATE TABLE myTest (
    X1 INT,
    Y1 INT,
    X2 INT,
    Y2 INT,
    SEQ INT
    );

INSERT INTO myTest VALUES 
    (0, 0, 10, 0, 1),
    (10, 0, 10, 10, 2),
    (10, 10, 0, 10, 3),
    (0, 10, 0, 0, 4);

-- Scenario 1.  A LINESTRING for each row
ALTER TABLE myTest ADD myLine Geometry(LINESTRING);

UPDATE myTest
SET myLine = ST_MakeLine(ST_MakePoint(x1,y1), ST_MakePoint(x2,y2));

SELECT ST_AsText(myLine),*
FROM myTest;

-- Scenario 2.  A single LINESTRING for all rows
CREATE TABLE myTestLine (
    myLine Geometry(LINESTRING)
    );

INSERT INTO myTestLine (myLine)
SELECT ST_MakeLine(ST_MakePoint(X1,Y1)ORDER BY SEQ)
FROM myTest;

SELECT ST_AsText(myLine)
FROM myTestLine;

-- Clean up
DROP TABLE myTest;
DROP TABLE myTestLine;
  • Thanks @MickyT. Is question more clear now? – Po Po Mar 26 '15 at 9:46

Not the answer you're looking for? Browse other questions tagged or ask your own question.