CODEWITHSUNDEEP
postgresqlpostgisplpgsqlunnest
Debaser
Debaser

Reputation: 467

Postgres unnest with points

I'm trying to write a PL/PGSQL function that takes delimited text as input and inserts rows into a table that includes points. I've got a test function that works OK:

rowsaz := string_to_array(input,'?');
INSERT INTO test (num1, num2, my_name)
SELECT * FROM unnest(string_to_array(rowsaz[1],',')::integer[],string_to_array(rowsaz[2],',')::integer[],string_to_array(rowsaz[3],',')::varchar[]);
return true;

So if you call the function with

SELECT myfunction('1,2,3?4,5,6?a,b,c')

Then you get a table like

1   4   a
2   5   b
3   6   c

But how do you do this when you have a table like

CREATE TABLE public.gps_points
(
    id integer NOT NULL DEFAULT nextval('gps_id_seq'::regclass),
    location geometry(Point,4326),
    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    user_id integer
)

An insert would look like INSERT INTO gps_points (location, user_id) VALUES (ST_GeomFromText('POINT(-71.060316 48.432044)', 4326),2);

But this gets tricky with the unnest because you have to pass the coordinates into the ST_POINT function. Then I would call the function with: SELECT myfunction('36.98,36.99,36.97?45.85,45.86,45.87?1,2,3')

I'm trying to do the following, but it won't work

insert into gps_points( geom, user_id)
select unnest( (ST_GeomFromText('POINT(string_to_array(rowsaz[1],',')::double precision[] string_to_array(rowsaz[2],',')::double precision[])', 4326),string_to_array(rowsaz[3],',')::double precision[]));

Upvotes: 1

Views: 216

Answers (1)

Debaser
Debaser

Reputation: 467

Somebody helped me get it:

insert into gps_points( geom, user_id)
  select ST_SetSRID(ST_MakePoint(lng, lat), 4326), uid from unnest(string_to_array(rowsaz[1],',')::double precision[],string_to_array(rowsaz[2],',')::double precision[],string_to_array(rowsaz[3],',')::integer[]) as u(lng,lat,uid);

Upvotes: 1

Related Questions