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.

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

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.

  • 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 Sep 13 '15 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 Sep 13 '15 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 Sep 13 '15 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 Sep 13 '15 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 Sep 14 '15 at 5:42

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.