[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