[postgis-users] geocode_intersection (was How to update your SVNcopy of PostGIS)

Paragon Corporation lr at pcorp.us
Sun Dec 11 18:27:45 PST 2011


fixed.
 
You get 4 because it interpolates 4 addresses at the 2 on one side and 2 on
the other where harwood cuts.  Since we aren't offsetting the point to the
correct side of the street, it gives the same point for each.
 
If you only care about the point, what you can do is just ask for the first
result.  
 
SELECT addy, ST_X(ST_SetSRID(geomout,4326)), ST_Y(ST_SetSRID(geomout,4326)),
rating
FROM geocode_intersection('ross', 'harwood', 'TX', 'dallas', '', 1)


  _____  

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Aren
Cambre
Sent: Sunday, December 11, 2011 9:17 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] geocode_intersection (was How to update your
SVNcopy of PostGIS)


That's it! Filed bug report at http://trac.osgeo.org/postgis/ticket/1366. 

Now I don't know if this is a bug per se, but if I run this:
SELECT addy, ST_X(ST_SetSRID(geomout,4326)), ST_Y(ST_SetSRID(geomout,4326)),
rating
FROM geocode_intersection('ross', 'harwood', 'TX', 'dallas')

I get this:
"(2001,,Ross,Ave,,,Dallas,TX,75201,t)";-96.799239;32.786929;3
"(2000,,Ross,Ave,,,Dallas,TX,75201,t)";-96.799239;32.786929;3
"(1999,,Ross,Ave,,,Dallas,TX,75201,t)";-96.799239;32.786929;3
"(1998,,Ross,Ave,,,Dallas,TX,75201,t)";-96.799239;32.786929;3

It seems weird that I am getting four almost identical locations, each with
the same rating. The lat/long for this is dead on.

Aren

On Sun, Dec 11, 2011 at 8:06 PM, Paragon Corporation <lr at pcorp.us> wrote:



Aren,
 
Try upper case TX.  I think we may have forgotten the logic to upper case
the state.  We'll check that out.


  _____  

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Aren
Cambre
Sent: Sunday, December 11, 2011 7:32 PM
To: PostGIS Users Discussion
Subject: [postgis-users] geocode_intersection (was How to update your
SVNcopy of PostGIS)


I think that was it! Ran the upgrade_geocode.sql script against my DB, and
now I have the geocode_intersection function. 

However, I'm getting no results with geocode_intersection. For example:
SELECT *
FROM geocode_intersection('ross', 'harwood', 'tx', 'dallas')

I get a blank result set.

However, if I geocode a known address at that intersection, I get a valid
location:
SELECT addy, ST_X(ST_SetSRID(geomout,4326)), ST_Y(ST_SetSRID(geomout,4326)),
rating
FROM geocode('1928 ross ave, dallas, tx');

Still investigating...

Aren


On Sat, Dec 10, 2011 at 6:17 PM, Aren Cambre <aren at arencambre.com> wrote:


Ah, I see the problem now. I was thinking I'd have to reinstall all of
PostGIS 2.0 to get the geocode_intersection function, but looking at
http://trac.osgeo.org/postgis/changeset/8276, I realize that it's the
tiger_geocoder extras package as you mention below.

So I looked at the upgrade_geocode.sql script
(http://trac.osgeo.org/postgis/browser/trunk/extras/tiger_geocoder/tiger_201
0/upgrade_geocode.sql?rev=8276), and I guess this may help make sure I have
the latest geocoding functions in my DB.

I'll try this and see what happens.


Aren 


On Sat, Dec 10, 2011 at 5:58 PM, Stephen Woodbridge
<woodbri at swoodbridge.com> wrote:


Ok, just checking.
http://svn.osgeo.org/postgis/
<http://svn.osgeo.org/postgis/trunk/extras/tiger_geocoder/tiger_2010/>
trunk/extras/tiger_geocoder/tiger_2010/

Looks like something like this might do it:

cd /usr/share/postgresql/contrib
# edit create_geocode.sh
# then run it
./create_geocode.sh

or just load them from here:

