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;
>
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
