Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have created one table in my Postgis. But I can not insert point. I do not know what's 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"

I do not know how to solve this.

share|improve this question
    
You may want to start marking some answers as accepted, to ensure you continue getting good responses. – MerseyViking May 1 '12 at 9:17
up vote 46 down vote accepted

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);
share|improve this answer
    
thanks it works.. – Shahjalal May 1 '12 at 10:00
    
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

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.

share|improve this answer

protected by underdark Mar 17 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.