[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] Help with PL/PGSQL!
From: Eric Aspengren <ericaspen () gmail ! com>
Date: 2012-07-30 20:16:25
Message-ID: CAFLHT5G1m94Ptj=daku1K_ubJmdRkvT4YSrYwUYHHV1T-K0i+A () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
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@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@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@postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
>
>
> --
> ************************************
> David William Bitner
>
> _______________________________________________
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
--
Eric Aspengren
(402) 478-VOTE
ericaspen@gmail.com
[Attachment #5 (text/html)]
Well, that was simple enough! Thanks! (as Eric rolls his eyes at himself)<br><br><div \
class="gmail_quote">On Mon, Jul 30, 2012 at 3:13 PM, David William Bitner <span \
dir="ltr"><<a href="mailto:bitner@gyttja.org" \
target="_blank">bitner@gyttja.org</a>></span> wrote:<br> <blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex">You have geocoded declared as a type record. ST_Within needs \
that to be of type geometry.<br><br><div class="gmail_quote"> <div><div class="h5">On \
Mon, Jul 30, 2012 at 1:32 PM, Eric Aspengren <span dir="ltr"><<a \
href="mailto:ericaspen@gmail.com" target="_blank">ericaspen@gmail.com</a>></span> \
wrote:<br>
</div></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px \
#ccc solid;padding-left:1ex"><div><div class="h5"><div>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):</div>
<div><br></div><div><div>CREATE OR REPLACE FUNCTION get_district(address \
text)</div><div> RETURNS text AS</div><div>$$</div><div>DECLARE</div><div> \
district RECORD;</div><div> geocoded RECORD;</div><div>BEGIN</div>
<div> SELECT geomout into geocoded from geocode(address) as g;</div><div> \
SELECT sldust from sldu into district where ST_Within(geocoded, the_geom);</div><div> \
return district;</div><div>END;</div><div>$$</div><div>
LANGUAGE 'plpgsql' \
IMMUTABLE;</div></div><div><br></div><div><br></div><div>geocoder=# select \
get_district('1700 C St Lincoln, \
NE');</div><div><br></div><div><br></div><div>ERROR: function st_within(record, \
geometry) does not exist</div>
<div>LINE 1: SELECT sldust from sldu where ST_Within( $1 , the_geom)</div><div> \
^</div><div>HINT: No function matches the given name and argument types. You might \
need to add explicit type casts.</div>
<div>QUERY: SELECT sldust from sldu where ST_Within( $1 , \
the_geom)</div><div>CONTEXT: PL/pgSQL function "get_district" line 6 at \
SQL statement</div><span><font color="#888888"><div><br></div>-- <br>
Eric Aspengren<br>
</font></span><br></div></div><div \
class="im">_______________________________________________<br> postgis-users mailing \
list<br> <a href="mailto:postgis-users@postgis.refractions.net" \
target="_blank">postgis-users@postgis.refractions.net</a><br> <a \
href="http://postgis.refractions.net/mailman/listinfo/postgis-users" \
target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br> \
<br></div></blockquote></div><span class="HOEnZb"><font color="#888888"><br><br \
clear="all"><div><br></div>-- <br>************************************<br>David \
William Bitner<br> </font></span><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" \
target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br> \
<br></blockquote></div><br><br clear="all"><div><br></div>-- <br>Eric \
Aspengren<br>(402) 478-VOTE<br><a href="mailto:ericaspen@gmail.com" \
target="_blank">ericaspen@gmail.com</a><br>
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic