FW: [postgis-users] Insert transformed coordinates

[Gary Townsend]

-----Original Message-----
From: Gary Townsend [mailto:garyt at spatialmapping.com] 
Sent: June 11, 2007 8:37 AM
To: 'Charlton Purvis'
Subject: RE: [postgis-users] Insert transformed coordinates

Actually I found the problem, someone on the pgsql-novice list pointed out
my error I was missing a concatenation in my point string and on top of that
I had the parameter for projection as numeric rather than integer so when it
would try to run gemofromtext it would return that it couldn't find a
function with that signature. Here is the final function that worked.

CREATE OR REPLACE FUNCTION vts_insert_stop(text, text, numeric, numeric,
integer)
  RETURNS void AS
'

DECLARE
	stopnum ALIAS for $1;
	stopdes ALIAS for $2;
	stopeasting ALIAS for $3;
	stopnorthing ALIAS for $4;
	projection ALIAS for $5;
	transCoord RECORD;
BEGIN
	SELECT INTO transCoord X(SubSel.transformed_geom),
Y(SubSel.transformed_geom) FROM (
	  SELECT SetSRID(
	    Transform(
	      GeomFromText(
		''POINT('' || stopeasting || '' '' || stopnorthing || '')'',
projection
	      ), 4326
	    ),
	  -1) AS transformed_geom) SubSel;
	
      INSERT INTO
vts_route_stops(stop_number,stop_description,stop_latitude,stop_longitude)
      VALUES(stopnum,stopdes,transCoord.Y,transCoord.X);

	RETURN void;

END'
  LANGUAGE 'plpgsql' VOLATILE;

-----Original Message-----
From: Charlton Purvis [mailto:cplist at secondcreek.org] 
Sent: June 8, 2007 2:43 PM
To: 'PostGIS Users Discussion'
Cc: garyt at spatialmapping.com
Subject: RE: [postgis-users] Insert transformed coordinates

Gary, 

>       SELECT INTO transCoord X(SubSel.transformed_geom),
> Y(SubSel.transformed_geom) FROM (

That part of the select into looks suspicious to me.  Have you tried
selecting into plain variables?

declare
...
  my_x float;
  my_y float;

...

select into my_x, my_y X(SubSel.transformed_geom),
Y(SubSel.transformed_geom) FROM (
        SELECT SetSRID(

... 

INSERT INTO
vts_route_stops(stop_number,stop_description,stop_latitude,stop_longitude)
VALUES(stopnum,stopdes,my_y,my_x);

I think that will work.  I might be off in the syntax above, but what looks
weird is the way you're selecting into components of a record set.

Charlton