http://svn.osgeo.org/postgis/
<http://svn.osgeo.org/postgis/trunk/extras/tiger_geocoder/tiger_2010/geocode
/> trunk/extras/tiger_geocoder/tiger_2010/geocode/

browse about and install what you need.

-Steve W 


On 12/10/2011 6:25 PM, Aren Cambre wrote:


I refreshed the functions list and the parent DB and even opened/closed
pgAdmin III. :-)

I also ran this script:

*SELECT * FROM pg_proc proc JOIN pg_language lang*
*ON proc.prolang = lang.oid*
*ORDER BY proname;*

Double-checked myself with:
*SELECT * FROM pg_proc proc JOIN pg_language lang*
*ON proc.prolang = lang.oid*
*WHERE proname LIKE '%geocode%'*
*ORDER BY proname;*

Definitely no *geocode.** functions there. 


Aren

On Sat, Dec 10, 2011 at 5:18 PM, Stephen Woodbridge

<woodbri at swoodbridge.com <mailto:woodbri at swoodbridge.
<mailto:woodbri at swoodbridge.com> com>> wrote:

   Try right click on database and/or function list and refresh it.


   On 12/10/2011 6:15 PM, Aren Cambre wrote:

       Thanks.

       Now here's what I don't get: where are the *geocode* and
       *geocode_intersection* functions? If I look at the functions list in
       *spatial_db_template*'s *public* schema (using pgAdmin III),
       should I

       see those two? I thought they were included in the base PostGIS
       2.0 product?

       Aren

       On Sat, Dec 10, 2011 at 5:09 PM, Stephen Woodbridge
       <woodbri at swoodbridge.com <mailto:woodbri at swoodbridge.
<mailto:woodbri at swoodbridge.com> com>

       <mailto:woodbri at swoodbridge. com 

       <mailto:woodbri at swoodbridge. <mailto:woodbri at swoodbridge.com> com>>>
wrote:

           You can ignore all the notices. As long as you do not get a
           rollback, then things should be ok.

           The script is just trying to clean out potential obsolete
       functions
           that might be there if the database was created many
       versions ago
           and upgraded to this point.

           -Steve W


           On 12/10/2011 5:46 PM, Aren Cambre wrote:

               Thanks. Yup, I see it now. My mind was stuck in 3rd
       generation
               languages
               where things stop when you get an exception.

               The script's first gripe was that the *spatial_ref_sys*
       table
               already

               exists. Shouldn't it automatically kill and reload that
       table?
               Is that a
               bug?

               Anyway, I deleted it and reran the script. Now I get a
       bunch of
               notices
               about functions that don't exist. See attachment.

               It appears it's mostly not finding overloads of
       functions. E.g.,
               it says
               *populate_geometry_columns()* and
       *populate_geometry_columns(
               oid)* are

               missing. I do have variants with different function
       signatures:
               *populate_geometry_columns( oid,boolean)* and
               *populate_geometry_columns( boolean)*.


               BTW, I'm trying to run this on my template PostGIS DB,
       which I named
               *spatial_db_template*.


               Aren

               On Sat, Dec 10, 2011 at 10:26 AM, Bborie Park
       <bkpark at ucdavis.edu <mailto:bkpark at ucdavis.edu>
       <mailto:bkpark at ucdavis.edu <mailto:bkpark at ucdavis.edu>>
       <mailto:bkpark at ucdavis.edu <mailto:bkpark at ucdavis.edu>
       <mailto:bkpark at ucdavis.edu <mailto:bkpark at ucdavis.edu>>>> wrote:

                   Oops!  My head is always in the raster world, so I
       just ran
               with the
                   raster upgrade.  Sorry about that.

                   If you're getting an error, you'll want to scroll up
       past the
                   transaction error message as the very first error
       message
               should be
                   related to the actual statement that is failing.

                   -bborie

                   On Sat, Dec 10, 2011 at 6:47 AM, Aren Cambre
       <aren at arencambre.com <mailto:aren at arencambre.com>
       <mailto:aren at arencambre.com <mailto:aren at arencambre.com>>
       <mailto:aren at arencambre.com <mailto:aren at arencambre.com>
       <mailto:aren at arencambre.com <mailto:aren at arencambre.com>>>> wrote:
        > Thanks. Yesterday I
        >
                   found postgis_upgrade_20_minor.sql in
               /usr/share/postgresql/9.1/ contrib/postgis-2.0.
        >
        > When I ran that, I got tons of errors like this:
        >
                   psql:/usr/share/postgresql/9.
       1/contrib/postgis-2.0/postgis_
               upgrade_20_minor.sql:3235:
        > ERROR:  current transaction is aborted, commands ignored
               until end of
        > transaction block
        >
        > I see you mentioned rtpostgis_upgrade_20_minor. sql. Isn't that

                   just for
        > raster functions? I searched my machine and cannot find it,
               anyway.
        >
        > Aren
        >
        > On Fri, Dec 9, 2011 at 10:02 PM, Bborie Park
       <bkpark at ucdavis.edu <mailto:bkpark at ucdavis.edu>
       <mailto:bkpark at ucdavis.edu <mailto:bkpark at ucdavis.edu>>
       <mailto:bkpark at ucdavis.edu <mailto:bkpark at ucdavis.edu>
       <mailto:bkpark at ucdavis.edu <mailto:bkpark at ucdavis.edu>>>> wrote:
        >>
        >> Aren,
        >>
        >> Since you're tracking trunk, after you've done the configure,
                   make and
        >> make install, you'll want to run the upgrade script
        >>
        >> rtpostgis_upgrade_20_minor.sql
        >>
        >> Usually, the file is installed in the same place as
                   rtpostgis.sql.  If
        >> you can't find the upgrade file, you can find it in your
               checkout's
        >> directory at:
        >>
        >> raster/rt_pg
        >>
        >> -bborie
        >>
        >> On Fri, Dec 9, 2011 at 7:53 PM, Aren Cambre
       <aren at arencambre.com <mailto:aren at arencambre.com>
       <mailto:aren at arencambre.com <mailto:aren at arencambre.com>>
       <mailto:aren at arencambre.com <mailto:aren at arencambre.com>
       <mailto:aren at arencambre.com <mailto:aren at arencambre.com>>>> wrote:
        >> > Thanks. I got the latest SVN snapshot. I was able to
                   configure, make,
        >> > and
        >> > make install. The problem is there seems to be missing steps
                   between
        >> > building the whole thing and getting changes into my
               preexisting
        >> > PostGIS-enabled database.
        >> >
        >> > Aren
        >> >
        >> >
        >> > On Fri, Dec 9, 2011 at 9:47 PM, Bborie Park
       <bkpark at ucdavis.edu <mailto:bkpark at ucdavis.edu>
       <mailto:bkpark at ucdavis.edu <mailto:bkpark at ucdavis.edu>>
       <mailto:bkpark at ucdavis.edu <mailto:bkpark at ucdavis.edu>
       <mailto:bkpark at ucdavis.edu <mailto:bkpark at ucdavis.edu>>>> wrote:
        >> >>
        >> >> If you used something like...
        >> >>
        >> >> svn check out https://svn.osgeo.org/postgis/ trunk
       <https://svn.osgeo.org/ postgis/trunk
       <https://svn.osgeo.org/ <https://svn.osgeo.org/postgis/trunk>
postgis/trunk>>
        >> >>
        >> >> You can safely run...
        >> >>
        >> >> svn update
        >> >>
        >> >> If that works, I'd recommend doing...
        >> >>
        >> >> svn cleanup
        >> >>
        >> >> -bborie
        >> >>
        >> >> On Fri, Dec 9, 2011 at 7:19 PM, Aren Cambre
       <aren at arencambre.com <mailto:aren at arencambre.com>
       <mailto:aren at arencambre.com <mailto:aren at arencambre.com>>
       <mailto:aren at arencambre.com <mailto:aren at arencambre.com>
       <mailto:aren at arencambre.com <mailto:aren at arencambre.com>>>>


        >> >> wrote:
        >> >> > I admit this is a dumb question, but here it is.
        >> >> >
        >> >> > I installed PostGIS on my Ubuntu 11.10 environment a few
                   weeks ago
        >> >> > using
        >> >> > the
        >> >> > excellent instructions
        >> >> > at
       http://www.letseehere.com/ postgis-geocoder-using-tiger-
               2010-data
       <http://www.letseehere.com/ postgis-geocoder-using-tiger-
       2010-data
       <http://www.letseehere.com/
<http://www.letseehere.com/postgis-geocoder-using-tiger-2010-data>
postgis-geocoder-using-tiger-2010-data>>.

        >> >> >
        >> >> > Since then, the geocode_intersection function was added,
                   and I need
        >> >> > it.
        >> >> > I'm
        >> >> > not clear what is the process of replacing the older
                   snapshot of
        >> >> > PostGIS
        >> >> > with this newer one.
        >> >> >
        >> >> > I don't see an obvious install/uninstall routine. I tried
                   the soft
        >> >> > upgrade
        >> >> >
        >> >> >
        >> >> > at
       http://postgis.refractions. net/documentation/manual-svn/
               postgis_installation.html# upgrading
       <http://postgis.refractions. net/documentation/manual-svn/
       postgis_installation.html# upgrading
       <http://postgis.refractions.
<http://postgis.refractions.net/documentation/manual-svn/postgis_installatio
n.html#upgrading>
net/documentation/manual-svn/postgis_installation.html#upgrading>>,

        >> >> > but that didn't work. Do I need to do the hard upgrade each
                   time I do
        >> >> > a
        >> >> > SVN
        >> >> > update?
        >> >> >
        >> >> > Aren
        >> >> >
        >> >> > ______________________________ _________________
        >> >> > postgis-users mailing list
        >> >> > postgis-users at postgis. refractions.net

       <http://refractions.net> 

       <mailto:postgis-users at postgis. refractions.net
       <mailto:postgis-users at postgis.
<mailto:postgis-users at postgis.refractions.net> refractions.net>>

       <mailto:postgis-users at postgis <mailto:postgis-users at postgis>.
       refractions.net <http://refractions.net> 

       <mailto:postgis-users at postgis. refractions.net
       <mailto:postgis-users at postgis.
<mailto:postgis-users at postgis.refractions.net> refractions.net>>>

        >> >> > http://postgis.refractions.
               net/mailman/listinfo/postgis- users

       <http://postgis.refractions. net/mailman/listinfo/postgis- users
       <http://postgis.refractions.
<http://postgis.refractions.net/mailman/listinfo/postgis-users>
net/mailman/listinfo/postgis-users>>
        >> >> >
        >> >>
        >> >>
        >> >>
        >> >> --
        >> >> Bborie Park
        >> >> Programmer
        >> >> Center for Vectorborne Diseases
        >> >> UC Davis
        >> >> 530-752-8380 <tel:530-752-8380> <tel:530-752-8380

       <tel:530-752-8380>> <tel:530-752-8380 <tel:530-752-8380>

       <tel:530-752-8380 <tel:530-752-8380>>> 

        >> >> bkpark at ucdavis.edu <mailto:bkpark at ucdavis.edu>
       <mailto:bkpark at ucdavis.edu <mailto:bkpark at ucdavis.edu>>

       <mailto:bkpark at ucdavis.edu <mailto:bkpark at ucdavis.edu> 

       <mailto:bkpark at ucdavis.edu <mailto:bkpark at ucdavis.edu>>>

        >> >> ______________________________ _________________
        >> >> postgis-users mailing list
        >> >> postgis-users at postgis. refractions.net

       <http://refractions.net> 

       <mailto:postgis-users at postgis. refractions.net
       <mailto:postgis-users at postgis.
<mailto:postgis-users at postgis.refractions.net> refractions.net>>

       <mailto:postgis-users at postgis <mailto:postgis-users at postgis>.
       refractions.net <http://refractions.net> 

       <mailto:postgis-users at postgis. refractions.net
       <mailto:postgis-users at postgis.
<mailto:postgis-users at postgis.refractions.net> refractions.net>>>

        >> >> http://postgis.refractions. net/mailman/listinfo/postgis-
               users

       <http://postgis.refractions. net/mailman/listinfo/postgis- users
       <http://postgis.refractions.
