1

I am a newbie in spatial database, so I am having lots of issues even in small tasks and now I am having some issue with inserting data to the table. I have followed the postgis documentation but however, I'm getting some error while inserting data to the table. In the code below, I am trying to insert an array of co-ordinates which are in long/lat format by converting them into points and adding to the table.

INSERT INTO route(ID, ROUTE_NAME, geom) VALUES (1, 'route 1', ST_GeomFromText('LINESTRING(ST_MakePoint(27.69858, 85.28154), ST_MakePoint(27.69804, 85.28155), ST_MakePoint(27.69337, 85.28174), ST_MakePoint(27.69356, 85.28275), ST_MakePoint(27.69378, 85.28370), ST_MakePoint(27.69409, 85.28449))',900913));

Table create structure: CREATE TABLE ROUTE( ID int4, ROUTE_NAME varchar(25), geom geometry(LINESTRING,900913) );

But, I am getting "LINESTRING(ST" <-- parse error at position 13 within geometry error.

Improve this question
1
  • 1
    Try googling what the Well Known Text string should contain -- hint: it's a text string with coordinates.
    – Vince
    Commented Sep 13, 2015 at 15:40

1 Answer 1

Reset to default
3

No need for ST_MakePoint, just chain your coordinates in ST_GeomFromText. You can just write something like ST_GeomFromText('LINESTRING(27.69858 85.28154, 27.69804 85.28155, 27.69337 85.28174, 27.69356 85.28275, 27.69378 85.28370, 27.69409 85.28449)', 900913). x and y parts of coordinates are separated by space, coordinate-pairs by commas. See also the ST_GeomFromText documentation for more information.

Improve this answer
5
  • Well its working very well now. But i have one more concern, that is there any way to convert the longitude/latitude to the Point and add to the table at the same time.
    – Ra Ka
    Commented Sep 13, 2015 at 15:50
  • Can you explain a bit more what you want to achieve? Why you would want to convert a latitude/longitude coordinate into a point? If inserting a point is what you need, just do it by ST_GeomFromText('POINT(27.69858 85.28154)'). If you want to "convert" from Latitude/Longitude to another reference system, use ST_Transform.
    – chkaiser
    Commented Sep 13, 2015 at 16:12
  • what i mean is that I want to store co-ordinates in the form of point(x,y) format instead of long/lat. So, I have array of long/lat data and I want to convert it into point before storing it into the table. In above case, whenever I select st_astext(geom), i simply get linestring of long/lat but what I want is to have line string of points instead of long/lat when i do st_astext(geom). BTW, i need this table's data to compute with another table data which is in point(x,y) format and also i don't want any extra conversion during computing if I can directly store in point form in this table.
    – Ra Ka
    Commented Sep 13, 2015 at 16:45
  • PostGIS does note make the difference between lat/long and x/y. It simply has a spatial reference system associated identified by its EPSG code. If it is a geographic reference system, it is automatically in lat/long, and if it is a projected reference system, in x/y. If you had lat/long coordinates in WSG84 (EPSG 4326), and you want to store them let's say in Google Mercator (EPSG 3857), you could do ST_Transform(ST_GeomFromText('LINESTRING(27.69858 85.28154, ...)', 4326), 3857).
    – chkaiser
    Commented Sep 13, 2015 at 18:28
  • Thanx, it is really helpful and now I am very clear. Also, I found that there is no necessarily convert lat/long to point. Somehow with your information I made some changes in my code and its working like a charm.
    – Ra Ka
    Commented Sep 14, 2015 at 5:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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