[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