[postgis] text -> geometry ?

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

[postgis] text -> geometry ?

Michael Scharber-2
Hi there,

Can anyone help me understand how to assign a geometry field a value that
is not a string literal?  In plpgsql I'm trying the following from within
a trigger'd function:

update <table>
  set <geom_field> = ''POINT('' || x || '' '' || y || '' '' || z || '')''
  where <field> = <some_value>;

where x, y and z are float8 types.

I've also tried the following in psql directly:

update <table>
  set <geom_field> = 'POINT(' || '1' || ' ' || '2' || ' ' || '3' || ')'
  where <field> = <some_value>;

In both cases I get
  "ERROR:  Attribute 'shape' is of type 'geometry' but expression is of type 'text'
        You will need to rewrite or cast the expression"

so I tried ('POINT(' || '1' || ' ' || '2' || ' ' || '3' || ')')::geometry
and got "ERROR:  Cannot cast type 'text' to 'geometry'".

I just started using Postgres, and PostGIS for that matter, yesterday so
I'm hoping someone can point out the blunder I'm making.

Thanks in advance.

Michael


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

        [hidden email]
        (858)534-1750
*******************************************************


------------------------ Yahoo! Groups Sponsor ---------------------~-->
Get VeriSign's FREE GUIDE: "Securing Your Web Site for Business." Learn about using SSL for serious online security. Click Here!
http://us.click.yahoo.com/KYe3qC/I56CAA/yigFAA/PhFolB/TM
---------------------------------------------------------------------~->

To unsubscribe from this group, send an email to:
[hidden email]

 

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



Reply | Threaded
Open this post in threaded view
|

Re: [postgis] text -> geometry ?

David Blasby-3
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

------------------------ Yahoo! Groups Sponsor ---------------------~-->
Get VeriSign's FREE GUIDE: "Securing Your Web Site for Business." Learn about using SSL for serious online security. Click Here!
http://us.click.yahoo.com/KYe3qC/I56CAA/yigFAA/PhFolB/TM
---------------------------------------------------------------------~->

To unsubscribe from this group, send an email to:
[hidden email]

 

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



Reply | Threaded
Open this post in threaded view
|

Re: [postgis] text -> geometry ?

Michael Scharber-2
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:
> [hidden email]
>
>
>
> 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

        [hidden email]
        (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:
[hidden email]

 

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



Reply | Threaded
Open this post in threaded view
|

Re: [postgis] text -> geometry ?

David Blasby-3
Michael Scharber wrote:
> Anxious to see what can be accomplished with Postgres, PostGIS,
> Mapserver and a little elbow grease.

Well, keep us informed with what you're up to - no point in others
re-inventing the wheel.

dave

------------------------ 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:
[hidden email]

 

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