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

Within a pl/pgsql function block, I'm wanting to populate a geometry column with x,y data.  However when I invoke the
function,I get the error message that column "y" doesn't exist, even though it does. 

The error message is:

SELECT load_xyz_data('/var/tmp/rscp_coverage_test.txt',32754);
ERROR:  column "y" does not exist
LINE 1: ...temp SET wkb_geometry = PointFromText(''POINT('|| y ||' '|| ...
           ^ 
QUERY:  SELECT 'UPDATE xyz_points_temp SET wkb_geometry = PointFromText(''POINT('|| y ||' '|| x ||')'','|| sourcesrid
||')'
CONTEXT:  PL/pgSQL function "load_xyz_data" line 24 at EXECUTE statement

My function is:

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


I suspect it's a syntax issue, but can't get it to work?   Any suggestions?


Thanks

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

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