Обсуждение: 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; >