Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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

2 Answers 2

up vote 33 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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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