[postgis-users] latitude / longitude from wkb_geometry

Pedro Doria Meunier pdoria at netmadeira.com
Mon Feb 12 11:32:44 PST 2007


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





More information about the postgis-users mailing list