[postgis-users] latitude / longitude from wkb_geometry
Pedro Doria Meunier
pdoria at netmadeira.com
Mon Feb 12 09:53:30 PST 2007
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
More information about the postgis-users
mailing list