Re: Problem using a pl/pgsql function to populate a geometry column with x,y data - SOLVED

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;
>