CODEWITHSUNDEEP
databasepostgresqltypespostgis
Praveen Rawat
Praveen Rawat

Reputation: 734

How to store POINTS(LANG, LAT) into geometry type column in PostGIS?

I have created a table itapp_cities in PostGIS which stores the data of cities. I have added a column location with datatype geometry to store longitude and latitude of a city. When I run the following INSERT query I get the error shown below.

INSERT query:

INSERT INTO itapp_cities(city_id, city_name, city_code, state_id, location) 
  VALUES (DEFAULT,'Ada', 'ada-ok',37,POINT(34.774531000000003, -96.678344899999999));

Table definition:

CREATE TABLE itapp_cities
(
  city_id bigserial NOT NULL,
  city_name character varying(100) NOT NULL,
  city_code character varying(5) NOT NULL DEFAULT ''::character varying,
  state_id bigint NOT NULL,
  location geometry,
  CONSTRAINT itapp_cities_pkey PRIMARY KEY (city_id),
  CONSTRAINT fk_states FOREIGN KEY (city_id)
      REFERENCES itapp_states (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)

Error:

ERROR:  column "location" is of type geometry but expression is of type point
LINE 2:   VALUES (DEFAULT,'Ada', 'ada-ok',37,POINT(34.77453100000000...
                                             ^
HINT:  You will need to rewrite or cast the expression.
********** Error **********

ERROR: column "location" is of type geometry but expression is of type point
SQL state: 42804

How can I store point values inside this column? I am new to PostGIS so forgive me for this silly question

Upvotes: 4

Views: 7077

Answers (3)

blindguy
blindguy

Reputation: 1009

According to the postGIS docs, your original insert was close. Just add ' around the POINT expression.

INSERT INTO itapp_cities(city_id, city_name, city_code, state_id, location) 
  VALUES (DEFAULT,'Ada', 'ada-ok',37,'POINT(34.774531000000003, -96.678344899999999)');

http://postgis.net/workshops/postgis-intro/geometries.html

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656391

You can use the function ST_MakePoint() and the set the SRID (Spatial Reference System Identifier) with ST_SetSRID():

SELECT ST_SetSRID(ST_MakePoint(longitude, latitude),4326)

Or while you enter literal values anyway, feed the string representation to ST_GeomFromText():

SELECT ST_GeomFromText('SRID=4326;POINT(34.774531 -96.6783449)')

Related answer on dba.SE with more details and links:

Upvotes: 3

user2414527
user2414527

Reputation:

Try this SQL and match it with your insert sql query 

    INSERT INTO itapp_cities(city_id, city_name, slug, state_id, location) 
  VALUES (DEFAULT,'Ada', 'ada-ok',37,st_GeomFromText('POINT(34.774531000000003 -96.678344899999999)', 312));

for more details go through this link

Upvotes: 2

Related Questions