49

I have created one table in my PostGIS nut I cannot insert point.

What is wrong with my query?

CREATE TABLE app ( 
  p_id INTEGER PRIMARY KEY

);

SELECT AddGeometryColumn('app','the_geom','4326','POINT',2);

INSERT INTO app(p_id, the_geom) VALUES(2, POINT(-71.060316, 48.432044));

After the last query it shows some error..

ERROR:  column "the_geom" is of type geometry but expression is of type point
LINE 1: ...SERT INTO app(p_id, the_geom) VALUES(2, POINT(-71....
                                                             ^
HINT:  You will need to rewrite or cast the expression.


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

ERROR: column "the_geom" is of type geometry but expression is of type point
SQL state: 42804
Hint: You will need to rewrite or cast the expression.
Character: 53

I already check my PostGIS version.

SELECT PostGIS_full_version();

I got the following output..

"POSTGIS="1.5.3" GEOS="3.3.1-CAPI-1.7.1" PROJ="Rel. 4.7.1, 23 September 2009" LIBXML="2.7.3" USE_STATS"
82

You are confusing SQL and WKT (well-known text). WKT is a like a geometry language to describe shapes, but it is not SQL, which is a language to query and manipulate databases. When working with WKT in an SQL query, it must be text, and not mixed-in with the SQL.

Your query works if you properly format the WKT (remove the ",") and set an SRID. For this method, ST_GeomFromText(wkt, srid) works well:

INSERT INTO app(p_id, the_geom)
VALUES(2, ST_GeomFromText('POINT(-71.060316 48.432044)', 4326));

If you have columns with numeric longitude/latitude, you can directly make a POINT geometry:

ST_SetSRID(ST_MakePoint(long, lat), 4326);

Check out the other geometry constructors in the manual.


For @vik86's request, the_geom can be updated in the table app from numeric columns long and lat using:

UPDATE app SET
  the_geom = ST_SetSRID(ST_MakePoint(long, lat), 4326);
  • Could you elaborate on using this feature, "If you have columns with numeric longitude latitude, you can directly make a POINT geometry: ST_SetSRID(ST_MakePoint(long, lat), 4326); " Right now I have a table with lat and long, to use the PostGIS api's I need it in the point format . Hence wanted to know how do i update the point column using this feature. Thanks Vikram – Vik86 Dec 28 '12 at 22:51
  • @Vik86 see updated answer – Mike T Jan 2 '13 at 20:21
1

If you are working with a Java client then my advice is to use the binary types to transfer the data. From memory, I recorded a performance improvement of 10% when I did this change over the ST_AsEWKT method.

protected by underdark Mar 17 '16 at 20:32

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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