Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux