<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7638.1">
<TITLE>Re: [postgis-users] latitude / longitude from wkb_geometry</TITLE>
</HEAD>
<BODY>
<DIV id=idOWAReplyText65394 dir=ltr>
<DIV dir=ltr><FONT face=Arial color=#000000 size=2>Unfortunately I don't think
there is any documentation to my knowledge aside from the help comments in the
.sql files. </FONT></DIV>
<DIV dir=ltr><FONT face=Arial color=#000000 size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial color=#000000 size=2>When I used it I was using it
mostly against Parcel data so I used the normalize_address and other
cleansing functions mostly. For what you want to do I think you would
be primarily interested in the </FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr>interpolate_from_address functions.</DIV>
<DIV dir=ltr> </DIV>
<DIV dir=ltr>But since you are using against the tiger data, you may be able to
use all of it as is.</DIV>
<DIV dir=ltr> </DIV>
<DIV dir=ltr>To use it, you'll need to download the gazeeteer tables that have
place names, zips etc which I think you can get from here</DIV>
<DIV dir=ltr> </DIV>
<DIV dir=ltr><A
href="http://www.census.gov/geo/www/gazetteer/gazette.html">http://www.census.gov/geo/www/gazetteer/gazette.html</A></DIV>
<DIV dir=ltr> </DIV>
<DIV dir=ltr>Thanks,</DIV>
<DIV dir=ltr>Regina</DIV>
<DIV dir=ltr> </DIV>
<DIV dir=ltr> </DIV>
<DIV dir=ltr> </DIV>
<DIV dir=ltr> </DIV></DIV>
<DIV dir=ltr><BR>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B> mark
[mailto:rkmr.em@gmail.com]<BR><B>Sent:</B> Mon 2/12/2007 2:49 PM<BR><B>To:</B>
PostGIS Users Discussion<BR><B>Cc:</B> Obe, Regina<BR><B>Subject:</B> Re:
[postgis-users] latitude / longitude from wkb_geometry<BR></FONT><BR></DIV>
<DIV>
<P><FONT size=2>Hi Regina,<BR>That is exactly what I want to do!<BR>Do you have
examples of using the tiger geocoder / documentation that<BR>you can point me to
?<BR>Thanks a lot!<BR>mark<BR><BR>On 2/12/07, Obe, Regina
<robe.dnd@cityofboston.gov> wrote:<BR>> Hi Mark,<BR>><BR>> It
sounds like you are trying to create a geocoder using tiger street data.
So given an address -- are you trying to get the point on the line where the
address sits?<BR>><BR>> If so, you may want to take a look at this
and see if you can use some of it<BR>><BR>> <A
href="http://postgis.refractions.net/download/tiger_geocoder.tar.gz">http://postgis.refractions.net/download/tiger_geocoder.tar.gz</A><BR>><BR>>
Hope that helps,<BR>> Regina<BR>><BR>>
________________________________<BR>><BR>> From:
postgis-users-bounces@postgis.refractions.net on behalf of Pedro Doria
Meunier<BR>> Sent: Mon 2/12/2007 2:32 PM<BR>> To: 'PostGIS Users
Discussion'<BR>> Subject: RE: [postgis-users] latitude / longitude from
wkb_geometry<BR>><BR>><BR>><BR>> Hi Mark,<BR>><BR>> You're
welcome. This is the bread-and-butter of this list... ;-)<BR>><BR>> I must
admit that I'm not familiar with TIGER data since I've never used<BR>>
it...<BR>> And living in Madeira Island, Portugal doesn't add any immediate
need for it<BR>> too... :D<BR>> Anyway...<BR>> If I understood your
question correctly here's the<BR>> Sql query:<BR>><BR>> SELECT
pointn(wkb_geometry,1) from masuf where<BR>>
field_containing_street_name='the adress';<BR>><BR>> There's, of course,
other things to take into consideration...<BR>> Like there's almost certainly
various rows containing the same address value<BR>> for different
towns...<BR>> So you should append a 'AND field_containing_town_name='town
name' to the<BR>> above statement. Also finishing it with a 'LIMIT 1' is
advisable...<BR>><BR>> So the statement goes:<BR>> SELECT
pointn(wkb_geometry,1) from masuf where<BR>>
field_containing_street_name='the adress' AND<BR>>
field_containing_town_name='town name' LIMIT 1;<BR>><BR>> This, again,
returns the first point for that linestring and thus a POINT<BR>>
object.<BR>><BR>> ---<BR>> Pedro Doria Meunier<BR>><BR>>
-----Original Message-----<BR>> From:
postgis-users-bounces@postgis.refractions.net<BR>> [<A
href="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</A>]
On Behalf Of mark<BR>> Sent: segunda-feira, 12 de Fevereiro de 2007
19:14<BR>> To: PostGIS Users Discussion<BR>> Subject: Re: [postgis-users]
latitude / longitude from wkb_geometry<BR>><BR>> Hi Pedro,<BR>> Thanks
a ton!!!<BR>> Will try all of this out!!!!<BR>><BR>> My location data
is a US street address. Can you tell me what is the<BR>> best way to get to a
POINT from the US Street address? I am<BR>> considering searching the masuf
table that I have which I have<BR>> populated with TIGER database . If I map
the US street address to a<BR>> row I get the line geometry from which I can
get approximage point<BR>> data.<BR>><BR>> Thanks a lot<BR>>
mark<BR>><BR>><BR>><BR>> On 2/12/07, Pedro Doria Meunier
<pdoria@netmadeira.com> wrote:<BR>> > Hi Mark<BR>> ><BR>>
> The distance function takes two parameters: distance(geometry,
geometry)<BR>> ><BR>> > It'll give you values based on the
geometry's SRID.<BR>> > (And you shouldn't mix two different SRIDs when
calling the function -- I<BR>> > expect that an exception would be
raised...)<BR>> ><BR>> > For SRID==4269 values are returned in
ddd.dddd (degrees) (GEOGCS).<BR>> ><BR>> > Should you want to work
in meters you must transform it to a projected<BR>> > coordinate
system.<BR>> ><BR>> > You can use the longitude to get the desired
zone. Here's the formula:<BR>> > utmzone = ((lon + 180) / 6) + 1<BR>>
><BR>> > Having obtained the desired utm zone the following sql
statement returns<BR>> the<BR>> > srid:<BR>> ><BR>> >
SELECT srid FROM spatial_ref_sys WHERE srtext LIKE 'PROJCS[\"WGS 84 /
UTM<BR>> > zone $utm%' LIMIT 1;<BR>> > Note: $utm is a variable, in
this case PHP is being used... substitute<BR>> with<BR>> > the value
found above<BR>> > Note1: the '%' is a metacharacter. In this case it
'validates' anything<BR>> > following the utm...<BR>> ><BR>> >
In your case the returned SRID would be 32651 (using -121.913666 as the<BR>>
lon)<BR>> ><BR>> > All this ends in this sql statement:<BR>> >
SELECT distance(transform(wkb_geometry,32651),<BR>> >
transform(geometryfromtext('POINT(-121.913666 37.292952)', 4269),32651)<BR>>
from<BR>> > masuf;<BR>> ><BR>> > You can then pick the
returned value and convert it to miles, whatever...<BR>> ><BR>> >
HTH,<BR>> > Pedro Doria Meunier.<BR>> ><BR>> > -----Original
Message-----<BR>> > From:
postgis-users-bounces@postgis.refractions.net<BR>> > [<A
href="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</A>]
On Behalf Of mark<BR>> > Sent: segunda-feira, 12 de Fevereiro de 2007
17:09<BR>> > To: PostGIS Users Discussion<BR>> > Subject: Re:
[postgis-users] latitude / longitude from wkb_geometry<BR>> ><BR>> >
Hi Pedro,<BR>> > Oops! I missed the previous email! Thanks a
lot!!!!<BR>> ><BR>> > Now if I want to use this point in distance
calculation should I need<BR>> > to convert it to geometry right? What
SRID should I use? The SRID for<BR>> > line string is 4269 and that is the
only row I have in<BR>> > geometry_columns table.<BR>> ><BR>>
> select geometryfromtext('POINT(-121.913666 37.292952)', SRID???)<BR>>
> Can I use the same SRID?<BR>> > PostGIS rocsk!!!<BR>> > Thanks
a lot!<BR>> > mark<BR>> ><BR>> > On 2/12/07, Pedro Doria
Meunier <pdoria@netmadeira.com> wrote:<BR>> > > Hi Mark<BR>>
> ><BR>> > > Please see the previous email (to this one).<BR>>
> > pointn() starts at 1, not 0... ;-)<BR>> > ><BR>> > >
So the sql statement should be:<BR>> > > SELECT
x(pointn(wkb_geometry,1)), y(pointn(wkb_geometry,1)) from masuf<BR>> >
> where ogc_fid=62560;<BR>> > ><BR>> > > This returns the
first point of the linestring object.<BR>> > ><BR>> > >
Cheers,<BR>> > > Pedro.<BR>> > ><BR>> > >
-----Original Message-----<BR>> > > From:
postgis-users-bounces@postgis.refractions.net<BR>> > > [<A
href="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</A>]
On Behalf Of mark<BR>> > > Sent: segunda-feira, 12 de Fevereiro de 2007
16:42<BR>> > > To: PostGIS Users Discussion<BR>> > > Subject:
Re: [postgis-users] latitude / longitude from wkb_geometry<BR>> >
><BR>> > > Hi Pedro<BR>> > > Thanks for your
replies.<BR>> > > I tried your SQL query on this row:<BR>> >
><BR>> > > SELECT x(pointn(wkb_geometry,0)),
y(pointn(wkb_geometry,0)) from masuf<BR>> > > where
ogc_fid=62560;<BR>> > ><BR>> ><BR>>
"62560";"0102000020AD10000002000000E65DF580797A5EC01EA67D737FA54240978C63247<BR>>
> ><BR>> ><BR>>
B7A5EC07DAEB6627FA54240";"TGR06085";"123181609";"";"O";"";"";"";"";"F10";"";<BR>>
> ><BR>> ><BR>>
"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"6";"6";"85";"85";"92830";"9283<BR>>
> > 0";"";"";"68000";"68000";"502601";"502601";"1019";"1019"<BR>> >
><BR>> > > I just get Null values for x and y.<BR>> >
><BR>> > > Can you tell what is wrong?<BR>> > > thanks a
lot!<BR>> > > mark<BR>> > ><BR>> > ><BR>> >
> On 2/12/07, Pedro Doria Meunier <pdoria@netmadeira.com>
wrote:<BR>> > > > Hi Mark<BR>> > > ><BR>> > >
> Addendum to the previous post...<BR>> > > > I didn't notice
that the geometry was LINESTRING :]<BR>> > > > So what we need here
is to concatenate some functions:<BR>> > > ><BR>> > > >
SELECT x(pointn(wkb_geometry,0)), y(pointn(wkb_geometry,0)) from<BR>>
masuf;<BR>> > > ><BR>> > > > This will give you the
first coordinate of each line.<BR>> > > ><BR>> > > >
Should you want to process all coordinates of each linestring here's a<BR>>
> > > little PHP that might help:<BR>> > > ><BR>> >
> > // ------ cut here<BR>> > > > <?php<BR>> > >
> $connection = pg_connect("host=yourhost port=5432 dbname=yourdb<BR>>
> > > user=yourusername password=yourpassword");<BR>> > > >
if (!$connection) {<BR>> > >
> print("Connection to the
database failed.");<BR>> > >
> exit;<BR>> > >
> }<BR>> > > > /*<BR>> > > > Get all the records from
the table.<BR>> > > > We get the unique ogc_fid and the
corresponding number of points for<BR>> the<BR>> > > > linestring
of this entry...<BR>> > > > */<BR>> > > > $sql="SELECT
ogc_fid, numpoints(wkb_geometry) from masuf";<BR>> > > >
$myresult=pg_exec($connection, $sql);<BR>> > > ><BR>> > >
> for ($row=0; $row<pg_numrows($myresult); $row++) {<BR>> > >
>
$unique=pg_result($myresult,$row,0);<BR>> > >
>
$npoints==pg_result($myresult,$row,0);<BR>> > >
> // now we process each
point in this entry<BR>> > >
> for ($point=0;
$point<$npoints; $point++) {<BR>> > >
> $sql= "SELECT
x(pointn(wkb_geometry,$point)),<BR>> > > >
y(pointn(wkb_geometry,$point)) FROM masuf WHERE ogc_fid='$unique'";<BR>> >
> >
$presult=pg_exec($connection, $sql);<BR>> > >
>
$lon=pg_result($presult,0,0);<BR>> > >
>
$lat=pg_result($presult,0,1);<BR>> > >
> /*<BR>> > >
> Do whatever you wish with
$lon, $lat....<BR>> > >
> */<BR>> > >
> }<BR>> > > >
}<BR>> > > > ?><BR>> > > > // ------ cut here<BR>>
> > ><BR>> > > ><BR>> > > > If the gurus out
there have a more efficient way to do this, I'd be<BR>> more<BR>> >
> > than interested in hearing about it! ;-)<BR>> > >
><BR>> > > > HTH,<BR>> > > > Pedro Doria
Meunier.<BR>> > > ><BR>> > > ><BR>> > > >
-----Original Message-----<BR>> > > > From:
postgis-users-bounces@postgis.refractions.net<BR>> > > > [<A
href="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</A>]
On Behalf Of<BR>> mark<BR>> > > > Sent: segunda-feira, 12 de
Fevereiro de 2007 5:57<BR>> > > > To:
postgis-users@postgis.refractions.net<BR>> > > > Subject:
[postgis-users] latitude / longitude from wkb_geometry<BR>> > >
><BR>> > > > how to get latitude and longitude from
wkb_geometry?<BR>> > > > My table structure is given below<BR>>
> > > thanks<BR>> > > > mark<BR>> > > ><BR>>
> > > CREATE TABLE masuf<BR>> > > > (<BR>> > >
> ogc_fid serial NOT NULL,<BR>> > > > wkb_geometry
geometry,<BR>> > > > module char(8),<BR>> > > > tlid
numeric(10),<BR>> > > > side1 numeric(1),<BR>> > > >
source char(1),<BR>> > > > fedirp char(2),<BR>> > > >
fename char(30),<BR>> > > > fetype char(4),<BR>> > > >
fedirs char(2),<BR>> > > > cfcc char(3),<BR>> > > >
fraddl char(11),<BR>> > > > toaddl char(11),<BR>> > > >
fraddr char(11),<BR>> > > > toaddr char(11),<BR>> > > >
friaddl char(1),<BR>> > > > toiaddl char(1),<BR>> > > >
friaddr char(1),<BR>> > > > toiaddr char(1),<BR>> > > >
zipl numeric(5),<BR>> > > > zipr numeric(5),<BR>> > > >
aianhhfpl numeric(5),<BR>> > > > aianhhfpr numeric(5),<BR>> >
> > aihhtlil char(1),<BR>> > > > aihhtlir char(1),<BR>>
> > > census1 char(1),<BR>> > > > census2 char(1),<BR>>
> > > statel numeric(2),<BR>> > > > stater
numeric(2),<BR>> > > > countyl numeric(3),<BR>> > > >
countyr numeric(3),<BR>> > > > cousubl numeric(5),<BR>> > >
> cousubr numeric(5),<BR>> > > > submcdl numeric(5),<BR>> >
> > submcdr numeric(5),<BR>> > > > placel numeric(5),<BR>>
> > > placer numeric(5),<BR>> > > > tractl
numeric(6),<BR>> > > > tractr numeric(6),<BR>> > > >
blockl numeric(4),<BR>> > > > blockr numeric(4),<BR>> > >
> CONSTRAINT masuf_pk PRIMARY KEY (ogc_fid),<BR>> > > >
CONSTRAINT enforce_dims_wkb_geometry CHECK (ndims(wkb_geometry) = 2),<BR>>
> > > CONSTRAINT enforce_geotype_wkb_geometry CHECK<BR>> > >
> (geometrytype(wkb_geometry) = 'LINESTRING'::text OR wkb_geometry IS<BR>>
> > > NULL),<BR>> > > > CONSTRAINT
enforce_srid_wkb_geometry CHECK (srid(wkb_geometry) = 4269)<BR>> > >
> )<BR>> > > >
_______________________________________________<BR>> > > >
postgis-users mailing list<BR>> > > >
postgis-users@postgis.refractions.net<BR>> > > > <A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>>
> > ><BR>> > > ><BR>> > > >
_______________________________________________<BR>> > > >
postgis-users mailing list<BR>> > > >
postgis-users@postgis.refractions.net<BR>> > > > <A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>>
> > ><BR>> > >
_______________________________________________<BR>> > > postgis-users
mailing list<BR>> > > postgis-users@postgis.refractions.net<BR>>
> > <A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>>
> ><BR>> > ><BR>> > >
_______________________________________________<BR>> > > postgis-users
mailing list<BR>> > > postgis-users@postgis.refractions.net<BR>>
> > <A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>>
> ><BR>> > _______________________________________________<BR>>
> postgis-users mailing list<BR>> >
postgis-users@postgis.refractions.net<BR>> > <A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>>
><BR>> ><BR>> >
_______________________________________________<BR>> > postgis-users
mailing list<BR>> > postgis-users@postgis.refractions.net<BR>> > <A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>>
><BR>> _______________________________________________<BR>>
postgis-users mailing list<BR>> postgis-users@postgis.refractions.net<BR>>
<A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>><BR>><BR>>
_______________________________________________<BR>> postgis-users mailing
list<BR>> postgis-users@postgis.refractions.net<BR>> <A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>><BR>><BR>><BR>><BR>><BR>>
-----------------------------------------<BR>> The substance of this message,
including any attachments, may be<BR>> confidential, legally privileged
and/or exempt from disclosure<BR>> pursuant to Massachusetts law. It is
intended<BR>> solely for the addressee. If you received this in error,
please<BR>> contact the sender and delete the material from any
computer.<BR>><BR>>
_______________________________________________<BR>> postgis-users mailing
list<BR>> postgis-users@postgis.refractions.net<BR>> <A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>><BR>><BR>><BR></FONT></P></DIV>
</BODY>
</HTML>