using columns as POINT objects parameters

[kidult]

the sql query

INSERT INTO location(geom, x, y, longitude, latitude, easting, northing)
SELECT
Transform(SetSRID(GeomFromText('POINT(primary_lat_dec primary_lon_dec)'), 10002), 2263) AS geom,
primary_lat_dec AS x,
primary_lon_dec AS y,
x(Transform(SetSRID(GeomFromText('POINT(primary_lat_dec primary_lon_dec)'), 10002), 4326)) AS longitude,
y(Transform(SetSRID(GeomFromText('POINT(primary_lat_dec primary_lon_dec)'), 10002), 4326)) AS latitude,
x(Transform(SetSRID(GeomFromText('POINT(primary_lat_dec primary_lon_dec)'), 10002), 2263))::int AS easting,
y(Transform(SetSRID(GeomFromText('POINT(primary_lat_dec primary_lon_dec)'), 10002), 2263))::int AS northing
FROM gnis;

throws the error

ERROR:  parse error - invalid geometry
CONTEXT:  SQL function "geomfromtext" statement 1

because the POINT object parameters

EX: Transform(SetSRID(GeomFromText('POINT(primary_lat_dec primary_lon_dec)'), 10002), 2263)

are columns and not to static numeric decimal values. when i use static numeric decimal values the query is successful.

EX: Transform(SetSRID(GeomFromText('POINT(44.3475481 -73.6287475)'), 10002), 2263)

what is the correct syntax for using columns as parameters for the POINT object?

[Michael Fuhr]

On Sun, Nov 26, 2006 at 03:47:54PM -0800, kidult wrote:
> what is the correct syntax for using columns as parameters for the POINT
> object?

You can use MakePoint():

SELECT latitude, longitude, AsText(MakePoint(longitude, latitude))
FROM test;

  latitude  |  longitude  |            astext            
------------+-------------+-------------------------------
 44.3475481 | -73.6287475 | POINT(-73.6287475 44.3475481)
(1 row)

Geometry coordinates are (X, Y) so use (longitude, latitude) instead
of (latitude, longitude).


--
Michael Fuhr
_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users