[postgis-users] latitude / longitude from wkb_geometry

Pedro Doria Meunier pdoria at netmadeira.com
Mon Feb 12 09:20:29 PST 2007 

Txs Emily!

Sharply done!

Pedro.

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Emily
Gouge
Sent: segunda-feira, 12 de Fevereiro de 2007 17:08
To: PostGIS Users Discussion
Subject: Re: [postgis-users] latitude / longitude from wkb_geometry

If you want to extract all the points in a linestring with a single query
you can try the 
generate_series function:

For Example:
SELECT astext( pointn(the_geom, generate_series(1, numpoints(the_geom))))
FROM (select geomfromtext('LINESTRING(0 0, 1 1, 2 2)') as the_geom) as foo;

Returns:
    astext
------------
  POINT(0 0)
  POINT(1 1)
  POINT(2 2)
(3 rows)


Pedro Doria Meunier 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