<http://postgis.refractions.net/mailman/listinfo/postgis-users>
net/mailman/listinfo/postgis-users>>
        >> >
        >> >
        >> >
        >> > ______________________________ _________________
        >> > postgis-users mailing list
        >> > postgis-users at postgis. refractions.net

       <http://refractions.net> 

       <mailto:postgis-users at postgis. refractions.net
       <mailto:postgis-users at postgis.
<mailto:postgis-users at postgis.refractions.net> refractions.net>>

       <mailto:postgis-users at postgis <mailto:postgis-users at postgis>.
       refractions.net <http://refractions.net> 

       <mailto:postgis-users at postgis. refractions.net
       <mailto:postgis-users at postgis.
<mailto:postgis-users at postgis.refractions.net> refractions.net>>>

        >> > http://postgis.refractions. net/mailman/listinfo/postgis-
               users

       <http://postgis.refractions. net/mailman/listinfo/postgis- users
       <http://postgis.refractions.
<http://postgis.refractions.net/mailman/listinfo/postgis-users>
net/mailman/listinfo/postgis-users>>
        >> >
        >>
        >>
        >>
        >> --
        >> Bborie Park
        >> Programmer
        >> Center for Vectorborne Diseases
        >> UC Davis
        >> 530-752-8380 <tel:530-752-8380> <tel:530-752-8380

       <tel:530-752-8380>> <tel:530-752-8380 <tel:530-752-8380>

       <tel:530-752-8380 <tel:530-752-8380>>> 

        >> bkpark at ucdavis.edu <mailto:bkpark at ucdavis.edu>
       <mailto:bkpark at ucdavis.edu <mailto:bkpark at ucdavis.edu>>

       <mailto:bkpark at ucdavis.edu <mailto:bkpark at ucdavis.edu> 

       <mailto:bkpark at ucdavis.edu <mailto:bkpark at ucdavis.edu>>>

        >> ______________________________ _________________
        >> postgis-users mailing list

        >> postgis-users at postgis. refractions.net <http://refractions.net> 

       <mailto:postgis-users at postgis. refractions.net
       <mailto:postgis-users at postgis.
<mailto:postgis-users at postgis.refractions.net> refractions.net>>

       <mailto:postgis-users at postgis <mailto:postgis-users at postgis>.
       refractions.net <http://refractions.net> 

       <mailto:postgis-users at postgis. refractions.net
       <mailto:postgis-users at postgis.
<mailto:postgis-users at postgis.refractions.net> refractions.net>>>

        >> http://postgis.refractions. net/mailman/listinfo/postgis-
               users

       <http://postgis.refractions. net/mailman/listinfo/postgis- users
       <http://postgis.refractions.
<http://postgis.refractions.net/mailman/listinfo/postgis-users>
net/mailman/listinfo/postgis-users>>
        >
        >
        >
        > ______________________________ _________________
        > postgis-users mailing list

        > postgis-users at postgis. refractions.net <http://refractions.net> 

       <mailto:postgis-users at postgis. refractions.net
       <mailto:postgis-users at postgis.
<mailto:postgis-users at postgis.refractions.net> refractions.net>>

       <mailto:postgis-users at postgis <mailto:postgis-users at postgis>.
       refractions.net <http://refractions.net> 

       <mailto:postgis-users at postgis. refractions.net
       <mailto:postgis-users at postgis.
<mailto:postgis-users at postgis.refractions.net> refractions.net>>>

        > http://postgis.refractions. net/mailman/listinfo/postgis-
               users

       <http://postgis.refractions. net/mailman/listinfo/postgis- users
       <http://postgis.refractions.
