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