[postgis] text -> geometry ?

[Michael Scharber]

Dave,

Worked like a charm.  From within my plpgsql function the following now
works just fine:

update <table>
  set <geom_col>
    = (''POINT('' || newlon || '' '' || newlat || '' '' || newht || '')'')::text::geometry
  where .....

Thanks a million!

Anxious to see what can be accomplished with Postgres, PostGIS,
Mapserver and a little elbow grease.

Cheers,

Michael

p.s. Thanks as well for the detailed explanation.


On Fri, 24 Aug 2001, Dave Blasby wrote:

> The reason is because there is no routine that converts 'text' to
> 'geometry'.
>
> In postgresql, if you type something inside single quotes, it is turned
> into a normal c-style string.
>
> mapserv3=# select 'dave';
>  ?column?
> ----------
>  dave
> (1 row)
>
>
> Here the result is 5 bytes of memory - the characters 'd', 'a', 'v',
> 'e', <null>.
>
> But, if you use the TEXT type, you actually get a postgresql variable
> length datastructure.
>
> mapserv3=# select 'dave'::text;
>  ?column?
> ----------
>  dave
> (1 row)
>
> Here the result is 8 bytes long. The first 4 are interpreted as an
> integer and give the full length of the structure (its value is 8).  The
> next 4 bytes are actually 'd', 'a', 'v', 'e'.
>
> To make a long story short, both the type and structure of 'dave' and
> 'dave'::text are quite different.
>
> I've added another function to the CVS version that will automagicly
> convert TEXT to GEOMETRY.
>
> mapserv3=# select 'POINT(0 0)'::text::geometry;
>       ?column?
> --------------------
>  SRID=-1;POINT(0 0)
> (1 row)
>
> Unfortunately, you'll have to upgrade to either the CVS version or to
> 0.6 (to be released when paul comes back from holiday).  0.6 is quite
> different from 0.5, so you might not want to upgrade until all the
> documentation is finished.
>
> If paul were here, I'd release a 0.5.2 for you.  Unfortunately, he's
> not.  Here's how to patch 0.5;
>
> To add this functionality to 0.5, you'll have to:
>
> 1. add this to the postgis_inout.c file: [thats
> DirectFunctionCall<number one>]
> //takes a text argument and parses it to make a geometry
> PG_FUNCTION_INFO_V1(geometry_text);
> Datum geometry_text(PG_FUNCTION_ARGS)
> {
> 	char		*input = (char *)  PG_DETOAST_DATUM(PG_GETARG_DATUM(0));
>
> 	if (*((int *) input) == 4)
> 	{
> 		//empty string
> 		PG_RETURN_NULL();
> 	}
> 	input = &input[4];
> 	PG_RETURN_POINTER (
> 		DatumGetPointer(
> DirectFunctionCall1(geometry_in,PointerGetDatum(input)))
> 			);
>
> }
>
> 2. add this to postgis.sql.in:
> CREATE FUNCTION geometry(text)
>    RETURNS GEOMETRY
>    AS '@MODULE_FILENAME@','geometry_text'
> 	     LANGUAGE 'c' WITH (iscachable,isstrict);
>
> 3. recompile and install.  You'll get a warning that 'geometry_text'
> hasnt been previously prototyped - ignore it.
>
> 4. if you look in the postgis.sql file, you'll see that the
> '@MODULE_FILENAME@' in step 2 has been replaced with an actual location
> of your .so.  Copy and paste the CREATE FUNCTION statement into psql.
>
> 5. test it by typing "select 'POINT(0 0)'::text::geometry;" into psql
>
> dave
>
>
> To unsubscribe from this group, send an email to:
> postgis-unsubscribe at yahoogroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>

-- 
*******************************************************
      	Michael Scharber
	Scripps Institution of Oceanography
	Institute of Geophysics and Planetary Physics
	8785 Biological Grade
	IGPP Room 4212
	La Jolla, CA 92037

	mscharber at josh.ucsd.edu
	(858)534-1750
*******************************************************


------------------------ Yahoo! Groups Sponsor ---------------------~-->
Secure all your Web servers now: Get your FREE Guide and learn to: DEPLOY THE LATEST ENCRYPTION,
DELIVER TRANSPARENT PROTECTION, and More!
http://us.click.yahoo.com/VihfLB/nT7CAA/yigFAA/PhFolB/TM
---------------------------------------------------------------------~->

To unsubscribe from this group, send an email to:
postgis-unsubscribe at yahoogroups.com

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/