From: | Mark Wynter <mark(at)wynterhouse(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Problem using a pl/pgsql function to populate a geometry column with x, y data - SOLVED |
Date: | 2012-07-24 23:01:00 |
Message-ID: | 12B3AA76-57D3-4BCD-AA8E-A7C2F2A44C12@wynterhouse.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Nothwithstanding the fact I had my x and y's around the wrong way, I've got it working using ST_MakePoint. See below
On 25/07/2012, at 12:39 AM, Mark Wynter wrote:
> CREATE OR REPLACE FUNCTION load_xyz_data(sourcefile text, sourcesrid integer)
> RETURNS text AS $$
> DECLARE
> BEGIN
>
> EXECUTE 'CREATE TEMPORARY TABLE xyz_points_temp
> (
> x numeric,
> y numeric,
> z numeric
> )
> WITH (
> OIDS=FALSE
> )
> ON COMMIT DROP';
>
> -- Load xyz data
> EXECUTE 'COPY xyz_points_temp FROM '|| quote_literal(sourcefile) ||' DELIMITER '',''';
> -- Add geometry column
> EXECUTE 'ALTER TABLE xyz_points_temp ADD COLUMN wkb_geometry geometry(POINT,'|| sourcesrid ||')';
>
> -- Populate geometry column with x,y data
-- This works
EXECUTE 'UPDATE xyz_points_temp SET wkb_geometry = ST_SetSRID(ST_MakePoint(x, y), '|| sourcesrid ||')';
--This does not work
> -- EXECUTE 'UPDATE xyz_points_temp SET wkb_geometry = ST_GeomFromText(''POINT('|| y ||' '|| x ||')'','|| sourcesrid ||')';
>
> -- Now do something else
>
> RETURN 'DATA LOADED';
> END;
> $$ LANGUAGE plpgsql STRICT;
>
From | Date | Subject | |
---|---|---|---|
Next Message | Mark Wynter | 2012-07-24 23:16:06 | Re: Problem using a pl/pgsql function to populate a geometry column with x, y data - SOLVED |
Previous Message | McGehee, Robert | 2012-07-24 22:21:48 | Smaller data types use same disk space |