[postgis-users] latitude / longitude from wkb_geometry

[Pedro Doria Meunier]

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