<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=us-ascii" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 10.00.9200.17088"></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=810590716-07102014><FONT color=#0000ff
size=2 face=Arial>Barry,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=810590716-07102014><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=810590716-07102014><FONT color=#0000ff
size=2 face=Arial>That's pretty much what I changed in the reverse_geocode
function to ignore non-numeric ranges and just treat them as
NULL.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=810590716-07102014><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=810590716-07102014><FONT color=#0000ff><FONT
size=2 face=Arial>Did you have trouble installing the </FONT>
<DIV dir=ltr align=left><SPAN><FONT face=Arial><FONT size=2>r<SPAN
class=810590716-07102014>evised function noted in
</SPAN></FONT></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff><A
href="http://trac.osgeo.org/postgis/ticket/2958" target=_blank><FONT size=2
face=Arial>http://trac.osgeo.org/postgis/ticket/2958</FONT></A><FONT
face=Arial><FONT size=2> <SPAN class=810590716-07102014> (want to make sure
if you are just installing the function, to put a tiger.reverse_geocode so
it doesn't get installed in your default
schema)</SPAN></FONT></FONT></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff><FONT face=Arial><FONT
size=2><SPAN
class=810590716-07102014></SPAN></FONT></FONT></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff><FONT face=Arial><FONT
size=2><SPAN class=810590716-07102014>Hope that
helps,</SPAN></FONT></FONT></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff><FONT face=Arial><FONT
size=2><SPAN
class=810590716-07102014>Regina</SPAN></FONT></FONT></FONT></SPAN></DIV></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=810590716-07102014><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=810590716-07102014><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV><BR>
<DIV lang=en-us class=OutlookMessageHeader dir=ltr align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B> postgis-users-bounces@lists.osgeo.org
[mailto:postgis-users-bounces@lists.osgeo.org] <B>On Behalf Of </B>Barry
McCall<BR><B>Sent:</B> Tuesday, October 07, 2014 10:23 AM<BR><B>To:</B> PostGIS
Users Discussion<BR><B>Subject:</B> Re: [postgis-users] reverse_geocode()
specific error atspecificgeom point<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV dir=ltr><SPAN style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif">I'm
seeing with more clarity now.</SPAN>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif">After running</DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><BR></DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif">
<DIV>SELECT * FROM addr</DIV>
<DIV>WHERE fromhn ~* '[a-zA-Z]'</DIV>
<DIV>AND tohn ~* '[a-zA-Z]'</DIV></DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><BR></DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif">it returns 381,739
results with chars in fromhn and tohn. Your message came in where you
stated </DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="FONT-FAMILY: Arial; COLOR: rgb(0,0,255)">'It was caused by a record not
being emitted but that gets the function applied.'</SPAN><BR></DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="FONT-FAMILY: Arial; COLOR: rgb(0,0,255)"><BR></SPAN></DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif">Being new and still
learning the system I'm assuming there is a function that should be omitting any
non integer/numerical values from these columns?</DIV></DIV>
<DIV class=gmail_extra><BR>
<DIV class=gmail_quote>On Tue, Oct 7, 2014 at 10:07 AM, Barry McCall <SPAN
dir=ltr><<A href="mailto:barry.mccall+postgis@gmail.com"
target=_blank>barry.mccall+postgis@gmail.com</A>></SPAN> wrote:<BR>
<BLOCKQUOTE class=gmail_quote
style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0.8ex; BORDER-LEFT: #ccc 1px solid">
<DIV dir=ltr>Check out this lil guy right here :-)
<DIV><BR></DIV>
<DIV>
<DIV>SELECT * FROM tiger_data.nc_addr</DIV>
<DIV>WHERE tlid = '72088154'</DIV></DIV>
<DIV><BR></DIV>
<DIV><BR></DIV></DIV>
<DIV class=HOEnZb>
<DIV class=h5>
<DIV class=gmail_extra><BR>
<DIV class=gmail_quote>On Tue, Oct 7, 2014 at 10:06 AM, Barry McCall <SPAN
dir=ltr><<A href="mailto:barry.mccall@gmail.com"
target=_blank>barry.mccall@gmail.com</A>></SPAN> wrote:<BR>
<BLOCKQUOTE class=gmail_quote
style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0.8ex; BORDER-LEFT: #ccc 1px solid">
<DIV dir=ltr>Check out this lil guy :-)
<DIV><BR></DIV></DIV>
<DIV>
<DIV>
<DIV class=gmail_extra><BR>
<DIV class=gmail_quote>On Tue, Oct 7, 2014 at 9:57 AM, Paragon Corporation
<SPAN dir=ltr><<A href="mailto:lr@pcorp.us"
target=_blank>lr@pcorp.us</A>></SPAN> wrote:<BR>
<BLOCKQUOTE class=gmail_quote
style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0.8ex; BORDER-LEFT: #ccc 1px solid"><U></U>
<DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff
face=Arial>Barry,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff face=Arial>It was caused
by a record not being emitted but that gets the function
applied.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff face=Arial>I put in a
patch for this issue for 2.1 and 2.2 branch. You just have to
replace your reverse_geocode function with the one referenced in this
ticket</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff face=Arial><A
href="http://trac.osgeo.org/postgis/ticket/2958"
target=_blank>http://trac.osgeo.org/postgis/ticket/2958</A></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff face=Arial>On a slgihtly
related note, I noticed this address geocodes to 1,Greenwood for
location instead of just Greenwood.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff face=Arial>That is
caused because the tiger_data.nc_cousub table has a bunch of 1s etc to
denote township number I guess. Not sure why they did
that.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff face=Arial>if you want
to get rid of those numbers, update the name field in
tiger_data.nc_cousub.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff face=Arial>I forget why
I use cousub instead of something else.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff face=Arial>Hope that
helps,</FONT></SPAN></DIV><SPAN>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff
face=Arial>Regina</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff face=Arial><A
href="http://www.postgis.us"
target=_blank>http://www.postgis.us</A></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff face=Arial><A
href="http://postgis.net"
target=_blank>http://postgis.net</A></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff
face=Arial></FONT></SPAN> </DIV><BR></SPAN>
<DIV lang=en-us dir=ltr align=left>
<HR>
<FONT face=Tahoma><SPAN><B>From:</B> <A
href="mailto:postgis-users-bounces@lists.osgeo.org"
target=_blank>postgis-users-bounces@lists.osgeo.org</A> [mailto:<A
href="mailto:postgis-users-bounces@lists.osgeo.org"
target=_blank>postgis-users-bounces@lists.osgeo.org</A>] <B>On Behalf Of
</B>Barry McCall<BR></SPAN><B>Sent:</B> Tuesday, October 07, 2014 8:17
AM<BR><B>To:</B> PostGIS Users Discussion<BR><B>Subject:</B> Re:
[postgis-users] reverse_geocode() specific error at specificgeom
point<BR></FONT><BR></DIV>
<DIV>
<DIV>
<DIV></DIV>
<DIV dir=ltr><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif">The change
from </SPAN><SPAN
style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif">
<DIV><BR>
<DIV>to_number(fromhn, '999999') As fromhn, to_number(tohn, '999999') As
tohn</DIV>
<DIV> </DIV>
<DIV><FONT color=#0000ff face=Arial>to</FONT></DIV>
<DIV><FONT color=#0000ff face=Arial></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial>fromhn, tohn</FONT></DIV></DIV>
<DIV><FONT color=#0000ff face=Arial><BR></FONT></DIV></SPAN>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="FONT-SIZE: small; FONT-FAMILY: arial">does work when I change it in
the debug code. When I update the function it spits out the error
below:</SPAN><BR></DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="FONT-SIZE: small; FONT-FAMILY: arial"><BR></SPAN></DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif">
<DIV>ERROR: operator does not exist: character varying - character
varying</DIV>
<DIV>LINE 1: ...ate_Point(var_redge.line, var_pt)*(var_redge.tohn -
var_redg...</DIV>
<DIV>
^</DIV>
<DIV>HINT: No operator matches the given name and argument type(s).
You might need to add explicit type casts.</DIV>
<DIV>QUERY: SELECT (var_redge.fromhn +
ST_Line_Locate_Point(var_redge.line, var_pt)*(var_redge.tohn -
var_redge.fromhn))::numeric(10)</DIV>
<DIV>CONTEXT: PL/pgSQL function reverse_geocode(geometry,boolean)
line 157 at assignment</DIV>
<DIV>********** Error **********</DIV>
<DIV><BR></DIV>
<DIV>ERROR: operator does not exist: character varying - character
varying</DIV>
<DIV>SQL state: 42883</DIV>
<DIV>Hint: No operator matches the given name and argument type(s). You
might need to add explicit type casts.</DIV>
<DIV>Context: PL/pgSQL function reverse_geocode(geometry,boolean) line 157
at assignment</DIV></DIV></DIV>
<DIV class=gmail_extra><BR>
<DIV class=gmail_quote>On Tue, Oct 7, 2014 at 7:24 AM, Paragon Corporation
<SPAN dir=ltr><<A href="mailto:lr@pcorp.us"
target=_blank>lr@pcorp.us</A>></SPAN> wrote:<BR>
<BLOCKQUOTE class=gmail_quote
style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0.8ex; BORDER-LEFT: #ccc 1px solid"><U></U>
<DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff face=Arial>I was able
to replicate the error by loading NC data.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff face=Arial>The problem
is with the NULL address range records (coming from those unnamed
streets) I think. I'll try to have a fix later
today.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff face=Arial>If you take
change</FONT></SPAN></DIV><SPAN>
<DIV> </DIV>
<DIV>to_number(fromhn, '999999') As fromhn, to_number(tohn, '999999') As
tohn</DIV>
<DIV> </DIV></SPAN>
<DIV><SPAN></SPAN><FONT color=#0000ff
face=Arial><SPAN>to</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial><SPAN></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial><SPAN>fromhn,
tohn</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial><SPAN></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial><SPAN>in the debug query
</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial><SPAN>you should see records come
back</SPAN></FONT></DIV><SPAN>
<DIV><FONT face=Arial><FONT color=#0000ff><FONT
size=+0><SPAN></SPAN></FONT></FONT></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial></FONT><BR></DIV>
<DIV lang=en-us dir=ltr align=left>
<HR>
<FONT face=Tahoma><B>From:</B> <A
href="mailto:postgis-users-bounces@lists.osgeo.org"
target=_blank>postgis-users-bounces@lists.osgeo.org</A> [mailto:<A
href="mailto:postgis-users-bounces@lists.osgeo.org"
target=_blank>postgis-users-bounces@lists.osgeo.org</A>] <B>On Behalf Of
</B>Barry McCall<BR><B>Sent:</B> Monday, October 06, 2014 6:03
PM<BR><B>To:</B> <A href="mailto:postgis-users@lists.osgeo.org"
target=_blank>postgis-users@lists.osgeo.org</A><BR><B>Subject:</B>
[postgis-users] reverse_geocode() specific error at specific geom
point<BR></FONT><BR></DIV>
<DIV></DIV></SPAN>
<DIV>
<DIV>
<DIV dir=ltr>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif">For some
reason when I reverse_geocode geometry point
0101000020AD10000095D4096822CC53C08A1F63EE5AAA4140</DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif">it returns
error </DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif">
<DIV>ERROR: invalid input syntax for type numeric: " "</DIV>
<DIV>********** Error **********</DIV>
<DIV><BR></DIV>
<DIV>ERROR: invalid input syntax for type numeric: " "</DIV>
<DIV>SQL state: 22P02</DIV></DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><BR></DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif">I performed
UPDATE tiger.geocode_settings SET setting = 'true' WHERE name =
'debug_reverse_geocode'; to try to get a grasp of what was going on;
however, there are so many CTEs in the debug I cant get a grasp of what
is really happening. </DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><BR></DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif">Does anyone
familiar with this system know why this is throwing this error? It seems
to only happen with lon,lat around -79.1866 35.3309. </DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><BR></DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><BR></DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif">SELECT
r.addy[1] FROM reverse_geocode(ST_GeomFromText('POINT(-79.1866
35.3309)',4269),true) AS r<BR></DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><BR></DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><BR></DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif">NOTICE:
Statement 1: </DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN> WITH ref AS
(</DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>
SELECT
'0101000020AD10000095D4096822CC53C08A1F63EE5AAA4140'::geometry As
ref_geom ) , </DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>f AS </DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>( SELECT faces.* FROM faces
CROSS JOIN ref</DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>WHERE faces.statefp = '37' AND
faces.countyfp = '105' </DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>AND ST_Intersects(faces.the_geom,
ref_geom)</DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN> ),</DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>e AS </DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>( SELECT edges.tlid ,
edges.statefp, edges.the_geom, CASE WHEN edges.tfidr = f.tfid THEN 'R'
WHEN edges.tfidl = f.tfid THEN 'L' ELSE NULL END::varchar As
eside,</DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif">
ST_ClosestPoint(edges.the_geom,ref_geom) As center_pt, ref_geom</DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>FROM edges INNER JOIN f ON
(f.statefp = edges.statefp AND (edges.tfidr = f.tfid OR edges.tfidl =
f.tfid)) </DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN> CROSS JOIN
ref</DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>WHERE edges.statefp = '37' AND
edges.countyfp = '105' </DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>AND ST_DWithin(edges.the_geom,
ref.ref_geom, 0.01) AND (edges.mtfcc LIKE 'S%') --only consider streets
and roads</DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN> )<SPAN
style="WHITE-SPACE: pre-wrap"> </SPAN>,</DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>ea AS </DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>(SELECT e.statefp, e.tlid,
a.fromhn, a.tohn, e.center_pt, ref_geom, a.zip, a.side, e.the_geom</DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>FROM e LEFT JOIN addr As a ON
(a.statefp = '37' AND e.tlid = a.tlid and e.eside =
a.side) </DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>)</DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>SELECT * </DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>FROM (SELECT DISTINCT ON(tlid,side)
foo.fullname, foo.streetname, foo.streettypeabbrev, foo.zip,
foo.center_pt,</DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN> side, to_number(fromhn,
'999999') As fromhn, to_number(tohn, '999999') As tohn,
ST_GeometryN(ST_Multi(line),1) As line, </DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN> dist</DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>FROM </DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN> (SELECT e.tlid,
e.the_geom As line, n.fullname, COALESCE(n.prequalabr || ' ','')
|| <A href="http://n.name/" target=_blank>n.name</A>
<DIV
style="MIN-HEIGHT: 16px; DISPLAY: inline-block; WIDTH: 16px"></DIV> AS
streetname, n.predirabrv, COALESCE(suftypabrv, pretypabrv) As
streettypeabbrev,</DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>
n.sufdirabrv, e.zip, e.side, e.fromhn, e.tohn , e.center_pt,</DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>
ST_Distance_Sphere(ST_SetSRID(e.center_pt,4326),ST_SetSRID(ref_geom,4326))
As dist</DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>FROM ea AS e </DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>LEFT JOIN (SELECT featnames.* FROM
featnames </DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN> WHERE
featnames.statefp = '37' ) AS n ON (n.statefp = e.statefp
AND n.tlid = e.tlid) </DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>ORDER BY dist LIMIT 50 ) As
foo </DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>ORDER BY foo.tlid, foo.side,
CASE 0 WHEN 0 THEN 0 WHEN 1 THEN CASE WHEN foo.fullname ~
'[0-9]+' THEN 0 ELSE 1 END ELSE CASE WHEN foo.fullname > '' AND NOT
(foo.fullname ~ '[0-9]+') THEN 0 ELSE 1 END END , foo.fullname ASC
NULLS LAST, dist LIMIT 50) As f ORDER BY f.dist, CASE WHEN fullname >
'' THEN 0 ELSE 1
END</DIV></DIV></DIV></DIV></DIV><BR>_______________________________________________<BR>postgis-users
mailing list<BR><A href="mailto:postgis-users@lists.osgeo.org"
target=_blank>postgis-users@lists.osgeo.org</A><BR><A
href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users"
target=_blank>http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</A><BR></BLOCKQUOTE></DIV><BR></DIV></DIV></DIV></DIV><BR>_______________________________________________<BR>postgis-users
mailing list<BR><A href="mailto:postgis-users@lists.osgeo.org"
target=_blank>postgis-users@lists.osgeo.org</A><BR><A
href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users"
target=_blank>http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</A><BR></BLOCKQUOTE></DIV><BR></DIV></DIV></DIV></BLOCKQUOTE></DIV><BR></DIV></DIV></DIV></BLOCKQUOTE></DIV><BR></DIV></BODY></HTML>