[postgis-users] Help with PL/PGSQL!

[Eric Aspengren]

Well, that was simple enough! Thanks! (as Eric rolls his eyes at himself)

On Mon, Jul 30, 2012 at 3:13 PM, David William Bitner <bitner at gyttja.org>wrote:

> You have geocoded declared as a type record. ST_Within needs that to be of
> type geometry.
>
> On Mon, Jul 30, 2012 at 1:32 PM, Eric Aspengren <ericaspen at gmail.com>wrote:
>
>> So, I've got this PL/PGSQL script that doesn't want to work. I've got the
>> TIGER geocoder up and running and I can get whatever I want from that.
>> However, when I try and combine ST_Within with the output from GEOCODE I
>> get an error. I assume there's a simple syntax error here, but I can't
>> figure it out. I've been able to get this to work when just cutting and
>> pasting the actual geometry data into where "geocoded" is below, but
>> replacing it with the variable name gives me an error (sldu is a table with
>> Senate districts and sldust is the district number column):
>>
>> CREATE OR REPLACE FUNCTION get_district(address text)
>>     RETURNS text AS
>> $$
>> DECLARE
>>     district RECORD;
>>     geocoded RECORD;
>> BEGIN
>>     SELECT geomout into geocoded from geocode(address) as g;
>>     SELECT sldust from sldu into district where ST_Within(geocoded,
>> the_geom);
>>     return district;
>> END;
>> $$
>> LANGUAGE 'plpgsql' IMMUTABLE;
>>
>>
>> geocoder=# select get_district('1700 C St Lincoln, NE');
>>
>>
>> ERROR:  function st_within(record, geometry) does not exist
>> LINE 1: SELECT sldust from sldu where ST_Within( $1 , the_geom)
>>                                       ^
>> HINT:  No function matches the given name and argument types. You might
>> need to add explicit type casts.
>> QUERY:  SELECT sldust from sldu where ST_Within( $1 , the_geom)
>> CONTEXT:  PL/pgSQL function "get_district" line 6 at SQL statement
>>
>> --
>> Eric Aspengren
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
>
>
> --
> ************************************
> David William Bitner
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>


-- 
Eric Aspengren
(402) 478-VOTE
ericaspen at gmail.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120730/16906e38/attachment.html>