CODEWITHSUNDEEP
ruby-on-railspostgresqlpostgis
Tom Hen
Tom Hen

Reputation: 55

Rails and inserting polygon data (geojson) to the postgis table

I'm struggling with inserting geoJSON polygon data into postgis table. PostGis working fine. I can execute this query from PG client but it doesn't work from app in rails.

The insert query in my model in rails:

class Route < ActiveRecord::Base

  def self.AddNew(id_partner, route_name, polygon, active)

    sql = "INSERT INTO routes('idPartner', 'name', 'polygon', 'active') VALUES ('#{id_partner}', '#{route_name}', ST_GeomFromGeoJSON('#{polygon}'),4326), '#{active}')"
    ActiveRecord::Base.connection.execute(sql)
 end
end

and I'm constantly getting:

PG::SyntaxError: ERROR: syntax error at or near "'idPartner'" LINE 1: INSERT INTO routes('idPartner', 'name', 'polygon', 'active')... ^ : INSERT INTO routes('idPartner', 'name', 'polygon', 'active') VALUES ('8', 'Dara', ST_GeomFromGeoJSON('{"type":"POLYGON","id":null,"coordinates":[[[19.00634765625,52.736291655910925],[22.1484375,52.133488040771475],[22.236328125,52.8823912222619]]]}'),4326), '1')

Table structure:

-- Table: public.routes

CREATE TABLE public.routes
(
  id integer NOT NULL DEFAULT nextval('routes_id_seq'::regclass),
  "idPartner" integer,
  name character varying,
  polygon geometry(Geometry,4326),
  active integer,
  CONSTRAINT routes_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.routes
  OWNER TO postgres;

Any thoughts?

Upvotes: 1

Views: 1370

Answers (1)

Mr Phung
Mr Phung

Reputation: 71

Your SQL query has problems. First, the name of colums not match the SQL syntax. Second, the parameter polygon is not correct, you must transform the srid by using the function transform grid, for example ST_SetSRID. You could check by run SQL in postgresql example 

INSERT INTO routes("idPartner", name, polygon, active) VALUES ('8', 'Dara', ST_SetSRID(ST_GeomFromGeoJSON(
    '{"type":"Polygon","coordinates":[[[19.00634765625,52.736291655910925],[22.1484375,52.133488040771475],[22.236328125,52.8823912222619]]]}'
),4326), '1');

Upvotes: 1

Related Questions