<http://postgis.refractions.net/mailman/listinfo/postgis-users>
net/mailman/listinfo/postgis-users>>
        >



                   --
                   Bborie Park
                   Programmer
                   Center for Vectorborne Diseases
                   UC Davis
       530-752-8380 <tel:530-752-8380> <tel:530-752-8380

       <tel:530-752-8380>> <tel:530-752-8380 <tel:530-752-8380>

       <tel:530-752-8380 <tel:530-752-8380>>> 

       bkpark at ucdavis.edu <mailto:bkpark at ucdavis.edu>
       <mailto:bkpark at ucdavis.edu <mailto:bkpark at ucdavis.edu>>

       <mailto:bkpark at ucdavis.edu <mailto:bkpark at ucdavis.edu> 

       <mailto:bkpark at ucdavis.edu <mailto:bkpark at ucdavis.edu>>>

                   ______________________________ _________________
                   postgis-users mailing list
               postgis-users at postgis. refractions.net

       <http://refractions.net> 

       <mailto:postgis-users at postgis. refractions.net
       <mailto:postgis-users at postgis.
<mailto:postgis-users at postgis.refractions.net> refractions.net>>

       <mailto:postgis-users at postgis <mailto:postgis-users at postgis>.
       refractions.net <http://refractions.net> 

       <mailto:postgis-users at postgis. refractions.net
       <mailto:postgis-users at postgis.
<mailto:postgis-users at postgis.refractions.net> refractions.net>>>

       http://postgis.refractions. net/mailman/listinfo/postgis- users

       <http://postgis.refractions. net/mailman/listinfo/postgis- users
       <http://postgis.refractions.
<http://postgis.refractions.net/mailman/listinfo/postgis-users>
net/mailman/listinfo/postgis-users>>




               ______________________________ _________________
               postgis-users mailing list
               postgis-users at postgis. refractions.net

       <http://refractions.net> 

       <mailto:postgis-users at postgis. refractions.net
       <mailto:postgis-users at postgis.
<mailto:postgis-users at postgis.refractions.net> refractions.net>>
       http://postgis.refractions. net/mailman/listinfo/postgis- users

       <http://postgis.refractions. net/mailman/listinfo/postgis- users
       <http://postgis.refractions.
<http://postgis.refractions.net/mailman/listinfo/postgis-users>
net/mailman/listinfo/postgis-users>>


           ______________________________ _________________
           postgis-users mailing list

           postgis-users at postgis. refractions.net <http://refractions.net> 

       <mailto:postgis-users at postgis. refractions.net
       <mailto:postgis-users at postgis.
<mailto:postgis-users at postgis.refractions.net> refractions.net>>
       http://postgis.refractions. net/mailman/listinfo/postgis- users


       <http://postgis.refractions. net/mailman/listinfo/postgis- users
       <http://postgis.refractions.
<http://postgis.refractions.net/mailman/listinfo/postgis-users>
net/mailman/listinfo/postgis-users>>




       ______________________________ _________________
       postgis-users mailing list
       postgis-users at postgis. refractions.net
       <mailto:postgis-users at postgis.
<mailto:postgis-users at postgis.refractions.net> refractions.net>
       http://postgis.refractions. net/mailman/listinfo/postgis- users
       <http://postgis.refractions.
<http://postgis.refractions.net/mailman/listinfo/postgis-users>
net/mailman/listinfo/postgis-users>


   ______________________________ _________________
   postgis-users mailing list
   postgis-users at postgis. refractions.net
   <mailto:postgis-users at postgis.
<mailto:postgis-users at postgis.refractions.net> refractions.net>
   http://postgis.refractions. net/mailman/listinfo/postgis- users
   <http://postgis.refractions.
<http://postgis.refractions.net/mailman/listinfo/postgis-users>
net/mailman/listinfo/postgis-users>




_______________________________________________
postgis-users mailing list
postgis-users at postgis. <mailto:postgis-users at postgis.refractions.net>
refractions.net
http://postgis.refractions.
<http://postgis.refractions.net/mailman/listinfo/postgis-users>
net/mailman/listinfo/postgis-users



_______________________________________________
postgis-users mailing list
postgis-users at postgis. <mailto:postgis-users at postgis.refractions.net>
refractions.net
http://postgis.refractions.
<http://postgis.refractions.net/mailman/listinfo/postgis-users>
net/mailman/listinfo/postgis-users





_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users




-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20111211/fd5469bc/attachment.html>


More information about the postgis-users mailing list